How to create an empty database in PostgreSQL?

How to create an empty database in PostgreSQL?

Postby AKRRKA » Mon 09 Jun 2014 07:32

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.
AKRRKA
 
Posts: 194
Joined: Thu 26 Jan 2012 15:07
Location: Russia

Re: How to create an empty database in PostgreSQL?

Postby MariiaI » Wed 11 Jun 2014 13:32

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.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: How to create an empty database in PostgreSQL?

Postby AKRRKA » Mon 16 Jun 2014 08:21

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.
AKRRKA
 
Posts: 194
Joined: Thu 26 Jan 2012 15:07
Location: Russia

Re: How to create an empty database in PostgreSQL?

Postby MariiaI » Mon 16 Jun 2014 13:21

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.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: How to create an empty database in PostgreSQL?

Postby AKRRKA » Tue 17 Jun 2014 15:17

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.
AKRRKA
 
Posts: 194
Joined: Thu 26 Jan 2012 15:07
Location: Russia

Re: How to create an empty database in PostgreSQL?

Postby MariiaI » Wed 18 Jun 2014 08:10

в 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.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: How to create an empty database in PostgreSQL?

Postby AKRRKA » Wed 18 Jun 2014 10:28

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.
AKRRKA
 
Posts: 194
Joined: Thu 26 Jan 2012 15:07
Location: Russia

Re: How to create an empty database in PostgreSQL?

Postby MariiaI » Wed 18 Jun 2014 13:03

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
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: How to create an empty database in PostgreSQL?

Postby MariiaI » Thu 26 Jun 2014 12:23

MariiaI wrote: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.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to LinqConnect (LINQ to SQL support)