DataSets in .NET

XQuery is an excellent example of how XML is baked into the experience of manipulating data on the

.NET Framework. The System.Data namespace and System.Xmlnamespace have started mingling

their functionality for some time. DataSets are another example of how relational data and XML data

meet in a hybrid class library. During the COM and XML heyday, the ADO 2.5 recordset sported the

capability to persist as XML. The dramatic inclusion of XML functionality in a class library focused

entirely on manipulation of relational data was a boon for developer productivity. XML could be pulled

out of SQL Server and manipulated.

Persisting DataSets to XML

Classes within System.Data use XmlWriter and XmlReader in a number of places. Now that you’re

more familiar with System.Xmlconcepts, be sure to take note of the method overloads provided by the

classes within System.Data . For example, the DataSet.WriteXml method has four overloads, one of

which takes in XmlWriter. Most of the methods with System.Data are very pluggable with the classes

from System.Xml. Listing 13-14 shows another way to retrieve the XML from relational data by loading

a DataSet from a SQL command and writing it directly to the browser with the Response object’s

TextWriterproperty using DataSet.WriteXml .

DataSets have a fairly fixed format, as seen in this example. The root node of the document isCustomers,

which corresponds to the DataSetName property. DataSets contain one or more named DataTableobjects,

and the names of these DataTablesdefine the wrapper element — in this case,Customer . The name of the

DataTableis passed into the loadmethod of the DataSet. The correlation between the DataSet’s name,

DataTable’s name, and the resulting XML is not obvious when using DataSets. The resulting XML is shown

in the browser in Figure 13-4.

DataSets present a data model that is very different from the XMLway of thinking about data. Much of the

XML-style of thinking revolves around the InfoSet or the DOM, whereas DataSets are row- and column-based. The XmlDataDocumentis an attempt to present these two ways of thinking into one relatively unified

model.

XmlDataDocument

Although DataSets have their own relatively inflexible format for using XML, the XmlDocument class

does not. In order to bridge this gap, an unusual hybrid object, the XmlDataDocument, is introduced.

This object maintains the full fidelity of all the XML structure and allows you to access XML via the

XmlDocument API without losing the flexibility of a relational API. An XmlDataDocumentcontains a

480

Chapter 13

DataSet of its own and can be called DataSet-aware. Its internal DataSet offers a relational view of the

XML data. Any data contained within the XML data document that does not map into the relational

view is not lost, but becomes available to the DataSet’s APIs

The XMLDataDocumentis a constructor that takes a DataSet as a parameter. Any changes made to the

XmlDataDocumentare reflected in the DataSet and vice versa.

Now take the DataSet loaded in Listing 13-14 and manipulate the data with the XmlDataDocument and

DOM APIs you’re familiar with. Next, jump back into the world of System.Data and see that the DataSets

underlying DataRows have been updated with the new data, as shown in Listing 13-15

Listing 13-15 extends Listing 13-14 by first commenting out changing the HTTP ContentType and the call

to DataSet.WriteXml. After the DataSet is loaded from the database, it is passed to the XmlDataDocument

constructor. At this point, the XmlDataDocument and the DataSet refer to the same set of information. The

EnforceConstraints property of the DataSet is set to false to allow changes to the DataSet. When

EnforceConstraints is later set to true, if any constraint rules were broken, an exception is thrown. An

XPath expression is passed to the DOM method SelectSingleNode, selecting the ContactTitlenode of

a particular customer, and its text is changed to Boss. Then by calling GetRowFromElement on the

XmlDataDocument , the context jumps from the world of the XmlDocument back to the world of the DataSet.

Column names are passed into the indexing property of the returned DataRow, and the output is shown in

this line:

Ana Trujillo is the Boss

The data is loaded from the SQL server and then manipulated and edited with XmlDocument -style

methods; a string is then built using a DataRowfrom the underlying DataSet.

XML is clearly more than just angle brackets. XML data can come from files, from databases, from infor-

mation sets like the DataSet object, and certainly from the Web. Today, however, a considerable amount

of data is stored in XML format, so a specific data source control has been added to ASP.NET 2.0 just for

retrieving and working with XML data.

Running Codes

VB

Dim connStr As String = “database=Northwind;Data Source=localhost; “ _

& “User id=sa;pwd=wrox”

Using conn As New SqlConnection(connStr)

Dim command As New SqlCommand(“select * from customers”, conn)

conn.Open()

Dim ds As New DataSet()

ds.DataSetName = “Customers”

ds.Load(command.ExecuteReader(), LoadOption.OverwriteChanges, “Customer”)

Response.ContentType = “text/xml”

ds.WriteXml(Response.OutputStream)

End Using

C#

string connStr = “database=Northwind;Data Source=localhost;User id=sa;pwd=wrox”;

using (SqlConnection conn = new SqlConnection(connStr))

{

SqlCommand command = new SqlCommand(“select * from customers”, conn);

conn.Open();

DataSet ds = new DataSet();

ds.DataSetName = “Customers”;

ds.Load(command.ExecuteReader(), LoadOption.OverwriteChanges, “Customer”);

Response.ContentType = “text/xml”;

ds.WriteXml(Response.OutputStream);

}

VB

Dim connStr As String = “database=Northwind;Data Source=localhost; “ _

& “User id=sa;pwd=wrox”

Using conn As New SqlConnection(connStr)

Dim command As New SqlCommand(“select * from customers”, conn)

conn.Open()

Dim ds As New DataSet()

ds.DataSetName = “Customers”

ds.Load(command.ExecuteReader(), LoadOption.OverwriteChanges, “Customer”)

‘Response.ContentType = “text/xml”

‘ds.WriteXml(Response.OutputStream)

‘Added in Listing 13-15

Dim doc As New XmlDataDocument(ds)

doc.DataSet.EnforceConstraints = False

Dim node As XmlNode = _

doc.SelectSingleNode(“//Customer[CustomerID = ‘ANATR’]/ContactTitle”)

node.InnerText = “Boss”

doc.DataSet.EnforceConstraints = True

Dim dr As DataRow = doc.GetRowFromElement(CType(node.ParentNode, XmlElement))

Response.Write(dr(“ContactName”).ToString() & “ is the “)

Response.Write(dr(“ContactTitle”).ToString())

End Using

C#

string connStr = “database=Northwind;Data Source=localhost; “

+ “User id=sa;pwd=wrox”;

using (SqlConnection conn = new SqlConnection(connStr))

{

SqlCommand command = new SqlCommand(“select * from customers”, conn);

conn.Open();

DataSet ds = new DataSet();

ds.DataSetName = “Customers”;

ds.Load(command.ExecuteReader(), LoadOption.OverwriteChanges,”Customer”);

//Response.ContentType = “text/xml”;

//ds.WriteXml(Response.OutputStream);

//Added in Listing 13-15

481

Working with XML

XmlDataDocument doc = new XmlDataDocument(ds);

doc.DataSet.EnforceConstraints = false;

XmlNode node = doc.SelectSingleNode(@”//Customer[CustomerID

= ‘ANATR’]/ContactTitle”);

node.InnerText = “Boss”;

doc.DataSet.EnforceConstraints = true;

DataRow dr = doc.GetRowFromElement((XmlElement)node.ParentNode);

Response.Write(dr[“ContactName”].ToString() + “ is the “);

Response.Write(dr[“ContactTitle”].ToString());

}