SQLXML Managed Classes

SQLXML 3.0 comes with a handful of managed classes designed to expose the functionality of SQLXML 3.0 inside the .NET Framework. SQLXML managed classes allow you bring XML data read from SQL Server into .NET Framework applications, process the data, and send any updates back to SQL Server as an ADO.NET DiffGram. The managed classes are exposed by the microsoft.data.sqlxml assembly.

SQLXML does not get along perfectly with the .NET Framework data provider for SQL Server. SQLXML needs to address special XML-driven functionalities of SQL Server 2000 that the .NET Framework data provider simply does not support. As a result, the SQL Server .NET Framework provider can handle traditional SQL queries, including FOR XML queries, but it can’t execute XML templates (for example, Updategrams) or server-side XPath queries over XML views. For this reason, SQLXML managed classes rely on the SQLXMLOLEDB OLE DB provider for all of the tasks that involve a SQL Server connection.

Figure 8-5 illustrates the key role that the SqlXmlCommand class and its ExecuteStream method play in the overall SQLXML 3.0 architecture.

The set of SQLXML managed classes consists of two main classes—SqlXml­Command and SqlXmlAdapter—plus a few ancillary classes like SqlXml­Parameter and SqlXmlException. SqlXmlCommand is the fundamental class used to execute an XML-driven command against SQL Server. The SqlXmlAdapter class is actually a wrapper for the command that simply exposes the results through a DataSet object.

The SqlXmlCommand Class

The SqlXmlCommand class represents any XML command you can send to SQL Server 2000. As mentioned, you should use this class only to issue those XML-related commands that the .NET Framework data provider for SQL Server does not natively support. The class reliance on an OLE DB provider makes rather ineffective any kind of abuse from within a .NET Framework application.

Do not use SqlXmlCommand to execute a simple FOR XML query, but take it into account when you need to work with Updategrams, server-side XPath queries (assuming that an XPath query makes sense at all in the context of the application), or XML views.

SqlXmlCommand Properties

The properties available in the SqlXmlCommand class let you configure the query. Unlike most ADO.NET command classes, the SqlXmlCommand class provides a command stream property that applications can use to pass potentially lengthy input data such as Updategrams. Table 8-2 summarizes the properties of the SqlXmlCommand class.

Streams play a key role in the SqlXmlCommand class. Not only can you use a stream to specify the input of a command, but you can also pick up the results of the command from an output stream. You can also control the encoding of this output stream. For a better understanding of these properties, review the ADO example about Updategrams in the section “Submitting Commands Through Updategrams,” on page 383.

Supported Command Types

The SqlXmlCommand class can execute a variety of commands. The allowable command types are defined in the SqlXmlCommandType enumeration and are shown in Table 8-3.

A template is an XML document that contains T-SQL commands wrapped in ad hoc XML attributes, as shown here:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:query>
    SELECT * FROM Employees FOR XML AUTO
  </sql:query>
</ROOT>

The template specifies a sequence of commands to produce a particular result set. Overall, a template is a dynamically defined stored procedure expressed using XML syntax and supporting XPath queries.

SqlXmlCommand Methods

On instantiation, the SqlXmlCommand class creates an instance of the SQLXMLOLEDB provider. Interestingly, it does not make use of an explicit wrapper assembly but instead gets a COM object type using the static method GetTypeFromCLSID from the Type class. Next it instantiates the COM object using the Activator class.

The methods provided by the SqlXmlCommand class are described in Table 8-4.

ExecuteStream is the key method in the interface in the sense that all other execute methods fall back internally to it. In particular, ExecuteNonQuery merely wraps a call to ExecuteStream, whereas ExecuteXmlReader creates and returns an XmlTextReader object built using the stream obtained from Execute­Stream.

ExecuteToStream does not use ExecuteStream internally, but the two methods have a similar architecture and use the same internal worker method. Basically, ExecuteStream calls an internal executor and sets it to work on a memory stream. The memory stream (MemoryStream class) is then returned as a generic Stream object. ExecuteToStream, instead, reads from, and writes to, the user-provided stream object. Figure 8-6 shows these two methods in action.

The following code shows how to use a SqlXmlCommand object. Notice that the connection string for SqlXmlCommand must necessarily use the SQLOLEDB provider because SQLXML 3.0 does not support the .NET Framework managed data provider.

string conn = "PROVIDER=sqloledb;SERVER=(local);" +
    "DATABASE=northwind;UID=sa";
SqlXmlCommand cmd = new SqlXmlCommand(conn);
cmd.CommandText = "SELECT * FROM Employees" +
    " FOR XML AUTO, BINARY BASE64";
Stream stm = cmd.ExecuteStream();

// Consumes the stream content
StreamReader sr = new StreamReader(stm)
Console.WriteLine(sr.ReadToEnd());
sr.Close();

The Employees table contains a BLOB field with a picture of each employee. If you want the binary field returned encoded as a string, use the BINARY BASE64 keyword in the FOR XML clause.

If the command that SqlXmlCommand executes does not return XML, an exception is raised because streaming is not supported over a result set with multiple columns. SqlXmlCommand works just fine on non-XML queries as long as they return a single column of data.

Executing Server-Side XPath Queries

A typical functionality of the SQLXML library is executing server-side XPath queries over SQL Server data. Personally, I would not recommend this practice—I believe that a well-designed SQL query outperforms any XPath engine. The XPath language does let you address hierarchically structured data more easily, however, but keep in mind that a server-side XPath query requires a preliminary step—the relational-to-XML data transformation, as shown here:

SqlXmlCommand cmd = new SqlXmlCommand(conn);
cmd.CommandText = "Emp[@EmployeeID >3]";
cmd.CommandType = SqlXmlCommandType.XPath;
cmd.SchemaPath = "MappingSchema.xml";
cmd.RootTag = "Northwind";
Stream stOut = cmd.ExecuteStream();

When the command type is XPath, you must necessarily set the SchemaPath property on the SqlXmlCommand object. The property points to an XSD or XDR file that defines the XML schema on which the XPath expression is called to operate. For example, consider the following schema:

<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>

This schema addresses a layout such as the following, in which FName and LName map to FirstName and LastName and the target table is Employees:

<Emp employeeid="...">
  <FName>...<FName>
  <LName>...<LName>
</Emp>

Given this underlying XML schema, using the following command text to select all the employees with an ID greater than 3 makes sense:

Emp[@EmployeeID >3]

The SqlXmlParameter Class

To pass parameters to a SqlXmlCommand object, you must use instances of the SqlXmlParameter class. Here’s an example:

string conn = "PROVIDER=sqloledb;SERVER=(local);" +
    "DATABASE=northwind;UID=sa";
SqlXmlCommand cmd = new SqlXmlCommand(conn);

// Define the command text
StringBuilder sb = new StringBuilder("");
sb.Append("SELECT * FROM Employees ");
sb.Append("WHERE employeeid=? ");
sb.Append("FOR XML AUTO, BINARY BASE64");
cmd.CommandText = sb.ToString();

// Set the parameter
SqlXmlParameter p = cmd.CreateParameter();
p.Value = 2;

// Execute the command
Stream stm = cmd.ExecuteStream();

When you have several parameters set on a particular instance of a SqlXmlCommand object and you want to reuse that instance for another command, use the ClearParameters method to clear in a single shot the parameters collection.

The SqlXmlAdapter Class

The SqlXmlAdapter class is a shrink-wrapped adapter class. It does not implement the IDataAdapter interface, so technically speaking, it can’t be presented as an adapter object. Nevertheless, the class provides adapter-like methods such as Fill and Update, as shown in the following code. These are also the only public methods for the class.

void Fill(DataSet ds);
void Update(DataSet ds);

The SqlXmlAdapter class also provides three constructors, shown in the following code, whose signatures reinforce the idea that this adapter is a mere wrapper class for SqlXmlCommand. In other words, the SqlXmlAdapter class is more a command that manages DataSet objects than a true data adapter object as it is described in the ADO.NET specification.

public SqlXmlAdapter(SqlXmlCommand cmd) 
public SqlXmlAdapter(string commandText, 
    SqlXmlCommandType cmdType, 
    string connectionString) 
public SqlXmlAdapter(Stream commandStream, 
    SqlXmlCommandType cmdType, 
    string connectionString)

These constructors use the information they receive to set up an internal instance of the SqlXmlCommand class. The Fill method makes full use of all the information passed through the constructor. For the Update method, on the other hand, only the connection string information is actually needed.

Filling an XML Adapter

The Fill method is rather simple. First it executes the embedded XML command using ExecuteStream. Next it uses the returned memory stream to fill the specified DataSet object through its ReadXml method.

The ReadXml method populates a DataSet object by reading XML data from a variety of sources, including streams and text readers, and inferring the schema. We’ll examine the inference process in detail in Chapter 9. For now, suffice to say that ReadXml can detect any in-line or referenced XSD schema or determine the schema dynamically.

Once the DataSet object has been filled from the XML stream generated by the command execution, all the changes are accepted so that the DataSet object appears intact and with no pending changes.

Updating Using an XML Adapter

The Update method takes a DataSet object and applies its pending changes to the target database. The parameters specified on instantiation contain the details about the connection string. The embedded SqlXmlCommand object has command text and a command type that are simply ignored during Update. Let’s see why.

When Update executes, the embedded command object is used to perform the task, but its command text and command type properties are silently and temporarily overwritten with DataSet-specific settings.

The Update method writes the contents of the DataSet object to a newly created memory stream. The DataSet object is serialized as a DiffGram. Next the contents of the stream—that is, the DiffGram representation of the DataSet object—are copied into the CommandText property of the underlying SqlXmlCommand object. The CommandType property is set to Template, and Execute­Stream is called to update the database. If all goes well, the DataSet changes are committed using the DataSet object’s AcceptChanges method.

Although COM is still involved, the SqlXmlAdapter object represents a way to architecturally improve the batch update mechanism in ADO.NET. By using SqlXmlAdapter, you actually obtain a DataSet object that is serialized as a DiffGram directly to SQL Server and processed entirely on the server. To optimize the bandwidth, you can pass a DataSet object that contains only changed rows. The GetChanges method provides for that.