A Practical Comparison of ADO and ADO.NET - Retrieving records as XML

(Page 6 of 7 )

SQL Server supports retrieving data using the FOR XML AUTO, FOR XML RAW, or FOR XML EXPLICIT keywords. Using ADO 2.6, we typically setup our query/stored procedure by adding FOR XML AUTO at the end of it and created a new ADO stream object, like this:

objComm.ActiveConnection = objConn

objComm.CommandType = 1 'adCmdText

objComm.CommandText = "SELECT TOP 1 * FROM authors FOR XML AUTO"

objComm.Properties("Output Stream") = objStream

objStream.Open

objComm.Execute , , 1024 'adExecuteStream

Response.Write objStream.ReadText

The output from the ASP/ADO code above return one record in the form of XML, which looks like this:

<authors au_id="172-32-1176" au_lname="Whitles" au_fname="Johnson" phone="408 496-7223" address="10932 Bigge Rd." city="Menlo Park" state="CA" zip="94025" contract="1"/>

ADO.NET makes it easier to work with XML, and provides a class call XmlReader, which exists in the System.Xml namespace. The ExecuteXmlReader() function returns an instance of the XmlReader class. We can loop through the results of that XmlReader class using its ReadOuterXml() function, like this:

<%@ import namespace="System.Data" %>

<%@ import namespace="System.Xml" %>

<%@ import namespace="System.Data.SqlClient" %>

<script language="c#" runat="server">

public void Page_Load(object sender, EventArgs e)

{

SqlConnection objConn = new SqlConnection("Server=(local); Database=Pubs; UId=sa; Pwd=");

objConn.Open();

SqlCommand objComm = new SqlCommand("SELECT TOP 1 * FROM authors FOR XML AUTO", objConn);

objComm.CommandType = CommandType.Text;

XmlReader objXR = objComm.ExecuteXmlReader();

while(objXR.Read())

{

Response.Write(objXR.ReadOuterXml());

}

}

</script>

The above script does exactly the same thing as the ADO/ASP example shown earlier, displaying an XML element in the browser.

Next: Conclusion >>
 

More ADO.NET Articles
More By Joe O'Donnell

Comments On: A Practical Comparison of ADO and ADO.NET

· Great article as it helps the transition. Unfortunately it is heavily .net...   >>> Post your comment now!