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:

 

[Picture of ArcaneCode.sdf]

 

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.

About these ads

15 Responses to “Create a SQL Server Compact Edition Database with C#”

  1. loc Says:

    can sent code to me

  2. Sourin Says:

    Hi Dude,

    This is a nice article. Keep it up :)

  3. paxx Says:

    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…

  4. fna Says:

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

  5. JER Says:

    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.

  6. Ravinder Says:

    Hi

    Great job done .it is realy appricated

  7. Dan Says:

    This helped me heaps with my school assignment! Thanks so much

  8. RK Says:

    Please update this article with exception handling.

  9. reza Says:

    hi every one!
    thanks
    very good and easy

  10. rahul Says:

    when i try to open the sdf file in sql server managament studio , it says password mismatch ???

  11. sasha Says:

    how to create database at custom location?

  12. amine Says:

    really , thx man ,

  13. Harsha Pal Says:

    Great article.. its very useful for my application. Thanks a lot!!!

  14. hide windows instantly Says:

    Hi it’s me, I am also visiting this website on a regular basis, this site is
    genuinely pleasant and the visitors are truly sharing good thoughts.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 106 other followers

%d bloggers like this: