Consuming a DataSet from an XML Web Service

Article
10/17/2014

The DataSet was architected with a disconnected design, in part to facilitate the convenient transport of data over the Internet. The DataSet and DataTable are "serializable" in that they can be specified as an input to or output from XML Web services without any additional coding required to stream the contents of the DataSet from an XML Web service to a client and back. The DataSet is implicitly converted to an XML stream using the DiffGram format, is sent over the network, and is then reconstructed from the XML stream as a DataSet on the receiving end. This gives you a very simple and flexible method for transmitting and returning relational data using XML Web services. For more information about the DiffGram format, see DiffGrams.

The following example shows the steps for creating an XML Web service, as well as a client for that service, that use the DataSet to transport relational data, including modifications to that data, and to resolve any updates back to the original data source.

Note You should always consider security implications when creating an XML Web service. For information on securing an XML Web service, see Securing XML Web Services Created Using ASP.NET.

To create an XML Web service that returns and consumes a DataSet

Create the XML Web service.

In the example, an XML Web service is created that returns data, in this case a list of customers from the Northwind database, and receives a DataSet with updates to the data which the XML Web service resolves back to the original data source.

The XML Web service exposes two methods: GetCustomers, to return the list of customers, and UpdateCustomers, to resolve updates back to the data source. The XML Web service is stored in a file on the Web server called DataSetSample.asmx. The following code outlines the contents of DataSetSample.asmx.

<% @ WebService Language = "VB" Class = "Sample" %>
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services

<WebService(Namespace:="https://microsoft.com/webservices/")> _
Public Class Sample

  Public nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")

  <WebMethod( Description := "Returns Northwind Customers", EnableSession := False )> _
  Public Function GetCustomers() As DataSet
    Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", nwindConn)

    Dim custDS As DataSet = New DataSet()
    custDA.MissingSchemaAction = MissingSchemaAction.AddWithKey
    custDA.Fill(custDS, "Customers")

    GetCustomers = custDS
  End Function

  <WebMethod( Description := "Updates Northwind Customers", EnableSession := False )> _
  Public Function UpdateCustomers(custDS As DataSet) As DataSet
    Dim custDA As SqlDataAdapter = New SqlDataAdapter()

    custDA.InsertCommand = New SqlCommand("INSERT INTO Customers (CustomerID, CompanyName) " & _
                                          "Values(@CustomerID, @CompanyName)", nwindConn)
    custDA.InsertCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
    custDA.InsertCommand.Parameters.Add("@CompanyName", SqlDbType.NChar, 15, "CompanyName")

    custDA.UpdateCommand = New SqlCommand("UPDATE Customers Set CustomerID = @CustomerID, " & _
                                          "CompanyName = @CompanyName WHERE CustomerID = @OldCustomerID", nwindConn)
    custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
    custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NChar, 15, "CompanyName")

    Dim myParm As SqlParameter = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", SqlDbType.NChar, 5, "CustomerID")
    myParm.SourceVersion = DataRowVersion.Original

    custDA.DeleteCommand = New SqlCommand("DELETE FROM Customers WHERE CustomerID = @CustomerID", nwindConn)
    myParm = custDA.DeleteCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
    myParm.SourceVersion = DataRowVersion.Original

    custDA.Update(custDS, "Customers")

    UpdateCustomers = custDS
  End Function
End Class
[C#]
<% @ WebService Language = "C#" Class = "Sample" %>
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;

[WebService(Namespace="https://microsoft.com/webservices/")]
public class Sample
{
  public SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

  [WebMethod( Description = "Returns Northwind Customers", EnableSession = false )]
  public DataSet GetCustomers()
  {
    SqlDataAdapter custDA = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", nwindConn);

    DataSet custDS = new DataSet();
    custDA.MissingSchemaAction = MissingSchemaAction.AddWithKey;
    custDA.Fill(custDS, "Customers");

    return custDS;
  }

  [WebMethod( Description = "Updates Northwind Customers", EnableSession = false )]
  public DataSet UpdateCustomers(DataSet custDS)
  {
    SqlDataAdapter custDA = new SqlDataAdapter();

    custDA.InsertCommand = new SqlCommand("INSERT INTO Customers (CustomerID, CompanyName) " +
                                          "Values(@CustomerID, @CompanyName)", nwindConn);
    custDA.InsertCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
    custDA.InsertCommand.Parameters.Add("@CompanyName", SqlDbType.NChar, 15, "CompanyName");

    custDA.UpdateCommand = new SqlCommand("UPDATE Customers Set CustomerID = @CustomerID, " +
                                          "CompanyName = @CompanyName WHERE CustomerID = @OldCustomerID", nwindConn);
    custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
    custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NChar, 15, "CompanyName");
    SqlParameter myParm = custDA.UpdateCommand.Parameters.Add("@OldCustomerID", SqlDbType.NChar, 5, "CustomerID");
    myParm.SourceVersion = DataRowVersion.Original;

    custDA.DeleteCommand = new SqlCommand("DELETE FROM Customers WHERE CustomerID = @CustomerID", nwindConn);
    myParm = custDA.DeleteCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
    myParm.SourceVersion = DataRowVersion.Original;

    custDA.Update(custDS, "Customers");

    return custDS;
  }
}

In a typical scenario, the UpdateCustomers method would be written to catch optimistic concurrency violations. For simplicity, the example does not include this. For more information about optimistic concurrency, see Optimistic Concurrency.

Create an XML Web service proxy.

Clients of the XML Web service will require a SOAP proxy to consume the exposed methods. A proxy can be created using the Web Services Description Language Tool (Wsdl.exe). For example, if the XML Web service is exposed at the URL, http://myserver/data/DataSetSample.asmx, issue a command like the following to create a Visual Basic .NET proxy with a namespace of WebData.DSSample and store it in the file sample.vb.
```
wsdl /l:VB /out:sample.vb http://myserver/data/DataSetSample.asmx /n:WebData.DSSample
```
To create a C# proxy in the file sample.cs, issue the following command.
```
wsdl /:CS /out:sample.cs http://myserver/data/DataSetSample.asmx /n:WebData.DSSample
```
The proxy can then be compiled as a library and imported into the XML Web service client. To compile the Visual Basic .NET proxy code stored in sample.vb as sample.dll, issue the following command.
```
vbc /t:library /out:sample.dll sample.vb /r:System.dll /r:System.Web.Services.dll /r:System.Data.dll /r:System.Xml.dll
```
To compile the C# proxy code stored in sample.cs as sample.dll, issue the following command.
```
csc /t:library /out:sample.dll sample.cs /r:System.dll /r:System.Web.Services.dll /r:System.Data.dll /r:System.Xml.dll
```

Create an XML Web service client.

After you have created the XML Web service proxy, you can import it into your client code and consume the XML Web service methods. The following sample code imports the proxy library, calls GetCustomers to get a list of customers, adds a new customer, and then returns a DataSet with the updates to UpdateCustomers. Notice that the example passes the DataSet returned by DataSet.GetChanges to UpdateCustomers because only modified rows need to be passed to UpdateCustomers. UpdateCustomers returns the resolved DataSet, which you can then Merge into the existing DataSet to incorporate the resolved changes and any row error information from the update.

Imports System
Imports System.Data
Imports WebData.DSSample

Public Class Client

  Public Shared Sub Main()
    Dim mySamp As Sample = New Sample()  ' Proxy object.

    Dim myDS As DataSet = mySamp.GetCustomers()

    Dim myTable As DataTable = myDS.Tables("Customers")

    Dim newRow As DataRow = myTable.NewRow()
    newRow("CustomerID") = "ABCDE"
    newRow("CompanyName") = "New Company Name"
    myTable.Rows.Add(newRow)

    Dim updDS As DataSet = mySamp.UpdateCustomers(myDS.GetChanges())

    myDS.Merge(updDS)
    myDS.AcceptChanges()
  End Sub
End Class
[C#]
using System;
using System.Data;
using WebData.DSSample;

public class Client
{
  public static void Main()
  {
    Sample mySamp = new Sample();  // Proxy object.

    DataSet myDS = mySamp.GetCustomers();

    DataTable myTable = myDS.Tables["Customers"];

    DataRow newRow = myTable.NewRow();
    newRow["CustomerID"] = "ABCDE";
    newRow["CompanyName"] = "New Company Name";
    myTable.Rows.Add(newRow);

    DataSet updDS = new DataSet();

    updDS = mySamp.UpdateCustomers(myDS.GetChanges());

    myDS.Merge(updDS);
    myDS.AcceptChanges();
  }
}

To compile the sample, you will need to supply the proxy library that was created (sample.dll), and the related .NET libraries. To compile the Visual Basic .NET version of the sample, stored in the file client.vb, issue the following command.

vbc client.vb /r:sample.dll /r:System.dll /r:System.Data.dll /r:System.Xml.dll /r:System.Web.Services.dll

To compile the C# version of the sample, stored in the file client.cs, issue the following command.

csc client.cs /r:sample.dll /r:System.dll /r:System.Data.dll /r:System.Xml.dll /r:System.Web.Services.dll

Share via

Consuming a DataSet from an XML Web Service

See Also

Additional resources