Create a SQL Server Compact Edition Database with C#

Before I start on the coding route, I found one other component you might want to download for SSCE. It’s not absolutely needed, but makes life in VS much easier. It’s the SSCE Developer SDK. It has the Northwind sample database, CAB files for installing SSCE on mobile devices, and MSI for installing the help files, and more. Grab it at the URL below and install all the goodies.

http://www.microsoft.com/downloads/details.aspx?FamilyId=E9AA3F8D-363D-49F3-AE89-64E1D149E09B&displaylang=en or http://shrinkster.com/lho

OK, let’s have a little fun. First thing I did was create a test app using a Windows Forms application. Next thing you need to do is create a reference to the SSCE. Click on Project, Add Reference. According to all directions I’ve read, all you have to do is be on the .Net tab, scroll down to System.Data.SqlServerCe, click on it and click OK. Found it?

Nah, I couldn’t find it either. So here’s the part the instructions don’t tell you. Go click on the Browse tab. Now navigate to C:\Program Files\Microsoft SQL Server Compact Edition\v3.1. Now pick the System.Dadta.SqlServerCe.dll, click on it, then click on OK to pull in the reference. (That little nugget will save you a lot of hair pulling, and if you did find it then good for you, just click on it and keep going.)

Now let’s write some code. First, go to the head of the class, whoops I mean header of your class and let’s create a using reference.

using System.Data.SqlServerCe;

using System.IO;

While I was at it I also set a reference to the System.IO namespace since we’ll want to check for the databases existence. Now in a method somewhere, let’s setup a few variables. Then we’ll check to see if the database already exists. Since this is just a sample, I’m going to delete it. This would be a valid action if the database was just a temporary cache for reports. In your situation you might want to return an error, or just exit gracefully without causing any problems.

string connectionString;

string fileName = “ArcaneCode.sdf”;

string password = “arcanecode”;

if (File.Exists(fileName))

{

File.Delete(fileName);

}

connectionString = string.Format(

“DataSource=\”{0}\”; Password='{1}'”, fileName, password);

You can see the last thing I did was establish the connection string. Some very important things you need to note. First, the DataSource must be surrounded with double quotes. The password, on the other hand, must be surrounded with single quotes.

In this example I’m just giving the database an SDF name, and letting it drop in the current directory. You could hard code a full path, or put it in a variable.

OK, we’ve got everything setup. Turns out it’s quite easy to create a new database. First, we create a new instance of a SqlCeEngine, and initialize it by passing the connection string into the constructor. Then, it’s as simple as calling the CreateDatabase method of the engine.

SqlCeEngine en = new SqlCeEngine(connectionString);

en.CreateDatabase();

And that’s all there is to it. Looking in our bin debug folder we see the new database:

Of course an empty database doesn’t do us a lot of good, so in the next installment we’ll start creating some tables, and maybe even put a little data in them.

15 thoughts on “Create a SQL Server Compact Edition Database with C#”

can sent code to me

Hi Dude,

This is a nice article. Keep it up 🙂

i write so similar program to your program but i have couple of errors that i cannot solve- can you send me your code:? so i can check and find that where is my mistake…
if you want i can send my code to you also…

thx..great help..
but is there some way to specify which version of db to use..? whether 3.0 / 3.5..?

Great article! Very helpful.
The only thing I would add is that in my case I had to copy the following dlls into the bin\Debug directory:

sqlceme30.dll
sqlceqp30.dll
sqlcese30.dll
sqlceca30.dll
sqlcecompact30.dll
sqlceer30en.dll
sqlceoledb30.dll

I found these files at
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE

Actually, I’m not 100% sure ALL of these files are really necessary, but my code runs with them. At first I was getting a dll not found exception where the sqlceme30.dll file was not in the bin\Debug folder. After I copied that one in, I got an unknown dll error exception, so I figured I’d just copy all the rest too. May be a bit of overkill, but for now it works.

Also, I did all this BEFORE installing that nifty SDK mentioned at the top of the article, so I don’t know if installing that first would have prevented my exceptions.