Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server

LINQ and Deploying SQL Server CE 3.5

Rate me:
Please Sign up or sign in to vote.
4.75/5 (18 votes)
28 May 2009CPOL4 min read 71K   34   21
How to deploy SQL Server CE 3.5 with an application that uses LINQ for data access.

Introduction

LINQ is an extremely useful new language feature for the .NET Framework 3.5. It allows querying all sorts of data structures via the actual programming language (thus the Language INtegrated in LINQ). In addition, the specific variant, LINQ to SQL, includes a full O/RM (Object/Relational Mapper). Since many developers spend a ton of time mapping their relational data to objects, this included tool is a phenomenal time-saver. Of course, this is not a new concept, nor is it the first of its kind for .NET. It's just free and included! LINQ to SQL currently only works with SQL Server 2005. It's not likely that Microsoft will bother with any other providers with the Entity Framework and LINQ to Entities on the horizon. There is a way to get standard LINQ to SQL to work with the SQL Server Compact Edition (SSCE), however. You have to use the SqlMetal command-line tool to generate the appropriate file(s). Once you generate the .dbml file, you can even open it in the Visual Studio 2008 designer.

Deploying SSCE when Using LINQ

Deploying SSCE with your desktop application is pretty easy. You can quickly publish your application via ClickOnce, but I'm not a big fan of that technology. Instead, I prefer an installer program. You can use any one that you prefer, and still easily deploy SSCE with a couple of options.

Windows Installer

First, you can choose to create a pre-requisite or dependency test for the system installation of SSCE for the version you require (3.5 or later to work with LINQ). If the engine is not installed, you can send the user to the Microsoft website for installation, or you can launch the redistributable Windows Installer file for it. This method requires that the user have Administrator privileges in order to perform the system install.

Please note that if you launch the .msi file to install SSCE and your installer is a Windows Installer program, you will have to close your installer first; two instances of Windows Installer cannot be run simultaneously.

X-Copy

Second, you can just copy the necessary redistributable library files for the SSCE engine into your application's install directory. This method does not require Administrator privileges! There are seven files that need to be copied with your application, and they should be copied to the same directory as your primary executable.

  • sqlceca35.dll
  • sqlcecompact35.dll
  • sqlceer35EN.dll
  • sqlceme35.dll
  • sqlceoledb35.dll
  • sqlceqp35.dll
  • sqlcese35.dll

Make sure your application references the System.Data.SqlServerCe.dll assembly and it is set to copy the assembly locally as well. This is especially important when you use LINQ to SQL with SSCE because that assembly is implied at runtime and therefore not explicitly required as a reference in your application until it's time to deploy on a system that does not have SSCE installed.

Please be aware that if you choose to redistribute SSCE in either form (Windows Installer or individual libraries), you must register for redistribution rights with Microsoft.

The Hidden Problem

There is a hidden problem with all of this that will finally crop up with the x-copy method. Since LINQ uses a Factory pattern to acquire the necessary data provider, the necessary reference to the provider does not occur until runtime. Herein lies the problem.

When your application makes that first attempt via LINQ to connect to the SSCE database file without a system install, you will receive the following error message:

Cannot open "X:Pathtodatafiledatafile.sdf". 
  Provider 'System.Data.SqlServerCe.3.5' not installed.

If SSCE is installed by the user via the Windows Installer file, this problem is handled for you. The installer adds an entry to your machine.config file. Since the whole purpose of an x-copy install is usually to avoid the need for Administrator privileges, this just will not do.

Instead, you will need to add an application configuration file to your desktop application, if you do not have one already. Right-click your project in "Solution Explorer", choose "Add new...", select "Application Configuration", and click OK. Make sure the new file looks like this:

XML
<?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"
        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.0.0,
          Culture=neutral,
          PublicKeyToken=89845dcd8080cc91"
      />
    </DbProviderFactories>
  </system.data>
</configuration>

This ensures that the appropriate database provider factory is added for your application, without the need to edit the end-user's machine.config file or requiring a system installation of SSCE.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
United States United States
I began programming on my Commodore 64 at around the age of 12. After migrating to DOS and then Windows, I decided to take on the Web. Several languages and platforms later, I have settled in with .NET nicely. I am currently the owner of a software consulting company and lead application developer for a learning-based technology consultation company.

The love of a finished application is usually at war with the desire to improve it as soon as it's released (they're never really finished).

Comments and Discussions

 
QuestionThanks Pin
ISanti21-Oct-16 1:52
ISanti21-Oct-16 1:52 
QuestionGrrrrr Pin
Breems27-Mar-14 14:46
Breems27-Mar-14 14:46 
AnswerRe: Grrrrr Pin
Matt Sollars7-Jul-14 18:21
Matt Sollars7-Jul-14 18:21 
GeneralMy vote of 5 Pin
eyedia5-Sep-12 8:54
eyedia5-Sep-12 8:54 
QuestionExcellent Solution! Thanks Pin
mkelowna14-Jul-12 12:18
mkelowna14-Jul-12 12:18 
GeneralRe: Excellent Solution! Thanks Pin
Matt Sollars15-Jul-12 6:16
Matt Sollars15-Jul-12 6:16 
GeneralThank you - solved difficult problem Pin
Member 172508519-May-11 7:55
Member 172508519-May-11 7:55 
QuestionAny idea if you need to do anything to target x64 Pin
kris.mackintosh20-Mar-11 21:59
kris.mackintosh20-Mar-11 21:59 
Hi there

First off, thanks for the article, its really usefull!

I was wondering if you knew if you had to do anything special when making an installer for an x64 machine
the Install.txt that comes with the sql ce download says you have to install both the x86 and the x64 versions if you are running an x64 system... which left me wondering what you have to do when deploying the binaries with your own application

Im guessing i just have to make 2 separate installers, one for x86 machines that deploys the x86 binaries and one for x64 machines that deploys the x64 binaries

-Kris
AnswerRe: Any idea if you need to do anything to target x64 Pin
Matt Sollars21-Mar-11 4:54
Matt Sollars21-Mar-11 4:54 
GeneralMy vote of 5 Pin
Tawani Anyangwe7-Mar-11 8:01
Tawani Anyangwe7-Mar-11 8:01 
NewsUpdate for SP1 and SP2 Pin
Michael900030-Jan-11 10:06
Michael900030-Jan-11 10:06 
GeneralExcellent article!! Pin
Santiago Santos Cortizo12-Aug-10 0:44
professionalSantiago Santos Cortizo12-Aug-10 0:44 
GeneralRe: Excellent article!! Pin
Matt Sollars12-Aug-10 3:23
Matt Sollars12-Aug-10 3:23 
GeneralMy vote of 4 Pin
VirtualVoid.NET30-Jul-10 23:54
VirtualVoid.NET30-Jul-10 23:54 
GeneralThanks! + version note Pin
trnilse19-Apr-10 2:56
trnilse19-Apr-10 2:56 
Generalthanks Pin
giddy_guitarist2-Jan-10 20:44
giddy_guitarist2-Jan-10 20:44 
GeneralThank you SO MUCH ! Pin
ElTchoupi6-Dec-09 7:28
ElTchoupi6-Dec-09 7:28 
GeneralRe: Thank you SO MUCH ! Pin
Matt Sollars6-Dec-09 8:54
Matt Sollars6-Dec-09 8:54 
Question[Message Deleted] Pin
Idoamrani30-Nov-09 23:33
Idoamrani30-Nov-09 23:33 
AnswerRe: Excellent article. That's exactly what I needed Pin
Matt Sollars1-Dec-09 4:18
Matt Sollars1-Dec-09 4:18 
GeneralRe: Excellent article. That's exactly what I needed Pin
Idoamrani1-Dec-09 23:15
Idoamrani1-Dec-09 23:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.