Accessing SQLCE

To access SQLCE Compact Framework programmatically, developers can use the managed .NET Data Provider referred to as SqlServerCe. In this section we'll explore the data provider and how it can be used to connect to, query, and update SQLCE, and we'll also show a technique for writing provider-independent code when an application must access both a remote SQL Server and SQLCE.

SqlServerCe Provider Architecture

The SqlServerCe provider was implemented using the same pattern as the SqlClient .NET Data Provider used to access remote SQL Servers, as discussed in the previous chapter; therefore, it consists of the same basic classes. This parity with the desktop provider allows developers to leverage their existing ADO.NET knowledge and begin writing applications for SQLCE.

Unlike SqlClient, however, the SqlServerCe provider is shipped in an assembly separate from System.Data.dll and, so, must be explicitly referenced by the developer in his or her SDP. Figure 5-4 shows a diagram of the architecture of the provider, all of whose classes are found in the System.Data.SqlServerCe namespace.^[7]

^[7] All the listings and code snippets in this chapter assume that the System.Data.SqlServerCe and System.Data namespaces have been imported (using C#).

You'll notice in Figure 5-4 that the layout of the classes is similar to that found in Figure 4-4. For example, SqlServerCe supports both the disconnected programming model using the DataSet via the SqlCeDataAdapter object and the connected model using SqlCeDataReader. SQL commands are encapsulated with the SqlCommand class and can use parameters represented by SqlCeParameter. The SqlCeConnection and SqlCeTransaction objects also support local transactions, while database engine errors are captured in SqlCeError objects and thrown using a SqlCeException object. In fact, SqlServerCe even supports the SqlCeCommandBuilder class that can be used to create the INSERT, UPDATE, and DELETE statements automatically for synchronizing data in a data set with SQLCE. However, you'll also notice that SqlServerCe includes the additional classes shown in Table 5-1. These classes are found only in the SqlServerCe provider and have no analogs in SqlClient.

Although SqlCeEngine will be discussed in the following section, both SqlCeReplication and SqlCeRemoteDataAccess used for synchronization will be covered in detail in Chapter 7.

Manipulating Data with SqlServerCe

Once the SqlServerCe provider is referenced in an SDP, it can be used to manipulate SQLCE on the device. In this section we'll look at the common tasks developers will need to perform against SQLCE.

Creating Databases and Objects

Although a database with the appropriate structure and data can be deployed with the application, it is sometimes necessary for developers to create databases and objects on the fly. This can be accomplished using the CreateDatabase method of the SqlCeEngine object. In fact, a good strategy is to encapsulate the creation in a utility class and expose the functionality through shared methods like that shown in Listing 5-1.

Listing 5-1 Creating a SQLCE Database. This method shows how to create a SQLCE database on the device using the SqlCeEngine class.

Public Shared Function CreateDb(ByVal filePath As String) As Boolean
   ' Delete and create the database

   Try
      If File.Exists(filePath) Then
          File.Delete(filePath)
      End If
   Catch e As Exception
      _lastException = e
      MsgBox("Could not delete the existing " & filePath, _
        MsgBoxStyle.Critical)
      Return False
   End Try

   Dim eng As SqlCeEngine
   Try
       eng = New SqlCeEngine("Data Source=" & filePath)
       eng.CreateDatabase()
       Return True
   Catch e As SqlCeException
       _lastException = e
       LogSqlError("CreateDb",e)
       MsgBox("Could not create the database at " & filePath, _
         MsgBoxStyle.Critical)
       Return False
   Finally
       eng.Dispose()
   End Try
End Function

In this case you'll notice that the CreateDb method first attempts to delete the database if it exists; it then passes the path to the database to the constructor of SqlCeEngine before calling the CreateDatabase method. The connection string need only consist of the Data Source attribute, and the Provider attribute will be defaulted to Microsoft.SQLSERVER.OLE-DB.CE.2.0.^[8] Other attributes may also be used, as discussed later in the chapter.

^[8] This differs from ADOCE used in eVB, where omitting the Provider attribute assumes the CEDB provider and not SQLCE.

If an exception is found, the exception is placed in a private variable called _lastException that is exposed as a read-only shared property of the class. In this way the caller can optionally access full information about the exception that occurred. The database error is also logged using a custom method. To use this method the calling code would look like the following (assuming the method was placed in the Atomic.SqlCeUtils class):

If Atomic.SqlCeUtils.CreateDatabase(FileSystem.DocumentsFolder & _
   "\Personal\mydb.sdf") Then
    ' Go ahead and create some tables
End If

Note that the calling code uses the FileSystem class shown in Listing 3-5 to retrieve the My Documents folder on the device and then creates the database in the Personal folder.

To create objects within a database, the application must first create a connection with the SqlCeConnection object. This is easily accomplished by passing the same connection string used to initialize the SqlCeEngine object in Listing 5-1 to the constructor of SqlCeConnection and calling the Open method as follows:

Dim cnCE As New SqlCeConnection(dbConnect)
cnCE.Open()

As you would expect, the previous snippet may throw a SqlCeException on either line if the connection string is malformed or the database is already open or does not exist. For this reason the opening of a connection should also be wrapped in a Try-Catch block.

More important, as SQLCE supports only one concurrent connection (unlike SQL Server 2000) because Windows CE is a single-user operating system, the connection object is usually obtained early in the run of the application and persisted in a variable until the application closes. It is therefore important to ensure that the connection eventually gets closed so that other applications (for example, the Query Analyzer) may connect to the database.

After creating a connection, DDL statements can be executed against the connection to create the appropriate tables and indexes. Each DDL statement must be encapsulated in a SqlCeCommand object and executed with the ExecuteNonQuery method. However, if the application requires that multiple statements be executed (to create several tables and their indexes, for example), it is possible to create a utility function to read the SQL from a resource file deployed with the application. This is accomplished by adding a text file to SDP and setting its Build Action property in the Properties window to Embedded Resource. Then the resource file can be populated with CREATE and ALTER statements, like those shown below, to create a table to hold batting statistics and add a primary key and an index.

CREATE TABLE Batting (Id int NOT NULL, LastName nvarchar(50),
 FirstName nvarchar(50),Year smallint NOT NULL,Team nchar(3),
 G smallint NULL,AB smallint NULL,R smallint NULL ,
 H smallint NULL,"2B" smallint NULL,"3B" smallint NULL ,
 HR smallint NULL,RBI smallint NULL);
ALTER TABLE Batting ADD CONSTRAINT pk_batting PRIMARY KEY (Id, Year);
CREATE INDEX idx_bat_team ON Batting (Year, Team ASC);

When the project is built, the file will then be compiled as a resource in the assembly and deployed to the device.

To read the resource script and execute its DDL, a method like that shown in Listing 5-2 can be written.

Listing 5-2 Running a SQL Script. This method reads from a resource file and executes all the commands found therein. Note that none of the commands may use parameters.

Public Shared Function RunScript(ByVal scriptName As String, _
  ByVal cn As SqlCeConnection) As Boolean

    ' Perform a simple execute non query
    Dim closeIt As Boolean = False
    Dim resource As Stream

    Try
        Resource = _
         [Assembly].GetExecutingAssembly().GetManifestResourceStream( _
         scriptName))
        Dim sr As New StreamReader(resource)
        Dim script As String = sr.ReadToEnd()
        Dim commands() As String
        commands = script.Split(";"c)

        ' Open the connection if closed
        If cn.State = ConnectionState.Closed Then
           cn.Open()
           closeIt = True
        End If

        Dim cm As New SqlCeCommand()
        cm.Connection = cn
        Dim s As String
        For Each s In commands
           If s <> "" Then
               cm.CommandText = s
               cm.ExecuteNonQuery()
           End If
        Next

    ' Clean up
    Catch e As SqlCeException
         _lastException = e
         LogSqlError("RunScript",e)
         MsgBox("Could not run script " & scriptName, _
          MsgBoxStyle.Critical)
        Return False
    Catch e As Exception
         _lastException = e
         MsgBox("Could not run script " & scriptName, _
          MsgBoxStyle.Critical)
        Return False
    Finally
       If closeIt Then cn.Close()
    End Try
    Return True
 End Sub

In Listing 5-2 you'll notice that the GetManifestResourceStream method of the System.Reflection.Assembly class is used to read the resource file into a Stream object. The Stream object is then read by a StreamReader and placed into a string variable. In this scenario, the method is expecting strings delimited with a semicolon and, therefore, creates an array of strings using the Split method. This is required in order to execute multiple statements, because SQLCE does not support batch SQL as SQL Server does. In other words, SQLCE can execute only one statement per SqlCeCommand object.

The method then proceeds to open the connection object if it is closed and create a SqlCeCommand object. The command object is then populated repeatedly in a loop, and each statement is executed using ExecuteNonQuery. You'll notice in the Finally block that the connection is closed only if it were opened by the method. The advantage to this technique is that it allows for looser coupling between the script and the code that executes it, so that the script can be changed without changing any code and the project recompiled and deployed. To put it all together, an application could use code like the following in its main form's Load event to create the database, connect to it, and create tables and indexes:

If Atomic.SqlCeUtils.CreateDatabase(FileSystem.DocumentsFolder & _
   "\Personal\mydb.sdf") Then
    ' Connect (cnCE is global)
   cnCE = New SqlCeConnection(dbConnect)
   cnCE.Open()
   ' Go ahead and create some tables
   If Atomic.SqlCeUtils.RunScript("firstrun.sql", cnCE) Then
      ' All is well and the database is ready
   End If
End If

Querying Data

As mentioned previously, SqlServerCe supports both the disconnected and connected programming models using the DataSet and data reader that were discussed in Chapter 4. Unfortunately, unlike in SQL Server 2000, SQLCE does not support stored procedures. As a result, developers will need to formulate SQL within the application and submit it to the database engine (although SQLCE does support parameterized queries, as will be discussed later). Also, as mentioned previously, SQLCE does not support batch SQL, and so, multiple SELECT statements cannot be executed and their results cannot be automatically populated in multiple DataTable objects in a DataSet or through multiple result sets using the NextResult property of the SqlCeDataReader. However, developers can still create data-access helper methods that reduce the amount of code required by the caller. For example, the method in Listing 5-3 adds data to a DataSet based on the SQL passed to the method.

Listing 5-3 Populating a Data Set. This method adds data to a data set given the SQL statement and the connection object to use.

Public Shared Sub FillSimpleDataSet(ByVal ds As DataSet, _
   ByVal sql As String, ByVal cn As SqlCeConnection, _
   ByVal acceptChanges As Boolean)

   Try
       Dim cm As New SqlCeCommand(sql, cn)
       Dim da As New SqlCeDataAdapter(cm)
       da.AcceptChangesDuringFill = acceptChanges

       da.MissingMappingAction = MissingMappingAction.Passthrough
       da.MissingSchemaAction = MissingSchemaAction.AddWithKey

       da.Fill(ds)
   Catch e As SqlCeException
       LogSqlError("FillSimpleDataSet",e)
       Throw New SqlCEUtilException( _
         "Could not fill dataset for: " & sql, e)
   End Try
End Function

You'll notice that in Listing 5-3 an existing connection object is used and that the caller determines whether AcceptChangesDuringFill is set to True or False to determine if the newly added rows are treated as new rows (with their RowState property set to Added) or as unmodified rows. In this case the connection object needn't be opened explicitly because the SqlCeDataAdapter will open it if it is not already open. The MissingMappingAction and MissingSchemaAction properties are also set to allow the data adapter to create any missing tables or columns in the DataSet and to add primary key information if available. Obviously, this method would not be useful if more sophisticated table mappings were required.^[9] If any errors occur, a custom exception of type SqlCeUtilException inherited from ApplicationException is thrown.

^[9] See Chapter 12 of Teach Yourself ADO.NET in 21 Days, by Dan Fox, for a complete explanation of how data adapters use table and column mappings.

Data readers can similarly be created to stream through the results from a table as shown in Listing 5-4.

Listing 5-4 Creating a Data Reader. This method creates and returns a SqlCeDataReader given a SQL statement and a connection object.

Public Shared Function ExecDataReader(ByVal sql As String, _
   ByVal cn As SqlCeConnection) As SqlCeDataReader

    Try
         ' Create the command
         Dim cm As New SqlCeCommand(sql, cn)
         If cn.State = ConnectionState.Closed Then
             cn.Open()
         End If

         ' Execute data reader
         Dim dr As SqlCeDataReader
         dr = cm.ExecuteReader()
         Return dr
    Catch e As SqlCeException
         LogSqlError("ExecDataReader",e)
         Throw New SqlCEUtilException( _
           "Could not execute data reader for :" & sql, e)
    End Try
End Function

In Listing 5-4 the method creates a command object and associates it with the connection passed into the method. In this case the method must also open the connection if it is not already open before executing the data reader and returning it. Note that although the ExecuteReader method supports the CloseConnection and other command behaviors, it is not used because typically a single global database connection remains open for the lifetime of the application.

A caller would then use the method as follows:

Dim dr As SqlCeDataReader
dr = SqlCeUtils.ExecDataReader( _
  "SELECT * FROM Batting WHERE Id = 660", cnCE)

Do While dr.Read()
   ' Process the data
Loop
dr.Close()

Although not shown in this listing, it is also interesting to note that unlike the SqlClient provider, the SqlServerCe provider does support multiple data readers on the same open connection object. In other words, developers needn't close the SqlCeDataReader before using the connection to execute another command. Again, this is the case because SQLCE supports only a single concurrent connection.

One of the most interesting new features of SQLCE is the inclusion of parameterized queries. Using parameterized queries, developers can simply populate SqlCeParameter objects associated with a SqlCeCommand, rather than having to manually concatenate parameters into a single string. In addition, parameterized queries are recommended for performance reasons. However, unlike SqlClient, SQLCE supports only positional parameters, and the parameters must be defined in the SQL statement using a question mark. In other words, developers must declare a SqlCeParameter object for each question mark in the SQL statement so that the SqlCeCommand object can perform the substitution at runtime. For example, in order to execute the query shown above as a parameterized query, a developer could do the following:

Dim dr As SqlCeDataReader
Dim cm As New SqlCeCommand("SELECT * FROM Batting WHERE Id = ?", cnCE)
cm.Parameters.Add(New SqlCeParameter("@Id", SqlDbType.Int))
cm.Parameters(0).Value = 660

dr = SqlCeUtils.ExecDataReader()

In this case, although the parameter was referenced by its ordinal, it could alternatively have been referenced by its name (@Id).

In a helper or utility class, the creation of parameter objects and their association with a command object can be handled by a structure and private method like that shown in Listing 5-5.

Listing 5-5 Listing 5-5: Automating Parameterized Queries. This structure and method can be used to create and attach parameters automatically to a SqlCeCommand object.

Public Structure ParmData
    Public Name As String
    Public Value As Object
    Public DataType As SqlDbType

    Public Sub New(ByVal name As String, ByVal dataType As SqlDbType, _
      ByVal value As Object)
        Me.Name = name
        Me.DataType = dataType
        Me.Value = value
    End Sub
End Structure

Private Shared Function PopulateCommand(ByVal sql As String, _
   ByVal parms As ArrayList, ByVal cn As SqlCeConnection) _
   As SqlCeCommand

    Dim cm As New SqlCeCommand(sql, cn)
    cm.CommandType = CommandType.Text

    ' Populate parameters
    Dim p As Object
    For Each p In parms
        Dim p1 As ParmData = CType(p, ParmData)
        cm.Parameters.Add( _
          New SqlCeParameter(p1.Name, p1.DataType, p1.Value))
    Next

    Return cm

End Function

In Listing 5-5 you'll notice that the private PopulateCommand method accepts an ArrayList of ParmData objects as a parameter and uses it to populate a SqlCeCommand created from the SQL statements and SqlCeConnection object passed in as well.^[10] With this technique an overloaded version of the method in Listing 5-4 can be created to accept parameterized SQL, as shown in Listing 5-6.

^[10] Since SQLCE does not support stored procedures, the StoredProcedure CommandType is also not supported.

Listing 5-6 Creating a Data Reader with Parameters. This method creates and returns a SqlCeDataReader given a SQL statement, parameters, and a connection object.

Public Shared Function ExecDataReader(ByVal sql As String, _
      ByVal cn As SqlCeConnection, _
      ByVal parms As ArrayList) As SqlCeDataReader

    Try
         ' Create the command
         Dim cm As SqlCeCommand = Me.PopulateCommand(sql, parms, cn)
         If cn.State = ConnectionState.Closed Then
             cn.Open()
         End If

         ' Execute data reader
         Dim dr As SqlCeDataReader
         dr = cm.ExecuteReader()
         Return dr
    Catch e As SqlCeException
         LogSqlError("ExecDataReader",e)
         Throw New SqlCEUtilException( _
           "Could not execute data reader for :" & sql, e)
    End Try
End Function

At this point the caller need create only the ParmData objects, specifying the appropriate data type, and place them in an ArrayList before passing them to ExecDataReader, as shown in this snippet:

Dim dr As SqlCeDataReader
Dim sql As String = "SELECT * FROM Batting WHERE Id = ?"
Dim parms As New ArrayList()

parms.Add(New ParmData("id", SqlDbType.Int, 660))
dr = SqlCeUtils.ExecDataReader(sql, cnCE, parms)

Using Indexes

Perhaps the biggest difference between the SqlClient provider and the SqlServerCe provider is the inclusion of index seeks using data readers in SqlServerCe. Using this technique allows developers to write code that performs better than issuing SELECT statements with WHERE clauses. This is the case because the SQLCE query processor must compile, optimize, and generate a query plan for each query, while performing the index seek directly avoids these costly steps. The caveat is that this works only against single tables, and the table must of course have an index. As a result, for complex queries developers will likely want to rely on the query processor.

For example, consider the scenario where a developer wanted to retrieve the statistics for a specific team and year from the batting table created in Listing 5-2, and it is known that the year will be in the range from 1980 to 1989. The batting table has a composite index on the Year and Team columns, and so a method like that shown in Listing 5-7 can be written to return a SqlCeDataReader positioned on the correct row.

Listing 5-7 Seeking a Row Using an Index. This method creates and returns a SqlCeDataReader positioned on the appropriate row for a given set of index values.

public static SqlCeDataReader ExecTeamReader(SqlCeConnection cn,
  string team, int year)
{
   SqlCeCommand cmd = new SqlCeCommand("Batting",cn);
   cmd.CommandType  = CommandType.TableDirect;

   if (cn.State == ConnectionState.Closed)
   {
      cn.Open();
   }

   // Index contains Year and Team
   cmd.IndexName = "idx_bat_team";

   object[] start = {1980, 1989};
   object[] end = {null, null};
   cmd.SetRange(DbRangeOptions.InclusiveStart |
     DbRangeOptions.InclusiveEnd, start, end);

   Try
   {
      SqlCeDataReader rdr = cmd.ExecuteReader();
      rdr.Seek(DbSeekOptions.AfterEqual, year, team);
      return rdr;
   }
   Catch (SqlCeException e)
   {
     LogSqlError("ExecTeamReader",e);
     // Throw a custom exception
     return null;
   }
}

You'll notice in Listing 5-7 that the SqlCeCommand must have its CommandText property set to the name of the table to search and that the CommandType must be set to TableDirect. The name of the index is then set using the IndexName property. Although it is not required, this listing also shows that the range of values searched can be restricted by passing arrays of start and end values to the SetRange method. The DbRangeOptions enumeration determines how the Seek method uses the start and end values. After opening the data reader using ExecuteReader, its Seek method is then called with a value from the DbSeekOptions enumeration. This value specifies which row if any is to be returned. In this case, AfterEqual is used and if a row is not found, the first row after the index range will be the one pointed to by the data reader. Alternatively, if FirstEqual is used, the Seek method will throw a SqlCeException if a row cannot be located.

A caller can then invoke the method to position a data reader at the statistics for the 1984 Chicago Cubs as follows:

SqlCeDataReader dr = SqlCeUtils.ExecTeamReader(cnCE,"CHN",1984);

Modifying Data

Inserting, updating, and deleting data in SQLCE are not handled any differently than they are using the SqlClient provider, with the exception, of course, that SQLCE does not support stored procedures. In other words developers may use the SqlCeDataAdapter to modify data in an underlying base table utilizing the table and column mappings collections and then invoking the Update method of the data adapter. Developers may also execute command objects directly. In either case parameterized queries are used and, in fact, are required for use with the SqlCeDataAdapter.

For example, to insert a new row into the Batting Table, the method shown in Listing 5-8 could be written to return the command object used in either scenario.

Listing 5-8 Inserting Data with a Command. This method creates and returns a SqlCeCommand to insert new rows into the Batting Table.

Public Shared Function GetBattingCmd(cnCE As SqlCeConnection, _
   trans As SqlCeTransaction) As SqlCeCommand

   Dim sql As String = "INSERT INTO Batting (Id, LastName, " & _
     "FirstName, Year, Team, G, AB, R, H, ""2B"", ""3B"", " & _
     "HR, RBI) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

   battingCmd = New SqlCeCommand(sql)
   battingCmd.CommandType = CommandType.Text
   If Not trans Is Nothing Then
       battingCmd.Transaction = trans
   End If

   battingCmd.Parameters.Add(New SqlCeParameter("Id", _
     SqlDbType.NVarChar, 9, "Id"))
   battingCmd.Parameters.Add(New SqlCeParameter("LastName", _
     SqlDbType.NVarChar, 50, "LastName"))
   battingCmd.Parameters.Add(New SqlCeParameter("FirstName", _
     SqlDbType.NVarChar, 50, "FirstName"))
   battingCmd.Parameters.Add(New SqlCeParameter("Year", _
     SqlDbType.SmallInt, 4, "Year"))
   battingCmd.Parameters.Add(New SqlCeParameter("Team", _
     SqlDbType.NVarChar, 3, "Team"))
   battingCmd.Parameters.Add(New SqlCeParameter("G", _
     SqlDbType.SmallInt, 4, "G"))
   battingCmd.Parameters.Add(New SqlCeParameter("AB", _
     SqlDbType.SmallInt, 4, "AB"))
   battingCmd.Parameters.Add(New SqlCeParameter("R", _
     SqlDbType.SmallInt, 4, "R"))
   battingCmd.Parameters.Add(New SqlCeParameter("H", _
     SqlDbType.SmallInt, 4, "H"))
   battingCmd.Parameters.Add(New SqlCeParameter("2B", _
     SqlDbType.SmallInt, 4, "2B"))
   battingCmd.Parameters.Add(New SqlCeParameter("3B", _
     SqlDbType.SmallInt, 4, "3B"))
   battingCmd.Parameters.Add(New SqlCeParameter("HR", _
     SqlDbType.SmallInt, 4, "HR"))
   battingCmd.Parameters.Add(New SqlCeParameter("RBI", _
    SqlDbType.SmallInt, 4, "RBI"))

   return battingCmd
End Function

The GetBattingCmd static method could then be used by a caller to retrieve the appropriate command before populating the parameters with values manually through code or by setting it to the InsertCommand property of the SqlCeDataAdapter.

Although not typically recommended for production scenarios with the SqlClient provider,^[11] the command builder included with SqlServerCe (SqlCeCommandBuilder) can be used in place of code like that shown in Listing 5-7. This is due to the fact that SQLCE is single user and runs in process with the application; therefore, an extra round trip isn't as costly in terms of performance. In any case, it can be used simply by passing the SqlCeDataAdapter to the constructor of the command builder:

^[11] Using the SqlCommandBuilder object always engenders one extra trip to the database server so that the command builder can determine column names and data types.

Dim cb as New SqlCeCommandBuilder(da)

When needed,^[12] the command builder will then build the insert, update, and delete commands based on the SELECT statement exposed in the CommandText property of the SelectCommand. Note that just as with the SqlClient provider, the SELECT statement used by the data adapter mustn't be complex (contain aggregates columns and joins) and must return at least one primary key or unique column, or an exception will be thrown.

^[12] When the RowUpdating events fire on the SqlCeDataAdapter object.

Handling Transactions

Just like SqlClient, the SqlServerCe provider supports transactions, or the ability to group a series of data modifications in an atomic operation. This is useful if an application needs to update two tables, with the requirement that if one of the updates fails, they both fail (a parent/child relationship, for example).

This is accomplished through the BeginTransaction method of the SqlCeConnection object, which spawns a SqlCeTransaction object used to control the outcome (Commit or Rollback) of the transaction. For example, the following code snippet uses the GetBattingCmd method shown in Listing 5-8 and the GetPitchingCmd method (not shown) to execute two commands in a single transaction:

SqlCeTransaction trans = null;
Try
{
    trans = cnCE.BeginTransaction();
    SqlCeCommand bat = SqlCeUtils.GetBattingCmd(cnCE, trans);
    SqlCeCommand pitch = SqlCeUtils.GetPitchingCmd(cnCE, trans);

    // populate the commands with the new values

    bat.ExecuteNonQuery();
    pitch.ExecuteNonQuery();
    trans.Commit();
}
catch (SqlCeException e)
{
  if (trans != null) {trans.Rollback();}
  LogSqlError("MyMethod",e);
  // most likely throw a custom exception
}

You'll notice here that the GetBattingCmd and GetPitchingCmd methods accept a transaction as the second argument. Referring to Listing 5-8, this transaction, if instantiated, is associated with the command object using its Transaction property.

However, the transactional behavior of SQLCE differs from SQL Server 2000, and so developers must be aware of four differences. First, SQLCE only supports an isolation level of ReadCommitted, which exclusively locks data being modified in a transaction. As a result, the IsolationLevel property of SqlCeTransaction can only be set to the ReadCommitted value of the IsolationLevel enumeration. Second, SQLCE supports nested transactions, but only up to five levels. Third, SQLCE holds an exclusive lock on any table that has been modified in a transaction.^[13] This means that any attempt to access any data from the table outside the transaction, while it is pending, will result in an exception. Fourth, if a data reader is opened within a transaction, the data reader will automatically be closed if the transaction is rolled back. If the transaction commits, the data reader can still be used.

^[13] As opposed to row- and page-level locks used by SQL Server 2000.

Abstracting .NET Data Providers

As discussed in Chapter 4, applications written with VS .NET 2003 and the Compact Framework can access a SQL Server 2000 server remotely using the SqlClient .NET Data Provider. And, as discussed in this chapter, applications can store data locally in SQLCE using the SqlServerCe .NET Data Provider. In some scenarios, an application may wish to do both, for example, by accessing the remote SQL Server when connected to a corporate LAN via a direct connection, WLAN, or WAN and accessing SQLCE when disconnected.

In these instances, developers can take advantage of the object-oriented nature of the Compact Framework to write code that can be used with either provider. Doing so allows a greater level of code reuse and easier porting of code from the desktop Framework to the Compact Framework. Abstracting data providers is possible since, as mentioned in Chapter 4, all .NET Data Providers are implemented using the same underlying base classes and interfaces. These include the interfaces IDbConnection, IDbCommand, IDataRecord, IDataParameter, IDbDataParameter, IDataParameterCollection, IDataReader, IDataAdapter, IDbDataAdapter, and IDbTransaction, along with the DataAdapter and DbDataAdapter classes, among others, found in the System.Data and System.Data.Common namespaces.

One technique for abstracting the data provider used is to implement the Abstract Factory design pattern documented in the book Design Patterns, as noted in the "Related Reading" section at the end of the chapter. This design pattern allows code to create families of related classes without specifying their concrete classes at design time. In this case, the family of related classes comprises the classes that make up a data provider, including connection, command, data adapter, and parameter.

Although it is possible to use the Abstract Factory pattern as documented in Design Patterns, a slight variant of the pattern, shown in Listing 5-9, is flexible because it allows the data provider to be specified in a shared method of the Abstract Factory class rather than having to be hard-coded at the creation of the class at runtime.

Listing 5-9 Implementing the Abstract Factory Pattern. This listing shows the code necessary to implement the Abstract Factory pattern so that polymorphic code can be written to use either of the data providers that ships with the Compact Framework. Note that the SqlClientFactory class is not shown.

Public Enum ProviderType
    SqlClient = 0
    SqlServerCe = 1
End Enum

Public MustInherit Class ProviderFactory

    Public Shared Function CreateFactory( _
      ByVal provider As ProviderType) As ProviderFactory
        If provider = ProviderType.SqlClient Then
            Return New SqlClientFactory
        Else
            Return New SqlServerCeFactory
        End If
    End Function

    Public MustOverride Function CreateConnection( _
      ByVal connect As String) As IDbConnection
    Public MustOverride Overloads Function CreateDataAdapter( _
      ByVal cmdText As String, _
      ByVal connection As IDbConnection) As IDataAdapter
    Public MustOverride Overloads Function CreateDataAdapter( _
      ByVal command As IDbCommand) As IDataAdapter
    Public MustOverride Overloads Function CreateParameter( _
      ByVal paramName As String, _
      ByVal paramType As DbType) As IDataParameter
    Public MustOverride Overloads Function CreateParameter( _
      ByVal paramName As String, _
      ByVal paramType As DbType, _
      ByVal value As Object) As IDataParameter
    Public MustOverride Function CreateCommand( _
       ByVal cmdText As String, _
       ByVal connection As IDbConnection) As IDbCommand

End Class

Public NotInheritable Class SqlServerCeFactory
   Inherits ProviderFactory

    Public Overrides Function CreateConnection( _
     ByVal connect As String) As IDbConnection
        Return New SqlCeConnection(connect)
    End Function

    Public Overloads Overrides Function CreateDataAdapter( _
      ByVal cmdText As String, _
      ByVal connection As IDbConnection) As IDataAdapter
        Return New SqlCeDataAdapter(cmdText, _
         CType(connection, SqlCeConnection))
    End Function

    Public Overloads Overrides Function CreateDataAdapter( _
      ByVal command As IDbCommand) As IDataAdapter
        Return New SqlCeDataAdapter(CType(command, SqlCeCommand))
    End Function

    Public Overloads Overrides Function CreateParameter( _
      ByVal paramName As String, _
      ByVal paramType As DbType) As IDataParameter
        Return New SqlCeParameter(paramName, paramType)
    End Function

    Public Overloads Overrides Function CreateParameter( _
      ByVal paramName As String, _
      ByVal paramType As DbType, _
      ByVal value As Object) As IDataParameter
        Dim parm As New SqlCeParameter(paramName, paramType)
        parm.Value = value
        Return parm
    End Function

    Public Overrides Function CreateCommand(ByVal cmdText As String, _
      ByVal connection As IDbConnection) As IDbCommand
        Return New SqlCeCommand(cmdText, _
         CType(connection, SqlCeConnection))
    End Function

End Class

As you'll notice in Listing 5-9, the ProviderType enumeration identifies which factory classes are available. The heart of the listing is the abstract (marked as MustInherit in VB and abstract in C#) ProviderFactory class. This class implements a shared method to create an instance of a concrete ProviderFactory class, along with a set of method signatures marked with the MustOverride keyword. This keyword ensures that the class inheriting from ProviderFactory will override the methods to provide an implementation. The SqlServerCeFactory class inherits from ProviderFactory, overriding the base class methods and returning instances of the appropriate SqlServerCe objects (SqlCeConnection, SqlCeDataAdapter, and so forth). Note that the methods of the ProviderFactory class return references to the interfaces implemented by data providers discussed earlier. This is the key to enabling the writing of polymorphic code. Although not shown in the listing due to space constraints, there would, of course, be a corresponding factory class for the SqlClient provider that also inherits from ProviderFactory.

To use the ProviderFactory class, a caller need only instantiate the correct class using the shared method, as follows:

Dim pf As ProviderFactory
If CheckForNetworkConn() Then
   ' Go remote
    pf = ProviderFactory.CreateFactory(ProviderType.SqlClient)
Else
   ' Go local
    pf = ProviderFactory.CreateFactory(ProviderType.SqlServerCe)
End If

In this snippet the CheckForNetworkConn method shown in Chapter 4 is used first to determine if a network connection is available; if so, it uses SqlClient and if not, SqlServerCe. Of course, the value for the ProviderType enumeration could also easily be read from a configuration file or passed into the method as a variable to allow for flexibility.

Once the concrete ProviderFactory has been created, it can be passed into methods like those shown in the listings in this chapter so that the methods can be used against either provider. For example, the ExecDataReader method shown in Listing 5-4 could then be rewritten as shown in Listing 5-10.

Listing 5-10 Using the Abstract Factory Pattern. This method shows the ExecDataReader method rewritten to use an instance of the ProviderFactory class to enable provider-independent database access.

Public Shared Function ExecDataReader(ByVal pf As ProviderFactory, _
   ByVal sql As String, ByVal cn As IDbConnection) As IDataReader

    Try
         ' Create the command
         Dim cm As IDbCommand = pf.CreateCommand(sql, cn)
         If cn.State = ConnectionState.Closed Then
             cn.Open()
         End If

         ' Execute data reader
         Dim dr As IDataReader
         dr = cm.ExecuteReader()
         Return dr
    Catch e As Exception
         LogSqlError("ExecDataReader",e)
         Throw New Exception( _
           "Could not execute data reader for :" & sql, e)
    End Try
End Function

Note that because the ExecDataReader method can now be used with either provider, it returns an object that implements the IDataReader interface and accepts an IDbConnection object, rather than the concrete types for SqlServerCe. In addition, the creation of the SqlCeCommand object has been replaced with a call to the CreateCommand method of the ProviderFactory, and the reference to the SqlCeException object in the Catch block has been replaced with the generic Exception object.^[14]

^[14] An alternative and more dynamic approach to creating an abstract factory class using the runtime type creation methods of the desktop Framework and Compact Framework can be found in Chapter 18 of Teach Yourself ADO.NET in 21 Days.