26.14 Creating a SqlServerCE Database

26.14 Creating a SqlServerCE Database

You want to use a data from a SQL Server database installed on a mobile device.

Technique

Visual Studio .NET 2003 ships with a version of SQL Server designed to run on Windows CE and Pocket PC devices. The first step to create a database on the device is to copy SQL Server CE onto the device itself. The easiest way to do so is to create a new Smart Device Application and add a reference to the System.Data.SqlServerCe assembly. Once you build and deploy the application to the emulator or your device, the necessary binaries for SqlServerCE are also copied over. The examples covered in this and the next recipe assume that the SQL Server CE is running locally on the device. It is possible to do remote database connections with the .NET Compact Framework, but that topic is beyond the scope of this book. When you are ready to deploy your application to end users, the SQL Server CE CAB files are located in the following path :

 <install drive>:\Program Files\Microsoft Visual Studio .NET 2003\CompactFrameworkSDK\v1.0.5000 

Additionally, you can use the techniques described in Recipe 26.8, "Building CAB Files for Application Distribution," to perform automatic installation of the CAB files.

Once you successfully transfer SQL Server CE to the device, you are ready to create a database. Run the Query Analyzer tool located in the Start menu with a link name of SQLCE Query. After the tool starts, click on the Databases item on the Objects tab and click on the Connect to Database toolbar located at the bottom of the window. This toolbar button has a green arrow on it to help distinguish it from the other button. Next, enter a name for the database, using an .sdf file extension and an optional password, and click on the New Database button.

After you create the database, expand the Databases node until you find the Tables node. Click on the new table toolbar button, which has a white plus sign, and enter a new table name. Within the Add Table dialog, you will also add any necessary columns . For this example and the next recipe, create a table named Catalog, which will hold information pertaining to a music collection. Click on the Insert Column button and add a column named ID. Because it will be a primary-key column, check the check box labeled Primary Key. To automatically increment this column, change the Identity value to Yes and enter the value 1 for the Identity Seed and Identity Inc fields.

Next, add a column named Title. The type for this column is nvarchar with an arbitrarily chosen length of 40. To ensure that the Title is specified when adding new items, uncheck the Allow Nulls check box. Finally, add another column named Artist using the same data type, but this time, leave the Allow Nulls check box checked.

Comments

Just as a small version of the .NET Framework was created for mobile devices, a small but powerful version of SQL Server was created. If you're like us, you tend to question technologies before using them to determine whether the path is clear before you dive right in. SQL Server CE Edition is one whose immediate benefits might not be particularly clear. After all, you can create a DataSet by simply reading in an XML file and a schema, so why not just use that? Consider the following scenario. You work as a developer for a company that wants to be able to take an inventory count at any time in the warehouse using a Pocket PC. The company maintains the current inventory of the items in the warehouse using a SQL database located on a large central server. By using SQL Server CE, you can create a Smart Device Application that updates a local database on the device, which will later be merged with the master database when you are finished. If you were to use other storage mechanisms, you would also have to create the additional toolset necessary to perform the database merge. With that in mind, the next recipe utilizes the database created in this recipe to add, delete, and modify the music catalog SQL Server CE database.