Creating a SQL Server Compact Edition Database Using Visual Studio Server Explorer

In the past I showed how to create a new SQL Server Compact Edition (SSCE) database using code. While it’s great for your app to be able to be able to create its own database(s), it’s not always necessary. For example, if you will be deploying the database with your app, it’s not really necessary to write the code to create it.

Instead, you can create the SSCE database right from within Visual Studio 2005. First, if Server Explorer is not visible select View, Server Explorer from the menu. Now right Click on Data Connections, and pick “Add Connection…”.

[Picture of SSCE and VS 1]

Note do NOT pick the Create New SQL Server Database… option, this works with full blown SQL Server such as SQL Server Express, not SSCE.

On the next screen, make sure the Data source is set to the “Microsoft SQL Server 2005 Compact Edition (.NET Framework Data Provider for SQL Server CE)”, if not click the Change button to launch a dialog where you can select it.

For Data Source make sure My Computer is picked, then enter the directory and path where you want your database stored. Note SSCE databases need to end with a .SDF extension (case is unimportant, .sdf works just as well).

Next is the password. I always create my databases with a password. It makes them more secure, and adding a password will also allow you to encrypt the database to keep it safe from prying eyes. However, you can select not to do so if you want.

Now you’ve got everything filled out, just click the “Create” button.

[Picture of SSCE and VS 2]

A new dialog will appear. Note this is your chance to encrypt the database, as I mentioned I always click this ON. Now click OK.

[Picture of SSCE and VS 3]

Note if you use a very simple password, as I did for this example (I picked arcanecode as the password) you may get an error dialog.

[Picture of SSCE and VS 4]

What it’s simply letting you know is that your password is not very secure. If this were a production system I would absolutely click No, and go enter a much more complex password. I entered a weak password for this demo so I could show you the dialog, so to move things along I’ll click Yes to continue with the password I entered.

You’ll note you’ve been returned to the Add Connection dialog. Let’s see if the create was successful. Click the Test Connection button. If all went well you will see the “Test connection succeeded” message. Click OK to continue.

Returning to the Server Explorer, your new database (which I named ArcaneCode2) should be listed in your Data Connections tree. Click the + button to expand, and you can see the Tables, System Views (not to be confused with a traditional database view), and a Replication folder.

[Picture of SSCE and VS 5]

Note that if you wanted to open an existing SSCE database, you’d simply skip the steps associated with the Create button. Instead you’d simply pick “Add Connection…”, key in (or browse for) the database name, enter the password and click Open.

Let’s create a new table. Right click on the Tables and select Create Table.

[Picture of SSCE and VS 6]

In the dialog that appears, key in some standard info for your table.

[Picture of SSCE and VS 7]

In this example, I named the table CodeCampAttendees, with the column ccaID being the unique ID. One thing I generally do is to use a system generated ID as a behind the scenes unique ID for a row in my databases. In some rare circumstances I’m comfortable using some intelligent key, but in most cases I prefer the computer generate me a key, and I’ll keep up with it behind the scenes (never, and I mean never show these to the user, they’ll abuse the heck out of it).

That said, what are your choices when it comes to unique ID’s? You have two, first is the uniqueidentifier data type, which is a GUID. I like GUIDs, but the one drawback they have is that SSCE won’t automatically generate one for you, you have to take care of that yourself.

The second choice is to use an Int or BigInt. Then in the properties area under the column editor, for the ccaID column change the Identity property to True. This will result in an new ID being generated for you everytime you insert a row. You can also fine tune by setting the value with which the IDs will be incremented on each insert (IdentityIncrement) and what you want the first value to be (IdentitySeed). Often rather than starting with “1”, people will prefer to start with a larger number such as 10000 so the numbers will look more uniform as they increment.

For this example, I’ve picked a BigInt, set the Identity property to true, and Primary Key true for the ccaID column, then entered my remaining columns as nvarchars.

Once you have entered all the fields you want, click OK. Drilling down into our new table, you can see all the columns that were entered. In addition, when I created the table I picked ccaID as my Primary Key, and you can see an Index was automatically created for us.

[Picture of SSCE and VS 8]

Finally we can even add a few rows, simply right click on the table name and pick “Open”. You can view, alter, delete or add new records. Here you can see I added a few new rows, note I did not enter the ccaID. SQL Server Compact Edition took care of that for me.

[Picture of SSCE and VS 9]

You can also do other standard table maintenance via Visual Studio, such as altering the table schema or dropping the table all together. One of the coolest things is the query creator.

Right click on the table name, and pick New Query, then pick the table name(s) from the next dialog. Now you’ll see a dialog that looks disturbingly like the old Access dialog for creating queries. I won’t go into details on how to use it here, but thought you’d like quick screen shot:

[Picture of SSCE and VS 10]

There’s not much more I can say here, this pretty much covers the major features surrounding table creation in SQL Server Compact Edition. Note many of the features will work with any database. Once you make a connection, you can use all the features I’ve shown here to maintain your database whether it’s in SQL Server, SSCE, or even Oracle (assuming you have permissions of course!).


26 thoughts on “Creating a SQL Server Compact Edition Database Using Visual Studio Server Explorer”

  1. NO! It is not possible to alter tables/columns within visual studio 2008. And this has caused me much greif. Basically, you have to regenerate the table within sql studio 2005, then re “upgrade” it into visual studio 2008 from what I can tell, and it is a HUGE pain.

  2. Hi
    I want to create one table within the other table

    How can I do this ?

    Table one
    ogrencibilgi this table has colums that ogrenciID, adisoyadi,numarasi
    I want second table be a sub table of ogrencibilgi

    How can I do this ?

  3. But in code how to add :
    connectionstrings[“”]connectionstring and in aplication configuration file what we must add at connectionstring to make succesful.

  4. Cannot open database “food” requested by the login. The login failed.
    Login failed for user ‘PRIMESERV\dts2’.

    why does the log in fail, how can i make it connect. Please reply A.S.A.P

  5. On the left side of this page is the links for Archives. Click on the April 2007 link. In the list of April posts you’ll find answers to both the connection string question and code examples for inserting the data. Just scroll down the page and you’ll come to all the various posts I did on compact edition.

    Alternatively, over on the right, right underneath the big MVP logo is a drop down combo box under the letter C. (The letter C is actually supposed to read Categories, but for some reason I can’t get WordPress to retain that. But nevermind.)

    Click on the drop down to see all the categories, there are two for SQL Server Compact Edition. They will only display posts for SSCE. Pick one (or both) of those and you will find everything I’ve written on SSCE which should answer your questions.

  6. thx so much for your help
    I am new in sqlce, and I am following your lessons step by step, so the previous examples succeeded, but now i faced this problem:
    i don’t find sqlce provider in data source list?
    taking in consideration that i installed sqlce 3.5, so how can I fix this?

    1. after searching again in the net, i found that vs2008 needed sqlcevstools.enu.msi, which fixed my problem.
      thanks again.

  7. if you set IsRowGuid to true on the uniqueIdentifier field then i think that SQL will generate your GUID for you.

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