| [ Team LiB ] |
|
Recipe 8.10 Filling a DataSet Using an XML Template QueryProblemYou have an XML template query that you need to use from ADO.NET. You need to fill a DataSet using an XML template query. SolutionUse 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:
The sample code contains two event handlers and one method:
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,
Orders.RequiredDate,
Orders.ShippedDate,
Orders.ShipVia,
Orders.Freight,
Orders.ShipName,
Orders.ShipAddress,
Orders.ShipCity,
Orders.ShipRegion,
Orders.ShipPostalCode,
Orders.ShipCountry,
[Order Details].ProductID,
[Order Details].UnitPrice,
[Order Details].Quantity,
[Order Details].Discount
from
Orders inner join [Order Details] on
Orders.OrderID=[Order Details].OrderID
where
Orders.CustomerID=@CustomerID
for xml auto
</sql:query>
</ROOT>
The C# code is shown in Example 8-15. Example 8-15. File: UsingXmlTemplateQueriesForm.cs// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using Microsoft.Data.SqlXml;
using System.Data;
using System.Data.SqlClient;
private const String XMLQUERYFILENAME =
ConfigurationSettings.AppSettings["Project_Directory"] +
@"Chapter 08\OrdersForCustomerQuery.xml";
// . . .
private void UsingXmlTemplateQueriesForm_Load(object sender,
System.EventArgs e)
{
String sqlText = "SELECT * FROM Customers";
// Load the list of customers into a table.
SqlDataAdapter da = new SqlDataAdapter(sqlText,
ConfigurationSettings.AppSettings["Sql_ConnectString"]);
DataTable table = new DataTable("Customers");
da.Fill(table);
// Bind the default view of the table to the customer grid.
customerDataGrid.DataSource = table.DefaultView;
// Update orders grid based on the default row selected.
customerDataGrid_CurrentCellChanged(null, null);
}
private void customerDataGrid_CurrentCellChanged(object sender,
System.EventArgs e)
{
// Retrieve the selected row from the customer grid.
int row = customerDataGrid.CurrentRowIndex;
// Get the customer ID.
String customerId =
((DataView)customerDataGrid.DataSource).Table.
Rows[row][0].ToString( );
// Call method to load orders for selected customer.
LoadOrderGrid(customerId);
}
private void LoadOrderGrid(String customerId)
{
// Create the SQL XML command.
SqlXmlCommand cmd = new SqlXmlCommand(
ConfigurationSettings.AppSettings["OleDb_SqlAuth_ConnectString"]);
cmd.CommandType = SqlXmlCommandType.TemplateFile;
cmd.CommandText = XMLQUERYFILENAME;
cmd.SchemaPath = XMLSCHEMAFILENAME;
// Set the customer ID parameter for the command.
SqlXmlParameter param = cmd.CreateParameter( );
param.Name = "@CustomerID";
param.Value = customerId;
// Create the DataSet.
DataSet ds = new DataSet( );
// Create the SQL XML DataAdapter.
SqlXmlAdapter da = new SqlXmlAdapter(cmd);
// Fill the DataSet.
try
{
da.Fill(ds);
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
// Bind the default view of the orders table to the orders grid.
orderDataGrid.DataSource = ds.Tables["Orders"].DefaultView;
orderDataGrid.CaptionText = "Orders [CustomerID: " + customerId + "]";
}
DiscussionSQLXML Managed ClassesThe SQLXML Managed Classes expose SQLXML functionality from the Microsoft .NET Framework. They allow access to XML data from instance of Microsoft SQL Server 2000 or later. SQLXML Managed Classes consist of three classes:
A description of the methods and properties of these classes are shown in Tables Table 8-6 through Table 8-8. Table 8-6 describes the methods of the SqlXmlCommand class.
Table 8-7 describes the properties of the SqlXmlCommand class.
The SqlXmlParameter class has no methods. Table 8-8 describes the properties of the SqlXmlParameter class.
The SqlXmlAdapter class has no properties. Table 8-9 describes the methods of the SqlXmlAdapter class.
For more information about the SQLXML Managed Classes, see the Microsoft SQLXML release documentation. Template queriesA template query is an XML document containing one or more SQL queries or stored procedures to execute. Template queries, like stored procedures, promote code reuse, facilitate security, and encourage good design by encapsulating database-specific functionality. Parameters for the query are identified by the <sql:param> tag with the name attribute used to specify the parameter name and the parameter default value optionally specified between the <sql:param> and <sql:param> tags. Parameter tags are enclosed within the <sql:header> tags. The example shows one parameter named CustomerID without a default value: <sql:header>
<sql:param name="CustomerID" />
</sql:header>
If the CustomerID parameter had a default value of ALFKI, the parameter would be defined as follows: <sql:param name="CustomerID">ALFKI</sql:param> The SQL command text must be enclosed within <sql:query> tags. This is shown in the example: <sql:query>
select
Orders.OrderID,
Orders.CustomerID,
. . .
[Order Details].Quantity,
[Order Details].Discount
from
Orders inner join [Order Details] on
Orders.OrderID=[Order Details].OrderID
where
Orders.CustomerID=@CustomerID
for xml auto
</sql:query>
The query is a standard SQL command with the required FOR XML clause to return the result set as XML. The query could also execute a stored procedure with the EXEC or EXECUTE command followed by the name of the stored procedure and a list of parameter values as required. The query in the previous example has a single parameter, CustomerID, which is prepended with an @ when referred to in the query: where Orders.CustomerID=@CustomerID |
| [ Team LiB ] |
|