ADO.NET | Professional VB 2005 with .NET 3.0 (Programmer to Programmer)

ADO.NET enables Visual Basic applications to generate XML documents and use such documents to update persisted data. ADO.NET natively represents its DataSet’s underlying datastore in XML. ADO.NET also enables SQL Server−specific XML support to be accessed. This chapter focuses on those features of ADO.NET that enable the XML generated and consumed to be customized. ADO.NET is covered in detail in Chapter 10.

The DataSet properties and methods that are pertinent to XML include Namespace, Prefix, GetXml(), GetXmlSchema(), InferXmlSchema(), ReadXml(), ReadXmlSchema(), WriteXml(), and WriteXmlSchema(). An example of code that uses the GetXml method is shown here:

  Dim adapter As New _     SqlClient.SqlDataAdapter("SELECT ShipperID, CompanyName, Phone " & _                    "FROM Shippers", _                    "SERVER=localhost;UID=sa;PWD=sa;Database=Northwind;") Dim ds As New DataSet adapter.Fill(ds) Console.Out.WriteLine(ds.GetXml())

The preceding code uses the sample Northwind database (which comes with SQL Server 2000 and MSDE), retrieving all rows from the Shippers table. This table was selected because it contains only three rows of data. The XML returned by GetXml() is as follows (where … signifies that <Table> elements were removed for the sake of brevity):

 <NewDataSet>   <Table>     <ShipperID>1</ShipperID>     <CompanyName>Speedy Express</CompanyName>     <Phone>(503) 555-9831</Phone>   </Table>   ... </NewDataSet>

What you are trying to determine from this XML document is how to customize the XML generated. The more customization you can perform at the ADO.NET level, the less need there will be later. With this in mind, note that the root element is <NewDataSet> and that each row of the DataSet is returned as an XML element, <Table>. The data returned is contained in an XML element named for the column in which the data resides (<ShipperID>, <CompanyName>, and <Phone>, respectively).

The root element, <NewDataSet>, is just the default name of the DataSet. This name could have been changed when the DataSet was constructed by specifying the name as a parameter to the constructor:

  Dim ds As New DataSet("WeNameTheDataSet")

If the previous version of the constructor was executed, then the <NewDataSet> element would be renamed <WeNameTheDataSet>. After the DataSet has been constructed, you can still set the property DataSetName, thus changing <NewDataSet> to a name such as <WeNameTheDataSetAgain>:

  ds.DataSetName = "WeNameTheDataSetAgain"

The <Table> element is actually the name of a table in the DataSet’s Tables property. Programmatically, you can change <Table> to <WeNameTheTable>:

  ds.Tables("Table").TableName = "WeNameTheTable"

You can customize the names of the data columns returned by modifying the SQL to use alias names. For example, you could retrieve the same data but generate different elements using the following SQL code:

 SELECT ShipperID As TheID, CompanyName As CName, Phone As TelephoneNumber FROM  Shippers

Using the preceding SQL statement, the <ShipperID> element would become the <TheID> element. The <CompanyName> element would become <CName>, and <Phone> would become <TelephoneNumber>. The column names can also be changed programmatically by using the Columns property associated with the table in which the column resides. An example of this follows, where the XML element <TheID> is changed to <AnotherNewName>:

  ds.Tables("WeNameTheTable").Columns("TheID").ColumnName = "AnotherNewName"

This XML could be transformed using System.Xml.Xsl. It could be read as a stream (XmlTextReader) or written as a stream (XmlTextWriter). The XML returned by ADO.NET could even be deserialized and used to create an object or objects using XmlSerializer. The point is to recognize what ADO.NET-generated XML looks like. If you know its format, then you can transform it into whatever you like.

ADO.NET and SQL Server 2000’s Built-In XML Features

Those interested in fully exploring the XML-specific features of SQL Server should take a look at Professional SQL Server 2000 Programming by Robert Vieira (Wrox Press, 2000). However, because the content of that book is not .NET-specific, the next example forms a bridge between Professional SQL Server 2000 Programming and the .NET Framework.

Two of the major XML-related features exposed by SQL Server are as follows:

FOR XML - The FOR XML clause of an SQL SELECT statement enables a rowset to be returned as an XML document. The XML document generated by a FOR XML clause is highly customizable with respect to the document hierarchy generated, per-column data transforms, representation of binary data, XML schema generated, and a variety of other XML nuances.
OPENXML - The OPENXML extension to Transact-SQL enables a stored procedure call to manipulate an XML document as a rowset. Subsequently, this rowset can be used to perform a variety of tasks, such as SELECT, INSERT INTO, DELETE, and UPDATE.

SQL Server’s support for OPENXML is a matter of calling a stored procedure call. A developer who can execute a stored procedure call using Visual Basic in conjunction with ADO.NET can take full advantage of SQL Server’s support for OPENXML. FOR XML queries have a certain caveat when it comes to ADO.NET. To understand this caveat, consider the following FOR XML query:

  SELECT ShipperID, CompanyName, Phone FROM Shippers FOR XML RAW

Using SQL Server’s Query Analyzer, this FOR XML RAW query generated the following XML:

 <row Shipper CompanyName="Speedy Express" Phone="(314) 555-9831" /> <row Shipper CompanyName="United Package" Phone="(314) 555-3199" /> <row Shipper CompanyName="Federal Shipping" Phone="(314) 555-9931" />

The same FOR XML RAW query can be executed from ADO.NET as follows:

  Dim adapter As New _     SqlDataAdapter("SELECT ShipperID, CompanyName, Phone " & _                    "FROM Shippers FOR XML RAW", _                    "SERVER=localhost;UID=sa;PWD=sa;Database=Northwind;") Dim ds As New DataSet adapter.Fill(ds) Console.Out.WriteLine(ds.GetXml())

The caveat with respect to a FOR XML query is that all data (the XML text) must be returned via a result set containing a single row and a single column named XML_F52E2B61-18A1-11d1-B105- 00805F49916B. The output from the preceding code snippet demonstrates this caveat (where … represents similar data not shown for reasons of brevity):

 <NewDataSet>   <Table>     <>       /&lt;row Shipper CompanyName="Speedy Express"       Phone="(503) 555-9831"/&gt;       ...     </>   </Table> </NewDataSet>

The value of the single row and single column returned contains what looks like XML, but it contains /< instead of the less-than character, and /> instead of the greater-than character. The symbols < and > cannot appear inside XML data, so they must be entity-encoded - that is, represented as /> and /<. The data returned in element <> is not XML, but data contained in an XML document.

To fully utilize FOR XML queries, the data must be accessible as XML. The solution to this quandary is the ExecuteXmlReader() method of the SQLCommand class. When this method is called, an SQLCommand object assumes that it is executed as a FOR XML query and returns the results of this query as an XmlReader object. An example of this follows:

  Dim connection As New _     SqlConnection("SERVER=localhost;UID=sa;PWD=sa;Database=Northwind;") Dim command As New _     SqlCommand("SELECT ShipperID, CompanyName, Phone " & _                     "FROM Shippers FOR XML RAW") Dim memStream As MemoryStream = New MemoryStream Dim xmlReader As New XmlTextReader(memStream) connection.Open() command.Connection = connection xmlReader = command.ExecuteXmlReader() ' Extract results from XMLReader

The XmlReader created in this code is of type XmlTextReader, which derives from XmlReader. The XmlTextReader is backed by a MemoryStream; hence, it is an in-memory stream of XML that can be traversed using the methods and properties exposed by XmlTextReader. Streaming XML generation and retrieval has been discussed earlier.

Using the ExecuteXmlReader() method of the SQLCommand class, it is possible to retrieve the result of FOR XML queries. What makes the FOR XML style of queries so powerful is that it can configure the data retrieved. The three types of FOR XML queries support the following forms of XML customization:

FOR XML RAW - This type of query returns each row of a result set inside an XML element named <row>. The data retrieved is contained as attributes of the <row> element. The attributes are named for the column name or column alias in the FOR XML RAW query.
FOR XML AUTO - By default, this type of query returns each row of a result set inside an XML element named for the table or table alias contained in the FOR XML AUTO query. The data retrieved is contained as attributes of this element. The attributes are named for the column name or column alias in the FOR XML AUTO query. By specifying FOR XML AUTO, ELEMENTS, it is possible to retrieve all data inside elements, rather than inside attributes. All data retrieved must be in attribute or element form. There is no mix-and-match capability.
FOR XML EXPLICIT - This form of the FOR XML query enables the precise XML type of each column returned to be specified. The data associated with a column can be returned as an attribute or an element. Specific XML types, such as CDATA and ID, can be associated with a column returned. Even the level in the XML hierarchy in which data resides can be specified using a FOR XML EXPLICIT query. This style of query is fairly complicated to implement.

FOR XML queries are flexible. Using FOR XML EXPLICIT and the movie rental database, it would be possible to generate any form of XML movie order standard. The decision that needs to be made is where XML configuration takes place. Using Visual Basic, a developer could use XmlTextReader and XmlTextWriter to create any style of XML document. Using the XSLT language and an XSLT file, the same level of configuration can be achieved. SQL Server and, in particular, FOR XML EXPLICIT, enable the same level of XML customization, but this customization takes place at the SQL level and may even be configured to stored procedure calls.

XML and SQL Server 2005

As a representation for data, XML is ideal in that it is a self-describing data format that enables you to provide your datasets as complex datatypes. It also provides order to your data. SQL Server 2005 embraces this direction.

More and more developers are turning to XML as a means of data storage. For instance, Microsoft Office enables documents to be saved and stored as XML documents. As an increasing number of products and solutions turn toward XML as a means of storage, this allows for a separation between the underlying data and the presentation aspect of what is being viewed. XML is also being used as a means of communicating datasets across platforms and the enterprise. The entire XML Web Services story is a result of this new capability. Simply said, XML is a powerful alternative to your data storage solutions.

Just remember that the power of using XML isn’t only about storing data as XML somewhere (whether that is XML files or not); it is also about the ability to quickly get at this XML data and to be able to query the data that is retrieved.

SQL Server 2005 makes a big leap toward XML in adding an XML datatype. This enables you to unify the relational aspects of the database and the current desires to work with XML data.

FOR XML has also been expanded from within this latest edition of SQL Server. This includes a new TYPE directive that returns an XML datatype instance. In addition, the NET 2.0 Framework includes a new namespace - System.Data.SqlXml - that enables allows you to easily work with the XML data that comes from SQL Server 2005. The SqlXml object is an XmlReader-derived type. Another addition is the use of the SqlDataReader object’s GetXml() method.