ADO .NET and XML

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 ChandName> <Address>Meadowlake Dr, DtownAddress> Student> <Student> <Name>Mike GoldName> <Address>NewYorkAddress> Student> <Student> <Name>Mike GoldName> <Address>New YorkAddress> 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 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: 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(" write something "); 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(" write something "); 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. 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. 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.