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!

Wednesday, May 16, 2012

SQL Server Compact Toolbox 3.0–Visual Guide of new features

After more than 110.000 downloads, version 3.0 of my free, open source SQL Server Compact Toolbox extension for Visual Studio 2010 is now available for download. This blog post is a visual guide to the new features included in this release, many suggested by users of the tool via the CodePlex issue tracker

Extensive support for Sync Framework 2.1

Thanks to a fantastic effort from fellow MVP June Tabadero (blog | twitter), extensive support for Sync Framework 2.1 has been added to the Toolbox, including Provisioning, Deprovisioning, Code Generation, Local Database Cache Code Generation and Explorer tree integration. You can read a nice walkthrough of the features in June’s blog post here. Notice that you will need to install the Sync Framework 2.1 bits for any of these features to work, you can download from here. June also recently blogged about using SQL Server Compact 4.0 with Sync Framework here.

clip_image002

Generate desktop LINQ to SQL classes both for 3.5 and 4.0 databases

I  decided to add the option to generate LINQ to SQL desktop classes, as I recently discovered that you can actually use LINQ to SQL with SQL Server Compact 4.0 (though NOT supported in any way by Microsoft). Read the blog post before you start using LINQ to SQL with 4.0.

clip_image003

clip_image005

Migrate a SQL Server Compact database directly to SQL Server (LocalDB/Express)

As you may know, the Toolbox already has features that allow you to generate a script of a SQL Server Compact database, and run it against a SQL Server database. But this release includes a feature to simplify this process, by not only generating a script, but also immediately executing it against a SQL Server database connected via Server Explorer.

clip_image006

clip_image007

Script only data (with "correct" table ordering)

Due to the increasing number of Database context menu items, I have moved all the script options to a separate “Script Database” sub-menu:

clip_image009

I have also added “Script Database Data”, which scripts only the data (!), sorted correctly by using the QuickGraph topological sorting of a DataSet.

WP DataContext - option to include ConnectionStringBuilder class

I have added the option to also have a ConnectionStringBuilder class generated for Windows Phone, to help constructing valid Windows Phone connection strings, with the limited amount of advanced options available on Windows Phone.

Other improvements

Scripting API fixes:
Tables are now ordered by topological sort when scripting entire database.
Data scripting now uses DbDataReader (or speed and to avoid some OOM issues)
“date” and “datetime2” SQL Server values are converted to “datetime” by default.
SQL scripts with DGML no longer generated.
Server based DGML now includes schema
Duplicate Execution plans fixed.
Improved script execution

Monday, May 7, 2012

Visual Studio 11 beta - Tooling for SQL Server Compact

Visual Studio 11 beta includes SQL Server Compact 4.0 SP1 CTP1, as I blogged about here. In this post, I will describe in greater detail the tooling support included with Visual Studio 11 beta. Notice the Visual Studio 11 is in beta, and things can change before release.

image

Visual Studio 11 beta only supports SQL Server Compact 4.0 in Server Explorer and other tools, so no longer support for 3.5 SP2, for development with that, you can still reference the 3.5 DLL files, but will not get any built-in tooling support.

This also means, that the EDM Wizard now only supports 4.0, so no longer the confusing mix of 4.0 support for Web projects, and 3.5 support for other project types.

image

The Transact-SQL Editor in Premium and Ultimate, that previously supported SQL Server Compact 3.5 and 4.0 now only support SQL Server, sadly. So in order to analyse SQL Server Compact queries, you must have SQL Server 2008 R2 Management Studio Express  (which is a free product) installed.

image

The Server Explorer is “missing colours”, but otherwise looks familiar:

image

The Server Explorer dialogs for SQL Server Compact, that were previously available, look very much the same!

image

And you can still add a “Local Database”, which creates an empty SQL Server Compact 4.0 database in your project:

image

The SQL Server Compact Toolbox also works with Visual Studio 11 beta, notice that is also support connections to 3.5 database files!

image