Friday, May 25, 2012

Private deployment of SQL Server Compact 3.5 SP2

The information found in the official documentation is not very extensive, and this blog post hopes to extend on the information found there. I have already blogged about private deployment with SQL Server Compact 4.0, and have an overview post here. 

SQL Server Compact 3.5 SP2 requires the following software:

The OS must be Windows XP SP3 or higher:

For applications targeting .NET 3.5 SP1, no additional software is required.

For applications targeting .NET 4.0, either .NET Framework 3.5 SP1 or the VC++ 2005 SP1 redistributable (for x86 and/or x64) is required.

Make sure the 3.5 SP2 runtime is properly installed, on x64 machines you must install both the x86 and x64 runtimes.

Let us assume that the requirements above are fulfilled (notice that Windows 7 includes .NET 3.5 SP1). So what else is required – let’s make a Console app and find out! Our goal is to create an application, that runs without SQL Server Compact 3.5 SP2 already installed, on both x64 and x86 systems. Notice that the instructions below works, no matter if your application targets “x86” (the 32 bit .NET Framework on all platforms, “Any CPU” (either the 32 or 64 bit .NET Framework), or x64 (the 64 bit Framework exclusively).

In Visual Studio, create a new Console project:

image

Now we must include the unmanaged SQL Server Compact C++ runtime files, each set of files in their own folder, which are platform specific. So create 2 folders in the project, one named x86 for the 32 bit files, and one named AMD64 (not x64!) for the 64 bit files.
NOTE: This convention, based on the value of the PROCESSOR_ARCHITECTURE environment variable is a special SQL Server Compact feature.

image 

Now we must locate the required files. If you are using a 32 bit machine, only the 32 bit files are installed on your machine, and you must manually extract the 64 bit files to a folder as described here. I am using (like most these days) a x64 machine, and it has the  most recent files for both platforms already installed. Make sure that all files you include have the exact same file version, or you will fail. The 3.5 SP2 file version is 3.5.8080.0, you can view the file version in Windows Explorer.

The files in the “C:\Program Files” folder are all 64 bit files, and the files in the “C:\Program Files (x86)” folder are all 32 bit files (on x64 systems)

The files you need to add are:
sqlceca35.dll
sqlcecompact35.dll
sqlceer35EN.dll
sqlceme35.dll
sqlceoledb35.dll
sqlceqp35.dll
sqlcese35.dll

So, add the files from C:\Program Files\Microsoft SQL Server Compact Edition\v3.5 to the AMD64 project folder, using Add, Existing Item (make sure to change the filter to “All files”):

image

Make sure all files are included with Build Action = Content, and Copy to Output Directory = Copy Always:

image

Then add files from C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5 to the x86 project folder, in the same way:

image

Finally, add the ADO.NET provider (System.Data.SqlServerCe.dll) to the project root, add this file form the C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Private (!) folder. Also set this file as Content, Copy Always:

image

Now add a reference to the ADO.NET provider in the root project folder:

image

Make sure the Version (Assembly Version) is 3.5.1.50, that indicates that it is the correct file:

image

Now build the project, and look in the bin/debug folder, to make sure all files are copied with the project output. You can now test that private deployment works either by uninstalling the 3.5 SP2 runtimes or on a PC without the runtimes installed.

If you are using only ADO.NET “Classic” (no LINQ to SQL or Entity Framework), this is all you need for private deployment. If you initialize a LINQ to SQL DataContext with a SqlCeConnection object, as I describe here, no additional configuration is required.

If you depend on the DbProvider API (LINQ to SQL and Entity Framework does), you must add the following to your project’s app.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<system.data>
<DbProviderFactories>
<remove invariant="System.Data.SqlServerCe.3.5" />
<add
name="Microsoft SQL Server Compact Data Provider 3.5"
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.50, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
/>
</DbProviderFactories>
</system.data>
</configuration>

UPDATE Feb 2013: Entity Framework private deployment is ONLY supported with Entity Framework 1.0, so below will not work in VS 2010/VS 2012 (EF 4.0 and EF 5.0)


If you use Entity Framework, you must add the C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Private\System.Data.SqlServerCe.Entity.dll to your project root as content, and have a configuration like the following (as described by the SQL Compact Team here)

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<system.data>
<DbProviderFactories>
<remove invariant="System.Data.SqlServerCe.3.5" />
<add name="Microsoft SQL Server Compact Data Provider 3.5" 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.50, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
</DbProviderFactories>
</system.data>
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly xmlns="">
<assemblyIdentity name="System.Data.SqlServerCe" publicKeyToken="89845dcd8080cc91" culture="neutral" />
<bindingRedirect oldVersion="3.5.1.0-3.5.1.50" newVersion="3.5.1.50" />
</dependentAssembly>
</assemblyBinding>
</runtime>
</configuration>


Hope this was useful!

27 comments:

Jen's Kitchen said...

It is a great guide.Thank you! I have a question here: right now I am working on a windows application written in C++ (MFC), and i want to deploy SQL compact with it. So that every time used exchanged data with the embedded SQL compact, the data will be synchronized with the SQL database on a remote server. The main reason to do so is to speed up the data storage speed. Is there a way to deploy SQL compact with C++ project? All the guides I found so far use C# project.

ErikEJ said...

Hi Jen: I am not sure about C++, but I think you can simply include all the files with your app.

DocSnyder said...

Hello,

I've created a WPf project with SQL Server CE SP1, without the private deployment stuff you mentioned here. Everything was fine and I could start the application on my dev pc. Now I updated the CE framework to 3.5 SP2, and put all the files and references as you described here. But now I get this error message on my dev pc:

[A]System.Data.SqlServerCe.SqlCeConnection cannot be cast to [B]System.Data.SqlServerCe.SqlCeConnection. Type A originates from 'System.Data.SqlServerCe, Version=3.5.1.50, Culture=neutral, PublicKeyToken=89845dcd8080cc91' in the context 'Default' at location 'C:\Users\michael\Documents\Visual Studio 2010\Projects\ComicBookCollector\ComicBookCollector\bin\Debug\System.Data.SqlServerCe.dll'. Type B originates from 'System.Data.SqlServerCe, Version=3.5.1.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' in the context 'Default' at location 'C:\Windows\assembly\GAC_MSIL\System.Data.SqlServerCe\3.5.1.0__89845dcd8080cc91\System.Data.SqlServerCe.dll'.

And I cannot execute the program on another pc (just copied the whole bin folder) without SSCE installed, there I get an error with some signature properties like:

EventType = clr20r3
P1 = myapp.exe
P4 = system.data.sqlserverce.entity
P5 = system.windows.markup.xamlparse (because the main window constructor wants to create the datacontext)

I have created the database using your sql server compact extension in vs 2010.

Do you know whats wrong here? How can I prevent my application to use the old version of SQLServerCE dll in the GAC or how to remove it from / update in GAC?

Regards, Michael

ErikEJ said...

Doc: Looks like you project or a dependent dll/project references the older version

DocSnyder said...

Hi Erik,

I have solved the problem. It seems that in the config file the mapping to the newer version was wrong, I changed the attribute value 3.5.1.0-3.5.1.50 to just 3.5.1.50 and everything is fine.

Regards, Michael

葉祐辰 said...

Thank you Erik! This is useful.
I have followed your guide, built the project, and it went well. However, after I input the "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5" into the connectionString, the screen showed the following error warning message:

The 'Microsoft.SQLSERVER.CE.OLEDB.3.5' provider is not registered on the local machine.

Here is my source code(C#):

//System.Data.Common.DbProviderFactory dpf = System.Data.SqlServerCe.SqlCeProviderFactory.Instance;
//string strConn = "Data Source=CkmainInsurance.sdf;SSCE:Max Database Size=512";
System.Data.Common.DbProviderFactory dpf = System.Data.OleDb.OleDbFactory.Instance;
string strConn = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=CkmainInsurance.sdf;SSCE:Max Database Size=512";

using (IDbConnection conn = dpf.CreateConnection())
{
conn.ConnectionString = strConn;
conn.Open();
string strSQL = "select * from AllProducts";

IDbDataAdapter da = dpf.CreateDataAdapter();
da.SelectCommand = dpf.CreateCommand();
da.SelectCommand.CommandText = strSQL;
da.SelectCommand.Connection = conn;

DataSet ds = new DataSet();
da.Fill(ds);
this.dataGridView1.DataSource = ds.Tables[0];
}

ErikEJ said...

You cannot use the OLEDB provider with private deployment, only the ADO.NET provider, and using the OLEDB provider form .NET with SQL Compact is not supported.

Tarek Saied said...

thanks but i have this exception

System.Data.SqlServerCe.SqlCeException was unhandled
Message=Unable to load the native components of SQL Server Compact corresponding to the ADO.NET provider of version 8080. Install the correct version of SQL Server Compact. Refer to KB article 974247 for more details.
Source=""
HResult=-1
NativeError=-1
StackTrace:
at System.Data.SqlServerCe.NativeMethods.LoadNativeBinaries()
at System.Data.SqlServerCe.SqlCeConnection..ctor()
at System.Data.SqlServerCe.SqlCeProviderFactory.CreateConnection()
at System.Data.EntityClient.EntityConnection.GetStoreConnection(DbProviderFactory factory)
at System.Data.EntityClient.EntityConnection.ChangeConnectionString(String newConnectionString)
at System.Data.Objects.ObjectContext..ctor(String connectionString, String defaultContainerName)
at DAL.OimDBEntities..ctor()
at DAL.OimRepository..ctor()
at Microsoft.Rtc.Collaboration.Sample.SubscribePresenceView.UCMASampleSubscribePresenceView.Subscribe()
at Microsoft.Rtc.Collaboration.Sample.SubscribePresenceView.UCMASampleSubscribePresenceView.Run()
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException: System.DllNotFoundException
Message=Unable to load DLL 'sqlceme35.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)
Source=System.Data.SqlServerCe
TypeName=""
StackTrace:
at System.Data.SqlServerCe.NativeMethods.GetSqlCeVersionInfo(IntPtr& pwszVersion)
at System.Data.SqlServerCe.NativeMethods.LoadValidLibrary(String modulePath, Int32 moduleVersion)
at System.Data.SqlServerCe.NativeMethods.LoadNativeBinaries()
InnerException:


ErikEJ said...

Tarek: Please share more information, is all DLL files present and correct under the .exs folder? What type of app? Did you install/extarct both x86 and x64 MSIs?

Michael Baer said...

Huge help. Thanks for putting this out there.

nmichalodi said...

I am having problems in building a setup project for my application deploying privately SQL Server 3.5 SP2. I have followed your steps and if I copy the Release/Debug folder to another pc it works great! When I use either InstallShieldLE or WIX I get an System.Data.SqlServerCe error. Is there anything in app.config that I should take in mind?

ErikEJ said...

nmichael: What is the error? and is the correct version of the dll present in the project root and included as content?

nmichalodi said...

Thank you for your reply. I have build an .NET 3.5 application. All the dll versions are correct, I did the steps again exactly as you mention in your article. when I attempt to execute the installed application file I get the following error:

Περιγραφή:
Stopped working

Υπογραφή προβλήματος:
Όνομα συμβάντος προβλήματος: CLR20r3
Υπογραφή προβλήματος 01: beautysalonmanagement.exe
Υπογραφή προβλήματος 02: 1.0.0.0
Υπογραφή προβλήματος 03: 51a725ff
Υπογραφή προβλήματος 04: System.Data.SqlServerCe
Υπογραφή προβλήματος 05: 3.5.1.50
Υπογραφή προβλήματος 06: 4b743b2f
Υπογραφή προβλήματος 07: 15c
Υπογραφή προβλήματος 08: 12
Υπογραφή προβλήματος 09: System.Data.SqlServerCe.SqlCe
Έκδοση λειτουργικού συστήματος: 6.1.7601.2.1.0.256.48

If I copy the Debug/Release folder the application works perfectly.....

If you have any ideas I would be greatfull....

ErikEJ said...

nmichael: I am not good with greek, and the comments here is probably not the best forum for ClickOnce support. Suggest you start by implementing proper execption logging/handling, and maybe ask in the MSDN form.

nmichalodi said...

Have you successfully created a setup project with the private deployment of SQL Server Compact 3.5 SP2? I mean private deployment of SQL Server Compact 3.5 SP2 is compatible with setup project right?

nmichalodi said...

Have you successfully created a setup project with the private deployment of SQL Server Compact 3.5 SP2? I mean private deployment of SQL Server Compact 3.5 SP2 is compatible with setup project right?

ErikEJ said...

nmichael: Sure, it is compatible. It is just a bunch of files, make sure you do not includes SQL Server Compact as a dependency...

nmichalodi said...

Thank you for your help Erik! Everything is as it should be, your instruction steps in privately deploying SQL CE work like a charm! Indeed one can create a setup project without any hassle. My problem was with the Windows Program Files read/write permissions! thanks for your time again!

Maui said...

Hi, thanks for the great article! I still have an issue when launching the application on x86 systems. I get an DllNotFoundException for the sqlceme35.dll. I checked the application's directory, but all of the .dll files are copied correctly (to both, x86 and AMD64 folder).

Has anyone the same issue or an idea what the problem might be?

ErikEJ said...

Maui: wrong dll file in x86 folder, or using wrong Ado.net dll. You can email me your project, and I will have a look.

Maui said...

Hi thanks for the quick answer. I can't email the project but what about the wrong ado.net .dll file? I added the file from Program Files/Microsoft SQL Server Compact/v3.5/Private
Do I have to add the x86 version as well? Maybe I didn't get it right from the project

Elena Sgonova said...

Hi Eric! Your article is great and helped me a lot, thanks! But I got the same DllNotFoundException for the sqlceme35.dll as Maui. I running winXP on my virtual machine. Do you have any solution for this problem?
What do you mean by "using wrong Ado.net dll"? And where exactly in my project's output folder should be amd64 and x86 directories?
I also have tested my app on other machine running Win7 and without SQL Server CE installed. App worked only in release, but it worked!!

ErikEJ said...

Elena: What is the Platform target of your project?

Elena Sgonova said...

I tried AnyCPU and x86 as well, with the same result

ErikEJ said...

Elena: Probably best to email me a repro project, or start an MSDN thread - also see my blog post here: http://erikej.blogspot.dk/2013/10/sql-server-compact-4-desktop-app-with.html (Easily applicable to 3.5 as well)

Elena Sgonova said...

Thank you for your help by e-mail! My problem really was that I used EF 4 with SqlServerCE 3.5! After I took 4.0 version there was only one thing that I had to do - install on my WinXP virtual machine Visual C++ 2008 Runtime Libraries, on other machines all works perfectly!

ErikEJ said...

Elena: Glad you got it working, notice that the VC++ runtime is always installed with .NET 3.5 SP1