Essential ADO.NET: XML and Data Access Integration

This chapter is from the book

Essential ADO.NET

Learn More Buy

This chapter is from the book

This chapter is from the book 

Essential ADO.NET

Learn More Buy

7.8 Using SQLXML and .NET

Now let's look at some of these techniques in detail. The SqlClient data provider supports ExecuteXmlReader, a provider-specific method on the SqlCommand class. Rather than provide a SqlDataReader to process the result of a SQL query, ExecuteXmlReader produces an XmlReader, which can be used to directly consume the results of a SELECT ... FOR XML query. The XmlReader might be used directly—for example, to serialize the resulting document to a Stream for transmission to a BizTalk server. The document could be serialized to disk by using an XmlTextReader. It could be read directly into the DataSet by using the DataSet's ReadXml method. Listing 7–39 shows an example. The interesting point of ExecuteXmlReader is that, if you use a FOR XML query that produces nested hierarchies of XML output (AUTO or EXPLICIT mode), it takes only a single SELECT statement to produce multiple Data-Tables with the appropriate DataRelations in the DataSet.

Listing 7–39 Using SQLXML through ExecuteXmlReader

SqlConnection conn = new SqlConnection(
  "server=.;uid=sa;database=pubs");
SqlCommand cmd = new SqlCommand(
  "select * from authors for xml auto, xmldata", 
  conn);
conn.Open();
	
XmlTextReader rdr;
rdr = (XmlTextReader)cmd.ExecuteXmlReader();

DataSet ds = new DataSet();
ds.ReadXml(rdr, 
   XmlReadMode.Fragment);

When using ExecuteXmlReader to obtain an XmlReader followed by DataSet.ReadXml to populate a DataSet, you must take certain precautions because the XML produced by SQL Server does not contain a root element. To obtain all the XML nodes, you must use XmlReadMode.XmlFragment, a special XmlReadMode. In addition, you must either prepopulate the DataSet's schema with information that matches the incoming fragment or use the XMLDATA keyword in your SQL statement to prepend an XDR schema to your fragment. This XDR format schema will prepopulate the DataSet schema, as illustrated in Listing 7–40.

Listing 7–40 Using SQLXML through ExecuteXmlReader

// 1. This produces no rows
SqlConnection conn = new SqlConnection(
  "server=.;uid=sa;database=pubs");
SqlCommand cmd = new SqlCommand(
  "select * from authors for xml auto", 
  conn);
conn.Open();

DataSet ds = new DataSet();
ds.ReadXml(
   (XmlTextReader)cmd.ExecuteXmlReader(), 
    XmlReadMode.Fragment);  

// 2. This produces 23 rows
SqlConnection conn = new SqlConnection(
  "server=.;uid=sa;database=pubs");
SqlCommand cmd = new SqlCommand(
  "select * from authors for xml auto, xmldata", 
  conn);
conn.Open();

DataSet ds = new DataSet();
ds.ReadXml(
   (XmlTextReader)cmd.ExecuteXmlReader(), 
    XmlReadMode.Fragment); 

// 3. This produces 23 rows, 2 columns
//    because two columns are mapped
//

SqlConnection conn = new SqlConnection(
  "server=.;uid=sa;database=pubs");
SqlCommand cmd = new SqlCommand(
  "select * from authors for xml auto", 
  conn);
conn.Open();

DataSet ds = new DataSet();

DataTable t = new DataTable("authors");
ds.Tables.Add(t);
t.Columns.Add("au_id", typeof(String));
t.Columns.Add("au_fname", typeof(String));

// "for xml" columns are attributes by default
for (int i=0; i<t.Columns.Count; i++)
  t.Columns[i].ColumnMapping = 
    MappingType.Attribute;

ds.ReadXml(
   (XmlTextReader)cmd.ExecuteXmlReader(), 
    XmlReadMode.Fragment); 

SQL Server's XML ISAPI application can also be used as an endpoint to produce an XmlTextReader. You can then use this XmlTextReader to populate the DataSet, as shown in Listing 7–41. This method can be executed from any machine that supports .NET. No SQL Server client software need be installed because only ordinary XML is being produced.

Listing 7–41 Using SQL Server 2000's ISAPI application

DataSet ds = new DataSet();
XmlTextReader rdr = new XmlTextReader(
   "http://localhost/northwind/template/modeauto1.xml");

ds.ReadXml(rdr);

Updategrams are supported by the OLE DB provider or ISAPI application, and although they are similar to DiffGrams, DiffGrams could be used with SQL Server 2000's ISAPI application. (SQLXML Web release 2 adds support for DiffGrams in the ISAPI application.) The updategram format is fairly straightforward and can be created most easily from the information in an updated DataSet. This book's Web site contains an example of creating updategrams from a DataSet programmatically. Updategrams and DiffGram are especially useful for composing inserts, updates, and deletes to multiple SQL Server tables in a single round-trip to SQL Server.

Although SQL Server's ability to understand MSSQLXML and XPath queries and to use streaming input and output is part of the OLE DB provider, this functionality uses recent extensions to the OLE DB specification introduced in OLE DB version 2.6. The OleDb data provider supports most of the "base" OLE DB specification, but it does not support these extensions at all. Instead of adding these extensions to the OleDb data provider (they were used only by SQLOLEDB), Microsoft released a new set of SqlXml managed data classes as part of the SQLXML Release 2 Web release. These classes not only add support for the SQLOLEDB 2.6 extensions (by wrapping the original OLE DB code) but also support client-side transformation.

The SqlXml data provider does not implement a Connection class, implementing only Command, Parameters/Parameter, and Adapter. The special Adapter class, SqlXmlAdapter, does not derive from System.Data.Common.DbDataAdapter. You use the provider to execute a FOR XML query by creating a SqlXmlCommand and using one of its methods. Three methods of SqlXmlCommand produce XML output. ExecuteStream produces a new System.IO.Stream instance containing the results, as demonstrated in Listing 7–42.

Listing 7–42 Using SqlXml's ExecuteStream

Stream s;
SqlXmlParameter p;
// note that provider keyword is required
SqlXmlCommand cmd = new SqlXmlCommand(
  "provider=sqloledb;server=localhost;" +
  "uid=sa;database=pubs");
cmd.CommandText = 
 "select * from authors where au_lname = ?" +
 " For XML Auto";

p = cmd.CreateParameter();
p.Value = "Ringer";
s = cmd.ExecuteStream(); 
StreamReader sw = new StreamReader(s);
Console.WriteLine(sw.ReadToEnd());

ExecuteToStream populates an existing instance of System.IO.Stream rather than produce a new one, as shown in Listing 7–43. SqlXmlCommand also implements the ExecuteNonQuery and ExecuteXmlReader methods, which work the same as the corresponding methods in SqlClient, adding support for the MSSQLXML and XPath dialects.

Listing 7–43 Using ExecuteToStream

SqlXmlParameter p;
SqlXmlCommand cmd = new SqlXmlCommand(
  "provider=sqloledb;server=localhost;" +
  "uid=sa;database=pubs");
cmd.CommandText = 
 "select * from authors where au_lname = ?" + 
 " For XML Auto";

MemoryStream ms = new MemoryStream();
StreamReader sr = new StreamReader(ms);
p = cmd.CreateParameter();
p.Value = "Ringer";
cmd.ExecuteToStream(ms); 
ms.Position = 0;
Console.WriteLine(sr.ReadToEnd());

SqlXml exposes all the extra functionality on the Command object that permits using streamed input, using MSSQLXML and XPath queries, specifying XML mapping schemas for XPath queries, adding XML root elements, and post-processing through an XSL stylesheet. All these are exposed as properties of SqlXmlCommand. For example, Listing 7–44 shows how to use an XPath query and XML mapping schema to execute a command on SQL Server and fetch the results.

Listing 7–44 Using an XPath query with SqlXml

Stream strm;
SqlXmlCommand cmd = new SqlXmlCommand(
  "provider=sqloledb;uid=sa;server=localhost;" +
  "database=northwind");
cmd.CommandText = "Emp";
cmd.CommandType = SqlXmlCommandType.XPath;
cmd.RootTag = "ROOT";
cmd.SchemaPath = "c:\\xml_mappings\\MySchema.xml";
strm = cmd.ExecuteStream();
StreamReader sr = new StreamReader(strm);
Console.WriteLine(sr.ReadToEnd());

<!-- this is MySchema.xml -->
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
   xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="Emp" sql:relation="Employees" >
   <xsd:complexType>
     <xsd:sequence>
        <xsd:element name="FName"  
                     sql:field="FirstName" 
                     type="xsd:string" /> 
        <xsd:element name="LName"  
                     sql:field="LastName"  
                     type="xsd:string" />
     </xsd:sequence>
     <xsd:attribute name="EmployeeID" type="xsd:integer" />
    </xsd:complexType>
  </xsd:element>
</xsd:schema>
<!-- end of MySchema.xml -->

Although this is interesting from a "use XML everywhere" point of view, what actually happens when this command is executed is that the SQLOLEDB provider processes the XPath statement and mapping schema to produce a SQL FOR XML EXPLICIT query, which is sent to SQL Server. In addition, an XML result (wrapped in a TDS packet) is returned from SQL Server to the client. Both of these behaviors might combine to make the execution quite a bit slower than using a SQL query and processing the result into XML (or HTML) on the client. When client processing is preferable, you can specify the Command.Client-SideXml property. When you use Command.ClientSideXml, the client (usually a Web server) must have SQL Server client libraries installed. The difference in processing is shown in Figure 7–4.

Figure 7-4 Database versus client transformations.

The SqlXmlAdapter has three constructors. One takes a single parameter, a SqlXmlCommand. The other two take three parameters. The first parameter is either a textual command or a CommandStream. The other two parameters are the same in both constructors: a CommandType (SqlXmlCommandType.Sql, XPath, Template, or TemplateFile), and a ConnectionString. The SqlXmlAdapter implements single Fill and Update methods, each using XML to read or update based on all the DataSet's tables. Listing 7–45 shows an example of using SqlXmlAdapter.

Listing 7–45 Using SqlXmlAdapter

SqlXmlAdapter da;
SqlXmlCommand cmd = new SqlXmlCommand(
  "provider=sqloledb;uid=sa;server=localhost;" +
  "database=northwind");
cmd.RootTag = "ROOT";
cmd.CommandText = "Emp";
cmd.CommandType = SqlXmlCommandType.XPath;
cmd.SchemaPath = "MySchema.xml";
//load data set
DataSet ds = new DataSet();
da = new SqlXmlAdapter(cmd);
da.Fill(ds);
DataRow row = ds.Tables["Emp"].Rows[0];
row["FName"] = "Bob";
da.Update(ds);

Finally, SQLXML Web Release 2 and the SqlXml data provider support using DiffGrams, in addition to updategrams, to update SQL Server. This is supported both through SqlXmlCommand and through the ISAPI application. When you use SqlXmlCommand, DiffGram is supported as SqlXmlCommandType.Template or TemplateFile. To use a DiffGram to perform updates, you must specify an XSD format mapping schema to map the DiffGram to database tables. Unlike an updategram, the DiffGram format does not include sync elements, so you are more constrained in using transactions than you are with the updategram. Also, when exceptional conditions occur when you update SQL Server through updategrams or DiffGrams, an exception is not thrown in the provider code. Instead, the resulting XML output contains the XML nodes not used, such as the nodes that were used to attempt to add a row to the database where the add failed. Listing 7–46 shows an example of updating using a DiffGram, mapping schema, and HTTP endpoint.

Listing 7–46 Using a DiffGram to update SQL Server

try
{
SqlDataAdapter da = new SqlDataAdapter(
  "select CustomerID, CompanyName, " +
  "ContactName from customers",
  "server=localhost;uid=sa;database=northwind");

DataSet ds = new DataSet();
da.Fill(ds, "Customers");

// map this to an XML Attribute
// to match the mapping schema
ds.Tables[0].Columns[0].ColumnMapping = 
   MappingType.Attribute;

// update the ninth row
ds.Tables[0].Rows[9][1] = "new customer name";
DataSet ds2 = ds.GetChanges();

HttpWebRequest r = (HttpWebRequest)WebRequest.Create(
  "http://zmv43/northwind/");
r.ContentType = "text/xml";
r.Method = "POST";

// MUST add mapping schema reference
String rootelem = "<ROOT " + 
 "xmlns:sql='urn:schemas-microsoft-com:xml-sql'" + 
 " sql:mapping-schema='diffgram1.xml'>";

String rootend = "</ROOT>";

StreamWriter s = new StreamWriter(
  r.GetRequestStream());
s.Write(rootelem, 0, rootelem.Length);
ds2.WriteXml(s, XmlWriteMode.DiffGram);
s.Write(rootend, 0, rootend.Length);
s.Close();

HttpWebResponse resp = 
    (HttpWebResponse)r.GetResponse();
StreamReader rdr = new StreamReader(
     resp.GetResponseStream());
Console.WriteLine(rdr.ReadToEnd());
}
catch (Exception e)
{
  Console.WriteLine(e.Message);
}

<!-- here's the mapping-schema -->
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  
<xsd:annotation>
  <xsd:documentation>
    Diffgram Customers/Orders Schema.
  </xsd:documentation>
</xsd:annotation>

<xsd:element name="Customers" sql:relation="Customers">
  <xsd:complexType>
    <xsd:sequence>
      <xsd:element name="CompanyName" type="xsd:string"/>
      <xsd:element name="ContactName" type="xsd:string"/>
    </xsd:sequence>
    <xsd:attribute name="CustomerID" 
     type="xsd:string" sql:field="CustomerID"/>
  </xsd:complexType>
</xsd:element>

</xsd:schema>
<!-- end of mapping-schema -->