Creating an XmlReader from a Command Object

Creating an XmlReader from a Command Object

In Chapter 4 we covered the Command object, which is a core object of a .NET Data Provider. We discussed its methods for executing commands namely, the ExecuteReader, ExecuteScalar, and ExecuteNonQuery methods. We mentioned that, although all .NET Data Providers implement these methods, the SqlCommand object supports an additional method, ExecuteXmlReader, which is used for directly retrieving and accessing XML data from Microsoft SQL Server.

The ExecuteXmlReader method returns an XmlReader, much in the same way that the ExecuteReader method returns a DataReader. Once you've become familiar with use of the XmlReader object, you can obtain one by executing the ExecuteXmlReader method.

Returning to the ADO-XML project, we do the following:

1. Add an additional button immediately below the btnWriteXML button from the Windows Forms tab of the Toolbox

2. In the Properties window, set the Name property of the button to btnExecuteXML and set the Text property to ExecuteXMLReader

3. Add the code shown in Listing 10.12 to the frmXML class.

Listing 10.12 Retrieving and handling data from SQL Server in XML format

     Private Sub btnExecuteXML_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExecuteXML.Click     Dim cn As New SqlConnection _       ("data source=localhost;initial cata log=pubs;user id=sa")     Dim cmd As New SqlCommand _       ("select * from stores for xml auto, elements", cn)     Dim reader As Xml.XmlReader     Dim str As New System.Text.StringBuilder()     cn.Open()     ' Execute SQL Select command, with FOR XML clause.     reader = cmd.ExecuteXmlReader()     ' Find and retrieve data from element nodes.     While reader.Read()       Select Case reader.NodeType         Case Xml.XmlNodeType.Element           str.Append("<" & reader.Name &">")         Case Xml.XmlNodeType.EndElement           str.Append("</" & reader.Name &">" & ControlChars.CrLf)         Case Xml.XmlNodeType.Text           str.Append(reader.Value)         Case Else           ' ignore in this example.       End Select     End While     MsgBox(str.ToString)     cn.Close()    End Sub 

The code in Listing 10.12 shows a simplified use of the ExecuteXmlReader method. All it does is display the data (including column tags) contained in the stores table of the pubs database. The SQL Select command sent to the SQL Server specifies explicitly that the columns are to be returned as XML elements:

 "select * from stores for xml auto, elements"  

Therefore we can simplify the handling of the various XML node types and just look at element begin and end nodes and the text nodes that contain the actual data. A more robust handling of an XML document would cover all possible node types in the Select-Case statement. The results of clicking on the btnExecuteXML button and executing the code in Listing 10.9 are shown in Figure 10.2.

Figure 10.2. Message box with XML data retrieved directly from SQL Server