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 
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 
I don't see why your SqlDataAdapters cannot use a SQL CE connection string for the datasets that pull from that product, while others use a SQL Server connection string for those datasets.

Now, if the same datasets pull from CE vs. Server depending on conditions, you'll have some design decisions to consider first. CE's limited data types may cause some issues with those in Server. However, if you can design the structures for the lowest common denominator (CE), you should be able to wrap your calls in a manager class that swaps the connection's string as needed.

If you're using SqlDataSources, you'll need to attach to their event handlers that fire before retrieving data in order to swap out the connection string as needed. The use of SqlDataSources throughout your site would require major surgery. However, I would create a new data source class of my own that extends SqlDataSource and provides a new property that lets me tell it which to use (or maybe even uses convention to determine what to use on its own).

Good luck!

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.