Adding Structure to a Microsoft SQL Server CE Database

After creating a SQL Server CE database, the next step is to add tables to the database. This can be done graphically by using SQL Server CE Query Analyzer or programmatically by using the SQL Server CE Data Provider classes. To learn more about the Query Analyzer, see Microsoft SQL Server CE Books Online.

To programmatically create database tables, you will need to connect to the database by using the SqlCeConnection class and issue DDL commands by using the SqlCeCommand class. Before we jump into how this is done, we will need to discuss the capabilities of SQL Server CE.

SQL Server CE supports a subset of the DDL. Table 7.2 describes the supported DDL statements.

Table 7.2. DDL Statements Supported by SQL Server CE

DDL STATEMENT	FUNCTION
CREATE DATABASE	Creates a new database and the file used to store the database.
CREATE TABLE	Creates a new table. Primary keys, unique and foreign keys, and defaults can be specified with this command.
ALTER TABLE	Modifies a table definition by altering, adding, or dropping columns and constraints.
CREATE INDEX	Creates an index on a given table.
DROP INDEX	Removes one or more indexes from the current database.
DROP TABLE	Removes a table definition and all data, indexes, and constraints for that table.

For more information on these commands, see the Microsoft SQL Server CE Books Online.

SQL Server CE also supports a subset of data types. Table 7.3 describes the data types supported by SQL Server CE.

Table 7.3. Data Types Supported by SQL Server CE

DATA TYPE	DESCRIPTION
Bigint	Integer (whole number) data from “2 63 ( “9,223,372,036,854,775,808) through 2 63 “ 1 (9,223,372,036,854,775,807).
Integer	Integer (whole number) data from “2 31 ( “2,147,483,648) through 2 31 “ 1 (2,147,483,647).
Smallint	Integer data from “32,768 to 32,767.
Tinyint	Integer data from 0 to 255.
Bit	Integer data with either a 1 or 0 value.
numeric (p, s)	Fixed-precision and scale-numeric data from “10 38 + 1 through 10 38 “ 1. p specifies precision and can vary between 1 and 38. s specifies scale and can vary between 0 and p .
Money	Monetary data values from “2 63 /10,000 through (2 63 “ 1)/10,000 ( “922,337,203,685,477.5808 through 922,337,203,685,477.5807 units).
Float	Floating-point number data from “1.79E+308 through 1.79E+308.
Real	Floating precision number data from “3.40E+38 through 3.40E+38.
Datetime	Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of one three-hundredth second, or 3.33 milliseconds. Values are rounded to increments of .000, .003, or .007 milliseconds .
nchar ( n )	Fixed-length Unicode data with a maximum length of 255 characters . Default length = 1.
nvarchar ( n )	Variable-length Unicode data with a length of 1 to 255 characters. Default length = 1.
ntext	Variable-length Unicode data with a maximum length of (2 30 “ 2) / 2 (536,870,911) characters.
binary ( n )	Fixed-length binary data with a maximum length of 510 bytes. Default length = 1.
varbinary ( n )	Variable-length binary data with a maximum length of 510 bytes. Default length = 1.
Image	Variable-length binary data with a maximum length of 2 30 “ 1 (1,073,741,823) bytes.
uniqueidentifier	A globally unique identifier (GUID).
IDENTITY [(s, i)]	This is a property of a data column, not a distinct data type. Only data columns of the integer data types can be used for identity columns. A table can have only one identity column. A seed and increment can be specified, and the column cannot be updated. s (seed) = starting value i (increment) = increment value
ROWGUIDCOL	This is a property of a data column, not a distinct data type. It is a column in a table that is defined by using the uniqueidentifier data type.

To learn more about theses data types, see the Microsoft SQL Server CE Books online.

Now, let's explore how to create the structure of a SQL Server database. The remaining samples in this chapter will rely on the tables we will now create. The sample database will contain two tables: the Package table and the TrackingEntry table. Tables 7.4 and 7.5 describe the columns and column types of the Package and TrackingEntry tables, respectively.

Table 7.4. Structure of the Package Table

COLUMN NAME	TYPE	SIZE
ID	Int	IDENTITY(1,1) PRIMARY KEY
Code	Nvarchar	12
DestinationID	Nvarchar	12

Table 7.5. Structure of the TrackingEntry Table

COLUMN NAME	TYPE	SIZE
ID	Int	IDENTITY(1,1) PRIMARY KEY
PackageID	Int	FOREIGN KEY
LocationID	Nvarchar	12
ArrivalTime	Datetime
DepartureTime	Datetime

The Package and TrackingEntry tables are part of a system that would help a shipping company track the location of a package. Each package has a package code ( Code ) and a destination ID ( DestinationID ). In practice it may be convenient to read the package code via a bar code scanner. The destination ID is a string that uniquely identifies the final shipping building the package leaves from before it is loaded onto a truck and delivered to the recipient.

As the package travels to its destination, it will make some stops along the way at intermediate shipping buildings. When a package comes into one of the intermediate shipping buildings , the package is scanned and an entry into the TrackingEntry table is created. This table tracks which shipping location it arrived at, what time it arrived, and what time it left the building. Listing 7.3, demonstrates how to create the Package and TrackingEntry tables.

Listing 7.3 Creating the Package and TrackingEntry tables

 C# public static void CreateTrackingDatabase() {   string connstr = @"Data Source=\My Documents\PTSystem.sdf";   using(SqlCeConnection conn = new SqlCeConnection(connstr)) {     conn.Open();     // Create an the package table     string ddlPackage =       "CREATE TABLE Package( " +       "ID int not null identity(1,1) PRIMARY KEY, " +       "Code nvarchar(12) not null, " +       "DestinationID nvarchar(12) not null)";       RunDDLCommand(conn, ddlPackage);       // Create the tracking entry table       string ddlTrackingEntry =         "CREATE TABLE TrackingEntry( " +         "ID int not null identity(1,1), " +         "PackageID int not null, " +         "LocationID nvarchar(12) not null, " +         "ArrivalTime datetime not null, " +         "DepartureTime datetime null, " +         "FOREIGN KEY (PackageID) REFERENCES Package(ID) )";       RunDDLCommand(conn, ddlTrackingEntry);       // Create an index on the tracking entry table       string ddlArrivalTimeNdx =         "CREATE INDEX ArrivalTime ON TrackingEntry(ArrivalTime )";       RunDDLCommand(conn, ddlArrivalTimeNdx );   } } VB Sub CreateTrackingDatabase()   Dim connstr As String   connstr = "Data Source=\My Documents\PTSystem.sdf"   Dim conn As SqlCeConnection   conn = New SqlCeConnection(connstr)   conn.Open()   'Create an the package table   Dim ddlPackage As String   ddlPackage = "CREATE TABLE Package( " & _     "ID int not null identity(1,1) PRIMARY KEY, " & _     "Code nvarchar(12) not null, " & _     "DestinationID nvarchar(12) not null)"   RunDDLCommand(conn, ddlPackage)   ' Create the tracking entry table   Dim ddlTrackingEntry As String   ddlTrackingEntry = "CREATE TABLE TrackingEntry( " & _     "ID int not null identity(1,1), " & _     "PackageID int not null, " & _     "LocationID nvarchar(12) not null, " & _     "ArrivalTime datetime not null, " & _     "DepartureTime datetime null, " & _     "FOREIGN KEY (PackageID) REFERENCES Package(ID) )"   RunDDLCommand(conn, ddlTrackingEntry)   ' Create an index on the tracking entry table   Dim ddlArrivalTimeNdx As String   ddlArrivalTimeNdx =     "CREATE INDEX ArrivalTime ON TrackingEntry(ArrivalTime )"   RunDDLCommand(conn, ddlArrivalTimeNdx)   conn.Close() End Sub 

Let's take a look at the method. This method starts out by creating a connection to the SQL Server database by using the SqlCeConnection object. This object instance is created by using the connection string that grants access to the database. Notice that this connection is embedded in a using statement to ensure that the connection's resources are cleaned up after it is done being used. Next, the connection to the database is opened with a parameter-less call to the SqlCeConnection.Open method. We then create the Package table. Using a string that contains the CREATE TABLE SQL command. To learn about the CREATE TABLE command, see the Microsoft SQL Server CE Books Online. Next, we create the TrackingEntry table. This table contains a foreign key constraint on the PackageID column. The values inserted in the PackageID column must exist in the ID column of the Package table. In other words, a package that has not been entered into the Package table cannot have a TrackingEntry record. Finally, for performance reasons, we create an index consisting of an ArrivalTime column on the TrackingEntry table. This index will be used later in this chapter. To learn more about the Create Index SQL command, see the Microsoft SQL Server CE Books Online.

In the preceding sample, the RunDDLCommand method actually creates the different elements of the database. Listing 7.4 contains the code for the RunDDLCommand method.

Listing 7.4 The implementation of the RunDDLCommand method

 C# public static void RunDDLCommand(SqlCeConnection conn, string ddlCmdStr) {   SqlCeCommand cmdDDL = null;   try {     cmdDDL = new SqlCeCommand(ddlCmdStr, conn);     cmdDDL.CommandType = CommandType.Text;     cmdDDL.ExecuteNonQuery();   } catch(SqlCeException scee) {     for(int curExNdx = 0; curExNdx < scee.Errors.Count; ++curExNdx) {       MessageBox.Show("Error:"+scee.Errors[curExNdx].ToString()+"\n");     }   } finally {     if( cmdDDL != null )       cmdDDL.Dispose();   } } VB sub RunDDLCommand(conn as SqlCeConnection , ddlCmdStr as string )   Dim cmdDDL As SqlCeCommand   cmdDDL = Nothing   Try     cmdDDL = New SqlCeCommand(ddlCmdStr, conn)     cmdDDL.CommandType = CommandType.Text     cmdDDL.ExecuteNonQuery()   Catch scee As SqlCeException     Dim curExNdx As Int32     For curExNdx = 0 To scee.Errors.Count       MessageBox.Show("Error:" & scee.Errors(curExNdx).ToString())     Next   Finally     If Not cmdDDL Is Nothing Then       cmdDDL.Dispose()     End If   End Try End Sub 

The sole purpose of RunDDLCommand is to run a DDL command against a database by using a specified connection. First, a SqlCeCommand object is created by using the specified SqlCeConnection object and the specified command string that contains the DDL statement. We then specify how the command string should be interpreted by setting the SqlCeCommand.CommandType property. Because the command string is a just a SQL command string, we use CommandType.Text . The complete list of CommandType enumeration values are listed, along with a description, in Table 7.6. Finally, the SqlCeCommand.ExecuteNonQuery method is called to actually run the DDL statement against the database. The ExecuteNonQuery method should be used when running SQL commands that do not return a result other than the number of rows affected.

There are two other notable code blocks in the RunDDLCommand method. First, notice that we ensure that the command is properly disposed by wrapping the command execution code in a try/catch and calling SqlCeCommand.Dispose . The second notable block is in the catch block. We catch SQL errors that occur in the command execution code by catching all SqlCeException objects that are thrown. The SqlCeException contains an Errors property that is a list of errors that caused the SqlCeException to be thrown. The code in the catch block walks the list of errors and displays them to the user .

Table 7.6. The CommandType Enumeration Values

MEMBER NAME	DESCRIPTION
StoreProcedure	The name of a stored procedure. Note that SQL Server CE does not support stored procedures.
Text	A SQL text command (default).
TableDirect	When the CommandType property is set to TableDirect , the CommandText property should be set to the name of the table or tables to be accessed. All rows and columns of the named table or tables will be returned when you call one of the Execute methods . In order to access multiple tables, use a comma-delimited list, without spaces or padding, that contains the names of the tables to access. When the CommandText property names multiple tables, a join of the specified tables is returned.