ADO.NET Cookbook

8.10. Filling a DataSet Using an XML Template Query

Problem

You have an XML template query that you need to use from ADO.NET. You need to fill a DataSet using an XML template query.

Solution

Use an XML template query to fill a DataSet using the SQLXML Managed Classes.

The sample uses one XML file as shown in Example 8-14:

OrdersForCustomerQuery.xml

Contains the XML template query

The sample code contains two event handlers and one method:

Form.Load

Loads all Customers from Northwind into a DataTable. The default view of the DataTable is bound to the top data grid on the form. The DataGrid.CurrentCellChanged event handler is called to refresh the bottom data grid.

DataGrid.CurrentCellChanged

Gets the CustomerID for the selected row in the top data grid and calls the LoadOrderGrid( ) method to refresh the bottom grid.

LoadOrderGrid( )

This method creates a SqlXmlCommand template query and its single SqlXmlParameter object. The parameter is set to the user-specified value. A SqlXmlDataAdapter object is created and executed to fill a new DataSet based on the template query. The default view of the Orders table in the result DataSet is bound to the lower data grid on the form displaying the orders for the selected customer.

Example 8-14. File: OrdersForCustomerQuery.xml

<?xml version="1.0" encoding="utf-8" ?> <ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'> <sql:header> <sql:param name="CustomerID" /> </sql:header> <sql:query> select Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, ...