Schema/Table creation

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
Ekki
Posts: 34
Joined: Sun 24 Nov 2013 18:50
Location: Ilmenau, Germany
Contact:

Schema/Table creation

Post by Ekki » Tue 19 May 2015 12:30

Hi,

I'm running an infrastructure consisting of compatible schemas, i.e. each schema has an identical set of table definitions. I'm using dotConnect for PostgreSQL together with LINQ. So far everything works perfect.

Now I want to create such schemas on demand (including assignment of owner and access rights). What is Best Practice to do so? Can this be done on C# side at all (i.e. w/o embedded SQL execution)? what would you suggest?

Thx,

Ekki

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Schema/Table creation

Post by MariiaI » Wed 20 May 2015 10:54

Ekki wrote:Now I want to create such schemas on demand
To create a new PostgreSql database/schema, you could try performing the following steps:
1) create LinqConnect model from an existing database with unchecked 'Preserve schema name in storage" option;
2) define a connection string for a new database/schema, e.g.:

string connect = "User Id=postgres;Password=*;Host=localhost;Port=5432;Database=new_db_name;Persist Security Info=True;Initial Schema=public"; // to create new database

or

string connect = "User Id=postgres;Password=*;Host=localhost;Port=5432;Database=db_name;Persist Security Info=True;Initial Schema=new_schema_name"; // to create new schema in the existing database

3) create a DataContext object:

Code: Select all

TestContext.TestDataContext context = new TestContext.TestDataContext(connect) { Log = Console.Out };
4) use the CreateDatabase method to create a new database:

Code: Select all

  context.CreateDatabase(false, true);
Ekki wrote:(including assignment of owner and access rights)
Ekki wrote:Can this be done on C# side at all (i.e. w/o embedded SQL execution)?
This could be done only via SQL query to the database, for example, using the ExecuteQuery/ExecuteCommand methods:
Link1
Link2

If you'll encounter any issues with this or have any further questions, feel free to contact us.

Ekki
Posts: 34
Joined: Sun 24 Nov 2013 18:50
Location: Ilmenau, Germany
Contact:

Re: Schema/Table creation

Post by Ekki » Wed 20 May 2015 13:29

Thank you very much. Sounds promising!

Ekki
Posts: 34
Joined: Sun 24 Nov 2013 18:50
Location: Ilmenau, Germany
Contact:

Re: Schema/Table creation

Post by Ekki » Wed 27 May 2015 13:04

Hi,

did as you wrote (at least I think so)

Code: Select all

			var tConnection = string.Format("User Id={0};Password={1};Host={2};Database={3};Persist Security Info=True;Initial Schema={4}",
				this.user, this.password, this.host, this.database, catalog.Name);

			var tContext = new DataContext(tConnection) {
				Log = Console.Out
			};

			tContext.CreateDatabase(false, true);
however I get the error below (Connection must be opened). I tried an Open() call but then I get the error that the specified schema does not exist (which is correct). There's one thing of your instructions I could not follow: '...database with unchecked 'Preserve schema name in storage"' - as I already have a LINQ model I tried to find out how to set this either at run time or even in the generated code but couldn't find any place that could match to your instruction.

Looking fwd your support. Thx ia Ekki

Code: Select all

CREATE DATABASE "MetaDC"
-- Context: Devart.Data.PostgreSql.Linq.Provider.PgSqlDataProvider Mapping: AttributeMappingSource Build: 4.4.529.0

System.Transactions Critical: 0 : <TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Critical"><TraceIdentifier>http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/Unhandled</TraceIdentifier><Description>Unhandled exception</Description><AppDomain>DC2DBTest01.vshost.exe</AppDomain><Exception><ExceptionType>Devart.Data.Linq.LinqCommandExecutionException, Devart.Data.Linq, Version=4.4.529.0, Culture=neutral, PublicKeyToken=09af7300eec23701</ExceptionType><Message>Error on executing DbCommand.</Message><StackTrace>   at Devart.Data.Linq.LinqCommandExecutionException.CanThrowLinqCommandExecutionException(String message, Exception e)
   at Devart.Data.Linq.DataProvider.CommandExecuter.Execute(String sql)
   at Devart.Data.PostgreSql.Linq.Provider.PgSqlDataProvider.CreateSchema(Boolean ignoreErrors)
   at Devart.Data.Linq.DataProvider.CreateDatabase(Boolean ignoreErrors, Boolean createSchema)
   at Devart.Data.Linq.DataProvider.b(Boolean A_0, Boolean A_1)
   at Devart.Data.Linq.DataContext.CreateDatabase(Boolean ignoreErrors, Boolean createSchema)
   at IG.Graphics.Tools.DC2DB.PostgreSQLRepository.CreateCatalog(Catalog catalog) in c:\Users\eb\IG.DEV\IG.Graphics.Tools.MetaDC\IG.Graphics.Tools.DC2DB.PostgreSQL\PostgreSQLRepository.cs:line 217
   at Application.Main(String[] args) in c:\Users\eb\IG.DEV\IG.Graphics.Tools.MetaDC\Test01\CS\Application.cs:line 84
   at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()</StackTrace><ExceptionString>Devart.Data.Linq.LinqCommandExecutionException: Error on executing DbCommand. ---&gt; System.InvalidOperationException: Connection must be opened.
   at Devart.Common.Utils.CheckConnectionOpen(IDbConnection connection)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
   at Devart.Common.DbCommandBase.ExecuteNonQuery()
   at Devart.Data.Linq.DataProvider.CommandExecuter.Execute(String sql)
   --- End of inner exception stack trace ---
   at Devart.Data.Linq.LinqCommandExecutionException.CanThrowLinqCommandExecutionException(String message, Exception e)
   at Devart.Data.Linq.DataProvider.CommandExecuter.Execute(String sql)
   at Devart.Data.PostgreSql.Linq.Provider.PgSqlDataProvider.CreateSchema(Boolean ignoreErrors)
   at Devart.Data.Linq.DataProvider.CreateDatabase(Boolean ignoreErrors, Boolean createSchema)
   at Devart.Data.Linq.DataProvider.b(Boolean A_0, Boolean A_1)
   at Devart.Data.Linq.DataContext.CreateDatabase(Boolean ignoreErrors, Boolean createSchema)
   at IG.Graphics.Tools.DC2DB.PostgreSQLRepository.CreateCatalog(Catalog catalog) in c:\Users\eb\IG.DEV\IG.Graphics.Tools.MetaDC\IG.Graphics.Tools.DC2DB.PostgreSQL\PostgreSQLRepository.cs:line 217
   at Application.Main(String[] args) in c:\Users\eb\IG.DEV\IG.Graphics.Tools.MetaDC\Test01\CS\Application.cs:line 84
   at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()</ExceptionString><InnerException><ExceptionType>System.InvalidOperationException, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType><Message>Connection must be opened.</Message><StackTrace>   at Devart.Common.Utils.CheckConnectionOpen(IDbConnection connection)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
   at Devart.Common.DbCommandBase.ExecuteNonQuery()
   at Devart.Data.Linq.DataProvider.CommandExecuter.Execute(String sql)</StackTrace><ExceptionString>System.InvalidOperationException: Connection must be opened.
   at Devart.Common.Utils.CheckConnectionOpen(IDbConnection connection)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
   at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
   at Devart.Common.DbCommandBase.ExecuteNonQuery()
   at Devart.Data.Linq.DataProvider.CommandExecuter.Execute(String sql)</ExceptionString></InnerException></Exception></TraceRecord>
The program '[2020] DC2DBTest01.vshost.exe' has exited with code -1 (0xffffffff).

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Schema/Table creation

Post by MariiaI » Thu 28 May 2015 10:57

Ekki wrote:however I get the error below (Connection must be opened)
We couldn't reproduce this error in our environment. We are sending you a small test project to the e-mail address you provided in your forum profile, please test it and notify us about the results. If this doesn't help, please modify it so that the issue could be reproduced and send it back to us, or send us your sample project.

You can try removing the schema name from the generated code manually:
- open YourDataContext.lqml file in Notepad (or any text editor);
- remove all the names of the schema in it (e.g. you can use the automatic replacement and change the Table Name="SCHEMANAME. to the Table Name=");
- save changes.

Ekki
Posts: 34
Joined: Sun 24 Nov 2013 18:50
Location: Ilmenau, Germany
Contact:

Re: Schema/Table creation

Post by Ekki » Thu 10 Sep 2015 13:09

Hi,

finally back on this issue. Did upgrade to the very latest versions of PostgreSQL driver and LinqConnect.

Here's what I'm doing:

Code: Select all

		var tContent = new DataContext("Host=localhost;User Id=ekki;Password=ekki;Database=MetaDC;Initial Schema=Test;Persist Security Info=True");
		tContent.CreateDatabase(false, true);
and this is what happens:

PostgreSql.PqSqlException: {"database \"ekki\" does not exist"}

On Localhost a server is running with DB MetaDC and login role ekki, etc. There's however no schema Test as this is what I want to create.

Really wondering why it's mentioning a database ekki...

Can u hlp, please?

Ekki

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Schema/Table creation

Post by Shalex » Mon 14 Sep 2015 14:54

Please send us a test project so that we can reproduce the "database \"ekki\" does not exist" error in our environment.

Post Reply