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.

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.

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.

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 DocumentsPTSystem.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 DocumentsPTSystem.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()+"
");
    }
  } 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.