Create a Table in SQL Server Compact Edition with C#

Before we create a table in SSCE, you need to understand that SSCE only supports a subset of the data types provided in full SQL Server. The specific list is: bigint, integer, smallint, tinyint, bit, numeric (p, s), money, float, real, datetime, national character(n) (Synonym:nchar(n)), national character varying(n) (Synonym:nvarchar(n)), ntext, nchar, binary(n), varbinary(n), image, uniqueidentifier, ROWGUIDCOL , and IDENTITY [(s, i)].

The last two, strictly speaking are not data types but properties of the data column. SSCE was designed to be small and lightweight (hence the Compact name). To keep it small, many of the datatypes were eliminated.

A full list, with detailed explanations can be found at on the MSDN site: or

Now that you know what’s available, let’s talk about how to create a table. In keeping with it’s theme of compactness, SSCE doesn’t include classes for manipulating the structures inside a SSCE database directly. Instead, you have to do everything through SQL DDL (Data Definition Language) statements. You know, Create Table, Drop Table, etc.

The code below shows the fairly simple steps involved. First you need to open a connection to the database. You do this by creating a SqlCeConnection object and passing in the connection string. The connection string has the same format I described in my previous post on creating the database.

Next, we create a string to hold some SQL (sql), and create a SqlCeCommand object (cmd). Finally we call the ExecuteNonQuery method of the command object to kick off the SQL. Here’s the code, with some try / catch logic thrown in to trap for errors.


      SqlCeConnection cn = new SqlCeConnection(ConnectString());


      if (cn.State==ConnectionState.Closed)





      SqlCeCommand cmd;


      string sql = “create table CoolPeople (“

        + “LastName nvarchar (40) not null, “

        + “FirstName nvarchar (40), “

        + “URL nvarchar (256) )”;


      cmd = new SqlCeCommand(sql, cn);





        lblResults.Text = “Table Created.”;


      catch (SqlCeException sqlexception)


        MessageBox.Show(sqlexception.Message, “Oh Fudge.”,

          MessageBoxButtons.OK, MessageBoxIcon.Error);


      catch (Exception ex)


        MessageBox.Show(ex.Message, “Fooey.”, MessageBoxButtons.OK,








There’s a few things I’d like to point out. First, the connection. In this simple example, I opened and closed the connection in the same routine. For talking to large databases, especially when doing so from a web app, this methodology makes a lot of sense. You keep the connection open for a brief time and reduce the load on your server.

With SSCE however that need does not exist. The database is local with one connection, so you save nothing by the brief open / close connection, and instead slow yourself down. With SSCE I would recommend you open the connection when your application launches, and close it when it exists. Pass it to the classes that need it. You will gain a lot of speed doing so, and minimal cost in terms of memory.

The other thing to note, within the line that instantiates the new SqlCeConnection I have a method called ConnectString(). To make life easy for my demo I encapsulated the connection string in a method that returns a string. You could place your connection in a string, method, or whatever is convenient for you. I already documented the code to create a connection string in yesterday’s post on creating a database, so I won’t bore you with it again.

Would you like to see the database? Cool. Open up SQL Server Management Studio (I’m guessing you have SQL Express or SQL Server Developer Edition installed). When it opens, pick SQL Server Mobile as the database type. Under Database File, click the Browse for more… option and navigate to the sdf file you created, and click on OK. Enter in the password, and click OK again to open the database.

Now you can do many of the normal things you’d do with a SQL Server database. Click on the Tables in the tree and you should see it expand and show our “CoolPeople” table. You can drill down further to see all the fields.

OK, we’ve created our table, the next step will be to load it. But that’s for the next post!

About these ads

13 Responses to “Create a Table in SQL Server Compact Edition with C#”

  1. deepak Says:

    very good code juhi
    please keep in touch

  2. thohira Says:

    Simple yet effective help:)

  3. Marco Says:

    Nice job, good code.

  4. Hemant Says:

    hi , gr8 work .but i have one query is it possible to pass the table name through variable.

  5. ethiopia Says:

    Nice work…………..GOOD

  6. chandan sharma Says:

    hello sir good coding thanks

  7. raf Says:

    Thanks a lot

  8. Says:

    The other day, while I was at work, my sister stole my iphone and tested to see if
    it can survive a thirty foot drop, just so she can be a youtube sensation. My iPad is
    now broken and she has 83 views. I know this is entirely
    off topic but I had to share it with someone!

  9. Nashville SEO Says:

    This post is really a good one it assists new the web people, who are wishing for blogging.

  10. bed bug bites Says:

    Use of organic methodology is such, that bed bugs can’t get resistant to it.
    Instead of ineffectual generalised spraying, arm
    yourself with a bright flashlight and target their nests.

    For belongings that are not washable I either thermally treat them
    or throw them away.

  11. bed bugs in action Says:

    One such type of trap is the bed bug Nightwatch monitor trap.
    The Bed Bugs cannot get through the cover and remain trapped inside of the mattress.
    They will be able to determine where the problem is and how it can be eliminated.

  12. doom hunter Says:

    Hey there! I’ve been reading your web site for some time now and finally got the courage to go ahead and give you a shout
    out from Austin Tx! Just wanted to say keep up the excellent work!

Leave a Reply

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

You are commenting using your 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


Get every new post delivered to your Inbox.

Join 102 other followers

%d bloggers like this: