ADO.NET Cookbook

4.11. Updating Server Data Using a Web Service

Problem

You want to update a data source using an XML web service and use the web service from your client application.

Solution

Use a DataSet object.

The XML web service code contains two methods:

LoadOrders( )

Creates and returns a DataSet containing the Orders and Order Details tables from Northwind and a DataRelation between those tables.

UpdateOrders( )

Takes a DataSet argument containing the changes made to the DataSet created by the LoadOrders( ) method, creates two DataAdapter objects with CommandBuilder generated update logic for each, and uses the DataAdapter objects to update the Orders and Order Details tables in Northwind.

The client-side code contains two event handlers:

Form.Load

Sets up the example by calling the LoadOrders( ) method in the web service to populate a DataSet. The default view of the Orders table is bound to the data grid on the form.

Update Button.Click

Calls the UpdateOrders( ) method in the web service passing a DataSet containing changes made to the DataSet since the form was loaded or since the last time the UpdateOrders( ) method was called.

The C# code for the XML web service is shown in Example 4-25.

Example 4-25. File: NorthwindServiceCS.asmx.cs

// Namespaces, variables, and constants 
using System;
using System.ComponentModel;
using System.Web.Services;
using System.Configuration;
using System.Data;
using System.Data.SqlClient; 

public const String ORDERS_TABLE        = "Orders";
public const String ORDERDETAILS_TABLE  = "OrderDetails";

public const String ORDERID_FIELD       = "OrderID";

public const String ORDERS_ORDERDETAILS_RELATION =
    "Order_OrderDetails_Relation"; 

//  . . . 

[WebMethod]
public DataSet LoadOrders( )
{
    DataSet ds = new DataSet( );
    
    SqlDataAdapter da;

    // Fill the Order table and add it to the DataSet.
    da = new SqlDataAdapter("SELECT * FROM Orders",
        ConfigurationSettings.AppSettings["DataConnectString"]);
    DataTable orderTable = new DataTable(ORDERS_TABLE);
    da.FillSchema(orderTable, SchemaType.Source);
    da.Fill(orderTable);
    ds.Tables.Add(orderTable);

    // Fill the OrderDetails table and add it to the DataSet.
    da = new SqlDataAdapter("SELECT * FROM [Order Details]",
        ConfigurationSettings.AppSettings["DataConnectString"]);
    DataTable orderDetailTable = new DataTable(ORDERDETAILS_TABLE);
    da.FillSchema(orderDetailTable, SchemaType.Source);
    da.Fill(orderDetailTable);
    ds.Tables.Add(orderDetailTable);

    // Create a relation between the tables.
    ds.Relations.Add(ORDERS_ORDERDETAILS_RELATION,
        ds.Tables[ORDERS_TABLE].Columns[ORDERID_FIELD],
        ds.Tables[ORDERDETAILS_TABLE].Columns[ORDERID_FIELD],
        true);

    return ds;
}

[WebMethod]
public bool UpdateOrders(DataSet ds)
{
    // Create the DataAdapters for order and order details tables.
    SqlDataAdapter daOrders =
        new SqlDataAdapter("SELECT * FROM Orders",
        ConfigurationSettings.AppSettings["DataConnectString"]);
    SqlDataAdapter daOrderDetails =
        new SqlDataAdapter("SELECT * FROM [Order Details]",
        ConfigurationSettings.AppSettings["DataConnectString"]);

    // Use CommandBuilder to generate update logic.
    SqlCommandBuilder cbOrders = new SqlCommandBuilder(daOrders);
    SqlCommandBuilder cbOrderDetails =
        new SqlCommandBuilder(daOrderDetails);

    // Update parent and child records.
    daOrderDetails.Update(ds.Tables[ORDERDETAILS_TABLE].Select(null, null,
        DataViewRowState.Deleted));
    daOrders.Update(ds.Tables[ORDERS_TABLE].Select(null, null,
        DataViewRowState.Deleted));
    daOrders.Update(ds.Tables[ORDERS_TABLE].Select(null, null,
        DataViewRowState.ModifiedCurrent));
    daOrders.Update(ds.Tables[ORDERS_TABLE].Select(null, null,
        DataViewRowState.Added));
    daOrderDetails.Update(ds.Tables[ORDERDETAILS_TABLE].Select(null, null,
        DataViewRowState.ModifiedCurrent));
    daOrderDetails.Update(ds.Tables[ORDERDETAILS_TABLE].Select(null, null,
        DataViewRowState.Added));
    
    return true;
}

The C# web services client-side code is shown in Example 4-26.

Example 4-26. File: UpdateServerThroughWebServiceForm.cs

// Namespaces, variables, and constants 
using System;
using System.Windows.Forms;
using System.Data;

// Table name constants
private const String ORDERS_TABLE       = "Orders";
private const String ORDERDETAILS_TABLE = "OrderDetails";

private DataSet ds;

//  . . . 

private void UpdateServerThroughWebServiceForm_Load(object sender,
    System.EventArgs e)
{
    Cursor.Current = Cursors.WaitCursor;

    // Create the Web Service object.
    NorthwindServiceCS nws = new NorthwindServiceCS( );
    // Load the DataSet containing orders and order details.
    ds = nws.LoadOrders( );

    // Bind the default view of the orders table to the grid.
    dataGrid.DataSource = ds.Tables[ORDERS_TABLE].DefaultView;

    Cursor.Current = Cursors.Default;        
}

private void updateButton_Click(object sender, System.EventArgs e)
{
    Cursor.Current = Cursors.WaitCursor;

    // Get the changes to the data.
    DataSet dsChanges = ds.GetChanges( );

    if (dsChanges!=null)
    {
        // Create the Web Service object.
        NorthwindServiceCS nws = new NorthwindServiceCS( );
        // Update the changes to the order and order detail
        // informatation.
        bool retVal = nws.UpdateOrders(dsChanges);
    }
    Cursor.Current = Cursors.Default;        
}

Discussion

An XML web service is software that is accessible using Internet standards such as XML and HTTP. Because they are accessible through open-standard interfaces, web services make it easy to allow heterogeneous systems to work together.

.NET makes it very easy to build XML web services. In .NET, web services are implemented as .ASMX files beginning with a @WebService directive. For example, the solution code contains the following directive:

<%@ WebService Language="c#" Codebehind="NorthwindServiceCS.asmx.cs"
    Class="NorthwindServiceCS" %>

Methods in the web service class that are exposed over the Web are tagged with the WebMethod attribute. Untagged methods can only be used internally by the web service. To deploy the web service, copy it to a directory on a web server that can be addressed by a URL.

To use the web service class, use wsdl.exe to create the client-side proxy class. For the solution, the command is:

wsdl.exe http://localhost/NorthwindWebServiceCS/NorthwindServiceCS.asmx

Then, as with a local class, the client is able to instantiate the web service class using the new operator.

For more information about creating and consuming XML web services, see the MSDN Library.

In the solution, the GetChanges( ) method is called on the client-side DataSet to create a copy of the DataSet containing only the changes. This DataSet is passed to the web service instead of the entire DataSet to minimize the bandwidth required to transmit data to the web service across a potentially slow link.

The UpdateOrders( ) method in the web service updates the database with the changes using method calls with different subsets of the DataSet as arguments. This technique, used to avoid referential integrity problems, is discussed in more detail in Recipe 4.10.

If this web service is intended for use by clients other than .NET clients—J2EE, for example—the DataSet should be marshaled as XML instead of the DataSet class, which is specific to .NET. This can easily be done using the WriteXml( ) and ReadXml( ) methods of the DataSet class.

The solution shows that there is very little difference between implementing the LoadOrders( ) and UpdateOrders( ) methods as a local class or web services class.