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…”.

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.

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.

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.

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.

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.

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

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.

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.

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:

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!).