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:

Method

Description

GetXml

Returns a string containing the XML representation of the data stored in the DataSet . No schema information is output.

GetXmlSchema

Returns just the schema for an XML representation of the data stored in the DataSet .

InferXmlSchema

Takes an XML document provided in a TextReader , XmlReader , Stream object, or a specified disk file, to infer the structure for the data in a DataSet .

ReadXml

Reads XML data (including a schema if present) into the DataSet from a TextReader , XmlReader , Stream object, or a specified disk file.

ReadXmlSchema

Reads an XML schema (only) into the DataSet from a TextReader , XmlReader , Stream object, or a specified disk file.

WriteXml

Writes the contents of the DataSet object to an XML document via a TextWriter , XmlWriter , or Stream object, or directly to a specified disk file. May include a schema - see the notes following the next example.

WriteXmlSchema

Writes a schema describing the contents of the DataSet object to a TextWriter , XmlWriter , or Stream object, or directly to a specified disk file.

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.

Note

Some of the example files in this chapter require write access to the server's wwwroot folder and subfolders . You will get an "Access Denied" message for these examples when running under the default configuration of ASP.NET. See the Setting Up the Samples section in Chapter 8 to configure the relevant permissions.

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:

click to expand
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.

click to expand
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.

click to expand
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:

Value

Description

WriteSchema

The default. Specifies that any loaded schema should be written out along with the XML data stored in the DataSet . If no schema was loaded (as in our example) then no schema is included.

IgnoreSchema

Even if there is a schema loaded it will not be written out with the XML data.

DiffGram

The data is written out in a form that includes all the original values, and any current values for columns in each row that have been changed since the DataSet was loaded. This allows any changes made to the data within the DataSet to be persisted within the XML. So, if the XML data is used to fill a DataSet again, the changes are persisted into the Original and Current versions of each column.

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.

click to expand
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.

Note

The DataSet does not validate the XML data against the schema “ it just uses it to infer the structure required for the tables in the DataSet . We'll look at the issues of validating an XML document against a schema later in this chapter.

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:

click to expand
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 .

click to expand
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="..\global\get-dataset-control.ascx" %>   ...   <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:

click to expand
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.

click to expand
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:

click to expand
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.




Professional ASP. NET 1.1
Professional ASP.NET MVC 1.0 (Wrox Programmer to Programmer)
ISBN: 0470384611
EAN: 2147483647
Year: 2006
Pages: 243

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net