Remote Data Access Synchronization with SQL Server 2005 Compact Edition and Visual C#.NET

United States (English)

Home Library Learn Samples Downloads Support Community Forums

SQL Server 2005 Compact Edition Technical Articles

Microsoft Corporation

January 2007

Applies to:
Microsoft Visual Studio 2005
Microsoft SQL Server 2005 Compact Edition

Summary: In this tutorial, you will learn how to create a Microsoft Windows Forms application using Microsoft Visual C#.NET that synchronizes data between a Microsoft SQL Server 2005 database and a Microsoft SQL Server 2005 Compact Edition database using Microsoft SQL Server 2005 Compact Edition Remote Data Access (RDA). (48 printed pages)

Tutorial Objective
Conclusion
Appendix A: Setting Up Your Computer

Tutorial requirements

Microsoft Windows XP Professional
Microsoft Internet Information Services (IIS)
This tutorial uses Microsoft IIS for Microsoft Windows XP Professional. Be sure that the IIS component is installed. For more information, see Appendix A.
Microsoft Visual Studio 2005
This tutorial requires Microsoft Visual Studio 2005 Standard, Professional, or Team System Edition. It will not work with any of the Express Editions. If you do not have the correct edition of Visual Studio 2005, find out how you can acquire it from the Visual Studio 2005 Developer Center.
Microsoft SQL Server 2005
Find out how to acquire a copy at the SQL Server Developer Center.
Visual Studio 2005 Service Pack 1
SQL Server 2005 Compact Edition
Northwind sample database
This tutorial uses the Northwind sample database that was provided with previous versions of SQL Server. If you do not have this database, a copy has been provided with this tutorial's installation. See Appendix A for instructions on attaching the database to your SQL Server.
Complete the tutorial titled "Configuring Internet Information Services for SQL Server 2005 Compact Edition Remote Data Access and Merge Replication."
Before performing this tutorial, you should have already performed the steps in the tutorial titled "Configuring Internet Information Services for SQL Server 2005 Compact Edition Remote Data Access and Merge Replication," which instructs you on how to configure your computer's IIS for SQL Server 2005 Compact Edition synchronization.
Code snippets
In order to save time in this tutorial, you can quickly insert code snippets that have been prepared for you. See Appendix A to install the code snippets on your computer.

Credentials used

Computer's name
Computer's SQL Server sa account password

Level

200

Estimated time to complete this tutorial

1 hour

Tutorial Objective

In this tutorial, you will learn how to create a Microsoft Windows Forms application using Microsoft Visual C#.NET that synchronizes data between a Microsoft SQL Server 2005 database and a Microsoft SQL Server 2005 Compact Edition database using Microsoft SQL Server 2005 Compact Edition Remote Data Access (RDA).

In this tutorial, you will perform the following exercise:

Creating a remote data access (RDA) synchronization application in Visual Studio

Creating a Remote Data Access (RDA) Synchronization Application in Visual Studio

In this exercise, you will learn how to create a Windows Forms application using Visual C#.NET that synchronizes data between a SQL Server 2005 database and a SQL Server 2005 Compact Edition database using SQL Server 2005 Compact Edition Remote Data Access.

Note Before performing this exercise, you should have already performed the steps in the tutorial titled "Configuring Internet Information Services for SQL Server 2005 Compact Edition Remote Data Access and Merge Replication," which instructs you on how to configure your computer's IIS for SQL Server 2005 Compact Edition synchronization.

To create a project

Start Visual Studio 2005 by clicking Start | All Programs | Microsoft Visual Studio 2005 | Microsoft Visual Studio 2005.
Click File | New | Project to create a new Windows Forms application.
In the New Project dialog box under Project types, browse to Visual C# | Windows.
Note Depending on your Visual Studio configuration, Visual C# might appear under Other Languages.
In the Templates box, select Windows Application.
Change the Name to RDADemo, and then click OK, as shown in Figure 1.
Note You can create the project in the Location of your choice.

Figure 1. Creating the RDADemo project (Click on the picture for a larger image)
Visual Studio creates a new project and opens Form1 in the form designer. Because one of the goals of this tutorial is to show server-hosted SQL Server 2005 data and a local client's SQL Server Compact Edition data at the same time, you will use a splitter control to show both views on the same screen.

To set up the application's form for server and client data

In the Visual Studio Solution Explorer, double-click Form1.cs to open the form designer.
On the Visual Studio menu, click View | Toolbox.
If it is not already expanded in the toolbox, expand Containers to reveal the SplitContainer control, as shown in Figure 2.
Figure 2. Expanding Containers to reveal the SplitContainer control
Drag a SplitContainer control onto the Form1 designer to create the splitContainer1 control, as shown in Figure 3.
Figure 3. Adding the splitContainer1 control to Form1
Click the splitContainer1 smart-tag arrow button to display the control's smart-tags menu, and then click Horizontal splitter orientation, as shown in Figure 4.
Figure 4. Select horizontal splitter orientation
Make sure that the Properties pane is visible by clicking View | Properties Window.
In the Properties pane drop-down list of controls, select Form1, as shown in Figure 5.
Figure 5. Selecting Form1 in the Properties pane drop-down list of controls
Set the Size property to 550, 450 to increase the form's size.
In the Properties pane drop-down list, select splitContainer1.
Set the SplitterDistance property to 200, so that each panel in the container takes up approximately half of the form.
Form1 should now look like Figure 6.
Figure 6. Form1's modified layout
The top panel will contain the SQL Server data, and the bottom panel will contain the SQL Server Compact Edition data. Before you can display the SQL Server data in the form, you must add a project data source for the SQL Server data.

To add the server data source to the project

On the Visual Studio menu, click Data | Show Data Sources to display the Data Source pane.
In the Data Source pane, click Add New Data Source.
In the Data Source Configuration Wizard, on the Choose a Data Source Type page, make sure that Database is selected, and then click Next, as shown in Figure 7.
Figure 7. Choosing a data-source type
On the Choose Your Data Connection page, click New Connection.
In the Add Connection dialog box, click Change to display the Choose Data Source dialog box.
Note If the Choose Data Source dialog box appears instead of the Add Connection dialog box, continue to the next step.
In the Choose Data Source list, select Microsoft SQL Server, and then click OK, as shown in Figure 8.
Note If the Choose Data Source dialog box appeared instead of the Add Connection dialog box, you must press the Continue button.
Figure 8. Choosing Microsoft SQL Server as the data source
In the Add Connection dialog box, under Server name, type the name of your machine. This should be the name of the SQL Server machine to which you attached the Northwind sample database, as directed in Appendix A of this tutorial.
Make sure that Use Windows Authentication is selected.
Note If your machine's SQL Server installation is not set up to allow the current Windows user to access the database using Windows Authentication, you will need to adjust these security settings according to your machine.
Make sure that Select or enter a database name is selected and, under it, type or select Northwind in the drop-down list box, as shown in Figure 9.
Figure 9. Adding a connection to the SQL Server Northwind database
Click OK.
The new data connection is displayed in the wizard, as shown in Figure 10.
Figure 10. The new Northwind SQL Server database connection
Click Next.
Because you will have both a server connection and a connection to a local SQL Server Compact Edition database, change the default connection string name to ServerNorthwindConnectionString, and then click Next, as shown in Figure 11.
Figure 11. Changing the connection string name
On the Choose Your Database Objects page, expand Tables, and then check Customers.
Note If you are viewing the screen cast that corresponds to this tutorial, you might notice that the presenter selects a subset of columns; the presenter is using a Customers table that has been modified from the original schema. You can just select the Customers table.
Change the DataSet name to ServerNorthwindDataSet, and then click Finish, as shown in Figure 12.
Figure 12. Selecting the Customers table
Visual Studio adds the ServerNorthwindDataSet typed DataSet to your project. Now, you must add the Customers table to the application's form.

To add the Customers table to Form1

In the Data Source pane, drag Customers to the Form1 designer, and drop it on the Panel1 control, as shown in Figure 13.

Figure 13. Adding the Customers table to Form1's Panel1 control (Click on the picture for a larger image)
Visual Studio adds the customersBindingNavigator and customersDataGridView controls to the form and several other controls to the form's component tray.
Click the customersDataGridView control's smart-tags button and, in the smart-tags pop-up menu, click Dock in parent container, so that the grid fills the splitter panel.
Notice that now the customersBindingNavigator control obscures the top part of the customersDataGridView control, as shown in Figure 14.
Figure 14. The customersBindingNavigator control obscures the top part of the customersDataGridView control.
In order to arrange the form's controls more easily, you will now use the Visual Studio Document Outline pane. You will be moving the customersBindingNavigator control, so that it is contained within the scope of the top panel of the splitter control. By moving the customersBindingNavigator control within the scope of the top panel of the splitter control, the customersBindingNavigator control and the customersDataGridView control dock within the same container and therefore properly share the display area, making the entire customersDataGridView control completely visible.
On the Visual Studio menu, click View | Other Windows | Document Outline.
The Document Outline pane is displayed, as shown in Figure 15.
Figure 15. The Form1 Document Outline
In the Document Outline, click and drag customersBindingNavigator, which appears near the top of the outline, and then drop it directly below customersDataGridView, as shown by the horizontal line in Figure 16.
Figure 16. Dropping customersBindingNavigator below customersDataGridView
You have just defined the customersBindingNavigator control to be contained within the scope of the splitContainer1.Panel1 control, and you have arranged the control's Z-orders so that they share the docking space in such a way that both controls are fully visible. The Document Outline should now appear like Figure 17.
Figure 17. The revised Document Outline
Form1 should now look like Figure 18, with both controls sharing the docking space and fully visible.
Figure 18. The modified Form1
Eventually, the bottom part of the form will display the contents of a local SQL Server Compact Edition copy of the Northwind Customers table. Later in this tutorial, you will change the data contained within the local SQL Server Compact Edition database and synchronize the local data with the SQL Server database. As you synchronize changes made to the local database with the server database, you will need a convenient way to reload the server data into the customersDataGridView that you have configured on the top part of the form; therefore, instead of just loading the server data into the application when the form is initially loaded, you will add a button and code to the form that will load the server data each time that you click the button.

To add a button and code to load SQL Server data

In the Form1 designer, click anywhere on the customersBindingNavigator control at the top of the form. A drop-down Add ToolStripButton button appears on the control to the right of the Save Data button, as shown in Figure 19.
Figure 19. The customersBindingNavigator control
Click the Add ToolStripButton button's drop-down arrow to reveal the menu that is shown in Figure 20.
Figure 20. The customersBindingNavigator Add ToolStripButton button's menu
Click Button to add a new button to the customersBindingNavigator tool strip.
The new button should already be selected, but if it is not, select it now. In the Properties pane, locate and click the button's (Name) property.
Type loadServerDataButton as the value of the (Name) property, and then press ENTER to rename the button control.
Locate and click the DisplayStyle property, and then select ImageAndText in its drop-down list.
Locate and click the Text property, type Load Server Data, and then press ENTER.
The tool strip should now look like Figure 21.
Figure 21. The Load Server Data button has been added to the tool strip.
Now, you must add code to load the server data when the new button is clicked. Visual Studio has already generated this code for you, but you must move it from the form's Load event handler to the button's Click event handler.
Double-click on the new Load Server Data button to open the form's code view and create a Click event handler for the button.
Locate the private void Form1_Load event handler and select the entire line of code that reads this.customersTableAdapter.Fill(this.serverNorthwindDataSet.Customers);.
On the Visual Studio menu, click Edit | Cut to move the text to the clipboard. You can delete the TODO comment from the Form1_Load event handler.
Click inside the private void loadServerDataButton_Click event handler, and then click Edit | Paste to move the code to the new method.
The revised Form1 code should now look like the following code example.
```
private void Form1_Load(object sender, EventArgs e)
{

}

private void loadServerDataButton_Click(object sender, EventArgs e)
{
    this.customersTableAdapter.Fill(this.serverNorthwindDataSet.Customers);
}
```
Now that you have created an application that loads SQL Server data and displays it, you are ready to add code to use RDA to retrieve a copy of the data from the server to a local SQL Server Compact Edition database. Instead of creating a local SQL Server Compact Edition database at design time, you will add code to check for the existence of a local database at run time when the user clicks a button to retrieve the data locally and, if it does not exist, you will create the local database at that time.

To add code that retrieves server data using RDA

In the same way as in the previous task, you will now add another button to the tool strip.

In the Form1 designer, add another button to the customersBindingNavigator's tool strip by following the same steps that you used in the previous task. Set the button's (Name) property to rdaPullButton, its DisplayStyle property to ImageAndText, and its Text property to RDA Pull.
The tool strip should now look like Figure 22.
Figure 22. The RDA Pull button has been added to the tool strip.
Now, you must add a Click event handler for the button to retrieve the data from the SQL Server Customers table and store it in a new table within a SQL Server Compact Edition database using RDA.
Double-click on the new RDA Pull button to open the form's code view and create a Click event handler for the button.
Because it might take a noticeable amount of time to retrieve data from a server and store it in a local database, you will provide related feedback to the user. In the rdaPullButton_Click event handler, set the form's current cursor, Cursor.Current, to be the standard wait cursor, Cursors.WaitCursor, as shown in the following code example.
```
    Cursor.Current = Cursors.WaitCursor;
```
In case anything goes wrong while the method is running, in order to make sure that the cursor is reset to the default cursor, create a try...catch...finally statement. In the catch block, add a call to MessageBox.Show to display any exception that might occur and, in the finally block, set the current cursor to Cursors.Default, as shown in the following code example.
```
    try
    {
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    finally
    {
        Cursor.Current = Cursors.Default;
    }
```
Just as you did in the case of the server database, you will store the local SQL Server Compact Edition database connection string as a setting in the Project Designer. In the server case, the connection string was automatically created by the Add Data Source wizard. In the case of the SQL Server Compact Edition database, you'll be creating the database programmatically instead of using the wizard to add the SQL Server Compact Edition data source; therefore, you must explicitly add the connection string to the project settings.
In Solution Explorer, double-click Properties to display the Project Designer.
Click the Settings tab on the left of the Project Designer screen. Notice that this is where the ServerNorthwindConnectionString connection string that you created earlier is maintained.
Add another setting named LocalNorthwindConnectionString, as shown in Figure 23.

Figure 23. Adding a new connection string setting to the project (Click on the picture for a larger image)
Change the type of the setting by selecting (Connection String) in the Type drop-down list for your new project setting. By using this type, Visual Studio displays a dialog box to help you to build the value of the connection string, as you will now see.
Click in the Value column of your new settings, and then click the ellipsis button that appears in the right-hand part of the Value text box.
In the Connection Properties dialog box, click Change to display the Choose Data Source dialog box.
Note If the Choose Data Source dialog box appears instead of the Add Connection dialog box, continue to the next step.
In the Choose Data source list, select Microsoft SQL Server 2005 Compact Edition, and then click OK, as shown in Figure 24.
Note If the Choose Data Source dialog box appeared instead of the Add Connection dialog box, you must press the Continue button.
Figure 24. Choosing Microsoft SQL Server 2005 Compact Edition as the data source
In the Connection Properties dialog box, under Data Source, make sure that My Computer is selected.
Under Database, type the relative path to the database file, .\Northwind.sdf, as shown in Figure 25. This will cause Visual Studio to connect to the database file in the same directory as the running application.
Figure 25. Adding a connection to the local SQL Server Compact Edition Northwind database
Press TAB to move to the next control on the dialog box and to trigger Visual Studio to validate the file path and enable the dialog box's OK button. Note that you will not create the database at this point; you are just defining the connection string.
Click OK.
Notice that the new connection string Value has been updated to Data Source =".\Northwind.sdf", as shown in Figure 26.

Figure 26. A new connection string setting that points to the local SQL Server Compact Edition database (Click on the picture for a larger image)
Click File | Save Selected Items to save the project settings, and then click File | Close.
When you pull data into a local database using RDA, the local database must already exist; therefore, you will now add a helper function to determine whether or not the database exists.
The code snippet that you are going to add uses the System.Data.SqlServerCe namespace, so that you must first add a reference to that namespace.
In the Solution Explorer, right-click RDADemo, and then click Add Reference.
Locate System.Data.SqlServerCe, select it, and then click OK. Visual Studio adds the reference to your project.
Back in the Form1.cs code editor, locate the other using statements at the beginning of the file. Add another using statement for the namespace you just referenced, so that the namespace's members do not have to be fully qualified in your code, as shown in the following code example.
```
using System.Data.SqlServerCe;
```
Create a new line on the last line of the class, before the class's terminating brace (or }) character. Note that the final brace character in the file is the namespace terminator; you will be adding a new method to the class, so your new line must be before the second-to-last brace character in the file. Keep your cursor positioned on the new line.
To reduce the amount of code that you must type, you will use the code snippets that you installed in preparation for this tutorial.
Click Edit | IntelliSense | Insert Snippet to display the code snippets IntelliSense menu in your code, as shown in Figure 27.
Figure 27. The Insert Snippet menu
Double-click My Code Snippets to display the list of code snippets that you have added for this tutorial.
Double-click Verifies a SQL Server Compact Edition to have Visual Studio insert the verifyDatabaseExists method that is shown in the following code example.
```
private bool verifyDatabaseExists()
{
    bool createdDatabase = false;
    using (SqlCeConnection conn = new SqlCeConnection())
    {
        conn.ConnectionString = 
Properties.Settings.Default.LocalNorthwindConnectionString;
        if (!System.IO.File.Exists(conn.Database))
        {
            createdDatabase = true;
            using (SqlCeEngine engine = new 
SqlCeEngine(Properties.Settings.Default.LocalNorthwindConnectionString))
                engine.CreateDatabase();
        }
    }
    return createdDatabase;
}
```
This code first declares a bool flag named createdDatabase to track whether or not the database has been created. The code then creates an instance of a SqlCeConnection object named conn and sets the conn's ConnectionString property to the value of the local SQL Server Compact Edition connection string project setting that you created previously. The project setting's value is accessed by referencing Properties.Settings.Default and the name of the setting that you created, LocalNorthwindConnectionString. When you associate the connection string with the SqlCeConnection instance, the Database property is assigned the actual database file path, because a SQL Server Compact Edition database is simply a file. Next, the code passes this database path to System.IO.File.Exists to determine if the database file exists. If it does not exist, createdDatabase is set to true, and an instance of SqlCeEngine named engine is created, passing the LocalNorthwindConnectionString setting value. Finally, engine's CreateDatabase method is called, creating an empty SQL Server Compact Edition database file, at the specified path, and the value of createdDatabase is returned. The CreateDatabase method contains all of the logic necessary to create the database file, so that you don't have to use any T-SQL.
Now, return to the rdaPullButton_Click method, and inside the try block, call the verifyDatabaseExists function, as shown in the following code example.
```
    try
    {
        verifyDatabaseExists();
    }
```
In order to use RDA to pull a copy of data locally, you must create an instance of the base RDA class, SqlCeRemoteDataAccess, and initialize several of its properties. Because you will need this object both when pulling and pushing data, you will encapsulate its creation and initialization in a class-level method.
Still inside the try block, declare a variable named rda of type SqlCeRemoteDataAccess, and then assign to it the return value of getRDAObject, a function that you will now create.
```
    SqlCeRemoteDataAccess rda = getRDAObject();
```
Instead of requiring direct client access to SQL Server data, RDA enables access through a Microsoft IIS Web server, so that the data can be accessed remotely and securely over the Internet without requiring SQL Server to be directly available on the Internet. RDA uses an IIS server component that acts as the intermediary between the SQL Server Compact Edition client database and the SQL Server database. You must assign the URL of the IIS server that is configured as the intermediary to the SqlCeRemoteDataAccess.InternetUrl property.
Note Because the IIS server component is the actual client to the SQL Server database instead of your remote application, the interaction between the IIS server component and the SQL Server database is very efficient and even takes advantage of database connection pooling.
Note Having configured your computer for RDA according to this tutorial's prerequisite tutorial, "Configuring Internet Information Services for SQL Server 2005 Compact Edition Remote Data Access and Merge Replication," you should be able to verify that Web access to the RDA server component is functioning properly by using your browser to browse to http://localhost/Sync/sqlcesa30.dll and seeing the Web response Microsoft SQL Server Compact Edition Server Agent displayed in the browser.
Create a new function named getRDAObject that returns a SqlCeRemoteDataAccess object in the Form1 class, as shown in the following code example.
```
private SqlCeRemoteDataAccess getRDAObject()
{
}
```
Inside the new function, declare and create a new instance of SqlCeRemoteDataAccess named rda, as shown in the following code example.
```
    SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess();
```
Assign the URL of the synchronization Web site that you have configured, http://localhost/Sync/sqlcesa30.dll, to the rda object's InternetUrl property, as shown in the following code example.
```
    rda.InternetUrl = "http://localhost/Sync/sqlcesa30.dll";
```
Now, assign the local SQL Server Compact Edition database connection string, Properties.Settings.Default.LocalNorthwindConnectionString, to the rda object's LocalConnectionString property. The local connection string identifies the local SQL Server Compact Edition database in which to store the pulled RDA data.
```
    rda.LocalConnectionString = 
Properties.Settings.Default.LocalNorthwindConnectionString;
```

Finally, return the rda object.

    return rda;

The completed getRDAObject function should now look like the following code example.

private SqlCeRemoteDataAccess getRDAObject()
{
    SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess();
    rda.InternetUrl = "http://localhost/Sync/sqlcesa30.dll";
    rda.LocalConnectionString = 
Properties.Settings.Default.LocalNorthwindConnectionString;
    return rda;
}

Note There are several other properties available to configure the rda object, such as Internet server logon information and proxy-server information.

Return to the rdaPullButton_Click method and, after the line that assigns the return value of the getRDAObject function to the rda variable, call the rda object's Pull method to pull the data from the server to the client. You will be retrieving the server data from the Customers table with a T-SQL SELECT statement. The local and server table names do not need to be the same; so, to demonstrate that fact, pass "Contacts" as the locaTableName parameter. The following code example shows the start of the call to the Pull method.
```
    rda.Pull("Contacts",
```
Pass the T-SQL statement "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers" as the sqlSelectString parameter. The rda Pull method call is continued in the following code example.
```
    rda.Pull("Contacts",
        "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, 
City, Region, PostalCode, Country, Phone, Fax FROM Customers",
```
Note You could use the Visual Studio Query Designer to build this T-SQL statement. For a demonstration of this technique, see the screen cast that corresponds to this tutorial or the tutorial titled "SQL Server 2005 Compact Edition Data Access with the SqlCeResultSet and Visual C#.NET."
Because RDA was released when OLE DB providers were the standard for connecting to SQL Server, RDA requires an OLE DB connection string to the SQL Server as the oleDBConnectionString parameter. To generate a connection string easily, you can again use the Project Designer.
In the Solution Explorer, double-click Properties to display the Project Designer.
Click the Settings tab on the left of the Project Designer screen.
Add another setting named ServerOleDBNorthwindConnectionString.
As you did with the previous connection string setting values, set the Type to (Connection String), click in the Value column of your new settings, and then click the ellipsis button that appears in the right-hand part of the Value text box.
Click the Change button to display the Change Data Source dialog box.
In the Change Data source list, select Microsoft SQL Server.
This time, instead of using the standard SQL Server Data provider, select .NET Framework Data Provider for OLE DB, as shown in Figure 28, so that the connection string that is generated is an OLE DB connection string.
Figure 28. Selecting Microsoft SQL Server as the data source and .NET Framework Data Provider for OLE DB as the data provider
Click OK.
In the Connection Properties dialog box, under Server name, type the name of your machine. This should be the name of the SQL Server machine to which you attached the Northwind sample database in this tutorial's appendix.
Because the queries that you submit to RDA are not being executed by your client-side logon, but are instead being executed on the Web server by the IIS process logon, you should not generate a connection string with Windows Authentication unless you have set up the IIS process to run as a user that has been configured to access the SQL Server. Instead of performing that configuration in this tutorial, you should just use the sa account or another SQL Server user that you have configured.
Note If your SQL Server was installed to use only Windows Authentication, you will need to set up your IIS process to run as your Windows user account and make sure that it has been configured to access the SQL Server.
Click Use SQL Server Authentication under Log on to the server.
Type sa as the User name, and your server's sa password as the Password.
Check Save my password.
Make sure that Select or enter a database name is selected and, under it, type or select Northwind in the drop-down list box, as shown in Figure 29.
Figure 29. Connecting to the SQL Server Northwind database using an OLE DB provider
Click OK.
If you are prompted to save the password in the connection string, click Yes.
Click File | Save Selected Items to save the project settings, and then click File | Close.
Now, back in the rdaPullButton_Click method, continue the call to rda.Pull by passing Properties.Settings.Default.ServerOleDBNorthwindConnectionString as the oleDBConnectionString, as shown in the following code example.
```
    rda.Pull("Contacts",
        "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, 
City, Region, PostalCode, Country, Phone, Fax FROM Customers",
        Properties.Settings.Default.ServerOleDBNorthwindConnectionString,
```
The trackOption parameter lets you specify two attributes of the table that is created by the pull operation. The first attribute is whether or not local data changes will be tracked for potential pushing back to the server. The second attribute is whether the local table will be defined with the same indexes and primary key with which the server's table is defined.
In this exercise, you will be pushing data changes back to the server, and you want the local SQL Server Compact Edition table to have the same indexes as the table on the server, so pass the RdaTrackOption.TrackingOnWithIndexes as the trackOption parameter, as shown in the following code example.
```
    rda.Pull("Contacts",
        "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, 
City, Region, PostalCode, Country, Phone, Fax FROM Customers",
        Properties.Settings.Default.ServerOleDBNorthwindConnectionString,
        RdaTrackOption.TrackingOnWithIndexes,
```

For the final parameter, errorTable, pass the string "Contacts_Errors", so that any errors that are encountered during the RDA push action are captured in a table by the name of Contacts_Errors. The error table is used only during push actions, not pull actions; however, the name of the table is specified as a parameter to the Pull method. If a table was specified upon the pull action, later, during the push action, any row changes that could not be sent to the server are added to this table in the local database, with three additional fields to assist in determining the cause of the error. The following code example shows the final rda.Pull method call.

    rda.Pull("Contacts",
        "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, 
City, Region, PostalCode, Country, Phone, Fax FROM Customers",
        Properties.Settings.Default.ServerOleDBNorthwindConnectionString,
        RdaTrackOption.TrackingOnWithIndexes,
        "Contacts_Errors");

The rdaPullButton_Click event handler should now look like the following code example.

private void rdaPullButton_Click(object sender, EventArgs e)
{
    Cursor.Current = Cursors.WaitCursor;
    try
    {
        verifyDatabaseExists();
        SqlCeRemoteDataAccess rda = getRDAObject();
        rda.Pull("Contacts",
            "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, 
City, Region, PostalCode, Country, Phone, Fax FROM Customers",
            Properties.Settings.Default.ServerOleDBNorthwindConnectionString,
            RdaTrackOption.TrackingOnWithIndexes,
            "Contacts_Errors");
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    finally
    {
        Cursor.Current = Cursors.Default;
    }
}

To test the application

Before you start debugging the application, set a breakpoint on the first line of the rdaPullButton_Click event handler.
In Visual Studio, click Debug | Start Debugging.
After the application appears, click the Load Server Data button.
The data is loaded from the SQL Server and displayed in the application, as shown in Figure 30.
Figure 30. Testing the application
Click the RDA Pull button.
When the Visual Studio debugger breaks on the breakpoint that you set, use the F11 key to step through the code line by line, to see the effects of the RDA pull code that is running. On the last line of the event handler, you can press the F5 key to continue execution.
After the pull action completes, close the application.
Remove the breakpoint that you added.
In the next task, you will add the local SQL Server Compact Edition database file to your project and use this database to help generate the application's remaining user interface.

To add the local data to the application's user interface

At the top of the Visual Studio Solution Explorer, click the Show All Files button to reveal all of the folders and files in your project directory.
Note You might need to hover your mouse pointer over the buttons to see their names. The button is outlined in Figure 31.
In the Visual Studio Solution Explorer, locate the RDADemo project node. Under the RDADemo project node, expand the bin and Debug folders to reveal a copy of Northwind.sdf, among other files, as shown in Figure 31. This is the SQL Server Compact Edition database that was created in the verifyDatabaseExists function.
Figure 31. The Northwind.sdf file created for the Debug build
In Solution Explorer, drag Northwind.sdf and drop it in the RDADemo project node, to move it from the Debug folder to the main project folder.
Now, to include the moved copy of the SQL Server Compact Edition database file in your project, right-click Northwind.sdf from its new location under RDADemo, and then click Include In Project.
Correctly assuming that you want to use this database as a data source in your application, Visual Studio displays the Data Source Configuration Wizard, as shown in Figure 32.
Figure 32. The Data Source Configuration Wizard
Expand the Tables node, and notice that there is a Contacts table, created from the Customers table in the server database, and a Contacts_Errors table with the same columns as the Contacts table, plus three additional columns for error tracking when the data is pushed back to the server.
Note The details of working with RDA error tables are outside the scope of this tutorial, but you can learn more by reading " RDA Conflict Detection and Reporting," in the MSDN Library.
Check the Contacts table to include it in the Visual Studio generated typed DataSet that is created upon finishing the wizard.
Change the DataSet name to LocalNorthwindDataSet, as shown in Figure 33, and then click Finish.
Figure 33. Choosing to include the Contacts table in the localNorthwindDataSet
After the wizard completes, double-click Form1.cs in Solution Explorer to show the form designer.
On the Visual Studio menu, click Data | Show Data Sources.
If it is not already expanded, expand localNorthwindDataSet.
Note You might need to click the Data Sources Refresh button to see the new data source.
Drag Contacts onto Panel2 in the form designer.
On the contactsDataGridView that you placed on the form, ensure that the smart-tags menu is displayed, and click Dock in parent container.
Form1 should now look like Figure 34.
Figure 34. Form1 now contains grids for both server and client data.
On the Visual Studio menu, click View | Toolbox.
In the toolbox, expand Data if it is not already expanded, and locate the BindingNavigator control.
Drag a BindingNavigator control onto the contactsDataGridView grid that you just created and docked.
Click View | Other Windows | Document Outline to show the Document Outline pane.
In the Document Outline, click and drag bindingNavigator1, and drop it below contactsDataGridView, so that it is at the bottom of the Z-order and shares the docking in its parent properly with the grid.
The Document Outline should now look like Figure 35.
Figure 35. Form1 now contains controls for both server and client data.
Form1 should now look like Figure 36.
Figure 36. Form1 now contains controls for both server and client data.
In the Form1 designer, right-click the bindingNavigator1 control, and then click Properties.
Locate the BindingSource property, and set its value to contactsBindingSource.
In the Form1 designer, click anywhere on the contactsBindingNavigator control that is located on the bottom half of the form. A drop-down Add ToolStripButton button appears on the control to the right of the Save Data button.
Click the Add ToolStripButton button's drop-down arrow, and then click Button to add a new button to the bindingNavigator1 tool strip.
The new button should already be selected, but if it is not, select it now. In the Properties pane, locate and click the button's (Name) property.
Type loadClientDataButton, and then press ENTER to rename the button control.
Change the DisplayStyle property to ImageAndText.
Change the Text property to Load Client Data.
The tool strip should now look like Figure 37.
Figure 37. The Load Client Data button has been added to the tool strip.
Now, you must add code to load the client data when the button is clicked. Visual Studio has already generated this code for you, but you must move it from the form's Load event handler to the button's Click event handler.
Double-click on the new Load Client Data button to open the form's code view and create a Click event handler for the button.
Locate the private void Form1_Load event handler and select the entire line of code that reads this.contactsTableAdapter.Fill(this.localNorthwindDataSet.Contacts);.
On the Visual Studio menu, click Edit | Cut to move the text to the clipboard. You can delete the TODO comment from the Form1_Load event handler.
Click inside the private void loadClientDataButton_Click event handler, and then click Edit | Paste to move the code to the new method.
The revised loadClientDataButton_Click code should now look like the following code example.
```
private void loadClientDataButton_Click(object sender, EventArgs e)
{
    this.contactsTableAdapter.Fill(this.localNorthwindDataSet.Contacts);
}
```
In order to save the local data, you need a Save button on the new tool strip. For simplicity, you will copy the Save button from the customersBindingNavigator control and paste it onto the bindingNavigator1 control. You will then add code to the new button that saves the local data to the SQL Server Compact Edition database.
Return to the Form1 designer, right-click the Save button on the customersBindingNavigator control (the button has a floppy-disk image on it), and then click Copy.
Now, right-click in the blank area of the bindingNavigator1 control, and then click Paste.
With the new Save button still selected, in the Properties pane, change the button's (Name) property to saveClientDataButton.
The tool strip should now look like Figure 38.
Figure 38. The Save Client Data button has been added to the tool strip.
Double-click the saveClientDataButton button to open the code editor and create a Click event handler.
Saving your application's data is a three-step process: First, the data in the contactsDataGridView grid is applied to the contactsBindingSource binding source; next, the contactsBindingSource applies the data to the underlying Contacts DataTable in the localNorthwindDataSet typed DataSet; finally, the DataSet's data is saved to the database file.
In the saveClientDataButton_Click event handler, call the form's Validate method to force the data-bound contactsDataGridView control to push its data into its binding source, contactsBindingSource, as shown in the following code example.
```
    this.Validate();
```
Now, commit any pending changes to the Contacts DataTable within the localNorthwindDataSet typed DataSet by calling the contactsBindingSource.EndEdit method.
```
    this.contactsBindingSource.EndEdit();
```
Finally, use the contactsTableAdapter.Update method to commit the data to the local SQL Server Compact Edition database file, passing the localNorthwindDataSet.Contacts table.
```
    this.contactsTableAdapter.Update(this.localNorthwindDataSet.Contacts);
```
The finished saveClientDataButton_Click event handler should look like the following code example.
```
private void saveClientDataButton_Click(object sender, EventArgs e)
{
    this.Validate();
    this.contactsBindingSource.EndEdit();
    this.contactsTableAdapter.Update(this.localNorthwindDataSet.Contacts);
}
```
Now, you must add a button to perform the RDA push.

To add code that pushes modified data back to the server using RDA

In the Form1 designer, add another button to the contactsBindingNavigator's tool strip by following the same steps that you used in the previous task. Set the button's (Name) property to rdaPushButton, its DisplayStyle property to ImageAndText, and its Text property to RDA Push.
The tool strip should now look like Figure 39.
Figure 39. The RDA Push button has been added to the tool strip.
Double-click the RDA Push button to open the code editor and create a Click event handler.
In the rdaPushButton_Click event handler, start with the same code structure that you did in the rdaPullButton_Click event handler to manage the cursor and create the SqlCeRemoteDataAccess instance, as shown in the following code example.
```
    Cursor.Current = Cursors.WaitCursor;
    try
    {
        SqlCeRemoteDataAccess rda = getRDAObject();

    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    finally
    {
        Cursor.Current = Cursors.Default;
    }
```
SQL Server Compact Edition internally tracks the server table that was used to populate the local table during the pull action. It then uses that information to transmit the required INSERT, UPDATE, and DELETE statements to the server.

After the line that assigns getRDAObject to the rda variable, call the rda object's Push method, passing "Contacts" as the localTableName parameter, because that is the name of the local SQL Server Compact Edition table that will contain your application's changes; Properties.Settings.Default.ServerOleDBConnectionString as the oleDBConnectionString parameter, as you did before; and RdaBatchOption.BatchingOn as the batchOption parameter, so that all changes are sent in a single transaction.

        rda.Push("Contacts", 
Properties.Settings.Default.ServerOleDBNorthwindConnectionString, 
RdaBatchOption.BatchingOn);

The finished rdaPushButton_Click event handler should look like the following code example.

private void rdaPushButton_Click(object sender, EventArgs e)
{
    Cursor.Current = Cursors.WaitCursor;
    try
    {
        SqlCeRemoteDataAccess rda = getRDAObject();
        rda.Push("Contacts", Properties.Settings.Default.ServerOleDBNorthwindConnectionString, 
RdaBatchOption.BatchingOn);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    finally
    {
        Cursor.Current = Cursors.Default;
    }
}

To test the application

In Visual Studio, click Debug | Start Debugging.
After the application appears, click Load Server Data to load the SQL Server data on the top part of the screen.
Next, click Load Client Data to load the SQL Server Compact Edition data on the bottom part of the screen. Remember that the last time that you tested the application, you pulled the data from the server to the client, so that the local data already exists and can be immediately displayed.
Click RDA Pull.
Your application displays the error message that is shown in Figure 40.
Figure 40. The RDA pull action fails, because the Contacts table already exists.
Because the Contacts table already exists in the local SQL Server Compact Edition database, the RDA pull action fails. In order to protect existing data from being overwritten, RDA does not support populating an existing local table, so you must add code to deal with this situation.
Click OK to dismiss the error message.
Close the application.

To add code to check for local table existence

In the Form1 code editor, locate the rdaPullButton_Click method and the line that calls verifyDatabaseExists. Besides checking to make sure that the database exists, you also must check to make sure that the table does not exist, and delete it if it does exist.
Now, you will add a helper function to delete a table if it exists from the code snippets that you installed in preparation for this tutorial.
Create a new line on the last line of the class, before the class's terminating brace (or }) character. Keep your cursor positioned on the new line.
Click Edit | IntelliSense | Insert Snippet to display the code snippets IntelliSense menu in your code.
Double-click My Code Snippets.
Double-click Delete a SQL Server Compact Edition Table to have Visual Studio insert the deleteTable method that is shown in the following code example.
```
private bool deleteTable(string tableName)
{
    bool tableFound = false;
    using (SqlCeConnection conn = new SqlCeConnection())
    {
        SqlCeCommand cmd = new SqlCeCommand();
        conn.ConnectionString = 
Properties.Settings.Default.LocalNorthwindConnectionString;
        cmd.Connection = conn;
        cmd.CommandText = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE 
TABLE_NAME = @tableName";
        cmd.Parameters.Add("@tableName", tableName);
        conn.Open();
        if (cmd.ExecuteScalar().Equals(1))
        {
            tableFound = true;
            cmd.Parameters.Clear();
            cmd.CommandText = String.Format("DROP TABLE {0}", tableName);
            cmd.ExecuteNonQuery();
        }
    }
    return tableFound;
}
```
This function receives a string parameter, tableName, to indicate the name of the table to delete. The code starts by declaring a bool flag, tableFound, to track whether or not the table exists in the database. The code then creates a SqlCeConnection object, conn, and points it to the local SQL Server Compact Edition database with Properties.Settings.Default.LocalNorthwindConnectionString. A SqlCeCommand object, cmd, is used to execute a T-SQL statement that selects a count from the special SQL Server Compact Edition table INFORMATION_SCHEMA.TABLES, where the TABLE_NAME equals the value passed in to the tableName parameter. INFORMATION_SCHEMA.TABLES is a SQL Server Compact Edition system table that contains a row for each table currently in the database; therefore, if a row is found with the indicated table name, the cmd object is reused to issue a T-SQL DROP statement that deletes the table from the database. Finally, the bool tableFound value is returned.
Now, return to the rdaPullButton_Click method.

Add a call to the deleteTable method that you just inserted immediately after the existing call to verifyDatabaseExists. Pass the value "Contacts", as shown in the following code example.

    deleteTable("Contacts");

The modified rdaPullButton_Click event handler should look like the following code example.

private void rdaPullButton_Click(object sender, EventArgs e)
{
    Cursor.Current = Cursors.WaitCursor;
    try
    {
        verifyDatabaseExists();
        deleteTable("Contacts");
        SqlCeRemoteDataAccess rda = getRDAObject();
        rda.Pull("Contacts",
            "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, 
City, Region, PostalCode, Country, Phone, Fax FROM Customers",
            Properties.Settings.Default.ServerOleDBNorthwindConnectionString,
            RdaTrackOption.TrackingOnWithIndexes,
            "Contacts_Errors");
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    finally
    {
        Cursor.Current = Cursors.Default;
    }
}

Now, you are ready to test the application. In order to test the application as if this were the first that time you were running the application, you will first delete the local database copy in the Debug build folder.

To delete the local database

In Visual Studio Solution Explorer, make sure that all files are shown. If all files are not visible, click the Show All Files button.
Still in the Solution Explorer, expand RDADemo | bin | Debug, and then click the Refresh button at the top of the Solution Explorer.
In the Debug folder, right-click Northwind.sdf, and then click Delete.
Click OK to the prompt that the file will be permanently deleted.
Now, you will prevent the project's copy of Northwind.sdf from being copied to the location from which you just deleted it.
Still in the Solution Explorer, locate and click on the Northwind.sdf file that is contained directly under the RDADemo project.
Change the file's Copy to Output Directory property value to Do not copy.

To test the application

In Visual Studio, click Debug | Start Debugging.
After the application appears, click Load Server Data to load the SQL Server data on the top part of the screen.
Note Because you have deleted the local database, and because there is no error-trapping logic around the Load Client Data handler, you should not click the Load Client Data button yet, or you will receive an unhandled error.
Click RDA Pull to create the local database.
After the RDA pull operation completes, click Load Client Data to load the local SQL Server Compact Edition data on the bottom part of the screen.
The application should now have all 91 rows loaded from each database, as shown in Figure 41.
Figure 41. The application has loaded both server and client data.
On the first row of data in the upper grid, click on the Region column, and then type Red. You might have to resize the window or scroll to the right to see the Region column.
Click the Save Data button on the top tool strip. The Save Data button has a floppy-disk icon.
Your data change has now been saved to the server, but your local database copy still contains the old data. To get the updated data into your local copy of the SQL Server Compact Edition database, you have to perform the RDA pull operation again.
Click RDA Pull now.
RDA is designed to receive a snapshot of server data on the client, track changes, and then push the changes back to the client; RDA does not support merging server data with existing changes to the local data. To retrieve server changes, you must delete the local table and then pull all of the data from the server again. Because of the RDA requirement that the local table not exist when performing the pull operation, all local data changes are lost when you perform the pull operation.
Click Load Client Data, and the application displays the updated value of Red in the lower grid, as shown in Figure 42.

Figure 42. The application has loaded both server and client data. (Click on the picture for a larger image)
Now, click in the first row of the Region column of the lower grid, and type Blue to change the data that is stored in the LocalNorthwindDataSet.
Click the Save Data button on the lower tool strip to save the data in the LocalNorthwindDataSet to the local SQL Server Compact Edition database.
Click Load Server Data, and notice that the value Red is still displayed in the upper grid. The server data is unchanged, because you have not yet pushed the changes made to the local SQL Server Compact Edition data to the server.
Click RDA Push to push the local changes to the server.
After the push operation completes, click Load Server Data again, and notice that the value Blue is displayed in the upper grid, as shown in Figure 43.

Figure 43. The application has pushed the local data back to the server. (Click on the picture for a larger image)
RDA is a solution that is designed specifically for SQL Server Compact Edition to communicate with SQL Server. Unlike other synchronization technologies, like merge replication, the SQL Server instance does not require any changes to the server data or database. As far as the SQL Server instance is concerned, the RDA client is just another database client that issues SELECT, INSERT, UPDATE, and DELETE commands. RDA's lightweight server impact often makes it an excellent replication choice.
RDA's lightweight design does, however, require that the system designer ensure that data is well partitioned, so that each client modifies a unique subset of the data. The client is able to pull down an initial data snapshot and the client is able to push locally made changes back to the server. These changes are applied without regard for conflicts with changes made on the server or by other clients. The client changes simply overwrite the server's current state. There is a similar issue with regard to downloading new or modified records from the server. When RDA pulls data down from the server, it simply issues a T-SQL statement. There is no implicit concept of getting only new or modified records. Individual systems must be specifically designed to manage these.
Click in the first row of the lower grid's Region column, and change the value from Blue to Green.
Click the lower grid's Save Data button.
Now, click in the first row of the upper grid's Region column, and change the value from Blue to Black.
Click the upper grid's Save Data button.
The application should now look like Figure 44.

Figure 44. Changes have been made to both the server and local data. (Click on the picture for a larger image)
Now, click RDA Push to send the modified data to the server.
Click Load Server Data, and notice that, as in Figure 45, the value Green has been pushed to the server and has overwritten the change that you made on the server. The RDA push logic simply looks to see if the primary key of the modified local row exists on the server and, if it does, updates the server with the local changes.

Figure 45. The RDA push operation has overwritten the modified server data. (Click on the picture for a larger image)
Close the application.

Conclusion

In this tutorial, you performed the following exercise:

Creating a remote data access (RDA) synchronization application in Visual Studio

In this tutorial, you have learned how to create a Windows Forms application using Visual C#.NET that synchronizes data between a SQL Server 2005 database and a SQL Server 2005 Compact Edition database using SQL Server 2005 Compact Edition Remote Data Access (RDA). RDA is a simple two-tier, but scalable synchronization system. The client does not connect directly to the server database, but instead sends requests through a Web server, which has the benefit of making the contents of the SQL Server database available without exposing the SQL Server database directly to the Internet. The client does have knowledge of the server database information, such as data queries and the connection string. Merge replication is another available synchronization implementation that handles changes on both the server and client, including conflict resolution.

Appendix A: Setting Up Your Computer

Before you begin this tutorial, you must be sure that the following procedures are completed.

To install Internet Information Services

On the desktop computer, click Start | Control Panel | Add or Remove Programs.
In the Add or Remove Programs dialog box, click Add/Remove Windows Components.
In the Windows Components list, select Internet Information Services (IIS) if it is not already selected, and then continue clicking Next until the Windows Components wizard is complete.
Close the Add or Remove Programs dialog box.

To attach the Northwind sample database to SQL Server

After installing the setup files for this tutorial, you must attach the Northwind sample database to your SQL Server.

On the desktop computer, click Start | All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio.
In the Connect to Server dialog box, select Database Engine under Server type, your machine name under Server Name, and Windows Authentication under Authentication, and then click Connect.
In SQL Server Management Studio Object Explorer, right-click Databases, and then click Attach.
In the Attach Databases dialog box, click Add.
Browse to the RDASync\C#\Database directory for this tutorial, click NORTHWIND.MDF, and then click OK.
Click OK.
After the Attach Databases dialog box completes and closes, close SQL Server Management Studio.

To install code snippets

This tutorial uses predefined Visual Studio code snippets to save time in typing some of the code that is necessary for the application. The code snippets are provided as part of this tutorial's installation, but you must add them to your Visual Studio environment according to the following steps.

On the desktop computer, click Start | Run.
Using Windows File Explorer, browse to the RDASync\C#\Setup directory for this tutorial, and then double-click SQLeCodeSnippets.vsi.
In the Visual Studio Content Installer, make sure that SQL Server Compact Edition Code Snippets - C# is checked, and then click Next.
In response to the No Signature Found dialog box, click Yes to allow installation of the code snippets.
On the page to select an installation location, under Location, scroll to and check My Code Snippets, and then click Next.
Click Finish.
After the installation completes, click Close.

To configure IIS for SQL Server Compact Edition synchronization

Before you begin this tutorial, you must configure your computer's IIS for SQL Server 2005 Compact Edition synchronization as described in the tutorial titled "Configuring Internet Information Services for SQL Server 2005 Compact Edition Remote Data Access and Merge Replication."

Contents

Tutorial requirements

Credentials used

Level

Estimated time to complete this tutorial

Tutorial Objective

Creating a Remote Data Access (RDA) Synchronization Application in Visual Studio

To create a project

To set up the application's form for server and client data

To add the server data source to the project

To add the Customers table to Form1

To add a button and code to load SQL Server data

To add code that retrieves server data using RDA

To test the application

To add the local data to the application's user interface

To add code that pushes modified data back to the server using RDA

To test the application

To add code to check for local table existence

To delete the local database

To test the application

Conclusion

Appendix A: Setting Up Your Computer