Once
you have completed configuring the components, you can develop and test
your RDA and Merge Replication applications. We begin with RDA, the
easier to administer but less capable of the two.
1. RDA Capabilities and Overhead
RDA enables a SQL Server CE application to do the following:
Retrieve data and schemas from a SQL Server database into a SQL Server CE database on a device
Add to, remove, or modify that data at the device and have SQL Server CE track those changes
Have
SQL Server CE submit the changed data to the SQL Server database and
receive any exceptions resulting from failed updates on the server
Submit SQL statements directly from the .NET Compact Framework application to a SQL Server database
RDA
is simpler than Merge Replication, but it requires that you write more
code, both C# code and SQL code. It also requires that you use an
ADO.NET class SqlCeRemoteDataAccess class. when we accessed a SQL Server database: the
RDA uses a concurrency model that the online documentation calls optimistic concurrency.
The meaning of optimistic concurrency in RDA is different from the
meaning known by many veteran SQL Server programmers. In RDA,
optimistic concurrency means the following activities take place.
Pull data from the server to the device.
Modify the data on the device.
Submit data changes to the server.
The server applies the changes even if the data on the server has been modified since it was pulled to the device.
So, you need to adopt a new mindset if you are used to optimistic concurrency meaning the following.
Retrieve data into the application.
Modify data within the application.
Submit data changes to the server.
The server does not apply the changes if the data on the server has been modified since it was read by the application.
For instance, the following RDA scenario causes one update to overlay another.
| 08:00 | Client A pulls data from the server. |
| 08:00 | Client B pulls data from the server. |
| 12:00 | Client A updates row X on device A. |
| 12:00 | Client B updates row X on device B. |
| 18:00 | Client A pushes updates to the server. |
| 19:00 | Client B pushes updates to the server. |
Client B’s update overlays Client A’s update. No warning, error, exception, or conflict occurs.
The following change to the scenario causes B’s update to fail.
| 12:00 | Client A deletes row X. |
In
the new scenario, there is no row X on the server at 19:00, and B’s
update fails. When this failure occurs, RDA logs the failure in the
local database. It is up to the client application to make whatever
changes to local data that it feels are appropriate and to notify the
user of the failure.
2. Programming for RDA
The primary ADO.NET class for RDA programming is SqlCeRemoteDataAccess. This class has three methods, corresponding to the three capabilities mentioned earlier (SubmitSQL, Pull, and Push),
plus additional methods inherited from its base classes. It also has a
set of properties for providing connection information. Table 1 lists the main methods and properties of the SqlCeRemoteDataAccess class.
Table 1. Methods and Properties of the SqlCeRemoteDataAccess Class
| Name | Comment |
|---|
| Methods |
| Pull | Submits a SELECT
statement to a SQL Server database. Creates a table and indexes in a
SQL Server CE database to hold the selected data. Retrieves the
selected rows and inserts them into the table. Initiates tracking of
changes made to the selected data |
| Push | Submits
changes made to previously pulled data back to the SQL Server. Receives
errors from the SQL Server and stores them in an application-specified
error table |
| SubmitSQL | Submits SQL statements to a SQL Server |
| Properties |
| InternetLogon | Internet username |
| InternetPassword | Internet user password |
| InternetProxyLogon | Proxy username |
| InternetProxyPassword | Proxy password |
| InternetProxyServer | Proxy server name |
| InternetURL | URL
of the sscesa20.dll file at a Web site (i.e., the virtual directory
specified in the Virtual Directory Alias and Content Folder dialog box
of the Virtual Directory Creation Wizard |
| LocalConnectionString | Connection string for the SQL Server CE database |
2.1. Pulling Data
The Pull method submits a SQL SELECT
statement to the SQL Server for processing. The method creates the
table to hold the returned rows; that table must not already exist.
Some of the information the Pull
method needs is specified by setting properties before invoking the
method; some is supplied through the parameters, which are shown in Table 2.
Table 2. Parameters of the Pull Method
| Parameter | Comment |
|---|
| localTableName | Name of the SQL Server CE table to be created to receive the output of the SELECT statement |
| sqlSelectString | The SELECT statement |
| oledbConnectionString | The connection string used to access the SQL Server database |
| trackOptions | An
enumerated indicator specifying whether changes to the data in the
table should be tracked so that they can be subsequently pushed back to
the SQL Server. Also specifies whether indexes should be created on the
local table |
| errorTable | The name of a table to be created in the local database that holds errors returned by subsequent pushes |
For example, the code shown in Listing 8.1 pulls information from the Northwind Categories table and stores it in a newly created table that is also to be named Categories. Listings 1 through 5 show the code that you can find in the RDA project at the book’s Web site.
Listing 1. An RDA Pull
// The database file
private string strDBFile = @"My Documents\Northwind.sdf";
// The local connection string
private string strConnLocal =
"Data Source=" + @"My Documents\Northwind.sdf";
// The remote connection string
private string strConnRemote = "Provider=sqloledb; "
+ "Data Source=Snowden; "
+ "Initial Catalog=Northwind; "
+ "Integrated Security=SSPI;";
// The URL
private string strURL =
"http://207.202.168.30/YaoDurantRDA/sscesa20.dll";
private void mitemPull_Click(object sender, EventArgs e)
{
// Create an RDA object.
SqlCeRemoteDataAccess rdaNW = new SqlCeRemoteDataAccess();
try
{
// Have RDA:
// Create local tables named Categories and
// ErrorCategories.
// Connect to the remote server and submit the
// SELECT statement.
// Place the results in the local Categories table.
rdaNW.LocalConnectionString = strConnLocal;
rdaNW.InternetUrl = strURL;
rdaNW.InternetLogin = "";
rdaNW.InternetPassword = "";
rdaNW.Pull("Categories",
"SELECT CategoryID, CategoryName " +
" FROM Categories",
strConnRemote,
RdaTrackOption.TrackingOnWithIndexes,
"ErrorCategories");
}
catch(SqlCeException exSQL)
{
HandleSQLException(exSQL);
}
finally
{
rdaNW.Dispose();
}
|
2.2. Fixing the IDENTITY Property
When
you pull a table from SQL Server down to your device using RDA, the
schema of that table is brought with the data so that the table can be
created on the device. That schema might be incorrect regarding the IDENTITY property. If the SQL Server table has an IDENTITY property column, the new SQL Server CE table also has that column designated as an IDENTITY property column, but its seed and increment are always set to (1,1). You must initially correct them to the values you want, as shown in Listing 2.
Listing 2. Setting the IDENTITY Property Column’s Seed Value
// The IDENTITY property seed value of the new table
// is at 1, even after the retrieved rows have
// been added to the table. "Fix" it.
SqlCeConnection connLocal = new SqlCeConnection(strConnLocal);
connLocal.Open();
SqlCeCommand cmndLocal = new SqlCeCommand();
int intMaxCategoryID;
try
{
cmndLocal.Connection = connLocal;
// Retrieve the highest CategoryID in the table.
cmndLocal.CommandText =
"SELECT max(CategoryID) FROM Categories";
string strMaxCategoryID =
cmndLocal.ExecuteScalar().ToString();
intMaxCategoryID = int.Parse(strMaxCategoryID);
// Set the seed one higher.
cmndLocal.CommandText =
"ALTER TABLE Categories " +
"ALTER COLUMN CategoryID IDENTITY (" +
(intMaxCategoryID + 1).ToString() + ",1)";
cmndLocal.ExecuteNonQuery();
}
catch( SqlCeException exSQL )
{
HandleSQLException(exSQL);
}
finally
{
connLocal.Close();
}
|
Be aware that the auto-generated CategoryID value of any rows that you insert into the local Categories table is always overridden with a new value when those rows are pushed to the host server.
2.3. Viewing the Pulled Schema
If
you use RDA to retrieve data from the desktop and you plan to propagate
local changes to that data back to the desktop server, you must specify
one of the change-tracking enumerations in the fourth parameter when
you pull the data.
Two of the tracking options also specify that indexes are to be created on the local table. The TrackingOnWithIndexes
option adds yet one more index to your table. Do your own performance
testing to determine whether your application benefits from the
indexes. Using either tracking option adds the two extra columns shown
in Figure 5 to your database. Using the TrackingOnWithIndexes option adds the extra index also shown in Figure 5.
2.4. Modifying Pulled Data Locally
The
connection to SQL Server is open only while the data is being pulled.
When the data has been transferred to the SQL Server CE data table, the
SQL Server connection is closed. Once you have pulled the data and, if
necessary, altered the IDENTITY property.
Listing 3. Modifying Pulled Data at the Device
private void mitemUpdate_Click(object sender, EventArgs e)
{
SqlCeConnection connLocal =
new SqlCeConnection(strConnLocal);
connLocal.Open();
SqlCeCommand cmndLocal = new SqlCeCommand();
try
{
cmndLocal.Connection = connLocal;
cmndLocal.CommandText =
"UPDATE Categories " +
" SET CategoryName = 'new Name' " +
" WHERE CategoryID = 2";
cmndLocal.ExecuteNonQuery();
cmndLocal.CommandText =
"DELETE Categories WHERE CategoryID = 3";
cmndLocal.ExecuteNonQuery();
cmndLocal.CommandText =
"INSERT Categories (CategoryName) " +
" VALUES ('new Category I') ";
cmndLocal.ExecuteNonQuery();
}
catch( SqlCeException exSQL )
{
HandleSQLException(exSQL);
}
finally
{
cmndLocal.Dispose();
connLocal.Close();
}
}
|
Figure 6 shows the Categories data after modification. The row for CategoryID = 3 has been deleted, the row for CategoryID = 2 has been modified, and a new row with CategoryID = 10 has been added.
The inserted and modified rows are marked in the s_Operation column; the deleted row is represented in the MSysRDATombstone table.
2.5. Pushing Data
To propagate the table’s changes back to the desktop SQL Server, you use the Push method of the SqlCeRemoteDataAccess class. This is shown in Listing 4.
Listing 4. An RDA Push
private void mitemPush_Click(object sender, EventArgs e)
{
// Create an RDA object.
SqlCeRemoteDataAccess rdaNW =
new SqlCeRemoteDataAccess();
try
{
// Have RDA:
// Create local tables named Categories and
// ErrorCategories.
// Connect to the remote server and submit
// the changes.
rdaNW.LocalConnectionString = strConnLocal;
rdaNW.InternetUrl = strURL;
rdaNW.InternetLogin = "";
rdaNW.InternetPassword = "";
rdaNW.Push("Categories", strConnRemote);
}
catch( SqlCeException exSQL )
{
HandleSQLException(exSQL);
}
finally
{
rdaNW.Dispose();
}
}
|
After the Push
method completes, you can detect the rows that failed to update at the
server due to concurrency conflicts by accessing the local table that
was specified as a Pull method parameter. In our case, this was the ErrorCategories table, shown in Figure 10, specified as the last parameter in the following code snippet extracted from Listing 1.
rdaNW.Pull("Categories",
"SELECT CategoryID, CategoryName " +
" FROM Categories",
strConnRemote,
RdaTrackOption.TrackingOnWithIndexes,
"ErrorCategories");
Figure 7 shows the contents of the Categories table that we pulled to the local database.
When we push the changes back to the SQL Server database, using the code in Listing 8.4, the following activities occur.
The modified row from our local database is modified on the server.
The deleted row in our local database fails to delete because of a foreign key violation; there are some Confections products in the remote database, so the category cannot be deleted.
The inserted row from our local database fails to insert because a CategoryId = 10 row already exists in the remote database. Perhaps some other user added this row; perhaps we added it with a SubmitSQL method call.
This can be verified by viewing the contents of the Categories table on the server after the push has completed, as shown in Figure 8.
When the Push method executed, it raised the exception shown in Figure 9. As a result of the exception, the rows shown in Figure 10 were added to the SQL Server CE ErrorCategories table.
There
is no automatic conflict resolution in RDA. You must examine the failed
rows placed in the error table and decide what changes to make to the
data on your device as a result of the failed server updates.
2.6. The SubmitSQL Method
The SubmitSQL
method is pretty self-explanatory. It bypasses the SQL Server CE
database completely and simply submits a SQL statement to the SQL
Server for processing. This method is used for submitting updates to
the server. (If you submit a SELECT statement, no exception will be thrown, but you will not accomplish anything.) For example, Listing 5 shows the code used to insert a row into the remote Categories table; in fact, it was one of the rows that caused our subsequent push to fail.
Listing 5. Executing the SubmitSQL Method
private void mitemSubmitSQL_Click(object sender, EventArgs e)
{
// Create an RDA object.
SqlCeRemoteDataAccess rdaNW = new SqlCeRemoteDataAccess();
try
{
// Have RDA:
// Create local tables named Categories and
// ErrorCategories.
// Connect to the remote server and submit
// the changes.
rdaNW.LocalConnectionString = strConnLocal;
rdaNW.InternetUrl = strURL;
rdaNW.InternetLogin = "";
rdaNW.InternetPassword = "";
rdaNW.SubmitSql(
"INSERT Categories (CategoryName, Description)" +
"VALUES ('New Category II', 'From SubmitSQL')",
strConnRemote);
}
catch( SqlCeException exSQL )
{
HandleSQLException(exSQL);
}
finally
{
rdaNW.Dispose();
}
}
|
As is the case with the Push and Pull methods, the SubmitSQL
method requires that you set property values with the information
appropriate to your Web site configuration and that you correctly code
your SQL statements. Once you have done that, transferring the data is
a matter of a single method call.
As
we are about to see, this emphasis on providing connection information
through property values and the need to understand database technology
continues into Merge Replication. But when you understand how your
application connects to the server and how to request data from the
server, you will be able to transfer data with even less code than RDA
requires.
