How to use XML in connected and disconnected ADO.NET applications (307224)

SUMMARY

Extensible Markup Language (XML) is a fundamental part of ADO.NET and Web services. This article demonstrates how to use XML in connected and disconnected ADO.NET applications. Connected ADO.NET applications can query SQL Server to return an XML result and then use the XmlReader class to iterate through the rows of XML data. Disconnected ADO.NET applications can use a DataSet object to pass data in XML format between the different tiers in a distributed system.

In this article, you write an XML Web service by using ASP.NET that queries a database and returns a DataSet in XML format to the client. The client application loads the XML data into a local DataSet object and binds it to a DataGrid control. If the user changes any data, the client application posts a DiffGram to the XML Web service. The DiffGram indicates which rows have been inserted, modified, or deleted. The XML Web service uses the DiffGram to update the database.

In this article, you also write a connected ADO.NET application for reporting purposes. This application uses an XmlReader class to display current information in the database.

Requirements

This article assumes that you are familiar with the following topics:

• Visual Basic .NET syntax
• ADO.NET principles
• ASP.NET principles
• XML concepts

Create an XML Web service using ASP.NET

1. Start Visual Studio .NET, and create a new ASP.NET Web Service application in Visual Basic.
2. In the Name text box, type ProductsService. In the Location drop-down list box, click http://localhost, and then click OK.
3. On the View menu, click Server Explorer.
4. Click to expand the Servers node, the node that lists your computer name, the SQL Servers node, the node that lists your computer name, the Northwind node, and finally the Tables node.
5. Drag the Products table onto the designer. This adds a SqlConnection object to your application to enable connections to the Northwind database. It also adds a SqlDataAdapter object to encapsulate queries and modifications to the Products table.
6. On the Data menu, click Generate Dataset.
7. Click New, and set the name of the new DataSet to ProductsDataSet. Select the Add this DataSet to the designer check box, and then click OK. This creates a typed DataSet class named ProductsDataSet (which is based on the Products table in the database), and creates a DataSet object named ProductsDataSet1.
8. View the code for the class, and add the method to follow. This XML Web service method queries the Products table and returns a DataSet in XML format to the client:

   <WebMethod()> Public Function GetProductsData() As String
      ' Query database, and fill the DataSet with the result.
      SqlDataAdapter1.Fill(ProductsDataSet1.Products)
   
      ' Write the DataSet to a string in XML format.
      Dim sw As System.IO.StringWriter = New System.IO.StringWriter()
      ProductsDataSet1.WriteXml(sw)
   
      ' Return the XML string to the client.
      Return sw.ToString()
   End Function
   					

9. Add another XML Web service method. The following method receives a DiffGram from a client and updates the database with the changes that are defined in the DiffGram:

   <WebMethod()> Public Sub UpdateProductsData(ByVal Diff As String)
      ' Read the XML DiffGram into the DataSet object in the XML Web service.
      Dim sr As System.IO.StringReader = New System.IO.StringReader(Diff)
      ProductsDataSet1.ReadXml(sr, XmlReadMode.DiffGram)
   
      ' Use the SqlDataAdapter object to update the database accordingly.
      SqlDataAdapter1.Update(ProductsDataSet1.Products)
   End Sub
   					

10. Build the XML Web service.

Create a disconnected ADO.NET Windows application

1. In Visual Studio .NET, create a new Visual Basic Windows Application. In the Name text box, type UseProducts. In the Location drop-down list box, click any folder on your computer, and then click OK.
2. Drag a DataGrid control onto the Windows Form designer. Also, drag a RichTextBox control and three Button controls. Change the names of the buttons to btnGetData, btnSaveData, and btnProcessData. Change the text of the buttons to Get Data, Save Data, and Process Data respectively.
3. Drag a DataSet object onto the form. In the Add DataSet dialog box, choose Untyped dataset, and then click OK. You will use this DataSet object as a local store for the data that the XML Web service returns.
4. On the Project menu, click Add Web Reference.
5. In the Add Web Reference dialog box, type http://localhost/ProductsService/Service1.asmx in the URL box, click Go, and then click Add Reference. This generates a proxy class named Service1, which enables you to invoke methods on the Service1 XML Web service.
6. Define a click event handler for the Get Data button. Add the following code to the method to request data from the XML Web service and display it in the DataGrid:

   ' Create a proxy object that is ready to invoke the XML Web service method.
   Dim svc As localhost.Service1 = New localhost.Service1()
   
   ' Invoke the XML Web service method to get Products data in XML format.
   Dim strXml As String = svc.GetProductsData()
   
   ' Display XML data in the text box to show what it looks like.
   RichTextBox1.Text = strXml
   
   ' Read the XML data into the local DataSet.
   Dim sr As System.IO.StringReader = New System.IO.StringReader(strXml)
   DataSet1.ReadXml(sr)
   DataSet1.AcceptChanges()
   
   ' Bind the DataGrid to the DataSet to display the data.
   DataGrid1.DataSource = DataSet1.Tables(0).DefaultView
   					

7. Define a click event handler for the Save Data button. Add the following code to the method to send an XML DiffGram to the XML Web service. The XML DiffGram tells the XML Web service what data has changed:

   ' Write the DataSet data as an XML DiffGram string.
   Dim sw As System.IO.StringWriter = New System.IO.StringWriter()
   DataSet1.WriteXml(sw, XmlWriteMode.DiffGram)
   
   ' Display the XML DiffGram in the text box to show what it looks like.
   RichTextBox1.Text = sw.ToString()
   
   ' Create a proxy object that is ready to invoke the XML Web service method.
   Dim svc As localhost.Service1 = New localhost.Service1()
   
   ' Invoke the XML Web service method to save Products data.
   svc.UpdateProductsData(sw.ToString())
   					

8. Define a click event handler for the Process Data button. Add the following code to load the DataSet data into an XmlDataDocument object. This enables you to use XML techniques (such as XPath) to process the data:

   ' Load DataSet data into an XmlDataDocument object (for DOM manipulation).
   Dim doc As System.Xml.XmlDataDocument
   doc = New System.Xml.XmlDataDocument(DataSet1.Copy())
   
   ' Create an XPathNavigator object that is ready to use XPath.
   Dim nav As System.Xml.XPath.XPathNavigator = doc.CreateNavigator()
   
   ' Evaluate an XPath expression.
   Dim obj As Object = nav.Evaluate("count(//*[local-name()='UnitPrice'])")
   MessageBox.Show("Total price for products: $" & obj)
   
   ' Obtain the names of all products that cost more than $50.
   Dim iter As System.Xml.XPath.XPathNodeIterator
   iter = nav.Select("//*[local-name()='ProductName']" & _
                     "[../*[local-name()='UnitPrice'] > 50]")
   
   'Display product names in the text box.
   RichTextBox1.Clear()
   While iter.MoveNext()
      RichTextBox1.AppendText("ProductName: " & iter.Current.Value & vbCrLf)
   End While
   					

9. Build the disconnected Windows Application.

Test the disconnected ADO.NET Windows application

1. Run the disconnected Windows application.
2. Click Get Data to retrieve data through the XML Web service. The retrieved data appears in XML format in the text box and in tabular format in the DataGrid.
3. Modify data in the DataGrid, and insert new rows.
4. Click Save Data to save the changes back to the database through the XML Web service.
5. Click Process Data to process the DataSet as XML. A message box displays the total price of all products. The text box displays the name of each product that costs more than 50 dollars.
6. Close the disconnected Windows application.

Create a connected ADO.NET Windows application

1. In Visual Studio .NET, create a new Visual Basic Windows Application.
2. In the Name text box, type ReportProducts. In the Location list box, click any folder on your computer, and then click OK.
3. View the code for the application. Add two Imports statements before the start of the class to simplify your code:

   Imports System.Data.SqlClient   ' For SqlConnection and SqlCommand
   Imports System.Xml              ' For XmlReader and XmlConvert
   					

4. In the Windows Form designer, drag a ListBox control and a Button control onto your form.
5. Define a click event handler for the button. Add the following code to the method to connect to the Northwind database and obtain product data in XML format by using the FOR XML AUTO clause:

   Dim cn As SqlConnection = New SqlConnection( _
     "data source=(local);initial catalog=Northwind;" & _
     "integrated security=SSPI;persist security info=True;packet size=4096")
   
   Dim cmd As SqlCommand = New SqlCommand( _
     "SELECT ProductName, UnitPrice FROM Products FOR XML RAW", cn)
   cn.Open()
   Dim r As XmlReader = cmd.ExecuteXmlReader()
   					

6. Add the following code after the preceding code to use XmlReader to loop through the rows of XML data:

   While r.Read()
      Dim name As String = r.GetAttribute("ProductName")
      Dim price As Double = XmlConvert.ToDouble(r.GetAttribute("UnitPrice"))
      ListBox1.Items.Add(name & ", $" & price)
   End While
   					

7. After you finish reading the data, close the XmlReader and database connection as follows:

   r.Close()
   cn.Close()
   					

8. Build the connected Windows Application.

Test the connected ADO.NET Windows application

1. Run the connected application.
2. Click the button on the form.
3. The list box displays the name and price for all products. Verify that the data reflects the changes that you made when you ran the disconnected Windows application earlier.