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. 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.
 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.
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:
Although RDA supports the ability to pull a set of rows from a data store and push modified rows back to a data store, it provides only client-side tracking. In other words, as other users make changes to data in a database, the client using RDA will not be aware of which rows have changed and will need to repull the set in order to see the changes. This is contrasted with merge replication where the server tracks changed rows and can send only the changed rows that the client doesn't already have.
Why Only SQL Server?
At first glance, it would seem that RDA would be an ideal technology for allowing SQLCE to interact with database products other than SQL Server; however, it's important to keep in mind that the SQL server agent running on the IIS machine does have to do some work to move data into and out of SQL Server, including mapping the data types between systems when creating SQLCE tables and generating SQL statements as changes are pushed from the device.
As a result, the server agent would need to include special handlers for each OLE DB data source that RDA could use, a task that Microsoft has not embarked on at the time of this writing.
To see how the server agent handles the mapping for SQL Server, see the "Supported Data Types and Data Type Mappings" topic in the SQLCE Books Online.
Closely related to the first item, RDA can support only a limited form of conflict resolution. Basically, as described later in the chapter, RDA uses optimistic concurrency where rows pulled to a device are not locked, and so, other users may modify them on the server. Because the server keeps no record of which rows were pulled, rows pushed from the device may overwrite these changed rows. RDA detects a conflict only if an error occurs while an attempt is made to insert, update, or delete the row.
The pull mechanism in RDA is limited to pulling data into a single SQLCE table at a time. Although the request sent to the data store can use JOIN clauses and even encapsulate a request to a stored procedure, the rows returned are placed into a single table. This means that developers who must work with multiple tables will need to formulate separate requests for each table. This is contrasted with replication, where a smart device application can subscribe to an entire publication, which may include multiple tables.
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, 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 to load into the main repository.
 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.
 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.
It is important to remember that because RDA requires that the table not exist in SQLCE when a pull is done, RDA is not well suited for applications that require incremental updates because RDA would need to pull down the entire table again, rather than simply the new rows.
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.
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.
 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. 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.
 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. 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.
 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.
SQL Server Authentication
Can be used with intranet or Internet (when coupled with SSL). Advantage is that a user's credentials can be passed from the device to the database, and the application would need to collect only one set of credentials.
Can be used with Internet (with SSL) or intranet scenarios. Allows the IIS authentication to be decoupled from SQL Server. All users can connect to SQL Server with the same account, enabling connection pooling. Disadvantage is that SQL Server credentials are stored on the device or two sets of credentials must be captured from the user.
Should be used in intranet scenarios only, where no proxy server is present. Is supported only if IIS is on same computer as SQL Server. Advantage is that no password is passed from the device across the channel.
Should be used for Intranet scenarios, where no proxy server is present. Advantages are that it can be used if the SQL Server does not reside on the same computer as IIS and it decouples the authentication. Disadvantage is that SQL Server credentials must be stored on the device or two sets of credentials must be captured from the user.
Should be used with intranet scenarios only, but a service account should be configured explicitly on IIS to connect to SQL Server. Advantage is that connection pooling can be taken advantage of and no credentials are stored on the device or passed over the channel. This option is not considered secure.
Can be used for intranet or Internet scenarios. Advantage is that it requires only one set of credentials to be captured from the user but is considered less secure than basic authentication because it relies only on SQL Server authentication.
For more information on both RDA and merge-replication security, see the white paper "Security Models for SQL Server 2000 Windows CE Edition 2.0" referenced at the end of this chapter. This white paper leads you through the testing of various security configurations through a sample installation.
For a technique that shows how the relevant properties might be configured, consider the code in Listing 7-1.
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 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.
 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.
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.
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.
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.
Keep in mind that the Transact-SQL statement needn't return any rows. In this case, the table will still be created in SQLCE and can still be tracked for changes by the SQLCE engine. This strategy is intentionally used when the data originates on the device (such as an order-creation process) and is later saved to the SQL Server using the Push method.
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.
 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.
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);
Of course, an alternative approach is simply to expose the SQLCE table name as an argument to the Pull methods and then automatically build the SELECT statement, thereby requiring one less argument to the methods. This comes at the cost of flexibility used to specify the columns and selection criteria.
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.
 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.
Although the SQL Server table's schema must remain unchanged, the indexes, foreign keys, identity columns, and DEFAULT constraints on tracked SQLCE tables can be changed.
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.
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.
Triggers are not supported in SQLCE; however, data modification statements executed against SQL Server during synchronization may cause triggers to fire on the server. For these triggers the SET NOCOUNT ON option should be set so that, if the server sends a message indicating no rows were affected, it won't cause the Push method to throw an exception.
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.
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.
Beware of SQL Injection Attacks
Since the Code Red and Nimbda attacks of 2001, Microsoft has become much more focused on security, as evidenced by their "Trustworthy Computing" initiative. This has led to products that are secure by default and, therefore, reduce the attack surface for applications built by organizations like yours; however, individual developers also need to be aware of secure coding practices.
One such example is protection against SQL injection attacks. These are attacks whereby a hacker attempts to encode valid, but often destructive, SQL in UI elements that are then unwittingly executed by an application to reveal or destroy confidential data. For example, a typical approach is for a hacker to include a Transact-SQL end of literal string character ('), followed by some destructive SQL, then to append a line comment (--) to invalidate any other Transact-SQL that may come later. These attacks can work on both SQL Server and SQLCE.
To avoid SQL injection attacks, developers should code their applications with the cardinal rule, don't trust user input, in mind. This means that anywhere an application embeds user input in a SQL statement, that input should be checked for validity. For example, to avoid the particular situation just discussed, the developer could replace all single quotes the user entered with two single quotes. Another more generic approach is to use the classes of the System.Text.RegularExpressions namespace to validate the user input using regular expressions so that special characters can be disallowed.
Although most Compact Framework applications are not as inherently prone to such attacks as public Web sites because of their lower profile (the applications are typically used by a smaller user base, only when the application is installed on the device), Compact Framework developers using RDA should still take note. This is because the Pull and SubmitSql methods simply accept SQL statements and do not use command and parameter objects, as do the SqlClient and SqlServerCe Data Providers (discussed in Chapters 4 and 5), which inherently protect against such attacks.
For more information on secure coding practices, see Howard and LeBlanc's book referenced in the "Related Reading" section at the end of this chapter.
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.
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