How to use XML in connected and disconnected ADO.NET applications by using Visual C# .NET or Visual C# 2005 (815672)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 2000 64 bit (all editions)
  • Microsoft ADO.NET (included with the .NET Framework) 1.0
  • Microsoft ADO.NET (included with the .NET Framework 1.1)
  • Microsoft Visual C# 2005, Express Edition
  • Microsoft Visual C# .NET (2003)
  • Microsoft Visual C# .NET (2002)
For a Microsoft Visual Basic .NET version of this article, see 307224.

IN THIS TASK

SUMMARY

Extensible Markup Language (XML) is a fundamental part of ADO.NET and Web services. This article describes 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.
Using the steps that are in this article, you use ASP.NET to write an XML Web service 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 are 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.

back to the top

Requirements

This article assumes that you are familiar with the following topics:
  • Visual C# .NET or Visual C# 2005 syntax
  • ADO.NET principles
  • ASP.NET principles
  • XML concepts
back to the top

Create an XML Web Service by Using ASP.NET

  1. Start Visual Studio .NET or Visual Studio 2005, and create a new ASP.NET Web Service application in Visual C# .NET or in Visual C# 2005.
  2. In the Location box, type http://localhost/ProductsService, and then click OK.
  3. On the View menu, click Server Explorer.
  4. In Server Explorer, expand the following nodes:
    • Servers
    • SQL Servers
    • Northwind
    • Tables
  5. Drag the Products table onto the designer.

    This action adds a SqlConnection object to your application to enable connections to the Northwind database. It also adds a SqlAdapter object to encapsulate queries and modifications to the Products table.
  6. On the Data menu, click Generate Dataset.
  7. Click New, and then name the new DataSet as ProductsDataSet.
  8. Click to select the Add this DataSet to the designer check box, and then click OK.

    This creates a typed DataSet class that is named ProductsDataSet (based on the Products table in the database), and creates a DataSet object that is named productsDataSet1.
  9. View the code for the class, and then add the following method. This XML Web service method queries the Products table and returns a DataSet in XML format to the client:
    [WebMethod]
public string GetProductsData()
{
    // Query database, and fill the DataSet with the result.
    sqlDataAdapter1.Fill(productsDataSet1.Products);
    // Write the DataSet to a string in XML format.
    System.IO.StringWriter sw = new System.IO.StringWriter();  
    productsDataSet1.WriteXml(sw);
    // Return the XML string to the client.
    return sw.ToString();
}
  10. 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 void UpdateProductsData(string Diff)
{
    //Read the XML DiffGram into the DataSet object in the XML Web service.
    System.IO.StringReader sr = new System.IO.StringReader(Diff);
    productsDataSet1.ReadXml(sr, XmlReadMode.DiffGram);

    // Use the SqlDataAdapter object to update the database accordingly.
    sqlDataAdapter1.Update(productsDataSet1.Products);
}
  11. Build the XML Web service.

back to the top

Create a Disconnected ADO.NET Windows Application

  1. In Visual Studio .NET or in Visual Studio 2005, create a new Visual C# Windows application and name it UseProducts.
  2. In the Location box, click any folder on your computer, and then click OK.
  3. Drag the following controls onto the Windows Form designer:
    • 1 DataGrid control
    • 1 RichTextBox control
    • 3 Button controls
  4. Change the names and the text of the 3 buttons as follows:

    Name of the button - Text of the button:

    • btnGetData - Get Data
    • btnSaveData - Save Data
    • btnProcessData - Process Data
  5. Drag a DataSet object onto the form. In the Add DataSet dialog box, select Untyped dataset, and then click OK.

    You use this DataSet object as a local store for the data that the XML Web service returns.
  6. On the Project menu, click Add Web Reference.
  7. In the Add Web Reference dialog box, click Web References on Local Web Server.
  8. In the Address box, type http://localhost/ProductsService/Service1.asmx, click the Go to button (green, curved arrow), and then click the Add Reference button.

    This generates a proxy class that is named Service1. Using this class, you can run methods on the Service1 XML Web service.
  9. 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.
localhost.Service1 svc = new localhost.Service1();

// Invoke the XML Web service method to get Products data in XML format.
string  strXml = 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.
System.IO.StringReader sr = 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;
  10. 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.
System.IO.StringWriter sw = 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.
localhost.Service1 svc = new localhost.Service1();

// Invoke the XML Web service method to save Products data.
svc.UpdateProductsData(sw.ToString());
  11. Define a click event handler for Process Data. Add the following code to load the DataSet data into an XmlDataDocument object. In this way, you can use XML techniques (such as XPath) to process the data:
    // Load DataSet data into an XmlDataDocument object (for DOM manipulation).
System.Xml.XmlDataDocument doc = new System.Xml.XmlDataDocument(dataSet1.Copy());

// Create an XPathNavigator object that is ready to use XPath.
System.Xml.XPath.XPathNavigator nav = doc.CreateNavigator();

// Evaluate an XPath expression.
Object obj = nav.Evaluate("count(//*[local-name()='UnitPrice'])");
MessageBox.Show("Total price for products: $" + obj.ToString ());

// Obtain the names of all products that cost more than $50.
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.ToString ()+ "\n" );
}
  12. Build the disconnected Windows Application.

back to the top

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 appears in tabular format in the DataGrid.
  3. Modify the 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.
back to the top

Create a Connected ADO.NET Windows Application

  1. In Visual Studio .NET, create a new Visual C# Windows application and name it ReportProducts.
  2. In the Location list box, click any folder on your computer, and then click OK.
  3. View the code for the application. Add two USING statements before the start of the class to simplify your code:
    using System.Data.SqlClient;   // For SqlConnection and SqlCommand
using 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 to obtain product data in XML format by using the FOR XML AUTO clause:
    SqlConnection  cn = new SqlConnection("data source=localhost;initial catalog=Northwind;integrated security=SSPI;persist security info=True;packet size=4096");
SqlCommand cmd = new SqlCommand("SELECT ProductName, UnitPrice FROM Products FOR XML RAW", cn);
cn.Open();
XmlReader r = cmd.ExecuteXmlReader();
  6. Add the following code after the previous code to use XmlReader to loop through the rows of XML data:
    while( r.Read())
{
    string name = r.GetAttribute("ProductName");
    double price = XmlConvert.ToDouble(r.GetAttribute("UnitPrice"));
    listBox1.Items.Add(name + ", $" + price.ToString ());
}
  7. After you finish reading the data, close the XmlReader and database connection by using the following code:
    r.Close();
cn.Close();
  8. Build the connected Windows Application.
back to the top

Test the Connected ADO.NET Windows Application

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

REFERENCES

For more information about ADO.NET objects and syntax, visit the following Microsoft Developer Network (MSDN) Web site:

Accessing Data with ADO.NET
http://msdn.microsoft.com/library/en-us/cpguide/html/cpconaccessingdatawithadonet.asp

Modification Type:MajorLast Reviewed:1/18/2006
Keywords:kbDataObject kbDataBinding kbDatabase kbXML kbWebServices kbHOWTOmaster KB815672 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.