Create a SQL Server 2005 Compact Edition database visually by using the Data Source Configuration Wizard in Microsoft Visual Studio 2005

Since developers spend a lot of their time within the Visual Studio 2005 IDE it is only natural that this tool has many features designed to create and maintain the contents of SQL Server 2005 Compact Edition databases.

NOTE: If any of the dialogs within your copy of Visual Studio 2005 refer to “SQL Mobile Database” or something similar you have an older version of SQL Server CE installed. See my Installing SQL Server Compact Edition blog entry for details on how to upgrade.

Creating a database

Screenshot showing Add New Item dialog

The quickest way to add a blank SQL Server Compact Edition database to a smart device project is to right click on the project within Solution Explorer and select the “New Item…” option within the “Add” submenu. This allows you to select the “Database File” item template, as shown in the above screenshot.

Once you have named the database file and clicked the Add button, the last step of the “Data Source Configuration Wizard” appears. You can press the cancel button to skip this wizard since our database is currently empty.

Another approach to creating a database file is to use the complete Data Source Configuration Wizard. This is accessible in a number of different places within the Visual Studio IDE, perhaps the easiest being via the “Add New Data Source…” menu item found within the main Data menu.

Screenshot showing first step of Data Source Configuration Wizard

The first step of the wizard asks what type of data source you would like to configure. For a SQL Server Compact Edition database we select “Database” and select the “Next” button to move to the second step of the wizard.

Second step of the Data Source Configuration Wizard

Since we are creating a new database our database will not be listed in the database combo box, so we press “New Connection…” to display the “Add Connection” dialog.

Screenshot showing the Add Connection dialog

This dialog can be used to configure a wide range of database products. If the read-only text box at the top of the dialog does not mention “Microsoft SQL Server 2005 Compact Edition” click the “Change…” button and select it from a small popup dialog that appears.

To create a database select the “Create…” button. This will display a dialog similar to the one seen within SQL Server Management Studio as discussed earlier.

Screenshot of Create New SQL Server Compact Edition Database dialog

Specifying the location we want the new database file to be stored in and pressing the OK button twice will return us to step 2 of the Data Source Configuration Wizard. Pressing the Next button takes us to the third and final step of the wizard. If the database file isn’t located within a subdirectory of the project you will get a prompt “the connection you selected uses a local data file that is not in the current project, would you like to copy the file to your project and modify the connection?”. Selecting yes is probably the best option, and will move a copy of the newly created database file into the project folder.

Screenshot showing third step of the Data Source Configuration Wizard

This last wizard step enables us to create a type-safe dataset for the database. Since we have just created a blank database there is not much use for this, so we can press the Cancel button to dismiss this step.

Opening a database

Double clicking on a SQL Server 2005 Compact Edition database file (*.sdf) within Solution Explorer will automatically open the Server Explorer pane and provide a view similar to the Object Explorer pane found within SQL Server Management Studio.

If the database file is not currently part of our solution we can use the “Connect To Database” toolbar button within the Server Explorer window to display the Data Source Configuration Wizard (as discussed above) to connect to the existing database file.

Creating tables

Screenshot of Server Explorer windowTo create a Posts table that looks like the example table we created in the previous tutorial we can use the Server Explorer window.

Right clicking on the “Tables” node underneath our database file will allow us to select a “Create Table” menu option. This will display a “New Table” dialog that allows us to graphically specify the details of our new table.

Screenshot showing the New Table dialog within Visual Studio 2005

One Response to “Create a SQL Server 2005 Compact Edition database visually by using the Data Source Configuration Wizard in Microsoft Visual Studio 2005”

  1. Jonathan says:

    NEED HELP!!! I am creating a sql serverce database however when I try to create it the option for a sqlserverce database is not there? Does any one know the reason why. So I tried a work around by downloading the express edition of VC# and using it to create the database on another computer. Did that and now I am able to add the datafile to the project but am not able to open the datafile in Visual studio. So my connection string works. It connects supposedly but it is now telling me when I try to update the database that the column name is incorrect. When I checked it out on the other computer (because I couldnt see the tables on this computer) they were the same column name. Does anyone know what to do about either problem. Please Comment Back!!!! THANK YOU IN ADVANCE!!!

Leave a Reply