9. Chapter 9 Handling Data in .NET Web Services

Why does this applied science, which saves work and makes life easier, bring us so little happiness? The simple answer runs: Because we have not yet learned to make sensible use of it. – Albert Einstein

Now that you know how to build and invoke Web services you’ll want to do something useful with them. The most common applications for Web services involve moving data in and out of data sources on the intranet or Internet. This chapter explains the options you have for dealing with data in .NET Web services including how to use ADO.NET DataSets, arrays, and your own custom classes.

Serialization and Web Services

Chapter 2 introduced you to the process of serializing objects and primitives to XML and you learned how .NET’s serialization attributes can be used to control this process. Serialization is an important part of Web services because it provides the mapping between the data structures you use in Visual Basic and XML messages.

It’s important to understand that all Web services receive and return is XML, everything else is an illusion performed by the tools you use. So the next time you create a Web method that returns a DataSet or an array of objects, you should keep in mind that what you are really returning is XML. This XML is obtained by serializing the DataSet or the array of objects. The client never sees your array or your DataSet, it receives only XML. Depending on the tools the client uses, it might treat that XML as an XML document, or it might deserialize it into an array of objects or a DataSet. For example, figure 9-1 shows a .NET Web service that exposes a Web method which returns a DataSet. The returned DataSet is serialized to XML and that’s all that clients receive.

Figure 9‑1

Serialization and Web services

A VB 6 client might handle this XML directly using the Document Object Model. A .NET client might deserialize this XML back into a DataSet, but this is not the same DataSet that was returned by the Web method call, it’s another DataSet reconstructed from the received XML.

.NET generally tries to make life easier for client developers by deserializing the received XML into client-side objects such as the DataSet. The exact types of objects used for this deserialization depends on the Web service’s WSDL. Therefore, if you want to change how the client sees these types, you need to somehow change the Web service’s WSDL. You do this using the serialization attributes discussed in Chapter 2. The next sections will examine applications of serialization and serialization attributes within the context of .NET Web services.

Starting with a Relational Database

A common Web service scenario is when you have a relational database and you want to expose some of it to applications over the Web. So you create a Web service with some Web methods that return DataSets. If you are going to allow clients to update your database, you might also create some Web methods that take in a DataSet and use it to update the database. Listing 9-1 shows an example Web method called GetCustomersDataSet that returns a DataSet from the Customers table in the Northwind database.

Listing 9‑1 A Web method that returns a DataSet. (VBWSBook\Chapter9\CustomerOrders.asmx.vb).

<WebMethod()> _

Public Function GetCustomersDataSet() As DataSet

Dim Sql As String = "SELECT CustomerID, CompanyName FROM Customers"

Dim ds As DataSet

ds = GetData(Sql, "Customers")

Return ds

End Function

Private Function GetData( _

ByVal Sql As String, _

ByVal TableName As String) As DataSet

Dim ConnStr As String = _

ConfigurationSettings.AppSettings.Get("ConnStr")

Dim cn As New SqlConnection(ConnStr)

Try

cn.Open()

Dim da As New SqlDataAdapter(Sql, cn)

Dim ds As New DataSet()

ds.Namespace = "http://www.LearnXmlWS.com/customerorders/ds"

da.Fill(ds, TableName)

Return ds

Finally

cn.Dispose()

End Try

End Function

GetCustomersDataSet uses GetData to create a new DataSet and fill it with a list of customer ids and company names from the Customers table. It then returns this DataSet to the client. Remember, it just looks like you’re returning a DataSet what’s really returned is XML. Listing 9-2 shows an example request message and the corresponding response message with the DataSet. It should be very clear from listing 9-2 that all the client receives is XML.

Listing 9‑2 Example request and response messages for the Web method in listing 9-1

<soap:Envelope

xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<soap:Body>

<GetCustomersDataSet

xmlns="http://www.LearnXmlWS.com/customerorders" />

</soap:Body>

</soap:Envelope>

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<soap:Body>

<GetCustomersDataSetResponse

xmlns="http://www.LearnXmlWS.com/customerorders">

<xsd:schema id="NewDataSet"

targetNamespace="http://www.LearnXmlWS.com/customerorders/ds"

xmlns="http://www.LearnXmlWS.com/customerorders/ds"

xmlns:xsd="http://www.w3.org/2001/XMLSchema"

xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"

attributeFormDefault="qualified"

elementFormDefault="qualified">

<xsd:element name="NewDataSet" msdata:IsDataSet="true">

</xsd:element>

</xsd:schema>

<diffgr:diffgram

xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"

xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">

<CustomerID>ALFKI</CustomerID>

<CompanyName>Alfreds Futterkiste</CompanyName>

</Customers>

</NewDataSet>

</diffgr:diffgram>

</GetCustomersDataSetResult>

</GetCustomersDataSetResponse>

</soap:Body>

</soap:Envelope>

The response message in listing 9-2 contains the serialized DataSet beginning with the <xsd:schema> element. By default, a serialized DataSet contains first an XSD schema then a DiffGram containing the data. If you take a look at the service’s WSDL document you’ll see that the type definitions reflect this as shown in listing 9-3.

Listing 9‑3 WSDL defining the response message for the method shown in listing 9-1

<s:element name="GetCustomersDataSetResponse">

<s:complexType>

<s:sequence>

<s:element minOccurs="1" maxOccurs="1"

name="GetCustomersDataSetResult" nillable="true">

<s:complexType>

<s:sequence>

<s:element ref="s:schema" />

<s:any />

</s:sequence>

</s:complexType>

</s:element>

</s:sequence>

</s:complexType>

</s:element>

I extracted the schema in listing 9-3 from the types section of the service’s WSDL. The schema declares an element called GetCustomersDataSetResult which is of a complex type that contains a sequence of two elements. First, <s:element ref="s:schema"/> means that the first element will be a <schema> element which belongs to the XSD namespace denoted here by the s prefix. Second, <s:any/> means another element will follow whose name and type are undetermined.

Typed DataSets

Although the type definition in listing 9-3 describes the serialized DataSet, it doesn’t really tell the client much about what this DataSet will contain. A developer looking at this WSDL cannot infer much about the structure of the returned XML. To make your service’s WSDL more specific about what you are returning, you should return a typed DataSet. A typed DataSet is a class that inherits from DataSet and defines the DataSet’s structure including tables, columns, and relations at design time. This makes it possible for .NET to emit more specific type definitions in the service’s WSDL which means clients can better understand the data they are getting.

If you have an XML Schema that describes your data, you can easily create a typed DataSet using xsd.exe (more on this later in this chapter). But if you just have the relational database, you can use Visual Studio’s Schema designer and Server Explorer to create a typed DataSet.

Figure 9‑2

Adding a new typed DataSet to the project

From the project menu, choose Add New Item. Select the DataSet template and enter the name of your typed DataSet e.g. Customers (as in figure 9-2) then click Open. This will add a new schema called Customers.xsd to your project and bring up the Schema designer.

Click on the DataSet tab at the bottom to switch to designer view if you’re not already there. Now open the Server Explorer and add a new database connection to your database server if you don’t already have one. Expand this connection and find the table you want e.g. Customers. Drag this table and drop it on the Schema designer. You should now have an element declaration with a complex type that corresponds to the Customers table. You can repeat this process for all the tables you want to have in the DataSet. For example, drag the Orders table onto the Schema designer to create an Orders element as shown in figure 9-3.

Figure 9‑3

Adding tables to the typed DataSet

You can now set some of the properties of the schema itself by clicking anywhere on the designer surface then opening the Properties window which will show a list of schema properties including targetNamespace and dataSetName as shown in figure 9-4. Go ahead and set the dataSetName to CustomerOrdersDataSet and the targetNamespace to http://www.LearnXmlWS.com/customerorders/schema.

Figure 9‑4

Settitng schema properties including targetNamespace

The schema you just created contains the elements Customers and Orders but they are unrelated. You can create a relation by simply dragging a Relation from the toolbox onto the CustomerID element. You’ll then get the Edit Relation dialog which is shown in figure 9-5. This dialog lets you specify the name of the relation as well the parent and child elements (tables) and fields (columns) involved in this relation.

At the bottom of this dialog you’ll see some DataSet-specific properties. These properties affect the structure of a DataSet that reads this schema. For example, checking “Create foreign key constraint only” would cause the DataSet to create primary key and foreign key constraints but the DataSet tables would not be related as parent/child. The remaining three properties affect how changes in primary key value in the parent table affect the corresponding records in the child table.Table 9-1 shows the meaning of these three properties.

Table 9‑1 DataSet relation rules

Property	Meaning
Update rule	How updates to the parent table are handled. Cascade (the default) means updates cascade down to records in the child table. SetNull or SetDefault means values in related rows are set to DBNull or the default value respectively. None means nothing is done to related rows.
Delete rule	How deleted parent records are handled. Cascade (the default) means child records are deleted. SetNull or SetDefault means values in related rows are set to DBNull or the default value respectively. None means nothing is done to related rows.
Accept/Reject rule	When you manipulate the parent DataTable and delete or update parent records then call AcceptChanges or RejectChanges, this rule is invoked to determine what happens. Cascade is the default and means that updates and/or deletes should be cascaded to the related table(s). None means no action should be taken on related tables.

Figure 9‑5

Editing a DataSet relation

After setting the relation’s properties and clicking OK, you should see a link between the Customers and Orders elements indicating they are related.

Figure 9‑6

Typed DataSet class called CustomerOrdersDataSet

When you build the project a .vb file is automatically generated which contains class definitions for the typed DataSet classes as shown in figure 9-6. For example, you’ll have a class called CustomerOrdersDataSet which inherits from System.Data.DataSet. You’ll also have the classes CustomersDataTable and OrdersDataTable both of which inherit from System.Data.DataTable.

You can use CustomerOrdersDataSet just like you would a DataSet including filling it with data using SqlDataAdapter and returning it from a Web method. Listing 9-4 shows an example Web method that returns an instance of CustomerOrdersDataSet.

Listing 9‑4 A Web method that returns an instance of the typed DataSet. (VBWSBook\Chapter9\CustomerOrders.asmx.vb)

<WebMethod()> _

Public Function GetCustomerOrdersTypedDataSet() As CustomerOrdersDataSet

Dim Sql1 As String = "SELECT * FROM Customers"

Dim Sql2 As String = "SELECT * FROM Orders"

Dim ds As New CustomerOrdersDataSet()

Dim ConnStr As String = _

ConfigurationSettings.AppSettings.Get("ConnStr")

Dim cn As New SqlConnection(ConnStr)

Try

cn.Open()

Dim da1 As New SqlDataAdapter(Sql1, cn)

Dim da2 As New SqlDataAdapter(Sql2, cn)

da1.Fill(ds, "Customers")

da2.Fill(ds, "Orders")

Catch ex As Exception

Debug.WriteLine(ex.Message)

Finally

cn.Dispose()

End Try

Return ds

End Function

The code in listing 9-4 uses two SqlDataAdapters to load data from the Customers and Orders tables into an instance of CustomerOrdersDataSet then returns that instance. The resulting WSDL imports the typed DataSet’s schema as shown in listing 9-5.

Listing 9‑5 Returning a typed DataSet causes the service’s WSDL to import the DataSet’s schema

<import namespace="http://www.LearnXmlWS.com/customerorders/schema" location=

"http://localhost/VBWSBook/Chapter9/CustomerOrders.asmx?schema=CustomerOrdersDataSet" />

<types>

<s:schema attributeFormDefault="qualified"

elementFormDefault="qualified"

<s:element name="GetCustomerOrdersTypedDataSetResponse">

<s:complexType>

<s:sequence>

<s:element minOccurs="1" maxOccurs="1"

name="GetCustomerOrdersTypedDataSetResult"

nillable="true">

<s:complexType>

<s:sequence>

<s:any

namespace="http://www.LearnXmlWS.com/customerorders/schema" />

</s:sequence>

</s:complexType>

</s:element>

</s:sequence>

</s:complexType>

</s:element>

</s:schema>

</types>

Listing 9-5 shows a fragment of the Web service’s WSDL. Notice that clients can get the typed DataSet’s schema from WebserviceUrl?schema=CustomerOrdersDataSet. Also note that the Web method’s return value is defined as having a sequence of any elements that belong to the namespace http://www.LearnXmlWS.com/customerorders/schema which is the namespace we used for the typed DataSet’s schema. A client can look at this WSDL and the typed DataSet’s schema and understand the structure of XML returned from GetCustomerOrdersTypedDataSet which is the primary benefit of using typed DataSets versus ordinary DataSets.

Nested Elements

If you open the typed DataSet’s schema in XML view you’ll see that the Customers and Orders elements are not nested. That is, an instance document would look something like this:

</CustomerOrdersDataSet>

Suppose you wanted Orders elements to be nested within Customers elements instead, like this:

</Customers>

</Customers>

</CustomerOrdersDataSet>

To get this format, you must change the Customers element’s complex type in the DataSet schema to indicate that it will contain Orders elements. You can do this by editing the schema directly or using the designer by dragging the Orders element onto the Customers element. The resulting typed DataSet will have two relations: One based on the nesting of Customers and Orders and the other based on the unique/keyref constraints. In most cases what you really want is one relation based on the unique/keyref constraints, i.e. you want to relate the two tables based on the primary key/foreign key constraints. To get rid of the extra relation, you need to edit the schema and add an msdata:IsNested="true" attribute on the <xsd:keyref> element like this:

<xsd:keyref name="CustomersOrders"

refer="NestedCustomersKey1"

msdata:IsNested="true">

IsNested is one of the annotations you can use to affect how a DataSet is constructed from a schema. Other annotations will be covered in detail later in this chapter. There’s an example nested DataSet in this chapter’s Web service project. Listing 9-6 shows the modified schema for this nested DataSet.

Listing 9‑6 Part of the modified schema for Orders nested within Customers. (VBWSBook\Chapter9\Customers.xsd)

<xsd:schema ...>

<xsd:element msdata:IsDataSet="true" name="NestedCustomers">

<xsd:complexType>

<xsd:choice maxOccurs="unbounded">

<xsd:element name="Customers">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="CustomerID" type="xsd:string" />

<xsd:element name="CompanyName" type="xsd:string" />

<xsd:element minOccurs="0"

maxOccurs="unbounded" name="Orders">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="OrderID" msdata:ReadOnly="true"

msdata:AutoIncrement="true" type="xsd:int" />

<xsd:element name="CustomerID"

minOccurs="0" type="xsd:string" />

<xsd:element name="EmployeeID"

minOccurs="0" type="xsd:int" />

<xsd:element name="OrderDate"

minOccurs="0" type="xsd:dateTime" />

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:choice>

</xsd:complexType>

</xsd:element>

</xsd:schema>

When you serialize the resulting typed DataSet it will nest <Orders> elements inside the parent <Customers> element as specified by the modified schema.

Round-Triping DataSets

Now that you know how to create typed DataSets, let’s look at a Web service and a Windows Forms client that use typed DataSets to send data back and forth over the Internet. Listing 9-7 shows a Web method called SaveCustomerOrdersTypedDataSet which receives a DataSet, uses it to update the Customers and Orders tables, then returns the refreshed DataSet. This method complements the GetCustomerOrdersTypedDataSet method in listing 9-4.

Listing 9‑7 A method to save changes to the database. (VBWSBook\Chapter9\CustomerOrders.asmx)

<WebMethod()> _

Public Function SaveCustomerOrdersTypedDataSet( _

ByVal ds As CustomerOrdersDataSet) _

As CustomerOrdersDataSet

Dim Sql1 As String = "SELECT CustomerID, CompanyName FROM Customers"

Dim Sql2 As String = "SELECT * FROM Orders"

Dim ConnStr As String = _

ConfigurationSettings.AppSettings.Get("ConnStr")

Dim cn As New SqlConnection(ConnStr)

Try

cn.Open()

Dim da1 As New SqlDataAdapter(Sql1, cn)

Dim da2 As New SqlDataAdapter(Sql2, cn)

Dim cb1 As New SqlCommandBuilder(da1)

Dim cb2 As New SqlCommandBuilder(da2)

da1.Update(ds, "Customers")

da2.Update(ds, "Orders")

cn.Close()

Catch ex As Exception

Debug.WriteLine(ex.Message)

Throw New Exception(ex.Message)

Finally

cn.Dispose()

End Try

Return ds

End Function

To update a database from a DataSet, you need to create a SqlDataAdapter (or an OleDbDataAdapter) for each database table that you want to update. You then have to create Select, Update, Insert, and Delete commands for the adapter. When there’s a one-to-one correspondance between tables in your DataSet and database tables, you can create just the Select command then use a SqlCommandBuilder which builds the remaining commands for you.

After you’ve created the data adapters, you call Update on each adapter passing it the DataSet and the name of the table to update. In addition to updating the database table, the adapter will also refresh the DataSet with the current database table’s content.

After updating both Customers and Orders tables, SaveCustomerOrdersTypedDataSet returns the refreshed DataSet. This is not necessary unless clients need to display the current content of the database which is usually the case. Listing 9-8 shows an example Windows Forms client code.

Listing 9‑8 A client invoking GetCustomerOrdersTypedDataSet and SaveCustomerOrdersTypedDataSet. (VBWSClientCode\Chapter9\frmRndTrip.vb)

Public Class frmRndTrip

Inherits System.Windows.Forms.Form

Private _UseProxy As Boolean

Private ds As vbwsserver.CustomerOrdersDataSet

Dim ws As vbwsserver.CustomerOrders

Private Sub frmRndTrip_Load(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles MyBase.Load

ws = New vbwsserver.CustomerOrders()

SetProxy(ws)

GetData()

End Sub

Private Sub btnRefresh_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles btnRefresh.Click

lblStatus.Text = "Refreshing ..."

Me.Refresh()

GetData()

lblStatus.Text = "Refresh completed successfully"

End Sub

Private Sub GetData()

ds = ws.GetCustomerOrdersTypedDataSet()

dgOrders.DataSource = ds

End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles btnSave.Click

lblStatus.Text = "Saving ..."

Me.Refresh()

Dim newDS As New vbwsserver.CustomerOrdersDataSet()

newDS = ws.SaveCustomerOrdersTypedDataSet(ds.GetChanges())

ds.Merge(newDS)

lblStatus.Text = "Save completed successfully"

End Sub

End Class

This Windows Forms client consists of a form with a data grid, a button for saving changes, and a button for refreshing the data. The client has added a Web reference to the Customers Web service on VBWSServer. As part of adding a Web reference, the generated proxy code includes a typed DataSet called CustomerOrdersDataSet which mirrors the one used by the service.

Listing 9-8 declares a member variable called ds of type CustomerOrdersDataSet (which belongs to the VBWSServer namespace). This variable is declared as a class member variable because it will be used to hold the data returned from the Web service and all updates made by the user.

When the form is loaded, the code in frmRndTrip_Load instantiates a Web service proxy and calls GetCustomerOrdersTypedDataSet to retrieve the DataSet. It then binds the data grid called dgOrders to the returned DataSet. The grid displays a hierarchy of customers and their orders as shown in figure 9-7.

The user can then edit the data and click Save which executes btnSave_Click in listing 9-8. This procedure first calls ds.GetChanges to get a new DataSet that contains changed rows. It then calls the Web service’s SaveCustomerOrdersTypedDataSet method passing it the changes DataSet and receiving a refreshed DataSet. To display any new data in the returned DataSet, it calls ds.Merge passing it the returned DataSet.

The code you write for sending and receiving DataSets on the Web service side is concise and straightforward. Similarly, .NET clients can leverage DataSets to write equally concise code. You have to keep in mind that other clients, such as VB 6 and Java, would have to work with the DataSet as XML. Using XML technologies such as the Document Object Model (DOM), XML Path (XPath), and Extensible Stylesheet Language Transformation, a non-.NET client has a rich programming model that allows extensive manipulation of a DataSet represented as XML. However, non-.NET clients will require more code to manipulate the DataSet compared to .NET clients. In chapter 12 you will see a Java client that calls this Web service, manipulates the returned dataset then sends it back to the Web service for saving.

Figure 9‑7

Customers and their orders displayed in a data bound grid

Starting with a Schema

The previous section covered the process of exposing/updating data when you start with a relational database. In many cases, especially business-to-business integration scenarios, you’ll start with an XML schema that describes the business documents to be exchanged.

The next sections explain some of the tasks you’ll perform when starting with a schema such as creating DataSets from the schema and importing/exporting data according to that schema.

DataSets from Schemas

Starting with a schema you have the option to create the DataSet structure at runtime or at design time. To create the DataSet structure at runtime, you instantiate a DataSet and tell it to read an XML schema causing it to create tables, columns, constraints, and relations based on the schema. You can then fill this DataSet with data from a relational database or from an XML document. The role of the schema here is just to tell the DataSet about the table structure.

Consider for example the invoice schema in listing 9-9. This simple schema declares an invoice element that contains the usual information like invoiceNumber and invoiceDate. The invoice also contains item elements whose type is defined by ItemType.

Listing 9‑9 An example invoice schema. (VBWSBook\Chapter9\Invoice.xsd).

<xsd:schema

targetNamespace="http://www.LearnXmlWS.com/Invoicing/schema"

xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:ws="http://www.LearnXmlWS.com/Invoicing/schema" elementFormDefault="qualified">

<xsd:element name="invoice">

<xsd:complexType>

<xsd:sequence>

<xsd:element name="invoiceNumber" type="xsd:string" />

<xsd:element name="supplierID" type="xsd:int" />

<xsd:element name="invoiceDate" type="xsd:date" />

<xsd:element name="poNumber" type="xsd:string" />

<xsd:element name="subTotal" type="ws:money" />

<xsd:element name="salesTax" type="ws:money" />

<xsd:element name="paymentReceived" type="ws:money" />

<xsd:element name="amtDue" type="ws:money" />

<xsd:element name="terms" type="xsd:string" minOccurs="0" />

<xsd:element name="contactName"

type="xsd:st