Professional ASP.NET 1.1 [Electronic resources] نسخه متنی

Reading XML Data Direct from SQL Server

Our earlier discussion of the format of the XML returned from a

DataSet mentioned an alternative format of using the attributes of a row element to hold the individual column or field data items. This was, in fact, the default format introduced with ADO 2.0/2.1 when XML support was added to ADO. It's also the default format for the XML created by the new features of Microsoft SQL Server 2000 (and the SQLXML Technology Preview that is still available for SQL Server 7.0).

The Reading XML Direct From SQL Server With An XmlReader (

xmldatareader-sql.aspx ) example page demonstrates this feature. To use it you must have SQL Server 2000 installed – the SQLXML Technology Preview does not work with .NET.

When you run the page, as shown in Figure 11-15, you see the connection string and the SQL

SELECT statement used. Notice that it includes the

FOR XML AUTO instruction. Below this is a series of XML elements that are created automatically by SQL Server in response to the instruction in the SQL statement:

Figure 11-15:

It's hard to see what the structure of the XML is from the screenshot, but if you break out one of the elements, you will see the structure quite clearly:

<BookList ISBN="1861003110" Title="Professional XML" 
PublicationDate="2000-01-01T00:00:00"/> 

There are a couple of things to note. This is not an XML document, as there is no document type declaration or root element. All you get back is a series of elements that contain the row values as attributes. This is the default format for the

AUTO part of the instruction used in the SQL statement. You would probably use the elements to build up your own specific XML documents, depending on the task your application has to achieve.

The Code for the SQLXML Example Page

The ability to extract data as XML from SQL Server using the SQLXML feature has proved very useful to developers already, and it's extremely fast and efficient. To support it in .NET simply entailed including an option to return an object that could hold XML document fragments.

The answer is a special version of the execute methods available in the

Command object used for relational data access, but which returns an

XmlReader object instead of a

DataReader object. This method is called, not surprisingly,

ExecuteXmlReader .

The code used in the example page demonstrates this. First we collect the connection string from the

web.config file, and then create the SQL statement that will extract the XML. Then we create a

StringBuilder object to hold the large strings that we expect to get back from the database. We also create the customary

Connection and

Command objects.

Notice that the objects from the

System.Data.SqlClient namespace are being used here (prefixed

Sql ). This page is only going to work with SQL Server 2000 anyway, and so we might as well take advantage of the performance boost that comes with the SQL TDS provider:

Dim strConnect As String 
strConnect = ConfigurationSettings.AppSettings("DsnWroxBooksSql") 
Dim strSelect As String 
strSelect = "SELECT * FROM BookList WHERE " _ 
& "ISBN LIKE '1861003%' FOR XML AUTO" 
 
'create a new StringBuilder to hold the results 
Dim objStrBuilder As New StringBuilder() 
 
'create a new Connection object using the connection string 
Dim objConnect As New SqlConnection(strConnect) 
 
'create new Command using the connection object and select statement 
Dim objCommand As New SqlCommand(strSelect, objConnect) 

Executing the Command

We need an object to receive the results of executing the SQL statement. The following code shows how to declare a variable to hold an

XmlTextReader object (a public class based on

XmlReader ) for this. You can then open the connection and call the

ExecuteXmlReader method. It returns the

XmlTextReader all ready to go:

'declare a variable to hold an XmlTextReader object 
Dim objXTReader As XmlTextReader 
 
'open the connection to the database 
objConnect.Open() 
 
'execute the SQL statement against the command to create the XmlReader 
objXTReader = objCommand.ExecuteXmlReader() 

Retrieving the XML Result

To retrieve the data once the SQL statement is executed, we call the

ReadString method of the

XmlTextReader to initialize it. Then we call the

GetRemainder method to read to the end of the results, and append it all to the

StringBuilder created earlier. To finish off, the reader and the connection are closed, and the results are displayed:

'read the first result to initialize the reader 
objXTReader.ReadString() 
 
'and then read remainder into the StringBuilder as well 
objStrBuilder.Append(objXTReader.GetRemainder().ReadToEnd()) 
 
'close the XmlReader and Connection 
objXTReader.Close() 
objConnect.Close() 
 
'display the results as Text to show XML elements 
outError.InnerText = objStrBuilder.ToString()