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

19 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. Alphonse Says:

    I almost never leave a response, but i did some searching
    and wound up here Create a SQL Server Compact Edition Database with C# |
    Arcane Code. And I do have a few questions for you if it’s allright.
    Could it be only me or does it seem like a few of the responses look like
    coming from brain dead visitors? :-P And, if you are posting at additional online sites, I would like to
    follow everything new you have to post. Could you list of every one of your social sites like your Facebook page, twitter feed, or linkedin profile?

  15. Sprinkler Repair Castle Pines CO Says:

    I was suggested this blog by my cousin. I am not sure whether this post is written
    by him as no one else know such detailed about my trouble.
    You are wonderful! Thanks!

  16. car Says:

    Things You’ll Need: Service Repair Manual 2-3 Gallon Drain Pan 50-50 Antifreeze
    Coolant Pliers. When acid rain is-allowed to sit on
    a scratched surface, it will actually start to rust.
    The macroenvironment consists of larger societal forces that affect
    the microenvironment (the company, suppliers, market intermediaries, customer
    markets, competitors and publics) demographically, economically, naturally,
    technologically, politically and culturally.

  17. 七彩神仙鱼 Says:

    Its like you read my mind! You seem to know a lot about this, like you wrote the book in it
    or something. I think that you could do with some pics to drive the message home a little bit,
    but instead of that, this is great blog. An excellent read.
    I will certainly be back.

  18. http://www.youtube.com/watch?v=2w88_zlSAHo Says:

    I’m amazed, I must say. Rarely do I encounter a blog that’s both equally educative and engaging,
    and let me tell you, you have hit the nail on the head. The problem is
    something not enough men and women are speaking intelligently about.
    I’m very happy that I found this in my search for something regarding
    this.


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 93 other followers

%d bloggers like this: