createdatabase in c#

createdatabase in c#

Postby hcro12 » Tue 03 Mar 2015 06:35

For some reason, the CreateDatabase call for a new MySql database does not work in my c# code, I've tried several options but either I get an 'Error on executing DbCommand.' with the internal error '{"Table 'test' already exists"}' (on a new database?) or a schema is created but has no tables in it while the context has several tables.

My code is rather simple:

ConfigDatabaseDataContext ctxt = new configDatabaseDataContext(connectionString);
ctxt.CreateDatabase(false, true);

The connectionstring is containing the new database name. This is the way it worked with Linq to SQL in case a MS SQL server was used (without the flags on the CreateDatabase call).


Can someone please give me a hint on how this should be done?
hcro12
 
Posts: 8
Joined: Tue 03 Mar 2015 06:11

Re: createdatabase in c#

Postby MariiaI » Tue 03 Mar 2015 09:39

In such scenarios it is necessary to create models with the unselected "Preserve schema name in storage" check box in the Model Settings.
Thus, to create a new database, please perform the following steps:
- create LinqConnect model from an existing database (or use Model-First approach) with the unselected 'Preserve schema name in storage" option;
- define a connection string for a new database;
- create a DataContext object:
Code: Select all
ConfigDatabaseDataContext ctxt = new configDatabaseDataContext(connectionString);
{ Log = Console.Out };

- use the CreateDatabase method to create a new database:
Code: Select all
ctxt.CreateDatabase(false, true);

JIC: use Log property to see the generated SQL query ( http://www.devart.com/linqconnect/docs/?Logging.html)

If you encounter any further issues with this or have any questions, please let us know.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: createdatabase in c#

Postby hcro12 » Tue 03 Mar 2015 09:56

Thanks for the quick reply, I was able to find the same solution and the database is
created but with errors. I get an error: "{"Identifier name 'FK_omse_networkelements_Omse_Networkelement_Lnk_Omse_NetworkElements' is too long"}"
when creating a table. I've used dbMonitor to see what's happening.
This is part of the output of the SQL statement going wrong:

CREATE TABLE omse_networkelements (
....
CONSTRAINT FK_omse_networkelements_Omse_Networkelement_Lnk_Omse_NetworkElements FOREIGN KEY (lnk_Omse_NetworkElements) REFERENCES omse_networkelements (NetworkElement_Id) ON DELETE NO ACTION,
CONSTRAINT FK_omse_networkelements_Omse_Networkelement_Type FOREIGN KEY (lnk_Omse_NetworkElement_Types) REFERENCES omse_networkelement_types (NetworkElement_Type_Id) ON DELETE NO ACTION
)


and indeed FK_omse_networkelements_Omse_Networkelement_Lnk_Omse_NetworkElements is too long.
I do not know why Linq creates this long name? The second FK is composed without the "_lnk ..". Shouldn't the FK be "FK_omse_networkelements_Omse_Networkelement" iso "FK_omse_networkelements_Omse_Networkelement_Lnk_Omse_NetworkElements"?

If I create this table via MySql workbench, I get:

KEY `Omse_NetworkElement_Omse_NetworkElement` (`lnk_Omse_NetworkElements`),
KEY `Omse_NetworkElement_Type_Omse_NetworkElement` (`lnk_Omse_NetworkElement_Types`), CONSTRAINT `Omse_NetworkElement_Omse_NetworkElement` FOREIGN KEY (`lnk_Omse_NetworkElements`) REFERENCES `omse_networkelements` (`NetworkElement_Id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `Omse_NetworkElement_Type_Omse_NetworkElement` FOREIGN KEY (`lnk_Omse_NetworkElement_Types`) REFERENCES `omse_networkelement_types` (`NetworkElement_Type_Id`) ON DELETE NO ACTION ON UPDATE NO ACTION
hcro12
 
Posts: 8
Joined: Tue 03 Mar 2015 06:11

Re: createdatabase in c#

Postby hcro12 » Tue 03 Mar 2015 11:42

I already added TruncateLongDefaultNames in de config file but no luck ...

Code: Select all
<Devart.Data.MySql.Entity>
    <CodeFirstOptions TruncateLongDefaultNames="true" />
  </Devart.Data.MySql.Entity>
hcro12
 
Posts: 8
Joined: Tue 03 Mar 2015 06:11

Re: createdatabase in c#

Postby MariiaI » Tue 03 Mar 2015 12:27

hcro12 wrote:I get an error: "{"Identifier name 'FK_omse_networkelements_Omse_Networkelement_Lnk_Omse_NetworkElements' is too long"}"

Please specify the version of LinqConnect you are working with and send us a small test project with the LinqConnect model, so that we are able to reproduce this error and find the solution for you in a shortest time.

hcro12 wrote: already added TruncateLongDefaultNames in de config file but no luck ...
This option is applicable for Entity Framework models, not LinqConnect.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: createdatabase in c#

Postby hcro12 » Tue 03 Mar 2015 13:01

I've installed Devart dotConnect for MySql Trial V8.3.352.0.
A small test project is sent to Devart support.

Regards,

Hugo
hcro12
 
Posts: 8
Joined: Tue 03 Mar 2015 06:11

Re: createdatabase in c#

Postby MariiaI » Wed 04 Mar 2015 12:34

Thank you for the test project. We have reproduced the issue. This behaviour is related to the MySQL constraints:
http://stackoverflow.com/questions/4464714/mysql-big-fk-identifier
We will investigate the possibility to implement changes, which could affect this behaviour, and inform you about the results as soon as any are available.

To avoid the error, you can try changing the names of the navigations properties in your model, for example, by double-clicking your association and setting Name for "End1" and " End2" to a shorter one ( e.g. "End1_name" and " End2_name").
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: createdatabase in c#

Postby MariiaI » Thu 26 Mar 2015 14:00

The bug related to long default names of identifiers (for example, indexes, primary keys, foreign keys) when using CreateDatabase() method is fixed.
New build of dotConnect for MySQL 8.3.379 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/mysql/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=2&t=31497.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to LinqConnect (LINQ to SQL support)