XML Data Readers

.NET Framework applications delegate all their low-level data access tasks to a special breed of connector objects called managed data providers. The object model around these connector components is known as ADO.NET. Basically, a data provider is the software component that enables any .NET Framework application to connect to a data source and execute commands to retrieve and modify data.

A .NET Framework data provider component interfaces client applications through the objects in the ADO.NET namespace and exposes any provider-specific behavior directly to consumers. A .NET Framework data provider component creates a minimal layer between the physical data source and the client code, thereby increasing performance without sacrificing functionality.

A .NET Framework data provider is fully integrated with the surrounding environment—the .NET Framework—so any results that a command generates are promptly and automatically packed into a familiar data structure—the ADO.NET and XML classes—for further use.

A key architectural goal for .NET Framework data providers is that they must be designed to work on a rigorous per-data source basis. They expose connection, transaction, command, and reader objects, all working according to the internal capabilities and structure of the DBMS. As a result, the programming interface of, say, the Microsoft Access data provider will not be completely identical to that of the SQL Server provider. An area in which this difference is palpable is in XML data queries.

Reading from XML Queries

The SQL Server .NET Framework data provider makes available a particular method in its command class, SqlCommand, that explicitly lets you obtain an XML reader whenever the command text returns XML data. In other words, you can choose to execute a SQL command with a trailing FOR XML clause and then pick up the results directly using an XML reader. Let’s see how.

The following code sets up a command that returns XML information about all the employees in the Northwind database:

string nwind = "DATABASE=northwind;SERVER=localhost;UID=sa;";
string query = "SELECT * FROM Employees FOR XML AUTO, ELEMENTS";
SqlConnection conn = new SqlConnection(nwind);
SqlCommand cmd = new SqlCommand(query, conn);

In general, an ADO.NET command can be run using a variety of execute methods, including ExecuteNonQuery, ExecuteReader, and ExecuteScalar. These methods differ in the format in which the result set is packed. The SQL Server 2000 ad hoc command class—SqlCommand—supplies a fourth execute method, ExecuteXmlReader, which simply returns the result set as an XML reader.

You use the ExecuteXmlReader method as a special type of constructor for an XmlTextReader object, as shown here:

conn.Open();
XmlTextReader reader = (XmlTextReader) cmd.ExecuteXmlReader();
ProcessXmlData(reader);
reader.Close();
conn.Close();

The ExecuteXmlReader method executes the command and returns an instance of an XmlTextReader object to access the result set. Of course, ExecuteXmlReader fails, throwing an InvalidOperationException exception, if the command does not return an XML result.

The SqlCommand class performs no preliminary check on the structure of the T-SQL command being executed to statically determine whether the command returns XML data. This means that any error that invalidates the operation is detected on the server. A client-side check could verify that the command text incorporates a correct FOR XML clause prior to sending the text to the database. However, such a test would also catch as erroneous a perfectly legitimate situation: selecting XML data from a text or a BLOB field. So while performing a preliminary check could still make sense for some user applications, it would be ineffective if done from within the command class.

The application shown in Figure 8-2 uses the schema we analyzed in the section “The FOR XML EXPLICIT Mode,” on page 356, while examining the FOR XML EXPLICIT clause. The application runs the same SELECT command we used in that section and then walks its way through the result set using an XML reader. The information read is used to fill up a treeview control.

The following code illustrates how to extract information from the previously described schema and add nodes to the treeview. The ProcessXmlData routine has an extra Boolean argument used to specify whether you want the application’s user interface to be generic. If the user interface is not generic, it makes assumptions about the structure of the XML data and attributes specific semantics to each element. If the user interface is generic, the sample application treats the data as a generic XML stream.

void ProcessXmlData(XmlTextReader reader, bool bUseGenericMode)
{
    // Clear the treeview
    dataTree.Nodes.Clear();
    dataTree.BeginUpdate();

    // Process elements
    while(reader.Read())
    {
        if(reader.NodeType == XmlNodeType.Element)
        {
            // Creates an hash table of nodes at various
            // depths so that each element can figure out
            // what its parent is
            int depth = reader.Depth;
            int parentDepth = depth -1;
            string text = "";

            if (m_ParentNodes.ContainsKey(parentDepth))
            {
                TreeNode n =
                    (TreeNode) m_ParentNodes[parentDepth];
                text = PrepareOtherDataDisplayText(reader,
                    bUseGenericMode);
                m_ParentNodes[depth] = n.Nodes.Add(text); 
            }
            else
            {
                // Only first-level nodes
                text = PrepareEmployeeDisplayText(reader,
                    bUseGenericMode);
                m_ParentNodes[depth] = dataTree.Nodes.Add(text);
            }
        }
    }
    dataTree.EndUpdate();
}

Figure 8-3 shows the user interface in generic mode.

A quick comment regarding the algorithm used to populate this treeview object: I make use of a small hash table to keep track of the latest node inserted at a given level—the Depth property of the XML text reader. Each element that is expected to have a parent—that is, a depth greater than 0—looks upward for a TreeNode object in the table and adds its description to the node. Next, the node itself registers as a parent node for its own level of depth.

Under the Hood of ExecuteXmlReader

Internally, the ExecuteXmlReader method first calls ExecuteReader and then creates a new instance of an XmlTextReader object. The XML reader is configured to work on an internal stream object whose class name is SqlStream. The SqlStream class represents the data stream that SQL Server uses to return rows to callers. The format of the SQL Server data stream is the TDS.

The following listing shows the pseudocode of ExecuteXmlReader. What happens under the lid of this method leads straight to the conclusion that the ability to execute a database command to XML can also be added to the Ole­Db­Command class as well as to the command classes in a number of other managed providers. We’ll examine this concept in more detail in a moment.

public XmlReader ExecuteXmlReader() 
{
    // Execute the command
    SqlDataReader datareader = ExecuteReader();

    // Obtain the TDS stream for the command
    SqlStream tdsdata = new SqlStream(datareader);

    // Create the XML text reader
    // (No context information specified)
    XmlReader xmlreader = new XmlTextReader(tdsdata, 
        XmlNodeType.Element, null);

    // Close the temporary data reader but leaves the 
    // stream open
    datareader.Close();

    return xmlreader;
}

As long as the XML reader is open and in use, the underlying database connection remains open.

At the end of the day, the trick that makes it possible to access the result set as XML is simply the availability of the data through a stand-alone XML reader object. SQL Server 2000 transforms the contents of its low-level TDS stream into XML and then builds an XML text reader from that. The whole process takes place on the server.

Reading from Text Fields

Most important with XML readers working on top of SQL commands is that the commands return XML data. With SQL Server 2000, this certainly happens if you use any of the FOR XML clauses. It also happens if the query returns one or more rows that, in combination, can be seen as a unique XML stream.

Text or ntext fields that contain XML data can be selected and then processed using an XML text reader. (The ntext data type is a variable-length Unicode data type that can hold a maximum of 1,073,741,823 characters. An ntext column stores a 16-byte pointer in the data row, and the data is stored separately.) Of course, the query must include a single column and possibly a single record. Let’s consider the following query from a modified version of the Northwind database. I created the XmlNet database by duplicating the Northwind databases Employees table and then wrapping all the strings stored in the Notes column in a <notes></notes> pair. The Notes column is of type ntext.

SELECT notes FROM employees

Although the SELECT command listed here does not explicitly return XML data, you can run it through the ExecuteXmlReader method, as shown here:

string nwind = "DATABASE=xmlnet;SERVER=localhost;UID=sa;";
string query = "SELECT notes FROM employees";
SqlConnection conn = new SqlConnection(nwind);
SqlCommand cmd = new SqlCommand(query, conn);

conn.Open();
XmlTextReader reader = (XmlTextReader) cmd.ExecuteXmlReader();
ProcessNotes(reader);
reader.Close();
conn.Close();

The XML reader will loop through the nodes, moving from one record to the next, as shown here:

void ProcessNotes(XmlTextReader reader)
{
    try 
    {
        while(reader.Read())
        {
            if (reader.NodeType == XmlNodeType.Text) 
                MessageBox.Show(reader.Value);
        }
    } 
    catch {}
    finally
    {
        MessageBox.Show("Closed...");
    }
}

The connection remains open until the reader is closed. Next store the results in a string variable and use that string to create a new XmlTextReader object. (See Chapter 2.) This technique gives you an extra advantage: you can work with the reader while you are disconnected from the database.

An XML Reader for Data Readers

An XML reader can work on top of different data containers, including streams, files, and text readers. By writing a custom XML reader, you can also navigate non-XML data using the same XML reader metaphor. In this case, you create a virtual XML tree and make the underlying data look like XML. (In Chapter 2, you learned how to visit CSV files the XML way.)

The ability to expose result sets via XML is specific to SQL Server 2000 and potentially to any other native managed provider for DBMS systems with full support for XML queries. You can’t, for example, use the ExecuteXmlReader method with an object of class OleDbCommand.

Recall from the section “Under the Hood of ExecuteXmlReader,” on page 366, the internal structure of ExecuteXmlReader. The ExecuteXmlReader method simply creates an XML text reader based on the internal stream used to carry data back and forth. What about creating a custom XML reader by building a virtual XML tree around the provider-specific data reader? In this way, you could easily extend any .NET Framework data provider by using the ExecuteXmlReader method. This method is not as effective as using the internal stream, but it does work and can be applied to all data providers.

Building the XML Data Reader

Let’s rework the CSV reader example from Chapter 2 and build an XmlData­Reader class inheriting from XmlReader, as follows:

public class XmlDataReader : XmlReader
{
    ...
}

The base class is for the most part abstract, thus requiring you to override several methods and properties. When designing an XML reader, a key step is defining the XML virtual tree that underlying data will populate. In this case, we’ll try for a relatively simple XML schema that closely resembles the schema of the FOR XML RAW mode, as shown here:

<row field1="..." field2="..." ... />
<row field1="..." field2="..." ... />
...

The XmlDataReader class features only one constructor, which takes any object that implements the IDataReader interface. The programming interface of a data reader object like OleDbDataReader and SqlDataReader consists of two distinct groups of functions: the IDataReader and IDataRecord interfaces. The former includes basic methods such as Read, Close, and GetSchemaTable. The latter contains specific reading methods including GetName, GetValue, and the Item indexer property.

By making the constructor accept a reference to the IDataReader interface, you enable the XmlDataReader class to support any data reader object. Internally, the class defines the following private properties:

protected IDataReader m_dataReader;
protected IDataRecord m_dataRecord;
protected ReadState m_readState;
protected int m_currentAttributeIndex;

The idea is to map the reading methods of the XmlDataReader class to the data reader object and use the m_currentAttributeIndex member to track down the currently selected attribute, as shown in the following code. Of course, each XML attribute corresponds to a column in the underlying result set.

public XmlDataReader(IDataReader dr)
{
    m_dataReader = dr;
    m_readState = ReadState.Initial; 
    m_dataRecord = (IDataRecord) dr;
    m_currentAttributeIndex = -1;
}

Notice that the same object is passed as a reference to IDataReader but can also be cast to IDataRecord. This is possible as long as the real object implements both interfaces, but for data reader objects this is true by design.

The XmlDataReader Implementation

Let’s review the implementation of a few properties and methods to grasp the essence of the reader, as shown in the following code. The entire source code is available for download in this book’s sample files.

// Return the number of attributes (for example, the field count)
public override int AttributeCount 
{
    get {return m_dataRecord.FieldCount;}
}

// Indexer property that works by index and name
public override string this[int i]
{
    get {return m_dataRecord.GetValue(i).ToString();}
}
public override string this[string name]
{
    get {return m_dataRecord[name].ToString();}
}

// Return the value of the current attribute
public override string Value
{
    get {
        if(m_readState != ReadState.Interactive)
            return "";
                    
        string buf = "";
        if (NodeType == XmlNodeType.Attribute)
            buf = this[m_currentAttributeIndex].ToString();
        return buf;
    }
}

The Read method calls into the Read method of the data reader and updates its state accordingly, as shown in the following code. The Close method closes the data reader and resets the internal state.

public override bool Read()
{
    // Read the new row and set the state
    bool canReadMore = m_dataReader.Read();
    m_readState = (canReadMore 
        ?ReadState.Interactive :ReadState.EndOfFile);

    return canReadMore; 
}

public override void Close()
{
    m_dataReader.Close();
    m_readState = ReadState.Closed;
}

The XML data reader object can work atop any provider-specific data readers, thus providing a free XML transformation service that is functionally equivalent to ExecuteXmlReader. The so-called XML transformation takes place on the client, but the connection with the database remains open until you close the reader.

Using XML with OLE DB Data Providers

Let’s see how to use the XmlDataReader class with an instance of the OLE DB data reader. As usual, you create an OleDbCommand object, execute the command, and get a living instance of the OleDbDataReader class. Next you pass the OLE DB data reader to the XmlDataReader constructor, as shown here:

string nwind, query;
nwind = "PROVIDER=sqloledb;SERVER=localhost;" +
    "DATABASE=northwind;UID=sa;";
query = "SELECT employeeid, firstname, lastname," +
    " title FROM employees";

OleDbConnection conn = new OleDbConnection(nwind);
OleDbCommand cmd = new OleDbCommand(query, conn);

// Create the XML data reader
conn.Open();
OleDbDataReader dr = cmd.ExecuteReader();
XmlDataReader reader = new XmlDataReader(dr);
ProcessDataReader(reader);
reader.Close();
conn.Close();

The reader can be used on demand to walk through the contents of the result set, as shown here:

private void ProcessDataReader(XmlReader reader)
{
    ResultsListBox.Items.Clear();
    while(reader.Read())
        ResultsListBox.Items.Add(reader.ReadOuterXml());
    reader.Close();
}

This code generates the output shown in Figure 8-4.