Always got exception for DeleteDatabase() or CreateDatabase()

[navirius]

I just downloaded new version of LinqConnect 4.2.272 trial, and make a Model from database first, then I make some modification for association and some modification for datatype of column field

I just use datacontext from model designer generated code, but it always fail, it says "Object reference not set to an instance of an object.", for deleting database and creating database

on my console program I just delete my database then recreate again

Code: Select all

namespace eCoMaxDBData3
{
    class Program
    {
        static void Main(string[] args)
        {
           eCoMaxDBDataContext context=new eCoMaxDBDataContext();
            context.Log = Console.Out;
  
            context.DeleteDatabase(true,false);
            context.CreateDatabase(true,false);
            Console.Write("Database Created");
            Console.ReadKey();
        }
    }
}

eCoMaxDBDataContext is class extend from DataContext that generated by data model desaigner

I used VS 2010
I attach simple console project that I used
http://www.mediafire.com/?vvrocfqn0d0ql5h

can you give me a hint why I got this message

[MariiaI]

We couldn't reproduce the "Object reference not set to an instance of an object." exception with your sample. We are able to delete and create database objects.
Please specify the following details:
- on what method exactly (DeleteDatabase or CreateDatabase) you are getting the exception;
- the stack trace of the exception.

Please try do not ignoring errors when using DeleteDatabase or CreateDatabase methods, re-write the code in the following way and tell us the results (whether you are getting any errors, what errors, etc.):

Code: Select all

context.DeleteDatabase(false,false);
context.CreateDatabase(false,false);

Looking forward to your reply.

[navirius]

I following your suggestion and got this error

Code: Select all

System.NullReferenceException was unhandled
  HResult=-2147467261
  Message=Object reference not set to an instance of an object.
  Source=Devart.Data.Linq
  StackTrace:
       at Devart.Data.Linq.Engine.SqlSchemaBuilder.c(MetaAssociation A_0)
       at Devart.Data.Linq.Engine.SqlSchemaBuilder.a(MetaDataMember A_0, List`1 A_1, List`1 A_2, List`1 A_3, List`1 A_4)
       at Devart.Data.Linq.Engine.SqlSchemaBuilder.a(MetaTable A_0, List`1 A_1, List`1& A_2, List`1& A_3, List`1& A_4)
       at Devart.Data.Linq.Engine.SqlSchemaBuilder.a(MetaModel A_0)
       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 eCoMaxDBData3.Program.Main(String[] args) in F:\My Document\Visual Studio 2010\Projects\eCoMaxDotNetSQLServer\eCoMaxDBData3\Program.cs:line 19
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       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()
  InnerException: 

I use SQL Server 2008 express R2 with Advance Service (x64), Visual Studio 2010
Windows 7 Enterprise x64
and my code stoped at CreateDatabase with same project and same model

[MariiaI]

Thank you for the additional information. We have reproduced this issue. We will investigate it and inform you about the results as soon as possible.

[navirius]

thanks for attention, waiting for your fix or workaround to this issue

[MariiaI]

The error occurs due to the fact that: there are two entity classes in your model - MasterHargaData and SupplierData - and there are three 1-to-1 associations between them. The SupplierData entity class doesn't have the generated navigation properties.

While the issue is in the progress, please use the following workaround:
- double-click on each of three associations and select the "Generate Related Property" check box for MasterHargaData class;
- save the changes and re-generate the code.

If the issue still persists after these changes, please check whether there are other associations with unselected "Generate Related Property" check box for one of the sides and change it.

Please tell us about the results.

[navirius]

I follow your suggestion and workaround, but because my model have a lot association, I delete all association, and its working, I can delete and create database without problem,

because of that, I must have association function that I create by my self, without association attribute from LinqConnect

If my understanding does not wrong, assosiaction in my model, included at sql table creation query, at database level
1. How to exclude this association from sql generation? I just want association at class data level, not database level, because,I dont want constraint or foreign key on database/table creation query, I use MySQL and SQLServer, one or another

2. How to change diagram connection or sql script generation for specific database provider? the original one is SQL Server like my diagram, I want to change it to MySQL

3. Can I create or delete at runtime for one specific table?

4. On database context class, have database attribute for database name, what different with connection string database name? ("database" on mysql or "Initial Catalog" for SQL Server), which one is most significant priority?

5. How I can define update behavior for associated table?, ex. I can define just for read associated table, if master table updated, associated table does not update (just like join select from several table), or if master table is updated, then associated table updated too

6. I recreated all my association, sample project attached, and I use MySQL data provider, instead SQL Server, with little edit on database context, I have 2 database context file, one for MySQL and another for SQL Server, I just exclude or include one of them, but for MySQL, and for MemberData class that have association for itself, parent child association, exception raised

Code: Select all

Devart.Data.Linq.LinqCommandExecutionException was unhandled
  HResult=-2146233088
  Message=Error on executing DbCommand.
  Source=Devart.Data.Linq
  StackTrace:
       at Devart.Data.Linq.LinqCommandExecutionException.CanThrowLinqCommandExecutionException(String message, Exception e)
       at Devart.Data.Linq.DataProvider.CommandExecuter.Execute(String sql)
       at Devart.Data.Linq.DataProvider.CommandExecuter.Execute(List`1 sqls)
       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 eCoMaxDBData3.Program.Main(String[] args) in F:\My Document\Visual Studio 2010\Projects\eCoMaxDotNetSQLServer\eCoMaxDBData3\Program.cs:line 19
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       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()
  InnerException: Devart.Data.MySql.MySqlException
       HResult=-2147467259
       Message=Can't create table '.\ecomax2\member.frm' (errno: 150)
       Source=Devart.Data.MySql
       ErrorCode=-2147467259
       Code=1005
       SqlState=HY000
       StackTrace:
            at Devart.Data.MySql.MySqlCommand.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords)
            at Devart.Common.DbCommandBase.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
            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)
       InnerException: 

if I execute my console program for SQL Server I have this exception

Code: Select all

Devart.Data.Linq.LinqCommandExecutionException was unhandled
  HResult=-2146233088
  Message=Error on executing DbCommand.
  Source=Devart.Data.Linq
  StackTrace:
       at Devart.Data.Linq.LinqCommandExecutionException.CanThrowLinqCommandExecutionException(String message, Exception e)
       at Devart.Data.Linq.DataProvider.CommandExecuter.Execute(String sql)
       at Devart.Data.Linq.DataProvider.CommandExecuter.Execute(List`1 sqls)
       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 eCoMaxDBData3.Program.Main(String[] args) in F:\My Document\Visual Studio 2010\Projects\eCoMaxDotNetSQLServer\eCoMaxDBData3\Program.cs:line 19
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       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()
  InnerException: System.Data.SqlClient.SqlException
       HResult=-2146232060
       Message=There are no primary or candidate keys in the referenced table 'member' that match the referencing column list in the foreign key 'FK_member_member_0'.
Could not create constraint. See previous errors.
       Source=.Net SqlClient Data Provider
       ErrorCode=-2146232060
       Class=16
       LineNumber=1
       Number=1776
       Procedure=""
       Server=127.0.0.1
       State=0
       StackTrace:
            at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
            at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
            at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
            at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
            at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout)
            at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
            at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
            at Devart.Data.Linq.DataProvider.CommandExecuter.Execute(String sql)
       InnerException: 

it says that no primary or candidate key, but on my model two field that I associated already entity key

and this my sample project, please use exclude or include project to switch between data sql server datacontext or mysql datacontext from SolutionExplorer

https://www.dropbox.com/s/bg21rztx8wc9t ... BData3.zip

[MariiaI]

The bug related to the "Object reference not set to an instance of an object" exception on CreateDatabase() method has been fixed.
The fix will be included in the next build of LinqConnect. We will inform you when it is available for download.

First of all, we recommend you to use an XML mapping approach for working with one DataContext and different databases. For more information please refer to this topic. Also, please pay attention to the Fluent mapping approach.

Regarding your questions:
1) Associations can't be excluded from the generated scripts. In case, you don't want to create the database with associations, create your model without them, then create your database and work with entities in the code via joins, or after creation of the database without associations add the necessary ones to the model and do not update your database structure after that.

2) You could change the database connection in the Database Explorer, by right-clicking on Database Connection and selecting Edit Connection Properties.
After changing the connection, select the "Regenerate Storage and Mapping" option for regenerating model parts, specific to the database server, in diagram shortcut menu. Or perform the "Update Database From Model" wizard with the "Regenerate Storage" check box selected.

3) It can't be done at run-time. You could generate the script for one specific table via the "Generate Database Script From Model" wizard in Entity Developer and then perform it via DataContext.ExecuteCommand() method.

4) [DatabaseAttribute(Name = "name")] defines the name of the database, which was specified in Entity Developer while creating model and specifying database connection.
It is equivalent to the "database" in MySQL and "initial catalog" in SQL Server connection string parameters. But if you create a DataContext instance specifying a connection string with another value of the "database" or "initial catalog" parameter, then the name in the connection string will be the priority.

5) Could you please describe the scenario you want to implement in more details, for example with any two entity classes in your sample:
- definitions of these two classes, their primary keys;
- association between them, what fields are involved;
- what updates should be made for main entity and what updates should be made for child entity, etc.

Also, please refer to http://www.devart.com/linqconnect/docs/ ... lated.html

6) These errors are not related to LinqConnect. They could be reproduced when performing generated scripts via any management tool for SQL Server or MySQL.
The reason - it is impossible to create one-to-many relationship in one table on two fields, which are the composite primary key (MemberId and DealerId), because the foreign key MemberId must refer to a unique field DealerId, and in this case DealerId is not unique separately.

[navirius]

thanks mariial, your answer is very clear, I think for my question number 5 is very clear too, refer to documentation link

[MariiaI]

New build of LinqConnect 4.2.292 is available for download now!
It can be downloaded from http://www.devart.com/linqconnect/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=31&t=27551.