ADO .NET and XML


This article has been excerpted from book "A Programmer's Guide to ADO.NET in C#".

There are two approaches to work with XML and ADO. First, you can use ADO.NET to access XML documents. Second, you can use XML and ADO.NET to access XML. Additionally, you can access a relational database using ADO.NET and XML.NET.

Reading XML using Data Set

In ADO.NET, you can access the data using the DataSet class. The DataSet class implements methods and properties to work with XML documents. The following sections discuss methods that read XML data.

The Read xml Method

ReadXml is an overloaded method; you can use it to read a data stream, TextReader, XmlReader, or an XML file and to store into a DataSet object, which can later be used to display the data in a tabular format. The ReadXml method has eight overloaded forms. It can read a text, string, stream, TextReader, XmlReader, and their combination formats. In the following example, create a new DataSet object.

In the following example, create a new DataSet object and call the DataSet. ReadXml method to load the books.xml file in a DataSet object:


        
//Create a DataSet object
        DataSet ds = new DataSet();
        
// Fill with the data
        ds.ReadXml("books.xml");


Once you've a DataSet object, you know how powerful it is. Make sure you provide the correct path of books.xml.

Note: Make sure you add a reference to System.Data and the System.Data.Common namespace before using DataSet and other common data components.

The ReadXmlSchema method

The ReadXMLSchema method reads an XML schema in a DataSet object. It has four overloaded forms. You can use a Text Reader, string, stream, and XmlReader. The following example shows how to use a file as direct input and call the ReadXmlSchema method to read the file:


        DataSet ds = new DataSet();
        ds.ReadSchema(@"c:\books.xml");


The following example reads the file XmlReader and uses XmlTextReader as the input of ReadXmlSchema:


        
//Create a dataset object
        DataSet ds = new DataSet("New DataSet");
        
// Read xsl in an XmlTextReader
        XmlTextReader myXmlReader = new XmlTextReader(@"c:\books.Xml");
        
// Call Read xml schema
        ds.ReadXmlSchema(myXmlReader);
        myXmlReader.Close();


Writing XML using Data Set

Not only reading, the DataSet class contains methods to write XML file from a DataSet object and fill the data to the file.

The Writexml Method

The WriteXml method writes the current data (the schema and data) of a DataSet object to an XML file. This is overloaded method. By using this method, you can write data to a file, stream, TextWriter, or XmlWriter. This example creates a DataSet, fills the data for the DataSet, and writes the data to an XML file.

Listing 6-26. Write xml Method 


using
 System;
using
 System.IO;
using
 System.Xml;
using
 System.Data;

namespace XmlAndDataSetsampB2
{
    class 
XmlAndDataSetSampCls
    {
        public static void Main()
        {
             
try
            {
                
// Create a DataSet, namespace and Student table
                
// with Name and Address columns
                DataSet ds = new DataSet("DS");
                ds.Namespace = "StdNamespace";
                DataTable stdTable = new DataTable("Student");
                DataColumn col1 = new DataColumn("Name");
                DataColumn col2 = new DataColumn("Address");
                stdTable.Columns.Add(col1);
                stdTable.Columns.Add(col2);
                ds.Tables.Add(stdTable);

                
//Add student Data to the table
                DataRow newRow; newRow = stdTable.NewRow();
                newRow["Name"] = "Mahesh Chand";
                newRow["Address"] = "Meadowlake Dr, Dtown";
                stdTable.Rows.Add(newRow);
                newRow = stdTable.NewRow();
                newRow["Name"] = "Mike Gold";
                newRow["Address"] = "NewYork";
                stdTable.Rows.Add(newRow);
                newRow = stdTable.NewRow();
                newRow["Name"] = "Mike Gold";
                newRow["Address"] = "New York";
                stdTable.Rows.Add(newRow);
                ds.AcceptChanges();

                
// Create a new StreamWriter
                
// I'll save data in stdData.Xml file
                System.IO.StreamWriter myStreamWriter = 
new
                System.IO.StreamWriter(@"c:\stdData.xml");

                
// Writer data to DataSet which actually creates the file
                ds.WriteXml(myStreamWriter);
                myStreamWriter.Close();
            }

            catch (Exception e)
            {
                Console.WriteLine("Exception: {0}", e.ToString());
            }
            return;
         }
    }
}


You wouldn't believe the WriteXml method does for you. If you see the output stdData.xml file, it generates a standard XML file that looks like listing 6-27.

Listing 6-27WriteXml method output


<?
xml version="1.0" ?>
<
DS xmlns="StdNamespace">
  <
Student>
    <
Name>Mahesh Chand</Name>
    <
Address>Meadowlake Dr, Dtown</Address>
  </
Student>
  <
Student>
    <
Name>Mike Gold</Name>
    <
Address>NewYork</Address>
  </
Student>
  <
Student>
    <
Name>Mike Gold</Name>
    <
Address>New York</Address>
  </
Student>
</
DS>

The Write xml schema method

This method writes DataSet structure to an XML schema. WriteXmlSchema has four overloaded methods. You can write the data to a stream, text, TextWriter, or Xmlwriter. Listing 6-28 uses XmlWriter for the output.

Listing 6-28. write xml schema sample


using
 System;
using
 System.IO;
using
 System.Xml;
using
 System.Data;

namespace
 XmlAndDataSetsampB2
{
    class 
XmlAndDataSetSampCls
    {
        public static void Main()
        {
             DataSet ds = new DataSet("DS");
            ds.Namespace = "StdNamespace";
            DataTable stdTable = new DataTable("Students");
            DataColumn col1 = new DataColumn("Name");
            DataColumn col2 = new DataColumn("Address");
            stdTable.Columns.Add(col1);
            stdTable.Columns.Add(col2);
            ds.Tables.Add(stdTable);

            
// Add student Data to the table
            DataRow newRow; newRow = stdTable.NewRow();
            newRow["Name"] = "Mahesh chand";
            newRow["Address"] = "Meadowlake Dr, Dtown";
            stdTable.Rows.Add(newRow);
            newRow = stdTable.NewRow();
            newRow["Name"] = "Mike Gold";
            newRow["Address"] = "NewYork";
            stdTable.Rows.Add(newRow);
            ds.AcceptChanges();
            XmlTextWriter writer = new XmlTextWriter(Console.Out);
            ds.WriteXmlSchema(writer);
            Console.ReadLine();
            Console.ReadLine();
            return;
        }
    }
}


Output of above listing

outputListing6.28.gif

XmlData Document and XML

As discussed earlier in this article, the XmlDocument class provides DOM tree structure of XML documents. The XmlDataDocument class comes from XmlDocument, which is comes from XmlNode.

Figure 6-10 shows the XmlDataDocument hierarchy.

Figure-6.10.gif

Figure 6-10. Xml Data Document hierarchy

Besides overriding the methods of XmlNode and XmlDocument, XmlDataDocument also implements its own methods. The XmlDataDocument class lets you lead relational data using the DataSet object as well as XML documents using the Load and LoadXml methods. As figure 6-11 indicates, you can use a DataSet to load relational data to an XmlDataDocument object and use the Load or LoadXml methods to read an XML document. Figure 6-11 shows a relationship between a Reader, Writer, DataSet, and XmlDataDocument.

Figure-6.11.gif

Figure 6-11. Reading and writing data using xml Data Document

The XmlDataDocument class extends the functionality of XmlDocument and synchronizes it with DataSet. As you know a DataSet is a powerful object in ADO.NET. As figure 6-11 shows, you can take data from two different sources. First, you can load data from an XML document with the help of XmlReader, and second, you can load data from relational data sources with the help of database provides and DataSet. The neat thing is the data synchronization between these two objects. That means if you update data in a DataSet object, you see results in the XmlDataDocument object and vice versa. For example, if you add a record to a DataSet object, the action will add one node to the XmlDataDocument object representing the newly added record.

Once the data is loaded, you're allowed to use any operations that you were able to use on XmlDocument objects. You can also use XmlReader and XmlWriter objects to read and write the data. 

The xmlData Documet class has property called DataSet. It returns the attached DataSet object with XmlDataDocument. The DataSet property provides you a relational representation of an XML document. Once you've a DataSet object, you can do anything with it such as attaching to a DataGrid. 

You Can use all XML read and write methods of the DataSet object through the DataSet property such as ReadXml, ReadXmlSchema, WriteXml, and WriteXml schema. Refer to the DataSet read write methods in the previous section to see how these methods are used.

Loading Data using Load and LoadXml from the XmlDataDocument

You can use either the Load method or the LoadXml method to load an XML document. The Load method takes a parameter of a filename string, a TextReader, or an XmlReader. Similarly, you can use the LoadXml method. This method passes an XML file name to load the XML file for example:


            XmlDataDocument doc = new XmlDataDocument();
            doc.Load("c:\\Books.xml");


Or you can load an XML fragment, as in the following example:


            XmlDataDocument doc = new XmlDataDocument();
            doc.LoadsXml("<Record> write something </Record>");


Loading Data Using a DataSet

A DataSet object has methods to read XML documents. These methods are ReadXmlSchema and LoadXml. You use the Load or LoadXml methods to load an XML document the same way you did directly from the XMLDataDocument. Again the Load method takes a parameter of a filename string, TextReader, or XmlReader. Similarly, use the LoadXml method to pass an XML filename through the dataset. For example:


          XmlDataDocument doc = new XmlDataDocument();
            doc.DataSet.ReadXmlSchema("test. Xsd");

Or 

           doc.DataSet.ReadXml("<Record> write something </Record>");


Displaying XML Data In a data Set Format

As mentioned previously, you can get DataSet object from an XmlDataDocument object by using its DataSet property. OK, now it's time to see how to do that. The next sample will show you how easy is to display an XML document data in a DataSet format.

To read XML document in a dataset, first you read to document. You can read a document using the ReadXml method of the DataSet object. The DataSet property of XmlDataDocument represents the dataset of XmlDataDocument. After reading a document in a dataset, you can create data views from the dataset, or you can also use a DataSet'sDefaultViewManager property to bind to data-bound controls, as you can see in the following code:


            XmlDataDocument xmlDatadoc = new XmlDataDocument();
            xmlDatadoc.DataSet.ReadXml("c:\\ xmlDataDoc.xml");
            dataGrid1.DataSource = xmlDatadoc.DataSet.DefaultViewManager;


Listing 6-29 shows the complete code. As you can see from Listing 6-29, I created a new dataset, Books, fill from the books.xml and bind to a DataGrid control using its DataSource property. To make Listing 6-29 work, you need to create a Windows application and drag a DataGrid control to the form. After doing that, you need to write the Listing 6-29 code on the Form1 constructor or Form load event.

Listing 6-29. XmlDataDocumentSample.cs


public
 Form1( )
                   {
            
// Initialize Component and other code here
            
// Create an XmlDataDocument object and read an XML
            XmlDataDocument xmlDatadoc = new XmlDataDocument();
            xmlDatadoc.DataSet.ReadXml("C:\\books.xml");
            
// Create a DataSet object and fill with the dataset
            
// of XmlDataDocument
            DataSet ds = new DataSet("Books DataSet");
            ds = xmlDatadoc.DataSet;
            
// Attach dataset view to the Data Grid control
            dataGrid1.DataSource = ds.DefaultViewManager;

                   }


The output of this program looks like figure 6-12. Only a few lines code, and you're all set. Neat huh?

Figure-6.12.gif

Figure 6-12. XmlDataDocumentSample.cs output

Saving Data from a DataSet to XML 

You can save a DataSet data as an XML document using the Save method of XmlDataDocument. Actually, XmlDataDocument comes from XmlDocument., and the XmlDocument class defines the Save method. I've already discussed that you can use Save method to save your data in a string, stream, TextWriter, and XmlWriter. 

First, you create a DataSet object and fill it using a DataAdapter. The following example reads the Customers table from the Northwind Access database and fills data from the read to the DataSet:


            string SQLStmt = "SELECT * FROM Customers";
            string ConnectionString =
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C: \\ Northwind.mdb";

            
// Create data adapter
            OleDbDataAdapter da = new OleDbDataAdapter(SQLStmt, ConnectionString);

            
// create a new dataset object and fill using data adapter's fill method
            DataSet ds = new DataSet();

            da.Fill(ds);


Now, you create an instance of XmlDataDocument with the DataSet as an argument and call the Save method to save the data as an XML document:


            XmlDataDocument doc = new XmlDataDocument(ds);
            doc.Save("C:\\XmlDataDoc.xml");


Listing 6-30 shows a complete program listing. You create an XmlDataDocument object with dataset and call the save method to save the dataset data in an XML file.

Listing 6-30. Saving the dataset data to an XML document


using
 System;
using
 System.Data;
using
 System.Data.OleDb;
using
 System.Xml;

namespace
 DataDocsampB2
{
          class Class1
          {
                   static void Main(string[] args)
                   {
// create SQL Query
                   string SQLStmt = "SELECT * FROM Customers";
                   // Connection string
                   string ConnectionString =
                   "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = C:\\ Northwind.mdb";
                   // Create data adapter
                   OleDbDataAdapter da = new OleDbDataAdapter(SQLStmt, ConnectionString);
                   // create a new dataset object and fill using data adapter's fill method
                   DataSet doc = new DataSet();
                   // Now use SxlDataDocument's Save method to save data as an XML file XmlDataDocument doc = new XmlDataDocument(ds);
                   doc.Save("C:\\XmlDataDoc.xml");
                   }
          }
}


XmlDataDocument: Under the Hood

After Looking at Listing 6-29, which illustrated the reading an XML document in a DataGrid control, you must be wondering how it happened? It's all the magic of the DataSet object. The DataSet object handles everthing for under the hood:


doc.DataSet.ReadXml("C:\\outdata.xml");


As you see in this first line calling DataSet.ReadXml method to read an XML document. The DataSet extracts the document and defines tables and columns for you.

Generally, the root node of the XML document becomes a table; the document's Name, Namespace, NamespaceURI, and prefix of the XML document become the dataset's Name, Namespace, NamespaceURI, and Prefix respectively. If an element's children have one or more children, they become another table inside the main table in a nested format. Anything left from the tables becomes columns of the table. The value of node is added as a row in a table. DataSet takes care of all of this under the hood.

Conclusion

Hope this article would have helped you in understanding ADO .NET and XML. See other articles on the website also for further reference.

adobook.jpg This essential guide to Microsoft's ADO.NET overviews C#, then leads you toward deeper understanding of ADO.NET.


Up Next
    Ebook Download
    View all
    Learn
    View all