Schema/Table creation
Schema/Table creation
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
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
Re: Schema/Table creation
To create a new PostgreSql database/schema, you could try performing the following steps:Ekki wrote:Now I want to create such schemas on demand
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 };
Code: Select all
context.CreateDatabase(false, true);
Ekki wrote:(including assignment of owner and access rights)
This could be done only via SQL query to the database, for example, using the ExecuteQuery/ExecuteCommand methods:Ekki wrote:Can this be done on C# side at all (i.e. w/o embedded SQL execution)?
Link1
Link2
If you'll encounter any issues with this or have any further questions, feel free to contact us.
Re: Schema/Table creation
Thank you very much. Sounds promising!
Re: Schema/Table creation
Hi,
did as you wrote (at least I think so)
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
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);
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. ---> 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).
Re: Schema/Table creation
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.Ekki wrote:however I get the error below (Connection must be opened)
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.
Re: Schema/Table creation
Hi,
finally back on this issue. Did upgrade to the very latest versions of PostgreSQL driver and LinqConnect.
Here's what I'm doing:
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
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);
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
Re: Schema/Table creation
Please send us a test project so that we can reproduce the "database \"ekki\" does not exist" error in our environment.