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
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.
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.
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.
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.
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.
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.
To 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.