Hi guys,

When developing my application, I need to switch between SQL CE mode and SQL Server 2005 mode. My LLBLGen project was originally generated for SQL CE. I saw there is a method in HelperClasses.DBUtils.cs named CreateConnection and made a little change in it. Here are changes:

public static DbConnection CreateConnection(string connectionString)
{
      if (!NetWork)
      {
          return new SqlCeConnection(connectionString);
      }
      else
      {
          return new SqlConnection(connectionString);
      }
}

I also passed the proper connection string for this method. But when the application start, I got an exeption

System.InvalidCastException: Unable to cast object of type 'System.Data.SqlClient.SqlConnection' to type 'System.Data.SqlServerCe.SqlCeConnection'.
   at System.Data.SqlServerCe.SqlCeCommand.set_DbConnection(DbConnection value)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.set_Connection(IDbConnection value)
   at SD.LLBLGen.Pro.ORMSupportClasses.Query..ctor(IDbConnection connectionToUse, IDbCommand commandToUse)
   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery..ctor(IDbConnection connectionToUse, IDbCommand commandToUse)
   at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified)
   at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause)
   at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.CreateQueryFromElements(ITransaction transactionToUse, IEntityFields fields, IPredicate filter, IRelationCollection relations, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize)
   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformGetMultiAsDataTableAction(IEntityFields fieldsToReturn, DataTable tableToFill, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicate selectFilter, IRelationCollection relations, Boolean allowDuplicates, IGroupByCollection groupByClause, ITransaction transactionToUse, Int32 pageNumber, Int32 pageSize)
   at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PerformGetMultiAsDataTableAction(Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPredicate selectFilter, IRelationCollection relations, Int32 pageNumber, Int32 pageSize)

I used the following code in CheckExistingData() method

dsCheckUser.Tables.Add(UserCollection.GetMultiAsDataTable(null, 0, null));

.

Any idea about this?

Thanks in advance.

You've referenced the SQL CE DQE in your application, so this happens inside the DQE for CE which is currently loaded in your application, because it's referenced.

The creation of the DQE is done in the DAO classes, namely in the constructors.

You've to use a different template for DAO classes. Instead of simply calling new DynamicQueryEngine(), you've to check whether the CE or the normal DQE has to be loaded. Say you reference normally the CE dqe. You then in your adjusted template for DAO classes, you call a routine in the CTor call which checks if it can simply do new DynamicQueryEngine() or that it has to load the normal DQE manually and create an instance of DynamicQueryEngine() from that assembly.

It's a bit of a pain. The desktop CE database is a CE Mobile clone, which means that the code for sqlserver 2005 can't be used on it, as it has the characteristics of CE mobile,including its client. This is also why there's a separate DQE for CE, because the SQL and the client assembly is different.

Thank you, Otis I have the same problem when switching between SQL CE and SQL 2005. I still do not know how to modify DAO Class template so that it can work with both SQL CE and SQL 2005. Could you give me an example about that?

Thank in advance,

Chunh

Thanks for clarifying, Otis. But I still don't understand. I see that both generated project used the same dll: SD.LLBLGen.Pro.ORMSupportClasses.NET20. I don't see other dll was referenced. So where are the different DQEs mentioned above?

chunh wrote:

I still do not know how to modify DAO Class template so that it can work with both SQL CE and SQL 2005. Could you give me an example about that?

Each dao class in the generated selfServicing code, instantiate the inherited **_dqeToUse** property by calling "new DynamicQueryEngine()" which is a class defined in the used DQE library.

So at runtime according to your need you may want to load another DQE dll and call "new DynamicQueryEngine()" for it.

IMHO, switching between different database types / DQE is better done and managed using the Adapter model.

Where you can generate the code twice, once for each database type, and use any of the DBGeneric projects (should be similar). And at runtime you can use the DataAccessAdapter from either of the generated DBSpecific projects (say...CEDBSpecific or normalDBSpecific) as you need.

Arthur wrote:

I see that both generated project used the same dll: SD.LLBLGen.Pro.ORMSupportClasses.NET20. I don't see other dll was referenced. So where are the different DQEs mentioned above?

I think you are using the Adapter model. The DQE should be referenced in the DBSpecifc project.

Walaa wrote:

I think you are using the Adapter model. The DQE should be referenced in the DBSpecifc project.

No, I'm using SelfServicing model.

So the DQE dll should be referenced in the generated code, otherwise it won't compile.

The dll WAS referenced in the code automatically.

The point is, I did generated different projects for SQL CE and SQL 2005. I tried to compare the differences in codes of those projects to point out where I can change the connection but didn't found any. It's easy to use "switch symbol" (like CE and !CE), but our customer wants to get only one exe file which could work well in both database type. Moving to Adapter model is impossible now, because it's a time consuming work.

Could you please point me to the code which uses DQEs for different connection? Thank you!

If you're using selfservicing in a desktop app, it can work. If it's a webapp, forget it as it will share the same connection code for all requests (as it's selfservicing).

The generated code project references a DQE dll, THAT dll is either the CE version or the normal version. However, as I explained above, there's a compatibility issue. This means that you've to load the DQE manually as I explained above.

Is it possible to install SQLServer express locally? Then you don't have a problem.

I've explained above what to do. What did you try yourself ? Remember, it comes down to reference 2 different database TYPES in your application. It doesn't matter that both are called 'sqlserver' as they're completely different: different SQL, different client, etc.