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
Dim ds As 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:
Dim ds As 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
Dim ds As New DataSet("New DataSet")
' Read xsl in an XmlTextReader
Dim myXmlReader As 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-ado-net-and-xml. Write xml Method
Imports System
Imports System.IO
Imports System.Xml
Imports System.Data
Namespace XmlAndDataSetsampB2
Class XmlAndDataSetSampCls
Public Shared Sub Main()
Try
' Create a DataSet, namespace and Student table
' with Name and Address columns
Dim ds As New DataSet("DS")
ds.[Namespace] = "StdNamespace"
Dim stdTable As New DataTable("Student")
Dim col1 As New DataColumn("Name")
Dim col2 As New DataColumn("Address")
stdTable.Columns.Add(col1)
stdTable.Columns.Add(col2)
ds.Tables.Add(stdTable)
'Add student Data to the table
Dim newRow As DataRow
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
Dim myStreamWriter As New System.IO.StreamWriter("c:\stdData.xml")
' Writer data to DataSet which actually creates the file
ds.WriteXml(myStreamWriter)
myStreamWriter.Close()
Catch e As Exception
Console.WriteLine("Exception: {0}", e.ToString())
End Try
Exit Sub
End Sub
End Class
End Namespace
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
Imports System
Imports System.IO
Imports System.Xml
Imports System.Data
Namespace XmlAndDataSetsampB2
Class XmlAndDataSetSampCls
Public Shared Sub Main()
Dim ds As New DataSet("DS")
ds.[Namespace] = "StdNamespace"
Dim stdTable As New DataTable("Students")
Dim col1 As New DataColumn("Name")
Dim col2 As New DataColumn("Address")
stdTable.Columns.Add(col1)
stdTable.Columns.Add(col2)
ds.Tables.Add(stdTable)
' Add student Data to the table
Dim newRow As DataRow
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()
Dim writer As New XmlTextWriter(Console.Out)
ds.WriteXmlSchema(writer)
Console.ReadLine()
Console.ReadLine()
Exit Sub
End Sub
End Class
End Namespace
Output of above listing
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. 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. 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:
Dim doc As New XmlDataDocument()
doc.Load("c:\Books.xml")
Or you can load an XML fragment, as in the following example:
Dim doc As 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:
Dim doc As 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:
Dim xmlDatadoc As 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 Sub New()
' Initialize Component and other code here
' Create an XmlDataDocument object and read an XML
Dim xmlDatadoc As New XmlDataDocument()
xmlDatadoc.DataSet.ReadXml("C:\books.xml")
' Create a DataSet object and fill with the dataset
' of XmlDataDocument
Dim ds As New DataSet("Books DataSet")
ds = xmlDatadoc.DataSet
' Attach dataset view to the Data Grid control
dataGrid1.DataSource = ds.DefaultViewManager
End Sub
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. 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:
Dim SQLStmt As String = "SELECT * FROM Customers"
Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C: \ Northwind.mdb"
' Create data adapter
Dim da As New OleDbDataAdapter(SQLStmt, ConnectionString)
' create a new dataset object and fill using data adapter's fill method
Dim ds As 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:
Dim doc As 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
Imports System.Data
Imports System.Data.OleDb
Imports System.Xml
Namespace DataDocsampB2
Class Class1
Private Shared Sub Main(ByVal args As String())
' create SQL Query
Dim SQLStmt As String = "SELECT * FROM Customers"
' Connection string
Dim ConnectionString As String = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = C:\ Northwind.mdb"
' Create data adapter
Dim da As New OleDbDataAdapter(SQLStmt, ConnectionString)
' create a new dataset object and fill using data adapter's fill method
Dim doc As 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")
End Sub
End Class
End Namespace
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.