SqlCe 3.5 with DAAB

Topics: Data Access Application Block
May 21, 2007 at 5:45 PM
Since SqlCe 3.1 doesn't support some of the sql extension, such as "Top", so I downloaded 3.5, I added the following mapping using the config tool:

<add databaseType="Microsoft.Practices.EnterpriseLibrary.Data.SqlCe.SqlCeDatabase, Microsoft.Practices.EnterpriseLibrary.Data.SqlCe, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" name="System.Data.SqlServerCe.3.5" />

I can connect to the database and do all the usual stuff, but when I issue a select top N statement to some table, it generates the following error:

System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
at System.Data.SqlServerCe.SqlCeCommand.ExecuteReader(CommandBehavior behavior)

To make sure the select top N statement works, I created a sample project and used the SqlCeConnection, SqlCeCommand object instead of DAAB and issued "select top (10) from table1", this time it runs fine and returns the expected result.

Does that mean problem is with DAAB? Anyone run into similiar problem?

thanks,
WL
May 22, 2007 at 3:28 AM
Since you are running a version of SqlCe that probably wasn't tested at the time of the DAAB release, it could be that the DAAB does not support it.

However, the SqlCeDatabase Class has some methods particular for SqlCe that you may want to use for querying the database. In particular, I am thinking about the ExecuteResultSet Methods. Per the documentation:

"Sql Server CE provides a new type of data reader, the SqlCeResultSet, that provides new abilities and better performance over a standard reader. This method provides access to this reader."

Perhaps if you use one of those methods or other methods directly provided on the SqlCeDatabase Class you will have a better experience.

Regards,

Dave

_______________________________

David Hayden
Microsoft MVP C#
May 22, 2007 at 1:39 PM
Thank you for the suggestion, I went back and tried SqlCeResultSet, however it gives the same error. I'll poke around SqlCeDatabase more.
Aug 6, 2007 at 7:14 PM
Edited Aug 6, 2007 at 7:18 PM
Hi,

I am trying to do the same, with Visual Studio 2008 Beta 2. I used the config tool and added the section as described in the first post, I am getting this error:

You are trying to access an older version of a SQL Server Compact Edition database. If this is a SQL Server CE 1.0 or 2.0 database, run upgrade.exe. If this is a SQL Server Compact Edition 3.0 or later database, run Compact / Repair. [ Db version = 3505053,Requested version = 3004180,File name = D:\\Documents\\Visual Studio 2008\\Projects\\SqlCe3.5TestApp\\AcquisitionDB.sdf ]

I also tried to modify the source of the SqlCe App Block, I changed the reference to the SQL CE 3.5 DLL (instead of 3.1). However, I get this error whenever SQLCE block tries to open a connection, Must be because SQL 3.5 changes how it shares connection objects? Seems to be coming from the SqlCeConnectionPool.CreateConnection method when it returns a new SqlCeConnection, it does not have the connection string assigned and the state is closed. Any ideas?

"Cannot access a disposed object. Object name: 'SqlCeConnection'." " at System.Data.SqlServerCe.SqlCeConnection.RemoveWeakReference(Object value)\r\n at System.Data.SqlServerCe.SqlCeCommand.setConnection(SqlCeConnection value)\r\n at System.Data.SqlServerCe.SqlCeCommand.setDbConnection(DbConnection value)\r\n at System.Data.Common.DbCommand.set_Connection(DbConnection value)\r\n at Microsoft.Practices.EnterpriseLibrary.Data.Database.PrepareCommand(DbCommand command, DbConnection connection) in D:\\Documents\\Visual Studio 2008\\Projects\\SqlCe3.5TestApp\\Data\\Database.cs:line 1226\r\n at ...




Here is my app.Config file with trying to set the provider mapping:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</configSections>
<dataConfiguration>
<providerMappings>
<add databaseType="Microsoft.Practices.EnterpriseLibrary.Data.SqlCe.SqlCeDatabase, Microsoft.Practices.EnterpriseLibrary.Data.SqlCe, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
name="System.Data.SqlServerCe.3.5" />
</providerMappings>
</dataConfiguration>
</configuration>
Sep 11, 2007 at 4:46 PM
I have the same problem as above,any idea?