Data Access and XML | XML Programming Bible

Prior to Windows .NET, OLEDB and ActiveX Data Objects (ADO) were the primary APIs for data access in Microsoft Windows. With the advent of .NET, Microsoft introduced a brand new data access object model, called ADO.NET, which is leaner and meaner than previous data access object models and provides functionalities tailored to excel in a multitier, distributed Web environment.

All three data access object models, OLEDB, ADO, and ADO.NET, provide varying levels of support for XML. Let's first take a look at OLEDB and ADO and their support for XML.

OLEDB and ADO

Both OLEDB and ADO are based on COM technologies. The OLEDB provides a low-level and highly efficient method of accessing and manipulating relational data, while ADO, which is built on top of OLEDB, provides an easier means of data access. XML support in ADO is provided through Recordset Persistence and is implemented by the Microsoft OLE DB Persistence Provider. This provider can take an ADO Recordset object, generate an XML document and related schema information, and save all this to a stream or file. Similarly, this provider can generate a read-only and forward-only Recordset object from an ADO-generated XML file or stream.

Listing 16-1 demonstrates how you can persist a Recordset object as an XML file.

Listing 16-1 ADORecordset2XML.cs: Persisting an ADO Recordset object to an XML file.

 using System; using System.Data; using System.Data.SQL; public class ADORecordset2XML { public static void Main( String[] args ) { try { String connString = "provider=SQLOLEDB;" + "initial catalog=pubs;" + "server=localhost;" + "uid=sa;" + "pwd="; String queryString = "select * from titles"; /* Instantiate a Recordet object */ ADODB.Recordset rs = new ADODB.Recordset(); /* Connect to the database and execute a SQL query */ rs.Open( queryString, connString,  ADODB.CursorTypeEnum.adOpenStatic,  ADODB.LockTypeEnum.adLockReadOnly, 0 ); /* Export Recordset to "titles.xml" file in XML format. */ rs.Save( "titles.xml", ADODB.PersistFormatEnum.adPersistXML ); /* close the Recordset object */ rs.Close(); } catch ( Exception e ) { Console.WriteLine( "Exception: {0}", e.ToString() ); } } }

In ADORecordset2XML.cs we first open a connection to the SQL Server PUBS database and then populate a Recordset object with all records from the TITLES table. To persist this Recordset object, we simply call the Save( ) method and specify the filename to which to write the data and the output format, which in this case is XML.

To compile ADORecordset2XML.cs, open a console window and type in the following command line:

 csc /r:System.dll /r:System.Data.dll /r:ADODB.dll ADORecordset2XML.cs

After you run the resultant ADORecordset2XML.exe program you should see a new titles.xml file in the current directory. This is the XML-persistent form of the Recordset object you created in ADORecordset2XML.cs.

Listing 16-2 shows how you can use ADO to load the titles.xml file you generated with ADORecordset2XML.cs into a new Recordset object.

Listing 16-2 XML2ADORecordset.cs: Recreating the Recordset object from the titles.xml file.

 using System; using System.Data; using System.Data.SQL; public class XML2ADORecordset { public static void Main( String[] args ) { try { String connString = "provider=SQLOLEDB;" + "initial catalog=pubs;" + "server=localhost;" + "uid=sa;" + "pwd="; Int32 adCmdFile = 256; /* Instantiate Recordset object */ ADODB.Recordset rs = new ADODB.Recordset(); /* Open the titles.XML file into Recordset object */ rs.Open( "titles.xml", connString,  ADODB.CursorTypeEnum.adOpenForwardOnly,  ADODB.LockTypeEnum.adLockReadOnly,  adCmdFile ); /* Display the value of the price field for the first record */ rs.MoveFirst(); Console.WriteLine( rs.Fields[ "price" ].Value ); /* close the Recordset object */ rs.Close(); } catch ( Exception e ) { Console.WriteLine( "Exception: {0}", e.ToString() ); } } }

In XML2ADORecordset.cs you can see that, to open a persisted Recordset file, you can use the normal Recordset Open( ) method and pass in the name of the persisted file. In the example, once we have loaded and recreated the Recordset object, we generate the value of the price field of the first record.

Use the following command to compile XML2ADORecordset.cs:

 csc /r:System.dll /r:System.Data.dll /r:ADODB.dll XML2ADORecordset.cs

When you run the resultant XML2ADORecordset.exe program, you will see the value of the price field displayed.

XML support in ADO is limited to say the least. You can only persist and reconstruct Recordset objects to and from XML files and streams. Once persisted, the XML file remains separate from the original Recordset object, and any changes you make to one will not be reflected in the other automatically.

ADO.NET, on the other hand, provides a much tighter relationship with XML, so much so that the two are actually based off a common architecture in the .NET Framework. Let's turn our attention to this exciting new technology.

ADO.NET

ADO.NET is touted as the successor to ADO and is based heavily on XML. The core focus of ADO.NET is to provide an API that facilitates the creation of distributed, scalable, and interoperable applications that share data in a disconnected, stateless fashion.

ADO.NET consist of two parts: the DataSet class and Managed Providers. The DataSet class provides the methods and properties for accessing and manipulating relational data. Managed Providers represent the underlying data stores such as a Microsoft SQL Server database.

Table 16-1 shows a list of the namespaces made available with ADO.NET.

Table 16-1 Namespaces Made Available with ADO.NET

Namespace	Description
System.Data	Contains the DataSet class and other basic classes.
System.Data.ADO	Contains the ADO Managed Provider classes.
System.Data.SQL	Contains SQL Server Managed Provider classes.
System.Data.SQLTypes	Contains classes for native types within SQL Server.

The DataSet Class

The DataSet class is central to ADO.NET and represents the primary encapsulation for data access and manipulation in the .NET Framework. DataSet is a disconnected and in-memory view of a database. You should appreciate that a DataSet knows nothing about SQL or even DBMS's. You can have a DataSet dynamically created and populated with data without a single byte of communication made with a DBMS. This decoupling between data and the underlying data source is the core enabler for the ADO.NET disconnected mode of operations.

Internally you can think of a DataSet as an XML document containing one or more pieces of separate or related tabular XML elements. In fact, .NET provides a unified programming model for accessing data represented as both XML data and relational data. You can start with a DataSet and work with the data following a non-sequential, hierarchical path driven by the XML DOM. Equally, you can work with XML documents in a row-after-row fashion through a DataSet using a relational model.

In .NET Framework the XmlDataDocument class provides this integration between the relational and XML models.

The XmlDataDocument Class

The XmlDataDocument class is extended from the XmlDocument class, which itself is the primary encapsulation in the .NET Framework for an XML document. XmlDataDocument provides the extra capability for manipulating relational data. Like all other XML-related classes, XmlDataDocument resides in the System.Xml namespace.

Once a relationship between a DataSet and XmlDataDocument is established, synchronization of the underlying data is automatic. Therefore, adding a record in the DataSet, for example, always creates a corresponding node in the XmlDataDocument. Conversely, adding a node in the XmlDataDocument also creates a new record in the DataSet.

Depending on the source of your data, you can instantiate an XmlDataDocument and associate a DataSet with it in two ways. The first method is that if your source data is in a DataSet, you instantiate a new XmlDataDocument, passing the DataSet in the constructor. Your DataSet is now attached to the XmlDataDocument, and you can use the exposed methods and properties to access and manipulate the relational data using the DOM.

Every rule has exceptions. A newly added node in the XmlDataDocument will result in a new record in the DataSet only if the new node is recognized as corresponding to a row in the DataSet. This behavior allows the XML document to contain elements that do not correspond to relational records.

In Listing 16-3 we show you a sample C# console program that retrieves data from an SQL Server database and uses XmlDataDocument to manipulate the data.

Listing 16-3 DataSet2XmlDataDocument.cs: Manipulating relational data using the DOM.

 using System; using System.IO; using System.Data; using System.Data.SQL; using System.Xml; public class DataSet2XmlDataDocument { public static void Main( String[] args ) { SQLConnection conn = null; try { /* connect to the SQL Server database */ conn = new SQLConnection( "server=
localhost;uid=sa;pwd=;database=pubs" ); /* execute SQL query */ SQLDataSetCommand cmd = new SQLDataSetCommand( "select * from Titles", conn ); /* create a DataSet and populate it with the records returned from  result of the above SQL query */ DataSet ds = new DataSet(); cmd.FillDataSet( ds, "Titles" ); /* associate this DataSet with an XmlDataDocument */ XmlDataDocument doc = new XmlDataDocument( ds ); /* you can now process this doc just like any 
DataDocument. For example,  the following uses DataDocumentNavigator to select the first Titles node  and modifies the value. */ DataDocumentNavigator nav = new DataDocumentNavigator
( doc ); nav.Select( "//NewDataSet/Titles[1]/title_id" ); if ( nav.MoveToNextSelected() ) { Console.WriteLine( "Original=" + nav.InnerText ); } else { Console.WriteLine( "Unexpected error: Node not found" ); } nav.InnerText = "BU1030"; /* modify the value of the title_id node */ /* XmlDataDocument auto synchronizes the above change with the associated  DataSet. */ Console.WriteLine( "Modified=" + ds.Tables[ "Titles" ].Rows[ 0 ][ "title_id" ] ); } finally { if ( conn != null && conn.State == DBObjectState.Open ) { conn.Close(); } } } }

Use the following console command to compile DataSet2XmlDataDocument.cs:

 csc /r:System.dll /r:System.Data.dll /r:System.Xml.dll DataSet2XmlDataDocument.cs

When you execute DataSet2XmlDataDocument.exe you should get an output in your console window similar to the following:

 Original=BU1032 Modified=BU1030

You can see that we have successfully used the DOM to modify the data in a DataSet.

If your source data is in an XmlDocument or even an XmlDataDocument, you can use its DataSet member property to access and manipulate the data using a relational model. In Listing 16-4 we have a C# console program that uses an associated DataSet to change the data originally stored in an XmlDataDocument.

Listing 16-4 XmlDataDocument2DataSet.cs: Manipulating an XML document using a DataSet.

 using System; using System.IO; using System.Data; using System.Xml; public class Xml2DataSet { public static void Main() { try { /* instantiate a XmlDataDocument */ XmlDataDocument doc = new XmlDataDocument(); /* load the schema */ doc.DataSet.ReadXmlSchema( "book.xsd" ); /* now load the XML document */ doc.Load( "book.xml" ); /* print original price value of first Title node */ DataDocumentNavigator nav = new DataDocumentNavigator( doc ); nav.Select( "//Book/Titles[1]/price" ); if ( nav.MoveToNextSelected() ) { Console.WriteLine( "Original=" + nav.InnerText ); } /* update a price using the associated DataSet */ DataTable books = doc.DataSet.Tables[ "Titles" ]; books.Rows[ 0 ][ "price" ] = "12.99"; /* display modified price */ nav.Select( "//Book/Titles[1]/price" ); if ( nav.MoveToNextSelected() ) { Console.WriteLine( "Modified=" + nav.InnerText ); } } catch (Exception e) { Console.WriteLine ("Exception: {0}", e.ToString()); } } }

XmlDataDocument2DataSet.cs is the example of an XML document we will use in Listing 16-5.

Listing 16-5 book.xml: An XML document used in XmlDataDocument2DataSet.cs.

 <Book> <Titles> <title_id>BU1032</title_id> <title>The Busy Executive's Database Guide</title> <type>business</type> <pub_id>1389</pub_id> <price>19.99</price> <advance>5000</advance> <royalty>10</royalty> <ytd_sales>4095</ytd_sales> <notes>An overview of available database systems with emphasis on common business applications. Illustrated.</notes> <pubdate>1991-06-11T16:00:00</pubdate> </Titles> <Titles> <title_id>BU1111</title_id> <title>Cooking with Computers: Surreptitious Balance Sheets
</title> <type>business</type> <pub_id>1389</pub_id> <price>11.95</price> <advance>5000</advance> <royalty>10</royalty> <ytd_sales>3876</ytd_sales> <notes>Helpful hints on how to use your electronic resources to the best advantage.</notes> <pubdate>1991-06-08T16:00:00</pubdate> </Titles> <Titles> <title_id>BU2075</title_id> <title>You Can Combat Computer Stress!</title> <type>business</type> <pub_id>0736</pub_id> <price>2.99</price> <advance>10125</advance> <royalty>24</royalty> <ytd_sales>18722</ytd_sales> <notes>The latest medical and psychological techniques for living 
with the electronic office. Easy-to-understand explanations.</notes> <pubdate>1991-06-29T16:00:00</pubdate> </Titles> </Book>

We have also included the governing schema for our book.xml document in Listing 16-6.

Listing 16-6 book.xsd: The XML schema for book.xml.

 <xsd:schema  targetNamespace="" xmlns="" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:msdata=
"urn:schemas-microsoft-com:xml-msdata"> <xsd:element name="Titles"> <xsd:complexType content="elementOnly"> <xsd:all> <xsd:element name="title_id" type="xsd:string"/> <xsd:element name="title" type="xsd:string"/> <xsd:element name="type" type="xsd:string"/> <xsd:element name="pub_id" minOccurs="0" type="xsd:string"/> <xsd:element name="price" minOccurs="0" type="xsd:decimal"/> <xsd:element name="advance" minOccurs="0" type="xsd:decimal"/> <xsd:element name="royalty" minOccurs="0" type="xsd:int"/> <xsd:element name="ytd_sales" minOccurs="0" type="xsd:int"/> <xsd:element name="notes" minOccurs="0" type="xsd:string"/> <xsd:element name="pubdate" type="xsd:timeInstant"/> </xsd:all> </xsd:complexType> </xsd:element> <xsd:element name="NewDataSet" msdata:IsDataSet="True"> <xsd:complexType> <xsd:choice maxOccurs="unbounded"> <xsd:element ref="Titles"/> </xsd:choice> </xsd:complexType> </xsd:element> </xsd:schema>

XmlDataDocument2DataSet.cs uses a DataSet to change the price node value of the first Titles node in book.xml from 19.99 to 12.99. The modification is automatically reflected in the doc XmlDataDocument object.

To compile XmlDataDocument2DataSet.cs, run the following command line in a console window:

 csc /r:System.dll /r:System.Data.dll /r:System.Xml.dll XmlDataDocument2DataSet.cs

When you run the resultant XmlDataDocument2DataSet.exe in a console window, you should see an output similar to the following:

 Original=19.99 Modified=12.99

We have successfully modified the price from 19.99 to 12.99 using the associated DataSet.