Tuesday, June 14, 2011

SQL Server Compact Private Deployment tweaks

As a follow up to my previous post about Private Deployment (the concept that you can simply include the SQL Server Compact DLL files with your application as content, so to speak), I will show you a couple of tweaks that the .NET Framework enables.

Forcing an existing application to use the private DLL files

If you have an existing application, that is compiled against the centrally deployed DLL files, for example assembly version 3.5.1.0 or 4.0.0.0, you can force the application to use the private assembly version files instead (3.5.1.50 and 4.0.0.1), via an application configuration file. Lets take ExportSqlCe40.exe as an example. This application is complied against assembly version 4.0.0.0, so it will not work unless SQL Server Compact 4.0 runtime is centrally installed.

image

To force this application to use Private Deployment only, create a .config file named ExportSqlCe40.exe.config, with the following contents:

image

<?xml version="1.0" encoding="utf-8" ?>
<
configuration>
<
runtime>
<
assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<
dependentAssembly>
<
assemblyIdentity name="System.Data.SqlServerCe" publicKeyToken="89845dcd8080cc91" />
<
bindingRedirect oldVersion="4.0.0.0" newVersion="4.0.0.1" />
</
dependentAssembly>
</
assemblyBinding>
</
runtime>
</
configuration>


If you run the application now, you will get this error:



image



Now copy all files from the C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private folder to the folder where the Exportsqlce40.exe file resides:



image



Now the application runs, and uses only the private DLL files.





Isolating the SQL Server Compact runtime files in a separate folder



Continuing the sample above, to be neater, it would be nice to have the SQL Server Compact DLL files in a subfolder below the .exe file location. This can be done by moving the files to a separate folder, for example named SqlCe4. Now I have moved all the SQL Server Compact files and folders to that folder:



image



Now modify the .config file as follows:



<?xml version="1.0" encoding="utf-8" ?>
<
configuration>
<
runtime>
<
assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<
probing privatePath="SqlCe4"/>
<
dependentAssembly>
<
assemblyIdentity name="System.Data.SqlServerCe" publicKeyToken="89845dcd8080cc91" />
<
bindingRedirect oldVersion="4.0.0.0" newVersion="4.0.0.1" />
</
dependentAssembly>
</
assemblyBinding>
</
runtime>
</
configuration>


Notice the Probing setting above, that has been added. Also notice that this comes before the bindingRedirect.



Hop you find this useful.

9 comments:

and-vari said...

What happens if microsoft publishes a hotfix for SqlServerCe? Let's assume that the new assembly version are 4.0.1.0 and 4.0.1.1. Do i have to deploy the newer version? Or is the GAC still picking version 4.0.0.1 from your application folder?

ErikEJ said...

It is explained here: http://msdn.microsoft.com/en-us/library/gg213826(v=sql.110).aspx

Arthur said...

I'm interested in the separate folder approach, but I couldn't make it work.
http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/ca71e6ef-39e5-4520-97e0-57c24654af25

Eagle3386 said...

Hi Erik,

it seems that I'm unable to adapt this tutorial to my project.
My current setup:
- .NET 4.5.1
- EF 1.0 (can't upgrade to EF6.. :()
- SQL CE 4.0
- Subfolder "SqlCe4" directly placed in the root output folder with this content:
System.Data.SqlServerCe.dll
System.Data.SqlServerCe.Entity.dll
{amd64}
{x86}

Both processor archicture-folders contain these files:
sqlceca40.dll
sqlcecompact40.dll
sqlceer40EN.dll
sqlceme40.dll
sqlceqp40.dll
sqlcese40.dll

Additionally, there's a folder called "Microsoft.VC90.CRT" in each of them and they contain three files:
Microsoft.VC90.CRT.manifest
msvcr90.dll
README_ENU.txt

I referenced "SqlCe4\System.Data.SqlServerCe.dll", set all files' properties to "Content" and "Copy always" and my App.config looks like this:


























Now, what happens is this:
1. I get an additional "System.Data.SqlServerCe.dll" file, placed in the root folder of my project's output folder.
2. As soon as I launch the application, I get this exception:
---
An exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll but was not handled in user code

Additional information: Unable to load the native components of SQL Server Compact corresponding to the ADO.NET provider of version 8876. Install the correct version of SQL Server Compact. Refer to KB article 974247 for more details.
---
3. As soon as I move the {amd64} and {x86} folders to the root output folder (without recompiling or any other action), the application runs just fine.

From my point of view, I followed your guide precisely, but it just won't work.
What am I doing wrong that I just don't see? :(

Best regards,
Martin

ErikEJ said...

Martin: I cannot see your config file, suggest you ask me in a forum, or share your project with me by email...

Eagle3386 said...

I'm starting to get angry about Blogger and its inability to provide proper code-tags, some sort of HTML's pre-Tag or at least a way to provide commentators with the ability to post code.
After all, this blog is about code..

Anyway, one last try:
{?xml version="1.0" encoding="UTF-8"?}
{configuration}
{runtime}
{assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"}
{probing privatePath="SqlCe4"/}
{dependentAssembly}
{assemblyIdentity name="System.Data.SqlServerCe" publicKeyToken="89845dcd8080cc91" /}
{bindingRedirect oldVersion="4.0.0.0" newVersion="4.0.0.1" /}
{/dependentAssembly}
{/assemblyBinding}
{/runtime}
{system.data}
{DbProviderFactories}
{remove invariant="System.Data.SqlServerCe.4.0" /}
{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.1, Culture=neutral, PublicKeyToken=89845dcd8080cc91" /}
{/DbProviderFactories}
{/system.data}
{connectionStrings}
{add name="MyEntities"
connectionString="metadata=res://*/MyModel.csdl|res://*/MyModel.ssdl|res://*/MyModel.msl;
provider=System.Data.SqlServerCe.4.0;
provider connection string="Data Source=X:\Path\To\Database.sdf""
providerName="System.Data.EntityClient" /}
{/connectionStrings}
{startup}
{supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.1" /}
{/startup}
{/configuration}

ErikEJ said...

Eagle: This is not a support forum, suggest you email me a repro project (I am not usre you need the binding redirect, for example), or post enough info to repro in a forum (MSDN or stack)

Alex Kuchta said...

Erik:

I wanted to thank you for this; it saved me on an application I am working.

I also wanted to let you know that the solution did not work when I placed the files in a consolidated folder for cleanup, despite the fact that I added the "probing privatePath" line. Once I removed this, the application fires perfectly; any thoughts on why?

ErikEJ said...

Alex: hard to tell without a repro app and config file to look at