Devart Forums

Discussion forums for open issues and questions concerning database tools, data access components and developer tools from Devart
https://forums.devart.com/

Schema/Table creation

https://forums.devart.com/viewtopic.php?f=3&t=31797

Page 1 of 1

Schema/Table creation

Posted: Tue 19 May 2015 12:30
by Ekki
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

Re: Schema/Table creation

Posted: Wed 20 May 2015 10:54
by MariiaI
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.

Re: Schema/Table creation

Posted: Wed 20 May 2015 13:29
by Ekki
Thank you very much. Sounds promising!

Re: Schema/Table creation

Posted: Wed 27 May 2015 13:04
by Ekki
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).

Re: Schema/Table creation

Posted: Thu 28 May 2015 10:57
by MariiaI
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.

Re: Schema/Table creation

Posted: Thu 10 Sep 2015 13:09
by Ekki
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

Re: Schema/Table creation

Posted: Mon 14 Sep 2015 14:54
by Shalex
Please send us a test project so that we can reproduce the "database \"ekki\" does not exist" error in our environment.
All times are UTC
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/