Professional ASP.NET 1.1 [Electronic resources] نسخه متنی

XML and the DataSet Object

Despite the ubiquitous presence of relational databases as the powerhouses of most commercial environments today, the use of XML as a data format is growing steadily. The ease of transmission and storage of XML as a text document (or within a database table as text), and its inherent cross-platform nature make it ideal for many situations. In fact, within the .NET Framework, XML is actually the foundation for all data storage and serialization. Now, there are no MIME-encoded

Recordset or COM objects that hold data in their own specific formats.

A good example is the

DataSet object used throughout the previous chapters. We've viewed it as a container for one or more data tables (rather like some wrapper around multiple

Recordset objects). This is a reasonable approach when you need to access the data using relational techniques. However, the

DataSet can persist its contents as a disk file, or into another object such as a

Stream . The format of the data at this point is XML.

The XML-Based Methods of the DataSet

The

DataSet object exposes several methods for working with an XML representation of the data it contains or will contain. These are summarized in the following table:

In general, when extracting XML from a

DataSet (unless you actually need a string representation of the data) the

GetXml and

GetXmlSchema methods should be avoided. It's much more efficient to create a

Stream or disk file directly, or take advantage of a

TextWriter or

XmlWriter when using the

WriteXml and

WriteXmlSchema methods. The next two examples demonstrated these two methods, and the

ReadXml and

ReadXmlSchema methods.

Writing Data from a DataSet to an XML File

This example (

write-data-as-xml.aspx ) demonstrates how to write data from a

DataSet directly to a disk file as an XML document. As the

DataSet is being filled from a relational database, you need to include the relevant .NET namespaces in the page, and collect the database connection string from the

web.config file as in the examples in earlier chapters. The page also contains three

<div> elements where the information and results are output.

<%@Import Namespace="System.Data" %> 
<%@Import Namespace="System.Data.OleDb" %> 
 
<div>Connection string: <span id="outConnect" runat="server" /></div> 
<div>SELECT command: <span id="outSelect" runat="server" /></div> 
<div id="outMessage" runat="server" /> 

Filling the DataSet

In the

Page_Load event, we fill the

DataSet using a SQL statement that joins two tables. The way the

DataSet is being filled is identical to the techniques and code used in the previous chapters, and you can use the [view source] link at the bottom of the page to see the complete code. We won't be describing it in detail again here.

Sub Page_Load() 
 
Dim strConnect As String 
strConnect = ConfigurationSettings.AppSettings("DsnWroxBooksOleDb") 
outConnect.innerText = strConnect 
 
Dim strSelect As String 
strSelect = "SELECT BookList.*, BookAuthors.FirstName, " _ 
& "BookAuthors.LastName FROM BookList INNER JOIN " _ 
& "BookAuthors ON BookList.ISBN = BookAuthors.ISBN " _ 
& "WHERE BookList.ISBN LIKE '18610033%'" 
outSelect.innerText = strSelect 
 
Dim objDataSet As New DataSet 
Try 
Dim objConnect As New OleDbConnection(strConnect) 
Dim objDataAdapter As New OleDbDataAdapter(strSelect, objConnect) 
objDataAdapter.Fill(objDataSet, "Books") 
 
Catch objError As Exception 
outError.innerHTML = "Error while accessing data.<br />" _ 
& objError.Message & "<br />" & objError.Source 
Exit Sub 
 
End Try 

Creating the XML Data and Schema Files

Now that there is a

DataSet containing the data, you can write it out to a disk file as XML. All you have to do is create the appropriate path and name for the two files, and then call the

WriteXml and

WriteXmlSchema methods. This is done within a

Try..Catch construct to trap any errors that might arise while writing the files – for example, if ASP.NET does not have the relevant permission or if the path does not exist. Note that you have to provide a physical path to the XML document files, not the virtual path. And if all goes well, the confirmation messages and hyperlinks to the new files are displayed.

Try 
'use the path to the current virtual application 
Dim strVirtualPath As String = Request.ApplicationPath _ 
& "/XML-from-DataSet.xml" 
Dim strVSchemaPath As String = Request.ApplicationPath _ 
& "/Schema-from-DataSet.xsd" 
 
'write data and schema from DataSet to documents on disk 
'use the Physical path to the file not the Virtual path 
objDataSet.WriteXml(Request.MapPath(strVirtualPath)) 
 
outMessage.innerHTML = "Written file: <a href=" & Chr(34) _ 
& strVirtualPath & Chr(34) & ">" _ 
& strVirtualPath & "</a><br />" 
 
objDataSet.WriteXmlSchema(Request.MapPath(strVSchemaPath)) 
outMessage.innerHTML &= "Written file: <a href=" & Chr(34) _ 
& strVSchemaPath & Chr(34) & ">" _ 
& strVSchemaPath & "</a></b>" 
 
Catch objError As Exception 
'display error details 
outMessage.innerHTML = "Error while writing disk file.<br />" _ 
& objError.Message & "<br />" & objError.Source 
Exit Sub ' and stop execution 
End Try 

The result can be seen in Figure 11-2. The page includes two hyperlinks to the files created – the XML data itself and the XSD schema that defines the structure of the XML document:

Figure 11-2:

The Resulting XML Document

If you click the hyperlinks in the page, you can view the created XML document and schema. The XML document is shown in Figure 11-3. IE 5's ability to collapse elements enables you to see the overall structure of the document. Each

<Books> element describes one row from the original rowset that was loaded into the

DataSet object.

Figure 11-3:

The Resulting XML Schema

The second link in the example page shown in Figure 11-2 displays the schema that was created as a separate disk file. Figure 11-4 shows that the structure is specified quite loosely – for example, the schema allows elements to be optional (

minOcccurs="0" ). It means that you may want to modify the schema if you use the XML for situations where a tighter definition is required – perhaps when communicating with BizTalk Server or some other application.

Figure 11-4:

The XmlWriteMode Enumeration

The

WriteXml method can be used with an optional second parameter that specifies in more detail how the data will be output as XML. The following code writes the data from a

DataSet object into a disk file in

DiffGram format:

DataSet.WriteXml(file-path, XmlWriteMode.DiffGram) 

This method accepts a value from the

XmlWriteMode enumeration:

Reading Data into a DataSet from an XML File

This example, shown in Figure 11-5, uses the XML file created in the previous example.

Figure 11-5:

The XML data and schema are loaded into a new

DataSet object. You can then display the content using

DataGrid server controls. In other words, the data is accessed as a data table using relational methods. Figure 11-5 shows what the example page, Reading Data Into a DataSet from an XML File (

read-data- from-xml.aspx ) looks like when you run it

The Code for This Example

Because we're creating a

DataSet object, an

Import directive for the

System.Data namespace has to be included. Next comes a

<div> for output messages, followed by two ASP.NET

DataGrid controls. The following code shows the relevant HTML sections:

<%@Import Namespace="System.Data" %> 
 
<div id="outMessage" runat="server" /><br /> 
 
<b>DataSet.Tables Collection</b> 
<asp:datagrid id="dgrTables" runat="server" /><br /> 
 
<b>Contents of DataSet.Tables("Books")</b> 
<asp:datagrid id="dgrValues" runat="server" /> 

Loading the XML Documents

The code in the

Page_Load event handler first creates a new empty

DataSet instance, and then builds up the path and name for each of the two XML documents that we'll be loading; the schema and the data. Next, it's simply a matter of calling the

ReadXmlSchema and

ReadXml methods to first load the schema and then the data. As with the previous example, you have to provide a physical path to the XML document files, rather than a virtual path.

'create a new DataSet object 
Dim objDataSet As New DataSet() 
 
Try 
'use the path to the current virtual application 
Dim strVirtualPath As String = Request.ApplicationPath _ 
& "/XML-from-DataSet.xml" 
Dim strVSchemaPath As String = Request.ApplicationPath _ 
& "/Schema-from-DataSet.xsd" 
 
'read schema and data into DataSet from documents on disk 
'use the Physical path to the file not the Virtual path 
objDataSet.ReadXMLSchema(Request.MapPath(strVSchemaPath)) 
outMessage.InnerHTML = "Reading file: <a href=" & Chr(34) _ 
& strVSchemaPath & Chr(34) & ">" _ 
& strVSchemaPath & "</a><br />" 
 
objDataSet.ReadXML(Request.MapPath(strVirtualPath)) 
outMessage.InnerHTML &= "Reading file: <a href=" & Chr(34) _ 
& strVirtualPath & Chr(34) & ">" _ 
& strVirtualPath & "</a>" 
 
Catch objError As Exception 
'display error details 
outMessage.InnerHTML = "Error while reading disk file.<br />" _ 
& objError.Message & "<br />" & objError.Source 
Exit Sub ' and stop execution 
 
End Try 

Displaying the Data

The data is now loaded into the

DataSet and ready to use. We simply bind the

Tables collection and the contents of the

Books table (the tablename is automatically inferred from the repeated element name in the XML file) to a pair of

DataGrid server controls to display the contents.

'assign the DataView.Tables collection first to the DataGrid control 
dgrTables.DataSource = objDataSet.Tables 
dgrTables.DataBind() 'and bind (display) the data 
 
'create a DataView object for the Books table in the DataSet 
Dim objDataView As New DataView(objDataSet.Tables("Books")) 
 
'assign the DataView object to the second DataGrid control 
 
dgrValues.DataSource = objDataView 
dgrValues.DataBind() 'and bind (display) the data 

With or Without a Schema – That Is the Question

Our example page explicitly tells the

DataSet what to expect. The schema loaded first contains a definition of the structure of the XML data that's loaded afterwards. However, you may not want to use a schema, or you may not have one available.

This is often the case if the XML structure is not constant over time. To avoid creating new schemas for dynamically generated XML data, you can omit the schema altogether and just use the

ReadXml method to load the XML data. The

DataSet will infer the structure of the data automatically from the structure of the XML document. However, if for some reason it can't do so (usually because the document structure is not consistent), you may not get any data in the

DataSet .

Bear in mind that without a schema, the

DataSet may get a different idea about the structure, so it's wise to include a schema whenever possible. As long as the XML document is well-formed, it will be loaded – but the results might not be what you expect. You can create the schema as in the previous example, by loading the appropriate data into the

DataSet and then calling the

WriteXmlSchema method.

You can also include a schema within the XML data file or document, rather than as a separate file. In this case, the structure of the data is specified automatically when you load the document and there is (of course) no need to execute

ReadXmlSchema .

Getting XML as a String from a DataSet

You can use the

GetXml and

GetXmlSchema methods to extract XML from a

DataSet . Bear in mind that it is not the recommended technique, unless you specifically want it as a string for use in your code. The

WriteXml method is more efficient. However, we've provided a simple example page that uses the

GetXml and

GetXmlSchema methods to extract data from a

DataSet and display it in the page.

Figure 11-6 shows what the Returning XML from a DataSet (

get-data-as-xml.aspx ) example looks like when you run it and click the Display XML Data checkbox:

Figure 11-6:

The two checkboxes simply execute an appropriate combination of the

GetXml and

GetXmlSchema methods of the

DataSet (which is filled with data using exactly the same code as the previous example). The two methods return a

String , which is assigned to the

InnerHtml property of an HTML

<xmp> element.

The HTML

<xmp> element automatically displays content as text, without attempting to render it, so the XML elements are visible. An alternative approach would be to use the

Server.HtmlEncode method on the string. It could then be displayed directly in the page with the elements visible.

'display the data and/or schema in the DataSet as an XML document 
xmpResults.InnerHtml = " 
If chkSchema.checked Then 
xmpResults.InnerHtml = objDataSet.GetXmlSchema() & vbCrlf & vbCrlf 
End If 
If chkXML.checked Then 
xmpResults.InnerHtml &= objDataSet.GetXml() 
End If 

Clicking the other checkbox in the page displays just the schema (as shown in Figure 11-7). You can check them both to display the schema and the XML data together. If you do this and copy the result to a new document, you'll get the appropriate combined document and schema as one file that represents the contents of this

DataSet .

Figure 11-7:

Nested XML and Related Data in a DataSet

When you export data from a

DataSet as XML, you don't have much control over the actual format of the XML itself. There are several interesting arguments for and against the two basic approaches to the structure of an XML document that contains repeating data (a representation of a rowset or data table):

Place the data within the elements as the text content.

Use a single element for each data row, and place the data itself in attributes of that element.

The first option tends to give a more human-readable result, but produces a bigger document as there is more markup (element tags). Using attributes produces smaller documents, but accessing the content using other XML techniques such as the DOM methods can be more difficult.

Later in this chapter, you'll see how to use SQL Server's built-in XML data-handling function to create different formats of XML. In the meantime, you also need to consider how the format of the XML is affected when you export data from more than one data table into an XML document.

The DataRelation.Nested Property

You've seen in the previous few chapters that a

DataSet can contain more than one table plus the relationships between these tables. These relationships can be used to extract the child rows from one table that were related to the current row in the parent table.

Each relationship (

DataRelation object) in a

DataSet also has a

Nested property. This is

False by default, and has no effect when accessing the data using relational techniques – for example, when you bind the data to a

DataGrid or iterate through the

Rows collection of a table. However, it does influence the way that the data is exported as XML when you use the

GetXML or

WriteXml methods. Our next example demonstrates this.

The Nested XML Data Example Page

The Extracting Nested XML from a DataSet (

nested-xml-from-dataset.aspx ) example page demonstrates how useful the

Nested property of a

DataRelation object is when it comes to exporting data as XML. We use the same custom user control (

get-dataset-control.ascx) that was introduced in Chapter 8 to create a

DataSet object, and fill it with three tables named

Books ,

Authors , and

Prices that contain data from the WroxBooks database. The control also creates two relationships between these tables.

The HTML section of the page, shown in the following code, includes three ASP.NET

DataGrid controls. The first is used to display the

DataSet object's

Tables collection, and the other two are used to show the contents of the

Relations collection before and after the

Nested property is set. Also, two

<div> elements are used to display the links to the non-nested and nested format XML files that are created.

<%@ Register TagPrefix="wrox" TagName="getdataset" 
Src=" %> 
... 
<b>DataSet.Tables Collection</b> 
<asp:datagrid id="dgrTables" runat="server" /><br /> 
<b>DataSet.Relations Collection</b> 
<asp:datagrid id="dgrRelsNormal" runat="server" /> 
<div id="outResultNormal" runat="server" /><p /> 
<b>DataSet.Relations Collection</b> 
<asp:datagrid id="dgrRelsNested" runat="server" /> 
<div id="outResultNested" runat="server" /><p /> 

Displaying the DataSet Contents

In the page code, we first create the

DataSet using the custom control. Then we can display information about it. The first

DataGrid control on our page is bound to the

Tables collection, and the second to the

Relations collection, as shown in the following code:

Dim strConnect As String = _ 
ConfigurationSettings.AppSettings("DsnWroxBooksOleDb") 
Dim objDataSet As DataSet 
objDataSet = ctlDataSet.BooksDataSet(strConnect, "ISBN LIKE '0764544%'") 
If IsNothing(objDataSet) Then Exit Sub 
'bind the collection of Tables to the first DataGrid on the page 
dgrTables.DataSource = objDataSet.Tables 
dgrTables.DataBind() 
'bind the collection of Relations to the second DataGrid on the page 
dgrRelsNormal.DataSource = objDataSet.Relations 
dgrRelsNormal.DataBind() 

Exporting the Data to an XML Disk File

Next, we write the data in the

DataSet to an XML document on disk. We build up a virtual path and name for the file, write it with the

WriteXml method, and display a hyperlink so that it can be viewed:

'save as a disk file and create hyperlink to load it 
Dim strVirtualPath As String = Request.ApplicationPath _ 
& "/Normal-XML-from-DataSet.xml" 
objDataSet.WriteXml(Request.MapPath(strVirtualPath)) 
outResultNormal.innerHTML = "Written file: <a href=" & Chr(34) _ 
& strVirtualPath & Chr(34) & ">" _ 
& strVirtualPath & "</a>" 

Figure 11-8 shows the results of running the page:

Figure 11-8:

You can see the two

DataGrid controls populated so far, and following them is the hyperlink to the

Normal-XML-from-DataSet.xml file just created. In the second

DataGrid in the page, you can see the contents of the

DataSet.Relations collection at this point in the code. The two relations, named

BookAuthors and

BookPrices , have the default value

False for their

Nested property.

The Resulting XML Document

If you click the first hyperlink in the page, you will see the XML file format that is created by default from a

DataSet containing more than one table. This is shown in Figure 11-9, with some elements collapsed (using this feature of IE 5) so that you can see the structure.

Figure 11-9:

It's pretty clear that the document contains separate

<Books> ,

<Authors> , and

<Prices> elements. There is no concept of hierarchy between the parent and child elements. They are all at the same level (they are siblings, in XML terminology).

However, they also all contain the primary key (the ISBN code), so an application could read and work with the data in a related manner if required.

Nesting the XML Result

The format you've just seen is not really what you might expect after making creating the relationships between the tables. This is where the

Nested property comes to the rescue. The code in the example page continues by setting the

Nested property to

True for both the

DataRelation objects. Then it redisplays the contents of the

Relations collection in the third

DataGrid control. If you look back at Figure 11-8, you can see that the third

DataGrid shows the

Nested property of both

Relations as being

True now. So you can now create another XML document by exporting the contents of the

DataSet again.

'set the Nested property of the two relation objects 
objDataSet.Relations("BookAuthors").Nested = True 
objDataSet.Relations("BookPrices").Nested = True 
'bind the collection of Relations to the third DataGrid on the page 
dgrRelsNested.DataSource = objDataSet.Relations 
dgrRelsNested.DataBind() 
'save as a disk file and create hyperlink to load it 
Dim strVirtualPath As String = Request.ApplicationPath _ 
& "/Nested-XML-from-DataSet.xml" 
objDataSet.WriteXML(Request.MapPath(strVirtualPath)) 
outResultNested.innerHTML = "Written file: <a href=" & Chr(34) _ 
& strVirtualPath & Chr(34) & ">" _ 
& strVirtualPath & "</a>" 

The Resulting XML Document

If you open and view the second XML document, you can see the difference:

Figure 11-10:

As shown in Figure 11-10, each

<Books> element is a child of the document root, and the

<Authors> and

<Prices> elements are nested within their respective books. This is pretty much an ideal format both for readability and usability, and is probably similar to what you would have come up with had you crafted the XML by hand. Perhaps the only downside is the repeated occurrence of the primary key within the child elements. Later in this chapter, we'll discuss a way to extract the XML from a SQL Server database so that the values are in attributes rather than as the content of elements.