Error Accesing .sdf 4.0 format - SQL Compact Edition 4

Topics: Data Access Application Block
Apr 7, 2012 at 11:38 PM

Hi,

I was able to compile with no problem the full library referencing sqldatace 4.0 instead of sqldatace 3.5 but when I use the lib and try to open a .sdf file version 4.0 it thrown an error of incompatibility, using a .sdf file 3.5 does not throw the error.

Am I missing something here?

thanks.

Apr 8, 2012 at 8:48 PM

I'm not able to recreate the issue.  Are the DbProviderFactories installed and configured?

After installing SQLCE 4 (and the VS 2010 tools) the DbProviderFactories are configured so that they now appear in the Enterprise Library Configuration tool (as a Database Provider).

In machine.config I have the following DbProviderFactories:

  <system.data>
    <DbProviderFactories>
      <add name="Microsoft SQL Server Compact Data Provider" invariant="System.Data.SqlServerCe.3.5" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.1.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
      <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
    </DbProviderFactories>
  </system.data>

If the version 4.0 DbProviderFactory is not configured in machine.config it can be added to your local app.config or web.config (if machine.config update is not an option).

In web.config the connection is configured like this:

  <dataConfiguration defaultDatabase="SQLCE4" />
  <connectionStrings>
    <add name="SQLCE4" connectionString="data source=|DataDirectory|\Database1.sdf"
      providerName="System.Data.SqlServerCe.4.0" />
  </connectionStrings>

If that doesn't help then please provide more information to reproduce the issue.

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com 

Apr 9, 2012 at 4:26 AM
Edited Apr 10, 2012 at 4:43 AM

 Thanks for your reply ...

This info appear in my app.config:

 

 and this one in my machine.config (both: for Framework x86 and x64 directory)

 

 

 

 

 

In fact, I can see the provider listed in the tool supplied with the library to create automatically the config files.

The only change compared with the original lib before compiling is the PublicKtyToken (after compilation remains as null)

Executing this simple code, create the connection but when I try to open the file (sqlce4.0 I can open it inside the IDE of visual studio 2010) it throws an error of incompatibility:

 

 

 

 

 

 

private void button1_Click(object sender, EventArgs e) { //SqlCeDatabase db = new SqlCeDatabase(@"Data Source=D:\sqlfiles\prueba.sdf;"); Database db = DatabaseFactory.CreateDatabase(); MessageBox.Show(db.ConnectionString); using (DbConnection con = db.CreateConnection()) { con.Open(); // Throws the error here

Error Message:

 

 Incompatible Database Version. If this was a compatible file, run repair. For other cases refer to documentation. [ Db version = 4000000,Requested version = 3505053,File name = \\?\D:\SQLFiles\prueba.sdf ]

 

 

 

 

 

 

public static DatabaseConnectionWrapper CreateConnection(SqlCeDatabase db, bool usePooledConnection)
        {
            string connectionString = db.ConnectionStringWithoutCredentials;
            DatabaseConnectionWrapper connection;
            lock (connections)
            {
                if (!connections.TryGetValue(connectionString, out connection))
                {
                    //
                    // We have to test this again in case another thread added a connection.
                    //
                    if (!connections.ContainsKey(connectionString))
                    {
                        DbConnection keepAliveConnection = new SqlCeConnection();
                        db.SetConnectionString(keepAliveConnection);
                        keepAliveConnection.Open(); // Here throws the error

 

http://i39.tinypic.com/2vbtohs.png

If I revert the format of the file to v3.5 the lib works as expected.

Maybe I did something wrong when I compiled the library, but to be honest the only thing I did ... change the reference from sqlce 3.5 to sqlce 4.0 and later do the compilation.

Any suggestion?

Thanks 

 

<add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" /> 

<configuration>
    <configSections>
        <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.414.0, Culture=neutral, PublicKeyToken=null" requirePermission="true"/>
    </configSections>
    <dataConfiguration defaultDatabase="ConnString2">
        <providerMappings>
            <add databaseType="Microsoft.Practices.EnterpriseLibrary.Data.SqlCe.SqlCeDatabase, Microsoft.Practices.EnterpriseLibrary.Data.SqlCe, Version=5.0.414.0, Culture=neutral, PublicKeyToken=null" name="System.Data.SqlServerCe.4.0"/>
            <add databaseType="Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.414.0, Culture=neutral, PublicKeyToken=null" name="System.Data.SqlClient" />
        </providerMappings>
    </dataConfiguration>
    <connectionStrings>
      <add name="ConnString1" connectionString="Data Source=MYSERVER;Initial Catalog=DB;User ID=a;Password=s" providerName="System.Data.SqlClient" />
      <add name="ConnString2" connectionString="Data Source=D:\sqlfiles\prueba.sdf;" providerName="System.Data.SqlServerCe.4.0"/>
    </connectionStrings>
<startup>
  <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
</startup>
</configuration>

Apr 9, 2012 at 4:54 AM

Another thing I notice is that:

Even I have compiled the the library with sqlce4.0 and keep the provider referenced in the app.config as sqlce4.0 I can open the file if and only if the f.sdf file is not v4.0

You can upload the library comiled using sqlce 4 but I like to see what is the error and mistake that I'm doing ...

Thanks.

Apr 9, 2012 at 5:44 AM
Edited Apr 9, 2012 at 6:04 AM

I see -- my example is actually using a GenericDatabase and you are trying to explicitly use a SqlCeDatabase.

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com 

Apr 9, 2012 at 6:17 AM

Well, Initially I tried using explicity SqlCeDatabase but as you can see here:

private void button1_Click(object sender, EventArgs e) { //SqlCeDatabase db = new SqlCeDatabase(@"Data Source=D:\sqlfiles\prueba.sdf;");

Database db = DatabaseFactory.CreateDatabase(); MessageBox.Show(db.ConnectionString); using (DbConnection con = db.CreateConnection()) { con.Open(); // Throws the error here

I'm just calling the default instance depending on what I like to use (from default string) ... compact edition or sql express

...

What do you thing is the problem here and how can I solve it?

thanks in advance.

Apr 9, 2012 at 6:28 AM
Edited Apr 9, 2012 at 6:28 AM

I've rebuilt EntLib against SQLCE4 and added the providerMapping and I can open the database fine as a SqlCeDatabase.  Are you sure at runtime that the correct version of Microsoft.Practices.EnterpriseLibrary.Data.SqlCe.dll is being loaded?  If it wasn't that would cause the error.  You can check via Debug->Windows->Modules.

If that doesn't help I would recommend uploading a project that can reproduce the issue (including your version of EntLib binaries).

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com 

Apr 10, 2012 at 4:47 AM

I add this and eerything is working fine .... but Why?

<runtime>
		<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
			<dependentAssembly>
				<assemblyIdentity name="System.Data.SqlServerCe" publicKeyToken="89845DCD8080CC91" culture="neutral"/>
				<bindingRedirect oldVersion="0.0.0.0-4.0.0.0" newVersion="4.0.0.0"/>
			</dependentAssembly>
		</assemblyBinding>
	</runtime>

Maybe because my app.config initially is referencing the library with null publickeytoken? or any other thing?

<configSections>
        <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.414.0, Culture=neutral, PublicKeyToken=null" requirePermission="true"/>
    </configSections>
    <dataConfiguration defaultDatabase="ConnString2">
        <providerMappings>
            <add databaseType="Microsoft.Practices.EnterpriseLibrary.Data.SqlCe.SqlCeDatabase, Microsoft.Practices.EnterpriseLibrary.Data.SqlCe, Version=5.0.414.0, Culture=neutral, PublicKeyToken=null" name="System.Data.SqlServerCe.4.0"/>
            <add databaseType="Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.414.0, Culture=neutral, PublicKeyToken=null" name="System.Data.SqlClient"/>
        </providerMappings>
    </dataConfiguration>

 

Any Ideas?

Thanks

Apr 10, 2012 at 5:30 PM

You've added a binding redirect to load the version 4 SqlCE.  The error you were getting was because the version 3.5 of SqlCE version was being used by Enterprise Library (which is why it was complaining about trying to open the version 4 database) .  I thought you said that you were using a custom build of Enterprise Library compiled against version 4 (which does works for me) and is a viable option.

The easiest way, though, is to use the out of the box Enterprise Library and add the configuration you have posted.  Here is my app.config used in conjunction with Enterprise Library from NuGet (5.0.505.0):

<?xml version="1.0"?>
<configuration>
  <configSections>
    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.505.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="true"/>
  </configSections>
  <dataConfiguration defaultDatabase="SQLCE4">
    <providerMappings>
      <add databaseType="Microsoft.Practices.EnterpriseLibrary.Data.SqlCe.SqlCeDatabase, Microsoft.Practices.EnterpriseLibrary.Data.SqlCe, Version=5.0.505.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
           name="System.Data.SqlServerCe.4.0"/>
    </providerMappings>
  </dataConfiguration>
  <connectionStrings>
    <add name="SQLCE4" connectionString="data source=C:\Database1.sdf" providerName="System.Data.SqlServerCe.4.0"/>
  </connectionStrings>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.Data.SqlServerCe" publicKeyToken="89845DCD8080CC91" culture="neutral"/>
        <bindingRedirect oldVersion="0.0.0.0-4.0.0.0" newVersion="4.0.0.0"/>
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
  </startup>
</configuration>

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com 

Apr 10, 2012 at 11:53 PM

Thanks for all your help ....

Your initial thoughts were right ... I compiled the Ent. Lib source coude against Sqlce 4.0 but I still needed to add the configuration redirect to get my application working ... I checked the path for th sql lib ... and all reference and they are all ok. ... I really don't get it ...

Apr 15, 2012 at 4:08 PM
randylevy wrote:

You've added a binding redirect to load the version 4 SqlCE.  The error you were getting was because the version 3.5 of SqlCE version was being used by Enterprise Library (which is why it was complaining about trying to open the version 4 database) .  I thought you said that you were using a custom build of Enterprise Library compiled against version 4 (which does works for me) and is a viable option.

The easiest way, though, is to use the out of the box Enterprise Library and add the configuration you have posted.  Here is my app.config used in conjunction with Enterprise Library from NuGet (5.0.505.0):

 

<?xml version="1.0"?>
<configuration>
  <configSections>
    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.505.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="true"/>
  </configSections>
  <dataConfiguration defaultDatabase="SQLCE4">
    <providerMappings>
      <add databaseType="Microsoft.Practices.EnterpriseLibrary.Data.SqlCe.SqlCeDatabase, Microsoft.Practices.EnterpriseLibrary.Data.SqlCe, Version=5.0.505.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
           name="System.Data.SqlServerCe.4.0"/>
    </providerMappings>
  </dataConfiguration>
  <connectionStrings>
    <add name="SQLCE4" connectionString="data source=C:\Database1.sdf" providerName="System.Data.SqlServerCe.4.0"/>
  </connectionStrings>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.Data.SqlServerCe" publicKeyToken="89845DCD8080CC91" culture="neutral"/>
        <bindingRedirect oldVersion="0.0.0.0-4.0.0.0" newVersion="4.0.0.0"/>
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>
  </startup>
</configuration>

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com 

 


Hi,

but you're using bindingRedirect too ... I thought that wasn't necessary to apply that kind of configuration once Enterprise Library were compiled against sqlce 4.0.

Apr 15, 2012 at 4:33 PM
Edited Apr 15, 2012 at 4:35 PM

> but you're using bindingRedirect too ... I thought that wasn't necessary to apply that kind of configuration once Enterprise Library were compiled against sqlce 4.0.

Yes, that is correct.  The configuration I posted was used in conjunction with Enterprise Library from NuGet (5.0.505.0) which is the out of the box version of Enterprise Library compiled against sqlce 3.5.

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com 

Apr 15, 2012 at 7:07 PM
randylevy wrote:

> but you're using bindingRedirect too ... I thought that wasn't necessary to apply that kind of configuration once Enterprise Library were compiled against sqlce 4.0.

Yes, that is correct.  The configuration I posted was used in conjunction with Enterprise Library from NuGet (5.0.505.0) which is the out of the box version of Enterprise Library compiled against sqlce 3.5.

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com 

 

Ok, My friend, then Why Do I still need to include bindingredirect even compiling against SQLCE 4.0?

Regards,

E/R.

Apr 15, 2012 at 8:37 PM
Edited Apr 16, 2012 at 12:12 AM

When I ran my custom build with SQLCE 4.0, I didn't need a binding redirect so I suspect that for some reason in your scenario SQLCE 3.5 is still being loaded.  If you are curious you can upload a project to reproduce the behavior (including the referenced binary libraries) and I can investigate further.

Thanks,

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com

Apr 16, 2012 at 2:14 AM
randylevy wrote:

When I ran my custom build with SQLCE 4.0, I didn't need a binding redirect so I suspect that for some reason in your scenario SQLCE 3.5 is still being loaded.  If you are curious you can upload a project to reproduce the behavior (including the referenced binary libraries) and I can investigate further.

Thanks,

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com


I really appreciate your help ... Do you know what? I finally got it ... but in this way: Adding the sql ce 4.0 compiling using the VB-IDE and later copy the libraries to the lib directory referenced by my program. To copy the libs I used the batch file (CopyAssemblies.bat) just as before. The only different thing that I didn't do was compile the libs using the batch file (BuildLibraryAndCopyAssemblies -> (BuildLibrary.bat)).

I made some changes to batch file "BuildLibrary.bat" like set the buildtype, the msbuilddir (to fw 4.0) and the environment path (%VS100COMNTOOLS%); everything is compiled as expected but using the System.Data.SqlServerCe (sqlce 4.0) in my system needs a BindingRedirect ... The same do not happen if I compile inside vidual studio 2010 and later copy the outcome libraries.

What do you think is wrong when the compiling batch file is used?

Cheers,

E/R.

Apr 17, 2012 at 2:34 AM
Edited Apr 30, 2012 at 8:47 PM

BuildLibrary.bat uses the EnterpriseLibrary.sln which targets .NET 3.5.  That is why you it was always binding to version 3.5.  I was using VS2010 with the EnterpiseLibrary.2010.sln to build with which targets .NET 4.0.  Mystery solved!

--
Randy Levy
Enterprise Library support engineer
entlib.support@live.com

Apr 17, 2012 at 2:39 AM

Yes ... you're Right I didn't notice that in the batch file ... I edited the batch and change to EnterpriseLibrary.2010.sln and everything is fine ... as you said mistery solved ... My mistake ...

Thanks ...