How to use XML in connected and disconnected ADO.NET applications in Visual C++ (815671)


The information in this article applies to:

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

IN THIS TASK

SUMMARY

XML is a fundamental part of Microsoft ADO.NET and XML Web Services. This article describes how to use XML in connected and disconnected ADO.NET applications:
  • Connected ADO.NET applications can query Microsoft 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.
This article describes how to use Microsoft ASP.NET to write an XML Web service that queries a database and returns a DataSet object in XML format to the client. The client application loads the XML data in 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:
  • Microsoft Visual C++ .NET or Microsoft 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 then create a new ASP.NET Web Service project in Visual C++.
  2. In the Location box, type C:\test\, type ProductsService in the Name box, and then click OK.
  3. On the View menu, click Server Explorer.
  4. In Server Explorer, right-click Data Connections, and then click Add Connection.
  5. Set the properties in the Data Link Properties dialog box as follows:
    1. Select or enter a server name: YourSQLSERVERName
    2. Enter logon information:
      • User name= YourSQLSERVERID
      • Password= YourSQLSERVERPassword
      Where YourSQLSERVERName is the name of your SQL Server server, where YourSQLSERVERID is your user name, and where YourSQLSERVERPassword is your password.
    3. Select the database on the server: Northwind
  6. Click OK, type your password again in the SQL Server Login dialog box, and then click OK.
  7. In Server Explorer, expand the following nodes:
    • Data Connections
    • YourSQLSERVERName.Northwind.dbo
    • Tables
  8. Drag the Products table to the designer. Click Include password in the warning dialog box.

    This action 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.
  9. On the Data menu, click Generate Dataset.
  10. Click New, and then name the new DataSet ProductsDataSet.
  11. Click Add this DataSet to the designer, 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.
  12. 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 object in XML format to the client.
    public:
[WebMethod]
String __gc* 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();
}
    Note You must add the common language runtime support compiler option (/clr:oldSyntax) in Visual C++ 2005 to successfully compile the previous code sample. To add the common language runtime support compiler option in Visual C++ 2005, follow these steps:
    1. Click Project, and then click <ProjectName> Properties.

      Note <ProjectName> is a placeholder for the name of the project.
    2. Expand Configuration Properties, and then click General.
    3. Click to select Common Language Runtime Support, Old Syntax (/clr:oldSyntax) in the Common Language Runtime support project setting in the right pane, click Apply, and then click OK.
    For more information about the common language runtime support compiler option, visit the following Microsoft Web site:

    /clr (Common Language Runtime Compilation)
    http://msdn2.microsoft.com/en-us/library/k8d11d4s.aspx

    These steps apply to the whole article.
  13. 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:
    public:	
[WebMethod]
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);
}
  14. 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 Windows Forms Application (.NET) project in Visual C++, and then name it UseProducts.
  2. In the Location box, click any folder on your computer, and then click OK.
  3. Add a DataGrid control, a RichTextBox control, and a Button control to the Windows Form designer.
  4. Change the names and the text of the 3 buttons as follows:
    NameText
    btnGetDataGet Data
    btnSaveData Save Data
    btnProcessData Process Data
  5. Drag a DataSet object to the form. In the Add DataSet dialog box, click 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 Address box, type http://localhost/ProductsService/ProductsService.asmx, click Go to, and then click Add Reference.

    This generates a proxy class that is named ProductsServiceClass. By using this class, you can run methods on the ProductsService XML Web service.
  8. 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 object:
    // Create a proxy object that is ready to invoke the XML Web service method.
localhost::ProductsServiceClass * svc = new       localhost::ProductsServiceClass();

// 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->get_Item(0)->DefaultView;
  9. 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::ProductsServiceClass * svc = new localhost::ProductsServiceClass();

// Invoke the XML Web service method to save Products data.
svc->UpdateProductsData(sw->ToString());
  10. Define a click event handler for Process Data. Add the following code to load the DataSet object data in an XmlDataDocument object. In this way, you can use XML techniques (such as XPath) to process the data:
    // Load DataSet data in 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(String::Concat("Total price for products: $" , obj->ToString ()));

// Obtain the names of all products that cost more than $50.
System::Xml::XPath::XPathNodeIterator * iter = nav->Select(String::Concat("//*[local-name()='ProductName']" , "[../*[local-name()='UnitPrice'] > 50]"));

//Display product names in the text box.
richTextBox1->Clear();
while (iter->MoveNext())
{
 richTextBox1->AppendText(String::Concat("ProductName: " , iter->Current->Value->ToString (), "\n") );
}
  11. 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 object.
  3. Modify the data in the DataGrid object, and then 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 or in Visual Studio 2005, create a new Windows Application (.NET) project in Visual C++, and then 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 namespace System::Data::SqlClient; // For SqlConnection and SqlCommand
using namespace System::Xml; // For XmlReader and XmlConvert
  4. In the Windows Form designer, add a ListBox control and a Button control to 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 the XmlReader object 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(String::Concat(name , ", $" , price.ToString()));
}
  7. After you finish reading the data, close the XmlReader object and the 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:

http://msdn.microsoft.com/library/en-us/cpguide/html/cpconaccessingdatawithadonet.asp

back to the top
Modification Type:MajorLast Reviewed:1/12/2006
Keywords:kbcode kbXML kbSQLServLogship kbHOWTOmaster KB815671 kbAudDeveloper kbAudITPRO
©2004 Microsoft Corporation. All rights reserved.