ADO.NET 101: SqlDataReader - SQLCommand's ExecuteXMLReader() Method

(Page 7 of 7 )

XMLReader and XMLWriter belong to the Abstract base classes. The Class View of XMLReader is shown in the next screen shot. While XMLReader reads though XMLTextReader and XMLNodeReader, the XMLWriter only writes using the XMLTextWriter.

• System.XML
  • XMLReader
    • XMLTextReader
    • XMLNodeReader
  • XMLWriter
    • XMLTextWriter

When the SQLCommand executes, the XMLReader will read a stream of data coming in the form of XML. This XML stream is produced by the SQL Query sent to the server to retrieve data in the form of an XML document using the For XML(Raw|auto|explicit) clause. The SQLStatement as shown here is for the For XML auto case. Please refer to SQL Server book on line [BOL] for the other clauses. The data, or rows returned by such a query are read by the SQLDataReader:

SELECT CustomerID, CompanyName,
       City, PostalCode, Phone
FROM  Customers FOR xml auto

The code shown here connects to an SQL Server through an SQLConnection. An SQLCommand is issued against the database using an SQLQuery fashioned to retrieve the XML document stream.

Private Sub Button1_Click(ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles Button1.Click

        Dim SQLConnection1 = New SqlClient.SqlConnection
        Dim strCon As String
        strCon = "workstation id=XPHTEK;packet size=4096;" & _
        "integrated security=SSPI;data source=XPHTEK;" & _
        "persist security info=False;initial catalog=Northwind"
        SQLConnection1.connectionstring = strCon
        SQLConnection1.Open()
        Dim SQLCmd As New SqlClient.SqlCommand
        SQLCmd.Connection = SQLConnection1
        SQLCmd.CommandType = CommandType.Text
        SQLCmd.CommandTimeout = 20
        SQLCmd.CommandText = "SELECT CustomerID, " & _
        "CompanyName, City, PostalCode, Phone " & _
        "FROM Customers FOR xml auto"
        Dim myXmlReader As System.Xml.XmlReader = _
        SQLCmd.ExecuteXmlReader()
        Response.Write("<p> </p><br/>")
        Response.Write("<table border='1'>")

        While myXmlReader.Read
     Response.Write("<tr><td>" & _
           myXmlReader.Item("CustomerID") & " " & _
           myXmlReader.Item("CompanyName") & _
           myXmlReader.Item("City") & "" & _
           myXmlReader.Item("PostalCode") & "</td></tr>")
        End While
        Response.Write("</table>")
        myXmlReader.Close()
        SQLConnection1.Close()
    End Sub

The following shows a sample of the records retrieved by the XMLReader. This is read into a single cell of an HTML table using the Response object's write() method.

Conclusions

For situations requiring the read only type, the fastest data access is by using the SQL native provider, using an SQLDataReader while retrieving data from an SQL Server (version 7.0 and above). It is also possible to connect to SQL Server using ODBC or OleDB. The analogous types of "DataReaders" can also be used, but there is an overhead involved due to the intermediary, be it ODBC or OleDb.

For some cases, as in connecting to OLAP cubes for data retrieval, or connecting to an earlier version of SQL Server, it may be necessary to use the OleDB provider. Also, for situations which require commands against the table requiring "TableDirect" commands, OleDb has to be used. Although all examples shown here are for the SQL native, similar processing can be carried out using the appropriate drivers for the other types. Moreover, the Ado.net objects are used the same way, whether it is a Windows application or a Web application.

Finally, this article is self-contained and complete. The main focus of this article is to understand the functionality of the SQLDataReader, and other necessary coding like exception handling is not intentionally implemented. The author may be reached via DevShed or direct email, mysorian

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

Comments On: ADO.NET 101: SqlDataReader

>>> Be the FIRST to comment on this article!