Introduction

ADO.NET can be used as a powerful XML middleware. Although coming from ADO, it has been entirely redesigned for a better support for Web applications. A main feature of ADO.NET is its disconnected recordsets, known as DataSet. It acts as a feature-rich in-memory database or data cache. In other words, it does not maintain any database server cursors. All recordset’s database characteristics are available in DataSet, such as sorting, paging, filtered views, relations, indexing, and primary/foreign keys.

Native support for XML is another principal feature for ADO.NET. In native mode, record tables are stored as XML documents where schema and data are treated as distinct and replaceable elements. It is quite different from ADO. The ADO native storage format for a recordset is the Advanced Data Table Gram (ADTG) file format, which is a proprietary, binary schema that represents a recordset’s in-memory image. The DataSet objects in ADO.NET can load its data from variant sources: a database server, a local or remote XML file and any accessible system resources. Once the data is loaded, they can be either treated as a record table or a virtual XML document. We principally discuss the latter. Now, I will give you several use cases. Each use case is a standalone unit test which can be run under NUnit. So you should install a latest version of NUnit. More information about its installation and usage can be found here.

Before executing these examples, you should also setup an ODBC connection for the database with the following steps:

Extract the download package. There is an access file “NWIND_2002.MDB” in the folder “UnitTest_ADO.NET_XML”.

Enter “XmlDb_NorthWind” as the Data Source Name (DSN), and choose the Access file “NWIND_2002.MDB” as the data source.

Here are the relationships between the tables in the Access file:

Figure 1: Relationships between tables

Transform a single table to XML document

With ADO.NET, we can easily build a virtual XML document on top of recordset tables. Its root element has exactly the same name as the DataSet object. Tables in the DataSet object present as child nodes of the root element. For example, there is a DataSet named “XmlDb”. When it is transformed into XML document, the name of its root element will be “XmlDb” (refer to Figure 2). All rows in the table “Customers” will be mapped to a unique child node of the root element, whose node name matches the table name “Customers”. In Figure 2, “CustomID”, “CompanyName”, etc. are fields of the table “Customers”. When they are transformed into XML, they present as child nodes of “Customers”. Their node name will be the same as the field name and node value will be the value of that field.

Figure 2: Transformation of a single table to XML document.

The following simple example explains how ADO.NET maps the DataSet object to a virtual XML document:

using System;
using System.IO;
using System.Xml;
using System.Data;
using System.Data.Odbc;
using NUnit.Framework;
[Test] publicvoid SingleTable2XML()
{
//Create an ODBC connection to the database. Here it is an Access file
OdbcConnection conn = new OdbcConnection("DSN=XmlDb_NorthWind");
//Create a DataSet with a name "XmlDb"
DataSet dataset = new DataSet("XmlDb");
//Create a DataAdapter to load data from original data source to the DataSet
OdbcDataAdapter adapter = new OdbcDataAdapter();
adapter.SelectCommand = new OdbcCommand("SELECT * FROM Customers", conn);
adapter.Fill(dataset, "Customers");
//Create a virtual XML document on top of the DataSet
XmlDataDocument doc = new XmlDataDocument(dataset);
//Output this XML document
doc.Save(Console.Out);
//NUnit test to confirm the result is exactly what we expect
Assert.AreEqual("XmlDb", doc.DocumentElement.LocalName);
Assert.AreEqual("Customers", doc.DocumentElement.FirstChild.LocalName);
}

Transform Master-Detail tables to XML document

Entity-Relationship model is a long-tested approach to map the real world to database structure. Relationships are often represented as Master-Detail tables, which can be naturally transformed into XML parent/child nodes, with ADO.NET. The Figure 3 shows you such a mapping:

Figure 3: Transformation of Master-Detail tables to XML document.

The key point to generate nested XML nodes is to setup a DataRelation object to link the master table and the detail table with the primary key and the foreign key. Like this:

Query a database with XPath

.NET Framework implements all DOM interfaces in its System.Xml namespace. Moreover, it has integrated XPath in the XmlNode level as an extension to DOM. So, once a virtual XML document is built, it can be queried with XPath immediately. For example, in last section, we have created an XML document which represents “Customers” and their correspondent “Orders”. Now, we want to find out all customers in Berlin and have asked to ship the ordered products to Germany. We can perform such a search on the XML document with one line of code:

The mapping between XmlElement and DataRow

Although accessing DataSet with XML provides some unique advantages, we still need to obtain some complemental information about the data. For example, ADO.NET marks every DataRow with its current state, namely Added, Deleted, Detached, Modified, and Unchanged. These states are important when we perform update to the data source. Fortunately, XmlDataDocument provides a useful method to help us get correspondent DataRow from the XmlElement. Once we get the DataRow, its current state can be obtained through its property RowState:

Directly generate HTML from DataSet with XSLT

It is quite attractive to convert the content in DataSet to variant output formats, such as HTML/XHTML, WAP, PDF, SVG etc. The .NET Framework provides perfect support for XSLT which facilitates such kinds of transformation. More information about XSLT can be found here.

Now, let’s suppose we want to generate a simple report about the order details of the ten most expensive products. Following the previous several sections, we can easily build a virtual nested XML document from the “Products” and “OrderDetails” tables. Then we create an XSTL file like this:

Extension: Advanced XPath Query

Standard XPath is not powerful enough to be a database query language, e.g.: it lacks the DateTime related functions. Fortunately, it could be enhanced with customized XPath functions. Mr. Prajakta Joshi has published a very comprehensive article on this topic in MSDN. But his approach is too complex to follow. You should manually point out the function name, arguments number and their types, return type for every customized function, etc. It is not so flexible to add new XPath functions and is hard to maintain. Now, let's use the Reflection mechanism in .NET Framework to simplify this process (refer to Figure 5).

Figure 5: Customize XPath functions

All customized functions can only present as static methods in the XmlDbXPathFunctions class. Once XPathExpress requires XsltContext to ResolveFunction(). The XsltContext creates an XmlDbXPathFunctionWrapper object which implements the IXsltContextFunction interface. A function name and required argument types will be passed to its constructor. In the constructor, XmlDbXPathFunctionWrapper tries to find a best-fit static method in the XmlDbXPathFunctions class. Then XPathExpression call the Invoke() method of XmlDbXPathFunctionWrapper, which will invoke the real correspondent method in the XmlDbXPathFunctions class and return the result.

This is a very flexible approach to extend the XPath. If you want to add your own function to XPath execution context, you just need to write a static method in the XmlDbXPathFunctions class. The new function will be detected automatically.

Some useful DateTime functions have already been added. Now you can extract the year, month, day, hour, minute, second, ticks form a DateTimeXmlNode. It is quite helpful when you try to filter the XmlNode set with the DateTime information. For example: "//Orders[ex:year(string(ShippedDate)) = 1995 and ex:month(string(ShippedDate)) <= 3]" will seek all orders shipped in the first quarter in the year of 1995.

If you need some special XPath functions, just add them as static methods into the XmlDbXPathExtensionFunctions class. These static methods will be found automatically and be applied to your XPath query. Really simple, isn't it?

Conclusion

Treating a database as a large virtual XML document with ADO.NET is not always a good idea. It demands more memory, and its performance could be a big problem in the case of complex and enormous databases. But for relatively small projects, it works perfectly and might save a lot of time for you. Anyways, you could optimize the code above to make it meet your requirements.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

Hi, I have been working in ASP.Net.But I haven't used XML in my applications.I need to know where should use XML in asp.net? and Which is better to use whether <b>SQL Datasource or XML Datasource</b>? Which is secured one while tranfering from Client side to server side?

Hi, I'm very new to xml technology plz tell me how to query where PunchDate is 09/01/2007. then I want to read other Elements like EmpNo,PunchIn,PunchOut. (in this case two records will select. For better visible I have changed '>' to '|")

I mad a dataset and made a main form with controls to edit the database. I also made other forms to edit other tables in the database. Some combo-boxes in my main form are linked to those other tables. When I run the application and edit a table with the forms that edit the other tables, the combo-boxes on my main form do not update unless I call:

Application.Exit()

or close with the reason:

CloseReason.UserClosing

Is there some method pre-made to refresh the combo-boxes or will I have to write some code? Please help me out!:->