Introduction To ADO.NET With XML

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 recordsets 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 recordsets 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:

1. Get the file (NWIND_2002.MDB) from the internet.
2. Open Control Panel -> Administrative Tools -> Data Source.
3. Choose the System Data Sources tab.
4. Click "Add" button and choose "Microsoft Access Driver (*.mdb)" from the list. Then an ODBC configuration dialog appears.
5. 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] public void 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);
}
Output:

ALFKI
Alfreds Futterkiste
Maria Anders
Sales Representative

Obere Str. 57

Berlin
12209
Germany
030-0074321
030-0076545

**********

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:

DataColumn primarykey = dataset.Tables["Customers"].Columns["CustomerID"];
DataColumn foreignkey = dataset.Tables["Orders"].Columns["CustomerID"];
DataRelation relation = dataset.Relations.Add(primarykey, foreignkey);

It is not enough. You must also set the "Nested" property of the DataRelation to "true":

relation.Nested = true;

Full code:

using System;
using System.IO;
using System.Xml;
using System.Data;
using System.Data.Odbc;
using NUnit.Framework;
[Test] public void MasterDetailTables2XML()
{
//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");

//Load master table from original data source to the DataSet
OdbcDataAdapter adapter = new OdbcDataAdapter();
adapter.SelectCommand = new OdbcCommand("SELECT * FROM Customers", conn);
adapter.Fill(dataset, "Customers");

//Load detail table from original data source to the DataSet
adapter.SelectCommand = new OdbcCommand("SELECT * FROM Orders", conn);
adapter.Fill(dataset, "Orders");

//Get the primary key column from the master table
DataColumn primarykey = dataset.Tables["Customers"].Columns["CustomerID"];

//Get the foreign key column from the detail table
DataColumn foreignkey = dataset.Tables["Orders"].Columns["CustomerID"];

//Assign a relation
DataRelation relation = dataset.Relations.Add(primarykey, foreignkey);

//Ask ADO.NET to generate nested XML nodes
relation.Nested = true;

//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);
Assert.AreEqual("Customers",
doc.GetElementsByTagName("Orders")[0].ParentNode.LocalName);
}

Output:


ALFKI
Alfreds Futterkiste
Maria Anders
Sales Representative

Obere Str. 57
Berlin
12209
Germany
030-0074321
030-0076545
10643
ALFKI
1995-09-25T00:00:00.0000000+02:00
1995-10-23T00:00:00.0000000+02:00
1995-10-03T00:00:00.0000000+02:00
29.4600
Alfreds Futterkiste
Obere Str. 57
Berlin
12209
Germany
10692
ALFKI
1995-11-03T00:00:00.0000000+01:00
1995-12-01T00:00:00.0000000+01:00
1995-11-13T00:00:00.0000000+01:00
61.0200
Alfred's Futterkiste
Obere Str. 57
Berlin
12209
Germany

**********

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:

XmlNodeList nodeList =
doc.SelectNodes("/XmlDb/Customers/Orders[../City='Berlin'" +
" and ShipCountry='Germany']");
It will put all qualified nodes into an XmlNodeList which can be visited with a "foreach":
Collapse Copy Code
foreach (XmlNode node in nodeList)
{
......
}
Here is a full example:

using System;
using System.IO;
using System.Xml;
using System.Data;
using System.Data.Odbc;
using NUnit.Framework;
[Test] public void QueryWithXPath()
{
//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");

//Load master table from original data source to the DataSet
OdbcDataAdapter adapter = new OdbcDataAdapter();
adapter.SelectCommand = new OdbcCommand("SELECT * FROM Customers", conn);
adapter.Fill(dataset, "Customers");

//Load detail table from original data source to the DataSet
adapter.SelectCommand = new OdbcCommand("SELECT * FROM Orders", conn);
adapter.Fill(dataset, "Orders");

//Get the primary key column from the master table
DataColumn primarykey = dataset.Tables["Customers"].Columns["CustomerID"];

//Get the foreign key column from the detail table
DataColumn foreignkey = dataset.Tables["Orders"].Columns["CustomerID"];

//Assign a relation
DataRelation relation = dataset.Relations.Add(primarykey, foreignkey);

//Ask ADO.NET to generate nested XML nodes
relation.Nested = true;

//Create a virtual XML document on top of the DataSet
XmlDataDocument doc = new XmlDataDocument(dataset);

//Create an output buffer
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("");

//Perform an XPath query
XmlNodeList nodeList =
doc.SelectNodes("/XmlDb/Customers/Orders[../City='Berlin'" +
" and ShipCountry='Germany']");

//Visit results in the list
foreach (XmlNode node in nodeList)
{
stringBuilder.Append(node.OuterXml);

//NUnit tests to confirm the result is exactly what we expect
Assert.AreEqual("ShipCountry", node.ChildNodes[10].LocalName);
Assert.AreEqual("Germany", node.ChildNodes[10].InnerText);
Assert.AreEqual("City", node.ParentNode.ChildNodes[5].LocalName);
Assert.AreEqual("Berlin", node.ParentNode.ChildNodes[5].InnerText);
}
stringBuilder.Append("");
XmlDocument docResult = new XmlDocument();
docResult.LoadXml(stringBuilder.ToString());
docResult.Save(Console.Out);
}

Output:

10643
ALFKI
1995-09-25T00:00:00.0000000+02:00
1995-10-23T00:00:00.0000000+02:00
1995-10-03T00:00:00.0000000+02:00
29.4600
Alfreds Futterkiste
Obere Str. 57
Berlin
12209
Germany
10692
ALFKI
1995-11-03T00:00:00.0000000+01:00
1995-12-01T00:00:00.0000000+01:00
1995-11-13T00:00:00.0000000+01:00
61.0200
Alfred's Futterkiste
Obere Str. 57
Berlin
12209
Germany

**********

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:

DataRow row = xmlDataDocument.GetRowFromElement(xmlElement);
Console.Write("RowState: ");
switch(row.RowState)
{
case DataRowState.Added:
Console.WriteLine("Added");break;
case DataRowState.Deleted:
Console.WriteLine("Deleted");break;
case DataRowState.Detached:
Console.WriteLine("Detached");break;
case DataRowState.Modified:
Console.WriteLine("Modified");break;
default:
Console.WriteLine("Unchanged");break;
}
We can also detect whether there are errors after performing update:
Collapse Copy Code
DataRow row = xmlDataDocument.GetRowFromElement(xmlElement);
if (row.HasErrors)
Console.WriteLine(row.RowError);
else
Console.WriteLine("Everything is OK.");

XmlDataDocument provides another method GetElementFromRow to map a DataRow to XmlElement. Here is a complete example:

using System;
using System.IO;
using System.Xml;
using System.Data;
using System.Data.Odbc;
using NUnit.Framework;
[Test] public void MappingBetweenXmlElementAndDataRow()
{
//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 Products", conn);
adapter.Fill(dataset, "Products");

//Create a virtual XML document on top of the DataSet
XmlDataDocument doc = new XmlDataDocument(dataset);

Console.WriteLine("=========== GetRowFromElement ================");

//Perform XPath query
XmlNodeList nodeList = doc.SelectNodes("/XmlDb/Products[CategoryID=3]");
foreach (XmlNode node in nodeList)
{
//Map XmlElement to DataRow
DataRow row = doc.GetRowFromElement((XmlElement) node);
Console.WriteLine("Product Name = " + row["ProductName"]);
Assert.AreEqual(3, row["CategoryID"]);
}

Console.WriteLine("=========== GetElementFromRow ================");

//Perform ADO.NET native query
DataRow[] rows = dataset.Tables["Products"].Select("CategoryID=3");
foreach (DataRow row in rows)
{
//Map DataRow to XmlElement
XmlElement elem = doc.GetElementFromRow(row);
Console.WriteLine("Product Name = " + elem.ChildNodes[1].InnerText);
Assert.AreEqual("3", elem.ChildNodes[2].InnerText);
}
}

Output:

=========== GetRowFromElement ================
Product Name = Pavlova
Product Name = Teatime Chocolate Biscuits
Product Name = Sir Rodney's Marmalade
Product Name = Sir Rodney's Scones
Product Name = NuNuCa Nuß-Nougat-Creme
Product Name = Gumbär Gummibärchen
Product Name = Schoggi Schokolade
Product Name = Zaanse koeken
Product Name = Chocolade
Product Name = Maxilaku
Product Name = Valkoinen suklaa
Product Name = Tarte au sucre
Product Name = Scottish Longbreads
=========== GetElementFromRow ================
Product Name = Pavlova
Product Name = Teatime Chocolate Biscuits
Product Name = Sir Rodney's Marmalade
Product Name = Sir Rodney's Scones
Product Name = NuNuCa Nuß-Nougat-Creme
Product Name = Gumbär Gummibärchen
Product Name = Schoggi Schokolade
Product Name = Zaanse koeken
Product Name = Chocolade
Product Name = Maxilaku
Product Name = Valkoinen suklaa
Product Name = Tarte au sucre
Product Name = Scottish Longbreads

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, lets 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:

Product Name

Price

Discount

Quantity

Total

#EEEEEE
#AAAAAA
10$
10
10%
10
10
10
10$
10

Now, write a simple function to perform the XSLT transformation:

[Test]

public void GenerateHTMLFromXSLT()

{

//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");
//Load "Products" table from original data source to the DataSet

OdbcDataAdapter adapter = new OdbcDataAdapter();
adapter.SelectCommand = new OdbcCommand("SELECT * FROM Products", conn);

adapter.Fill(dataset, "Products");

//Load "Order Details" table from original data source to the DataSet
adapter.SelectCommand = new OdbcCommand("SELECT * FROM [Order Details]", conn);
adapter.Fill(dataset, "OrderDetails");
//Create a relationship between the two tables

dataset.Relations.Add(dataset.Tables["Products"].Columns["ProductID"],

dataset.Tables["OrderDetails"].Columns["ProductID"]).Nested = true;

//Build a virtual XML document on top of the DataSet

XmlDataDocument doc = new XmlDataDocument(dataset);

//Load the XSLT file. NOTE: Here it is compiled as an embedded resource file

Assembly assembly = System.Reflection.Assembly.GetExecutingAssembly();

XslTransform xslTran = new XslTransform();

Stream xslStream = assembly.GetManifestResourceStream("UnitTest_ADO.NET_XML.Test.xslt");

XmlTextReader reader = new XmlTextReader(xslStream);

xslTran.Load(reader, null, null);
//Output the result a HTML file

XmlTextWriter writer = new XmlTextWriter("xsltresult.html",System.Text.Encoding.UTF8);

xslTran.Transform(doc.CreateNavigator(), null, writer, null);

writer.Close();

}

Output:

Figure 4: HTML Output

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 DateTime XmlNode. 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.

[Test]
public void XPathExtension()
{
//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 Orders", conn);
adapter.Fill(dataset, "Orders");
//Create a virtual XML document on top of the DataSet XmlDataDocument doc = new XmlDataDocument(dataset);
//Create an XPath navigator
XPathNavigator nav = doc.CreateNavigator();
//XPath expression
String xpath = "//Orders[ex:year(ShippedDate)=1995 and ex:month(ShippedDate)<=3]"; //Compile the XPath expression
XPathExpression xpathexp = nav.Compile(xpath);
//Assign a customized XPath context
XmlDbXPathContext context = new XmlDbXPathContext(new NameTable()); context.AddNamespace("ex", "http://openvue.net"); xpathexp.SetContext(context);

//Perform XPath query
XPathNodeIterator it = nav.Select(xpathexp);
//Output the result
StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("");
while (it.MoveNext())

{

XmlElement elem = (XmlElement)((IHasXmlNode)it.Current).GetNode();

stringBuilder.Append(elem.ChildNodes[4].OuterXml);

DateTime dt = Convert.ToDateTime(elem.ChildNodes[4].InnerText);

Assert.AreEqual(1995, dt.Year);

Assert.IsTrue(dt.Month <= 3);

}
stringBuilder.Append(" ");

XmlDocument docResult = new XmlDocument();
docResult.LoadXml(stringBuilder.ToString());
docResult.Save(Console.Out);

}

Output:

1995-01-03T00:00:00.0000000+01:00

1995-01-02T00:00:00.0000000+01:00

1995-02-16T00:00:00.0000000+01:00

1995-01-13T00:00:00.0000000+01:00

1995-01-16T00:00:00.0000000+01:00

1995-02-10T00:00:00.0000000+01:00

1995-02-10T00:00:00.0000000+01:00

1995-02-09T00:00:00.0000000+01:00

1995-03-02T00:00:00.0000000+01:00

1995-02-27T00:00:00.0000000+01:00

1995-02-27T00:00:00.0000000+01:00

1995-03-03T00:00:00.0000000+01:00
1995-03-27T00:00:00.0000000+02:00
1995-02-27T00:00:00.0000000+01:00
1995-03-17T00:00:00.0000000+01:00
1995-03-09T00:00:00.0000000+01:00

1995-03-31T00:00:00.0000000+02:00

********

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.