How to create an empty database in PostgreSQL?

[AKRRKA]

I have a license for the LinqConnect Professional and dotConnect Universal Professional.
Help please use one of these components to create a database for PostgreSQL.
If in the case of using LinqConnect with context model, need not to created the table.
Just need to create an empty database, without any tables.
How can i do it with Devart components?
Thanks.

[MariiaI]

The possible solutions are:
1) If the LinqConnect model is empty:

Code: Select all

PostgreDataContext context = new PostgreDataContext(connectionString);
 context.CreateDatabase(false, true);

JIC: currently, the error occurs in such scenario ("There is no tables to process"), however new database is created successfully. We will consider the possibility to make the changes, so that the exception doesn't occur and inform you about the results as soon as possible. Thus, at the moment, it is necessary to handle this exception.

2) Using plain SQL via the ExecuteCommand method.
3) If it is dotConnect Universal, using plain SQL via the ExecuteNonQuery method.

If you have any further questions, feel free to contact us.

[AKRRKA]

1) No. Model no empty. I create context with dynamicly mapping set from resources. Like this:

Code: Select all

                
Stream contextStream = Assembly.GetExecutingAssembly().GetManifestResourceStream("ceDataSource.Model.ceDataContext" + ProviderPrefix + ".xml");
MappingSource mappingSource = XmlMappingSource.FromStream(contextStream);

var contextForCreateDatabase = new ceDataContext(ConnectionString, mappingSource);

contextForCreateDatabase.CreateDatabase(false, true);

My be if don`t use MappingSource, can get empty database with CreateDatabase()?
Like this:

Code: Select all

                
var contextForCreateDatabase = new ceDataContext(ConnectionString, null);

contextForCreateDatabase.CreateDatabase(false, true);

2)3) Please show a example command for send with ExecuteCommand or ExecuteNonQuery, and get create empty database. Information ip:10.5.54.123, user:postrges, pass:1, databasename:mydb, schema:public, port:5432. Need create new empty database with name "mydb".

Thanks.

[MariiaI]

1) No, this will not work.
2) Here is the example with LinqConnect:

Code: Select all

TestDataContext ctx = new TestDataContext("User Id=postgres;Password=1;Host=db;Port=5432;Database=test;Initial Schema=public");
// you can use here the default connection for your DataContext
 ctx.ExecuteCommand("CREATE DATABASE mydb...");

3) Here is the example with dotConnect Universal:

Code: Select all

UniConnection conn = new UniConnection("provider=PostgreSQL;host=db;port=5432;uid=postgres;pwd=1");
conn.Open();
UniCommand comm = new UniCommand("CREATE DATABASE mydb...", conn);
comm.ExecuteNonQuery();

JIC: the user must have enough privileges to create a new database.

Please tell us if this helps.

[AKRRKA]

2) Dont work:

Connection string =

"User Id=postgres;Password=masterkey;Host=10.5.117.117;Port=5432;Database=test;Initial Schema=public"

On command:

Code: Select all

var contextForCreateDatabase = new ceDataContext(ConnectionString);

Get Exception:

System.ArgumentException перехвачено
HResult=-2147024809
Message=Ключевое слово не поддерживается: "host".
Source=System.Data
StackTrace:
в System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey)
в System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules)
в System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
в System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
в System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
в System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key)
в System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
в Devart.Data.Linq.DataProvider.Initialize(Object connection)
в Devart.Data.Linq.DataProvider.a(IDataServices A_0, Object A_1)
в Devart.Data.Linq.DataContext.a(Object A_0, MappingSource A_1, Type A_2)
в Devart.Data.Linq.DataContext..ctor(String connectionString, MappingSource mapping)
в ceContext.ceDataContext..ctor(String connection) в d:\Repository\mvp\trunk\source\ceModules\ceDataSource.Model\ceDataContext.Designer.cs:строка 339
в ceDatabaseService.Model.DatabaseService.ComandCreateDatabase() в d:\Repository\mvp\trunk\source\ceModules\ceDatabaseService.Model\DatabaseService.cs:строка 527
InnerException:

3) This is work:

Code: Select all

var conn = new UniConnection("provider=PostgreSQL;host=10.5.117.117;port=5432;uid=postgres;pwd=masterkey");
conn.Open();
var comm = new UniCommand("CREATE DATABASE mytestdb;", conn);
comm.ExecuteNonQuery();

Thanks.

[MariiaI]

в System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
в System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)

You are getting this error because you are using connection string format for PostgreSQL with the ceDataContext object, which was created for SQL Server (i.e., according to the stack trace of the exception, the data provider is SqlClient). Please make sure, you are using the correct mapping in this scenario. According to your previous post, you are using different databases in your application, thus, you should create your DataContext with the necessary XML mapping file and necessary connection string:

Code: Select all

var contextForCreateDatabase = new ceDataContext(ConnectionString, mappingSourcePG);

If this doesn't help, please send us a small test project, so that we are able to find the solution for you.

[AKRRKA]

I already used UniConnection. And this solution is work.
But if you use mapping, when the context is created, unless the base will be empty?
Or it will be empty if not used CreateDatabase and use ExecuteCommand?
I can try to remake back, but now it is not actual.
Thanks.

[MariiaI]

But if you use mapping, when the context is created, unless the base will be empty?
Or it will be empty if not used CreateDatabase and use ExecuteCommand?

The newly created database will be empty after performing ExecuteCommand method via the DataContext, which based on your existing model.
I.e.:

Code: Select all

var contextForCreateDatabase = new ceDataContext(ConnectionString, mappingSourcePG); //connection string for the PostgreSQL database, which is already exists; this needed to create DataContext with the proper connection

contextForCreateDatabase.ExecuteCommand("CREATE DATABASE mydb..."); // this SQL is performed directly to the database via an existing DataContext object

The empty database "mydb" will be created.

[MariiaI]

1) If the LinqConnect model is empty:

Code: Select all

PostgreDataContext context = new PostgreDataContext(connectionString);
 context.CreateDatabase(false, true);

JIC: currently, the error occurs in such scenario ("There is no tables to process"), however new database is created successfully. We will consider the possibility to make the changes, so that the exception doesn't occur and inform you about the results as soon as possible. Thus, at the moment, it is necessary to handle this exception.

New build of LinqConnect 4.4.529 is available!
It can be downloaded from http://www.devart.com/linqconnect/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=31&t=29870.