Schema/Table creation

Schema/Table creation

Postby 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
Ekki
 
Posts: 29
Joined: Sun 24 Nov 2013 18:50
Location: Ilmenau, Germany

Re: Schema/Table creation

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

Re: Schema/Table creation

Postby Ekki » Wed 20 May 2015 13:29

Thank you very much. Sounds promising!
Ekki
 
Posts: 29
Joined: Sun 24 Nov 2013 18:50
Location: Ilmenau, Germany

Re: Schema/Table creation

Postby 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. ---&amp;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).
Ekki
 
Posts: 29
Joined: Sun 24 Nov 2013 18:50
Location: Ilmenau, Germany

Re: Schema/Table creation

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

Re: Schema/Table creation

Postby 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
Ekki
 
Posts: 29
Joined: Sun 24 Nov 2013 18:50
Location: Ilmenau, Germany

Re: Schema/Table creation

Postby 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.
Shalex
Devart Team
 
Posts: 7780
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL