Using LINQ to SQL with SQL Server Compact Edition

30. January 2009
Comments (1)

I'm currently building a desktop application that needs to store a bunch of data on the users computer, so I've decided to use SQL Server Compact 3.5 for this. There's only one problem, Visual Studio 2008 doesn't support using LINQ to SQL with SQL Server Compact. However, the .NET Framework does and there's a work around to get it working in VS'08 that I'll describe below.

In case you aren't aware of what SQL Server Compact 3.5 is, here's the short description that Microsoft gives on their website: "Microsoft SQL Server Compact is a free SQL Server embedded database ideal for building standalone and occasionally connected applications for mobile devices, desktops, and Web clients." You can download it and view more information here: http://www.microsoft.com/Sqlserver/2008/en/us/compact.aspx

Steps to create a LINQ to SQL application using SQL Server Compact

To make this easy to follow I'm going to start with Creating a Solution in Visual Studio and go through all the steps needed to get it running off of SQL Server Compact.

Also, you'll first want to make sure you have SQL Server Compact 3.5 installed. It may have been installed when Visual Studio 2008 installed; I'm not sure which editions install SQL Server Compact 3.5, when I installed Visual Studio 2008 Team Suite it did install SQL Server Compact 3.5 too. If you need to install it, you can find the installer here: http://www.microsoft.com/Sqlserver/2005/en/us/compact-downloads.aspx

Step 1: Create your new project within Visual Studio

It doesn't matter if it's a Windows Forms or WPF applciation. For this example, I'll be creating a WPF application.

Step 2: Create your SQL Server Compact database. For Example: MyDatabase.sdf

You can do this by right-clicking on your Solution within the Solution Explorer and selecting "Add - New Item..." then select the "Data" category on the left and "Local Database" on the right.

Step 3: Add some Tables to the Database

You can do this within Visual Studio 2008 using the "Server Explorer". The easiest way to open up the Database within the Server Explorer is to just double click it withinthe Solution Explorer.

Also, in this example I'm just going to create a "Person" table with ID, FirstName and LastName columns.

Step 4: Create a .dbml descriptor file for the Database

To do this you need to use the SqlMetal.exe tool. Just type the following into the Visual Studio 2008 Command Prompt:

SqlMetal.exe MyDatabase.sdf /dbml:MyDatabase.dbml

By default, the SqlMetal.exe is located at drive:\Progream Files\Microsoft SDKs\Windows\vn.nn\bin.

Step 5: Add the Existing .dbml file to your project

Once you add the .dbml file, Visual Studio 2008 will automatically generate the .NET code (C# or VB.NET) for the System.Data.Linq.DataContext class that you'll use to access your data.

 

Step 6: Start Coding away using LINQ to SQL!

You can now code using LINQ and your new System.Data.Linq.DataContext class against your database.

A GUI UI for SqlMetal.exe

The SQL Metal Open Source User Interface Project over on CodePlex has been built just for the purpose of making it easier to generate mapping files for SQL Server Compact databases.

You can download it here: http://www.codeplex.com/sqlmetalosui

Related Links

Here are some related links on this topic:

LINQ with SQL Server Compact (a.k.a. DLINQ with SQL CE)

LINQ to SQL...compact!

Using LINQ to Access SQL Server Compact Directly - A follow up

database , , ,

SQL Compact And The "Unable to load DLL 'sqlceme35.dll'" Runtime Exception

7. January 2009
Comments (2)

I installed SQL Server Compact 3.5 x86 and started testing it out when I got a nice "Unable to load DLL 'sqlceme35.dll" runtime exception. This seems like odd behaviour for a clean install. In a fairly quick search I found an article on the SQL Server Compact Team Blog that covers a couple possible causes/solutions for this exception.

http://blogs.msdn.com/sqlservercompact/archive/2007/10/26/can-t-find-p-invoke-dll-sqlcemenn-dll.aspx

My Solution - Target x86 Only

Even though there are 5 possible causes/solutions listed, only one of them was the issue I was experiencing and I think is probably the most common issue.

Quoted from the above post:

If your machine is a 64-bit box, you might have been got trapped into default target platform 'Any CPU' trap, please set the target platform as 'x86'.  Need more details, please visit ErikEJ's blog post.

To fix it all you need to do is open up the Build Configuration Manager within Visual Studio and change the Active Solution Platform for your application from "Any CPU" to "x86". Then rebuild and it'll work just fine.

The reason for this issue according to ErikEJ's post is "SQL Compact is only supported in 32 bit WoW (Windows on Windows) mode on the x64 platform." From my understanding of how things work, when you compile for "Any CPU" your application will be run under the 64-bit .NET Framework on x64 Windows, and since SQL Compact is only 32-bit then Windows is unable to fire it up within the same process. This is because a single process in Windows can be either 32-bit or 64-bit and cannot run both 32-bit and 64-bit code within the same process. This is why it runs without throwing the exception once you change your app to target "x86" (32-bit).

What about 64-bit (x64) support?

Since the above SQL Server Compact Team Blog post was written, they have released a x64 version of SQL Server Compact 3.5. So x64 IS supported.

Now, if you distribute your application targeting "Any CPU" and bootstrap the SQL Compact x86 installer within your applications installer you will end up having issues if your users are running a 64-bit edition of Windows. So , to really support x64 Windows you really have two options:

  1. Change your application to target "x86" and distribute it that way. It'll run on both x86 and x64 editions of Windows.
  2. Alternatively, you could create two seperate versions of the installer for your application; one that targets x86 and one that targets x64.

I prefer the first option because it has a couple benefits. Even though it only targets x86, it will work on x64 systems too, and you relieve users from being required to understand whether they should install the x86 or x64 version. Plus this way you only need to worry about building your application and installer a single time for each release.

At the below link you can download both the x86 and x64 versions of SQL Server Compact 3.5 SP1:

http://www.microsoft.com/downloads/details.aspx?FamilyId=DC614AEE-7E1C-4881-9C32-3A6CE53384D9&displaylang=en

General, database ,