2. Configuring Merge Replication
We have completed the necessary steps that lead up to configuring Merge Replication, as summarized here.
1. | The configuration of an IIS site is complete.
|
2. | The necessary domain users and groups have been added.
|
3. | The necessary SQL Server logins and users have been added.
All Northwind employees have been added as SQL Server logins. (An alternative scenario would have been to add them as domain groups and users and use Windows Authentication, but we wanted to keep the focus on SQL Server, not on domain management.) The logins have been added as users of the Northwind database and given the necessary permissions.
|
4. | The publication has been designed but not implemented.
It consists of the tables and columns shown earlier in Figure 1. It contains a dynamic filter to limit the data to that which is appropriate for the user executing the synchronization.
|
We are ready to define
our publication. Fortunately, SQL Server’s Enterprise Manager console
snap-in has the Create Publication Wizard to help us. We run it by
selecting <Server Name> | Databases | Northwind | Publications in
the Enterprise Manager’s Tree window, right-clicking on it, and choosing
New Publication from the pop-up menu. After working through about
twenty dialog boxes, including the specification of dynamic filters and
expanded (join) filters, we have defined our publication.
If we need to modify the
definition of our publication once we have completed the Create
Publication Wizard, we can do so by right-clicking on the publication
name in the Enterprise Manager’s Tree window and selecting Properties.
When we make changes to a publication, we need to resynchronize all
devices. Additionally, some selections cannot be changed, requiring that
we delete the publication and create a new one with the desired
selections. As always, having a solid publication design before
beginning development is most important.
Once we have completed the
publication, we are two-thirds of the way home. Configuring our Internet
connectivity was the first third, defining our publication was the
second third, and writing the .NET Compact Framework application that
does synchronizations and modifies data is the final third.
3. Programming for Merge Replication
Programming a .NET Compact Framework application to work with Merge Replication consists of using the SqlCeReplication class located in the System.Data.SqlServerCe
namespace to initially subscribe to a publication and to periodically
synchronize with that publication.
Our
sample application will subscribe to the publication we just created,
synchronize the data to the device, modify the data on the device, and
resynchronize to transmit the modifications back to the server and to
receive new data (other users’ modifications) from the server. Along the
way, we use some utility routines to view the schema created by the
subscription and the data delivered by the synchronization. The
application is predominately menu-driven, with a single DataGrid control for displaying information. Figure 2 shows the application.
Listing 1. Declaring Connection Parameters
/// <summary>
/// Summary description for Form1
/// </summary>
public class FormMain : System.Windows.Forms.Form
{
internal System.Windows.Forms.MainMenu MenuMain;
internal System.Windows.Forms.MenuItem mitemFile;
:
:
private System.Windows.Forms.DataGrid dgridOutput;
public FormMain()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// The main entry point for the application
/// </summary>
static void Main()
{
Application.Run(new FormMain());
}
// The database file
private string strDBFile = @"My Documents\Northwind.sdf";
// The local connection string
private string strConnLocal =
"Data Source=" + @"My Documents\Northwind.sdf";
// The URL
private string strURL =
"http://207.202.168.30/YaoDurantRDA/sscesa20.dll";
|
using System;
using System.IO;
using System.Windows.Forms;
using System.Data;
using System.Data.Common;
using System.Data.SqlServerCe;
The FormMain
class begins with some generated code followed by the local connection
information and the remote connection information. The remote
information defines the connection to the Web site but not the SQL
Server connection string. That string will be built by the sscesaNN.dll
code from parameters that will be passed in the AddSubscription and Synchronize calls, parameters that are declared in Listing 8.6.
3.1. Subscribing to a Publication
A
.NET Compact Framework application needs to subscribe to a publication
only once. Doing so causes information about the subscription to be
stored at the server. Thereafter, each time the application attempts to
synchronize, the server verifies that the subscription does exist, that
the publication has not be modified or re-created since the previous
subscription, and that valid credentials are being presented by the
application.
To subscribe, the application must create a SqlCeReplication object, set its properties, invoke its AddSubscription method, and dispose of it, as shown in Listing 2. Subscribing does not cause data or schemas to be transmitted to the device.
Listing 2. Subscribing to a Publication
private void mitemCreateSubscription_Click(object sender,
EventArgs e)
{
SqlCeReplication replNW = new SqlCeReplication();
try
{
replNW.ExchangeType = ExchangeType.BiDirectional;
replNW.InternetUrl = strURL;
replNW.InternetLogin = "";
replNW.InternetPassword = "";
replNW.Publisher = "SNOWDEN";
replNW.PublisherDatabase = "Northwind";
replNW.Publication = "EmployeeOrderInfo";
replNW.PublisherSecurityMode =
SecurityType.DBAuthentication;
replNW.PublisherLogin = "Davolio";
replNW.PublisherPassword = "Nancy";
replNW.Subscriber = "YaoDurant";
replNW.SubscriberConnectionString = strConnLocal;
replNW.AddSubscription(AddOption.CreateDatabase);
}
catch( SqlCeException exSQL )
{
HandleSQLException(exSQL);
}
finally
{
replNW.Dispose();
}
}
|
The properties whose names begin with Pub are used by the sscesa20.dll file at the strUrl
location to connect to the specified server, find the database, and
subscribe to the publication. Because we specified SQL Server
Authentication, both at the server and here in our code, we must specify
a publisher login and password. That login will be used by sscesa20.dll
as it subscribes to the publication on the application’s behalf; that
is, it will subscribe as "Davolio". "Davolio", in turn, will become the user_name value that the publication filter uses. Thus, information about Nancy Davolio’s orders will be synchronized to the device.
3.2. Synchronizing the Data
Once we have subscribed
to a publication and once the snapshot agent for that publication has
run at least once, we can synchronize with the publication, resulting in
the two-way exchange of data between the server and the device.
Listing 3 shows the code. It is identical to the subscription code in Listing 8.7 except that the Synchronize method is called instead of the AddSubscription method.
Listing 3. Synchronizing the Device with the Remote Server
private void mitemSynchronize_Click(object sender,
System.EventArgs e)
{
SqlCeReplication replNW = new SqlCeReplication();
try
{
replNW.ExchangeType = ExchangeType.BiDirectional;
replNW.InternetUrl = strURL;
replNW.InternetLogin = "";
replNW.InternetPassword = "";
replNW.Publisher = "SNOWDEN";
replNW.PublisherDatabase = "Northwind";
replNW.Publication = "EmployeeOrderInfo";
replNW.PublisherSecurityMode =
SecurityType.DBAuthentication;
replNW.PublisherLogin = "Davolio";
replNW.PublisherPassword = "Nancy";
replNW.Subscriber = "YaoDurant";
replNW.SubscriberConnectionString = strConnLocal;
replNW.Synchronize();
}
catch( SqlCeException exSQL )
{
HandleSQLException(exSQL);
}
finally
{
replNW.Dispose();
}
}
|
Synchronization
brings new data and schemas, if necessary, to the device and closes the
connection to the server. Because we have not previously synchronized,
there is no data already on our device that came from the remote server
and therefore no data that will be returned to the server during this
synchronization.
Our sample application has methods that select all rows from msysObjects and msysConstraints and display the results in the DataGrid control. Figures 3 and 4
show the information presented by these routines, respectively. We can
see that both tables and constraints have been propagated from the
server to the device.
3.3. Modifying the Data at the Device
Listing 4 shows a simple example.
Listing 4. Updating the Data Locally
private void mitemLocalUpdate_Click(object sender,
System.EventArgs e)
{
SqlCeConnection connLocal =
new SqlCeConnection(strConnLocal);
connLocal.Open();
SqlCeCommand cmndLocal = new SqlCeCommand();
try {
cmndLocal.Connection = connLocal;
cmndLocal.CommandText =
"INSERT \"Order Details\" " +
" (OrderID, ProductID, UnitPrice, " +
" Quantity, Discount) " +
" VALUES (10258, 1, 19.95, 44, 0.0)";
cmndLocal.ExecuteNonQuery();
}
catch( SqlCeException exSQL )
{
HandleSQLException(exSQL);
}
finally
{
cmndLocal.Dispose();
connLocal.Close();
}
}
|
After executing this statement, we examine the Order Details table on the device to verify that the row is there, as shown in Figure 5.
When we synchronize again, we use Enterprise Manager to view the row on the server (see Figure 6), thus verifying that it was received.
This completes the
walkthrough of our sample application. During the walkthrough, we
subscribed, synchronized, modified data, and resynchronized—the steps
that every Merge Replication application must perform.
A Merge Replication
application requires a small amount of code to subscribe and synchronize
because it is drawing on components defined outside
the program, mainly the virtual directory configuration and the
publication definition. Good planning and design of these outside
components is essential for success.
