RDA

The first of the two synchronization mechanisms made available through SQLCE is RDA. This mechanism provides simple client push-and-pull functionality along with the ability to submit statements to a back-end data store using OLE DB. In a nutshell, the client agent provides an API that applications can use to submit requests over HTTP to the server agent. Compact Framework developers access this API through the SqlCeRemoteDataAccess class exposed by the SqlServerCe .NET Data Provider.^[6] The server agent then uses a connection string sent from the client to initiate a connection to SQL Server 7.0 (service pack 4 or later) or 2000 and forwards the request to the data store as shown in Figure 7-2. The request is then processed by the SQL Server, and the results (rows, errors, messages), if any, are sent back to the device by the server agent. The SQLCE database engine manages the rows returned from an RDA pull request and can even track which rows in the table have been pulled from the remote data source so that they can be sent back during a push request.

^[6] eMbedded Visual Tools developers can use RDA through the RDA COM object by referencing the SQL Server CE Control 2.0 library in eVB or by including the appropriate files in an eVC++ project.

In the remainder of this section, the scenarios applicable to RDA will be discussed, along with an overview of RDA security and examples of how it can be used in a Compact Framework solution.

Features and Scenarios

RDA includes several features that make it attractive as a part of a solution:

• Reduced management: Unlike merge replication discussed later in this chapter, RDA requires no special configurations on the back-end server. For example, a publication needn't be created on SQL Server 2000 in order for rows to be pulled down to a device, updated, and later pushed back to the server using RDA. This lessens the administrative burden for applications using RDA.

• Simplicity: Because connectivity to the data store is managed by the server agent and the SQLCE database engine tracks pulled rows, the API for using RDA is extremely simple and consists of just seven properties, three methods, and two enumerations in the Compact Framework, making it very easy to implement. In fact, when making a pull request, the target table in SQLCE is automatically created, and RDA can even create the appropriate index on the table when pulled from SQL Server 2000.

• Scalability: Because there is no extra processing (change tracking and conflict detection and resolution) on the server side, as there is with merge replication, RDA provides for better scalability.

However, while RDA provides certain advantages, it is more limited than a full merge-replication solution in three specific ways:

After boiling down the features and limitations of RDA, it becomes apparent that RDA is most useful in the following scenarios:

• Data loading: Because RDA pulls data from SQL Server and automatically creates the appropriate tables in SQLCE, complete with indexes, it is obviously useful for populating a SQLCE database with tables used for lookups in an application such as a price list or product information list. This might occur the first time a user starts a particular application or at predefined intervals in order to refresh a relatively static set of data. As mentioned in Chapter 5, RDA would also be appropriate for prebuilding SQLCE databases that are later deployed to devices via a LAN/WAN or storage device.

• Data capture: Some smart device applications are used simply to collect data that has no corresponding row on the server. For these scenarios, an empty row set can initially be pulled from the data store in order to set up tracking in the SQLCE table,^[7] and then periodically, the new rows can be pushed back to SQL Server using RDA. In these sorts of scenarios, sometimes the data ultimately does not end up in a SQL Server, but in Oracle or DB2. For these scenarios you can think of the SQL Server as an intermediate data collection point from which you can then extract the data using SQL Server's Data Transformation Services (DTS), linked tables, or heterogeneous replication^[8] to load into the main repository.

  ^[7] RDA requires that for a table to be pushed back to a data store, it must have originally been created through a Pull request so that tracking in the SQLCE engine could be enabled.

  ^[8] Heterogenous replication is a replication option in SQL Server 7.0 and 2000 that allows any OLE DB data source to act as a subscriber to a SQL Server publication. See the SQL Server online help for more information.

• Working with highly partitioned data: Because RDA does not include sophisticated conflict resolution, it works well in scenarios where data is naturally partitioned and few conflicts are expected. For example, a set of clients assigned to a particular account manager could be pulled to the device without fear of other users updating the client exchanges before the account manager pushes the data back to the data store.

• Connected data manipulation: In addition to the push-and-pull functionality, like the SqlClient .NET Data Provider discussed in Chapter 4, RDA supports the ability to send any statement to a data store that does not return rows. This allows an application to make modifications directly to the data store at any time and not have to wait for a specific synchronization. This could be used on a shop floor, for example, where the device is always connected to a WLAN; however, in practice, the SqlClient provider would likely be the first choice for such a scenario.

Configuration

As mentioned previously, in order for either RDA or merge replication to work, the server agent must be installed on IIS, and one or more virtual directories would have to be created. Once accomplished, there are two other configuration issues to consider: security and logging.

Security

In order for requests initiated on the device by the client agent to access data successfully on the SQL Server, authentication and authorization must take place. The three layers of authentication that are supported include authenticating through a proxy server, to the IIS computer, and finally to SQL Server itself.

• Proxy server: The credentials for the proxy server can be specified using the InternetProxyServer, InternetProxyLogin, and InternetProxyPassword properties of the SqlCeRemoteDataAccess object. A proxy server or firewall cannot be used when integrated Windows authentication is used in IIS; so, integrated Windows authentication is most often used in intranet scenarios when the device will connect via a LAN. The InternetProxyServer property must also be used when using SQL Server CE Relay to connect to SQL Server via older versions of ActiveSync.^[9]

  ^[9] In this case, the property is set to ppp_peer:<client port number>.

• IIS: The location of the IIS server and its credentials for authentication are specified using the InternetURL, InternetLogin, and InternetPassword properties of the SqlCeRemoteDataAccess object. These are used when IIS is configured for either basic or integrated Windows authentication; however, as with typical Web applications, basic authentication will send the password as cleartext, and so SSL is recommended to encrypt the entire channel during communication. To use SSL, a server certificate must be installed on the configured virtual directory in the same fashion as on any other Web site. Windows CE maintains a database of trusted certificate authorities (CA) and consults this database when SSL connections are made. If your organization uses its own CA, you must perform a one-time update of the database of CAs on each device with your own root certificate, as described in the SQLCE Books Online.^[10] The IIS credentials are not required if anonymous authentication is used. As mentioned previously, regardless of the authentication mode in use, the account that is authenticated (either the user's account or the group, if using basic or integrated Windows, or the IUSR_computername account) must also be granted read and execute NTFS permissions to the server agent DLL in the virtual directory and read and write permissions to the folder itself, which can be done with the SQL Server CE Connectivity Management MMC snap-in shown in Figure 7-1.

  ^[10] The topic that describes this is called "Updating the Database of Trusted Certificate Authorities on a Windows-CE based Device" in the SQLCE Books Online.

• SQL Server: The SQL Server credentials can be specified in the connection string (formulated as an OLE DB connection string) passed to the Push, Pull, and SubmitSQL methods of the SqlCeRemoteDataAccess object using the user id and password properties, if SQL Server authentication is used, and the Integrated Security= SSPI setting, if integrated Windows authentication is used. When using integrated Windows authentication in SQL Server, however, the token that IIS will send to SQL Server depends on the IIS authentication mode. For example, if anonymous is activated, then the guest account's token configured in IIS (typically computername\IUSR_computername) is used. If basic or integrated Windows authentication is configured in IIS, then a token created with the InternetLogin specified on the device is passed to SQL Server. The caveat is that the SQL Server must reside on the same machine as IIS if integrated Windows authentication is used in IIS because the token is not able to pass between machines.^[11] Of course, then within SQL Server the token passed from IIS would need the appropriate database access, including a login and permissions to access the appropriate tables. If possible, this should be done using group accounts rather than user accounts for ease of administration.

  ^[11] Delegation of trusted accounts can only occur using Kerberos, which is not supported by SQLCE.

The end result would be typical configurations like those shown in Table 7-1, which lists the considerations for the various intersections of IIS and SQL Server security.

For a technique that shows how the relevant properties might be configured, consider the code in Listing 7-1.

Listing 7-1 Setting Security Properties. This listing shows a helper class that can be created to set the various properties required by RDA. This class also ensures that only one instance of the SqlCeRemoteDataAccess class will be created in the application.

Namespace Atomic.SqlCeUtils

    Public NotInheritable Class RDA

        Public Shared ReadOnly Instance As RDA = New RDA
        Private Shared _rda As SqlCeRemoteDataAccess
        Private _sqlCePath As String

        Private Sub New()
            ' Private, so no public instance can be created
        End Sub

        Shared Sub New()
            ' Read the default values from a config file or the registry
            ' and use the appropriate constructor
            _rda = New SqlCeRemoteDataAccess
        End Sub

        Public ReadOnly Property SqlCeRDA() As SqlCeRemoteDataAccess
            Get
                Return _rda
            End Get
        End Property

        Public Property URL() As String
            Get
                Return _rda.InternetUrl
            End Get
            Set(ByVal Value As String)
                _rda.InternetUrl = Value
            End Set
        End Property

        Public Property Login() As String
            Get
                Return _rda.InternetLogin
            End Get
            Set(ByVal Value As String)
                _rda.InternetLogin = Value
            End Set
        End Property

        Public Property Password() As String
            Get
                Return _rda.InternetPassword
            End Get
            Set(ByVal Value As String)
                _rda.InternetPassword = Value
            End Set
        End Property

        Public Property SqlCePath() As String
            Get
                Return _sqlCePath
            End Get
            Set(ByVal Value As String)
                _sqlCePath = Value
                _rda.LocalConnectionString = _
        "Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0;Data Source=" & Value
            End Set
        End Property

        Public ReadOnly Property SqlCeConnectionString() As String
            Get
                Return _rda.LocalConnectionString
            End Get
        End Property

       Public Property SqlConnectionString() As String
         Get
             Return _sqlconn
         End Get
         Set(ByVal Value As String)
             _sqlconn = Value
         End Set
      End Property

     Public Property ProxyServer() As String
            Get
                Return _rda.InternetProxyServer
            End Get
            Set(ByVal Value As String)
                _rda.InternetProxyServer = Value
            End Set
        End Property

        Public Property ProxyLogin() As String
            Get
                Return _rda.InternetProxyLogin
            End Get
            Set(ByVal Value As String)
                _rda.InternetProxyLogin = Value
            End Set
        End Property

        Public Property ProxyPassword() As String
            Get
                Return _rda.InternetProxyPassword
            End Get
            Set(ByVal Value As String)
                _rda.InternetProxyPassword = Value
            End Set
        End Property

        Public Sub SaveSettings()
            ' Save the configuration properties
        End Sub
    End Class

End Namespace

In Listing 7-1, the Atomic.SqlCeUtils.RDA class is a sealed class that uses the Singleton design pattern^[12] and composition to expose an instance of the SqlCeRemoteDataAccess class using the Instance read-only field. Using this pattern all of the code in the application can use the same instance of the class, and it can be configured automatically the first time it is used. This can be accomplished via the registry or a configuration file, which is read in the shared constructor. Because the various security properties are exposed as read/write, they can also be configured through a UI in the application or set programmatically on the instance. You can see that the class also exposes a SaveSettings method to save the configuration settings back to the registry or config file.

^[12] For more information, see the MSDN article referenced at the end of this chapter.

Although it is not discussed earlier, the SqlCeRemoteDataAccess class also needs the connection string to the local SQLCE database, here exposed as the SqlCeConnectionString property. A C# client using this class can then programmatically change some of the settings and default the others as follows:

RDA.Instance.Password = txtPwd.Text;
RDA.Instance.Login = txtUser.Text;
RDA.Instance.SqlCePath = FileSystem.RuntimeFolder + "\\mylocaldb.sdf";
RDA.Instance.URL = "http://myserver/virtualdirectory/sscesa20.dll";

Alert readers will notice that the class contains a read-only property to expose the SqlCeRemoteDataAccess object itself. Although this can be done, a more encapsulated solution would be to add methods to the RDA class to support the Push, Pull, and SubmitSQL functionality of the SqlCeRemoteDataAccess class. In this way, the RDA class acts as a complete wrapper for RDA, and parameters to the various methods such as the connection string to the remote SQL Server can be populated automatically.

Logging

Although RDA will report conflicts on the client if configured to do so, it is also important to log information on the server because it will enable administrators to diagnose problems more easily. To enable logging by the server agent, a particular registry key (HKLM\Software\Microsoft\MSSQLSERVERCE\Transport) and value, pointing to the local path of the virtual directory, must be created on the IIS computer. After IIS is restarted, an XML log file (Sscerepl.log) will be created in the virtual directory. The log file can contain three levels of information specified by the registry value: errors only; errors and warnings; and errors, warnings, and informational messages. The last is recommended only when attempting to troubleshoot a particular problem and contains various timings that are defined in the Books Online.

The log file contains a series of <STATS> elements that are written to the file every few minutes and contain any activity for either RDA or merge replication during that time.

Using RDA

The API for the SqlCeRemoteDataAccess object mimics that of the RDA object available to eVB and eVC developers and simply contains the Pull, Push, and SubmitSQL methods.

Pull

The Pull method executes any valid Transact-SQL statement on the SQL Server, retrieves the data, creates the associated table in SQLCE from the result set, optionally turns on client tracking in SQLCE, and optionally defines the error table in SQLCE, which will be created during the Pull operation and populated if errors are encountered when the data is pushed back to the server.

Typically, developers would want the SQL statement passed to the Pull method to include a WHERE clause or to call a stored procedure that includes one. This ensures that only a subset of the server table is retrieved so as not to overload the device and can be used to partition the data horizontally based on the identity of the user, customer, geography, or some other partitioning column. Data can also be partitioned vertically only by including a subset of the columns from the table in the SELECT clause.

The SQL statement can also return rows from multiple tables generated through a JOIN clause, although doing so does not allow SQLCE to track the changes made to the rows, and so the table cannot be updated using the Push method. This is the case with any nonupdatable result set returned from the Pull method, and so in these cases, the tracking option (defined as an enumeration) passed to the method must be set to RdaTrackOption.TrackingOff. Creating a nonupdatable result set is useful for static lookup data (product and location codes, for example), and the judicious use of JOINs can increase performance by allowing data to be retrieved in one roundtrip to the database server, rather than in two or more.

If the result set is updatable and the table has a primary key defined, tracking can be enabled either with or without other indexes in SQLCE. If indexes are enabled, then the same indexes that exist on the SQL Server table will be created on the SQLCE table, provided that the SELECT clause contains the indexed column or, in the case of a composite index, all of the columns that make up the index. This can obviously improve performance for queries on the device (especially if the Seek method is used as described in Chapter 5) but should be considered in light of how the SQLCE database will be used by the application. Creating unnecessary indexes will serve only to slow performance and consume memory on an already constrained device. In either case, the primary key constraint will be created in SQLCE. With tracking enabled, SQLCE will create two system columns on the table that are used to track the changes made to each row.^[13]

^[13] S_BinaryKey and S_Operation. These columns are protected by the SQLCE engine.

Of course, as mentioned previously, the server agent must map the data types returned from SQL Server to those supported by SQLCE. If the result set contains an unsupported data type (such as timestamp), an exception will be thrown by the SqlServerCe Data Provider.

Because of the fairly simple nature of the Pull method, it is relatively easy for developers to implement. For example, the class in Listing 7-1 could be augmented to expose a series of overloaded Pull methods as shown in Listing 7-2.

Listing 7-2 Calling the Pull Method. This listing augments the RDA class shown in Listing 7-1 and includes overloaded methods used to call the Pull method.

Public Overloads Sub Pull(ByVal sqlCeTable As String, _
 ByVal sqlString As String)
    ' Assume no tracking and no indexes
    _pull(sqlCeTable, sqlString, RdaTrackOption.TrackingOff, Nothing)
End Sub

Public Overloads Sub Pull(ByVal sqlCeTable As String, _
 ByVal sqlString As String, ByVal indexes As Boolean)
    ' Check for using indexes
    If indexes Then
        _pull(sqlCeTable, sqlString, _
          RdaTrackOption.TrackingOffWithIndexes, Nothing)
    Else
        _pull(sqlCeTable, sqlString, _
          RdaTrackOption.TrackingOff, Nothing)
    End If
End Sub

Public Overloads Sub Pull(ByVal sqlCeTable As String, _
 ByVal sqlString As String, ByVal indexes As Boolean, _
 ByVal errorTable As String)

    ' Validate the error table name and check for using indexes
    If errorTable Is Nothing OrElse errorTable.Length = 0 Then
        Throw New ArgumentNullException("Must supply an error table.")
    End If

    If indexes Then
        _pull(sqlCeTable, sqlString, _
          RdaTrackOption.TrackingOnWithIndexes, errorTable)
    Else
        _pull(sqlCeTable, sqlString, _
          RdaTrackOption.TrackingOn, errorTable)
    End If
End Sub

Private Sub _pull(ByVal sqlCeTable As String, _
  ByVal sqlString As String, ByVal tracking As RdaTrackOption, _
  ByVal errorTable As String)

    If sqlCeTable Is Nothing OrElse sqlCeTable.Length = 0 Then
        Throw New ArgumentNullException( _
         "SQLCE table must be specified.")
    End If
    If sqlString Is Nothing OrElse sqlString.Length = 0 Then
        Throw New ArgumentNullException( _
          "A SQL statement must be specified.")
    End If

    ' Add the tracked table to the arraylist
    If (tracking = RdaTrackOption.TrackingOn Or _
      tracking = RdaTrackOption.TrackingOnWithIndexes) Then
        _pulledTables.Add(sqlCeTable)
    End If

    ' Call the actual pull method
    Try
        _rda.Pull(sqlCeTable, sqlString, _
          Me.SqlConnectionString, tracking, errorTable)
    Catch e As SqlCeException
        ' Log the error
        LogSqlError("RDA.Pull",e)
        If e.HResult = &H80004005 Then
            ' Table already exists
            ' Wrap the exception in a custom object with the message
        Else
            ' Other errors
            ' Wrap the exception in a custom object with the message
        End If
    End Try
End Sub

You'll notice that Listing 7-2 includes three overloaded Pull methods. These methods accept the SQLCE table to be created, the SQL statement to execute against the SQL Server, the name of the error table to create if errors are generated when the data is synchronized, and, finally, whether indexes should be created on the device. The three public methods are responsible for validating the arguments and then delegating the actual work to the private _pull method and responsible for calling the Pull method of the SqlCeRemoteDataAccess object and handling any exceptions. As discussed in Chapter 5, if an exception does occur, a best practice is to wrap the exception in a custom exception class derived from System.ApplicationException and add custom error messages and other information before throwing the exception back to the caller.

A C# developer using these methods could then write the following code to retrieve a list of teams from a SQL Server and place them in the Teams table in SQLCE:

string teamSql = "exec usp_GetTeams";
RDA.Instance.Pull("Teams", teamSql, true);

While the Pull method is very simple to use, it does have several minor limitations:

• Computed columns are not allowed in the SELECT clause, or an exception will be thrown.

• Columns with the attribute ROWGUIDCOL are not allowed and must be excluded from the SELECT clause.

• Data cannot be pulled from a table having a primary key of type char, nchar, varchar, or nvarchar, if they have a length greater than 255 characters.^[14]

  ^[14] This is because those data types will be mapped to the ntext data type, and a primary key cannot be created on ntext.

• SQLCE is not case sensitive, and so, if the SQL Server uses a case-sensitive collation, developers will need to be aware that queries used on the server (or stored procedures invoked to pull rows down to the device) may not return the same data as those on the device.

• The source table on SQL Server cannot be dropped, renamed, have its primary key dropped, or have any columns added, renamed, or dropped if the SQLCE table is tracking changes and will later be synchronized.

Push

After an application creates tracked SQLCE tables and allows the user to manipulate them, changes to those tables can be synchronized with the SQL Server using the Push method of the SqlCeRemoteDataAccess class. This method simply accepts the SQLCE table to synchronize, the OLE DB connection string for the SQL Server, and a value that specifies whether the changes are to be applied within the context of a single transaction or individual transactions for each row.

Following the previous example, the methods in Listing 7-3 could be added to the RDA class to support synchronizing a table with the SQL Server.

Listing 7-3 Calling the Push Method. This listing augments the RDA class shown in Listing 7-1 and includes overloaded methods used to call the Push method to synchronize changes on the device with SQL Server.

Public Overloads Sub Push(ByVal sqlCeTable As String)
    _push(sqlCeTable, False)
End Sub

Public Overloads Sub Push(ByVal sqlCeTable As String, _
  ByVal batch As Boolean)
    _push(sqlCeTable, batch)
End Sub

Private Sub _push(ByVal sqlCeTable As String, ByVal batch As Boolean)

   Dim bOpt As RdaBatchOption
   If batch Then
        bOpt = RdaBatchOption.BatchingOn
   Else
        bOpt = RdaBatchOption.BatchingOff
   End If

   If sqlCeTable Is Nothing OrElse sqlCeTable.Length = 0 Then
        Throw New ArgumentNullException( _
         "SQLCE table must be specified.")
   End If

   Try
        _rda.Push(sqlCeTable, Me.SqlConnectionString, bOpt)
   Catch e As SqlCeException
        ' Log the error
        LogSqlError("RDA.Push",ex)
       ' Wrap the exception and throw it back to the caller
   End Try
End Sub

Once again, the two overloaded public methods expose the SQLCE table that is to be synchronized and an optional argument that determines whether the changes should be made in the context of a single transaction. If the batching option is used, then the server agent starts a transaction on the SQL Server before issuing any INSERT, UPDATE, or DELETE statements. If any errors occur, the server agent rolls back the entire transaction. When not in batch mode, each row that is synchronized is treated as an independent or implicit transaction. In either case, all rows that cause errors when synchronized with SQL Server are copied to the error table specified in the method, along with the OLE DB error message, the time the error occurred, and the OLE DB error number. This ensures that applications can be aware of all the rows that will have conflicts on the server, even when using batch mode. It is also important to note that because server-level tracking is not supported by RDA, the server agent simply uses optimistic concurrency (it does not lock records when a Pull occurs). As a result, RDA defaults to a "last one in wins" scenario, whereby the last device to synchronize data will have its changes persisted to the database. Once again, this is the reason why RDA is best suited for applications whose data is naturally well partitioned.

It is important to note that the error table is cleaned out before each Push method, and so, it will reflect only the errors from the most recent synchronization. In addition, when not in batch mode, rows that cause errors will be deleted from the tracked SQLCE table after being added to the error table. This implies that applications must be designed to read from the error table, allow the user to fix the data, and subsequently merge the data back into the original table to be pushed back to the server at a later time. However, because sophisticated conflict resolution is not supported by RDA, the majority of the errors encountered will likely be caused by connectivity and security problems or rows being deleted on the server.

You could imagine that because the RDA class is wrapping the functionality of the SqlCeRemoteDataAccess object (_rda), the class could be extended to keep track of all the tables that were pulled with tracking enabled and then expose a method to synchronize them all. In fact, the code to track the pulled tables is in the private _pull method shown in Listing 7-2, where tracked tables are added to a private ArrayList. This ArrayList can then be iterated using a For Each loop to synchronize all tables in a PushAll method as shown in Listing 7-4.

Listing 7-4 Grouping Synchronization. This method synchronizes all the tables pulled with tracking in the RDA class by iterating an ArrayList built when the Pull method is called.

Public Sub PushAll(ByVal batch As Boolean)
    ' Push all the tracked tables
    Dim bOpt As RdaBatchOption
    Dim tab As String

    If batch Then
        bOpt = RdaBatchOption.BatchingOn
    Else
        bOpt = RdaBatchOption.BatchingOff
    End If

    Dim tab As String
    Dim sqle As New SqlCEUtilException( _
     "An error occurred pushing multiple tables")
    Dim errors As Boolean = False

    For Each tab In _pulledTables
        Try
            _rda.Push(tab, Me.SqlConnectionString, bOpt)
        Catch ex As SqlCeException
            ' Log the error
            LogSqlError("RDA.Push of table " & tab,ex)
            errors = True
            Dim er As New SqlCeUtilError
            er.Message = "Could not push table " & tab & _
             "[" & ex.Message & "]"
            er.Number = 100
            er.Table = tab
            sqle.Errors.Add(er)
        End Try
    Next
    ' Throw the exception if an error occurred
    If errors Then Throw sqle

End Sub

The PushAll method in Listing 7-2 also ensures that an attempt to synchronize each table is made. If an exception occurs while the table is being processed, it is placed in a custom SqlCeUtilError object and added to a collection of errors in a custom exception class (SqlCeUtilError). If any of the tables has been unsuccessfully synchronized, the method throws the exception after the loop.

You'll notice that this algorithm could be modified to throw the exception on the first table that causes an error, which would be appropriate if the tables are dependent on each other as in the case of order and order details tables.

SubmitSql

The final method exposed by the SqlCeRemoteDataAccess class and the simplest to use is the SubmitSql method. When invoked, the server agent simply executes the Transact-SQL statement passed as an argument to the method against SQL Server. The only caveat is that the method cannot return a result set. The method is quite useful for executing stored procedures in SQL Server to perform data modifications and data cleanup tasks invoked after a successful synchronization. The SubmitSql method of the RDA class developed in this chapter is shown in Listing 7-5.

Listing 7-5 Submitting Transact-SQL. This method submits a SQL statement using the SubmitSql method of the SqlCeRemoteDataAccess class.

Public Sub SubmitSql(ByVal sqlString As String)
    ' Validate the argument
    If sqlString Is Nothing OrElse sqlString.Length = 0 Then
        Throw New ArgumentNullException("Must supply a SQL statement")
    End If

    Try
        _rda.SubmitSql(sqlString, Me.SqlConnectionString)
    Catch ex As SqlCeException
        ' Log the error
        LogSqlError("RDA.SubmitSql with SQL of " & sqlString,ex)
       ' Wrap and throw the exception
    End Try
End Sub