Trek Innovations

Connecting Data & XML via ADO.NET

In this article you can see how to work with XML documents with the help of ADO.NET. In this there are two approaches to work with XML and ADO.

1. You can use ADO.NET to access XML documents.

2. You can use ADO.NET and XML to access XML. Apart from this, you can access RDBMS using ADO.NET and XML.NET.

First we will see how to read XML using a Dataset

Reading XML using a Dataset

Normally in ADO.NET, we can access the data using the Dataset class. The Dataset class implements methods and properties to work with XML documents.

In this article first we discuss the methods that read XML data.

ReadXml Method

ReadXml is used to read a data stream or an XML file and to store into a Dataset object, later which can be used to display the data in a tabular format. ReadXmlmethod has eight overloaded forms. ReadXML can read a text, string, stream, TextReader, XmlReader, and combination formats. In the following example, first we create a new DataSet object as follows:

DataSet ds = new DataSet();

After creating a dataset object we will call the ReadXML method to read the xml file into the dataset as follows

// Fill with the data

ds.ReadXml(“books.xml “);

We should remember one thing when we are working with Datasets we have to add some references like some namespaces

System.Data

System.Data.Common

Now we are going to see how the ReadXmlSchema method works

ReadXmlSchema method

Basically ReadXMLSchema method reads an XML schema in a DataSet object. You can use a string, stream,Text Reader and XmlReader.

In the below example we will see how to read a file using ReadXmlSchema.

First create an object for the dataset

DataSet ds = new DataSet( );

Then using the ReadSchema method read the XML file like this

ds.ReadSchema (@”c:\books. xml”);

See the below example which uses XMLTextReader as input of ReadXmlSchema

DataSet ds = new DataSet(“New DataSet”);

// Now we are going to read xml using XmlTextReader as follows

XmlTextReader objXmlReader = new XmlTextReader(@”c:\books.Xml”);

//Now Call Read xml schema method which takes the parameter as XmlTextReader

ds.ReadXmlSchema(objXmlReader);

//Now close the XmlTextReader

objXmlReader.Close();

Writing XML using a Dataset

Dataset class not only contains methods to read the data but still it has got methods to write XML file from dataset in to the file also. Now we will see writeXml method.

WriteXml Method

WriteXml method is used to write the data from Datasetto an XML file. By using WriteXml method, you can write data to a file, stream, TextWriter, or XmlWriter. In this example let’s creates a Dataset and fills the corresponding data into the Dataset, and then writes the data into an XML file. Below is the example for WriteXml Method.

Using System;

Using System.IO;

Using System.Xml;

Using System.Data;

Namespace XmlDataset

{

Class XmlDataset

{

Public static void Main ()

{

DataSet ds = new DataSet(“DataSet”);

ds.Namespace = “EmpNamespace”;

//Now create DataTable and add columns to the datatable

DataTable dt = new DataTable(“Employee”);

DataColumn col1 = new DataColumn(“Name”);

DataColumn col2 = new DataColumn(“Address”);

dt.Columns.Add(col1);

dt.Columns.Add(col2);

ds.Tables.Add(dt);

//Add Employee Data to the table

//Create a new row and add the data to the corresponding columns

DataRow dr; dr = dt.NewRow();

dr ["Name"] = “Rajesh”;

dr ["Address"] = “Hagley Road”;

dt.Rows.Add(dr);

dr = dt.NewRow();

dr ["Name"] = “Tim Easton”;

dr ["Address"] = “London”;

dt.Rows.Add(dr);

dr = dt.NewRow();

dr ["Name"] = “Sara”;

dr ["Address"] = “Oxford”;

// Now add the datarows to the datatable

dt.Rows.Add(dr);

// After adding the rows to the datatable use AcceptChanges method if there are any changes

ds.AcceptChanges();

//Now order to stored the data in Xml File, we create a new StreamWriter and then save the data in to the Xml file as follows

StreamWriter myStreamWriter = new

StreamWriter(@”c:\xmldata.xml”);

// Now for the dataset use the writeXml method which writes the data from dataset to Xml file, it takes the parameter as streamwriter

// It writes the data in to the xml file

ds.WriteXml(myStreamWriter);

myStreamWriter.Close();

return;

}

}

}

Now in order to see the output of this program, open the xmldata.xml file. You can see the standard XML file as shown below.

Output for WriteXml method

-<DataSet xmlns=” EmpNamespace “>

- <Student>

<Name>Rajesh</Name>

<Address>Hagley Road</Address>

</Student>

- <Student>

<Name>Tim Easton</Name>

<Address>London</Address>

</Student>

- <Student>

<Name>Sara</Name>

<Address>Oxford</Address>

</Student>

</DataSet >

Now we will see the Write Xml Schema method how it works.

Write xml schema method

Write xml schema method writes the dataset structure into an XML schema. Using this method you can write the data to a stream, text, TextWriter, or Xmlwriter.

Below is the example for the write xml schema.

// Create object for the dataset

DataSet ds = new DataSet(“DataSet”);

ds.Namespace = “EmpNamespace”;

//Now create DataTable and add columns to the datatable

DataTable dt = new DataTable(“Employee”);

DataColumn col1 = new DataColumn(“Name”);

DataColumn col2 = new DataColumn(“Address”);

dt.Columns.Add(col1);

dt.Columns.Add(col2);

ds.Tables.Add(dt);

//Add Employee Data to the table

//Create a new row and add the data to the corresponding columns

DataRow dr; dr = dt.NewRow();

dr ["Name"] = “Rajesh”;

dr ["Address"] = “Hagley Road”;

dt.Rows.Add(dr);

dr = dt.NewRow();

dr ["Name"] = “Tim Easton”;

dr ["Address"] = “London”;

dt.Rows.Add(dr);

dr = dt.NewRow();

dr ["Name"] = “Sara”;

dr ["Address"] = “Oxford”;

// Now add the datarows to the datatable

dt.Rows.Add(dr);

// After adding the rows to the datatable use AcceptChanges method if there are any changes

ds.AcceptChanges();

// Now create an object for the xmlTextWriter

XmlTextWriter writer = new XmlTextWriter(Console.Out);

// using the writeXmlSchema method write the data

ds.WriteXmlSchema(writer);

Now we will see about the XmlDataDocumet

What is XmlDataDocument?

XmlDataDocument is a class defined within the System.Xml namespace. Basically it extends the XmlDocument class. The XmlDataDocument class allows the information stored within a DataSet to be treated as XML. Using this class, developers can manipulate data stored within a DataSet as XML or relational data.

Load and LoadXml from the XmlDataDocument

In order to load the data in to xmlDataDocument either you can user Load method or the LoadXmlmethod. The difference between Load and LoadXml is Load method takes the parameter as a filename string or TextReader. But in the case of LoadXml it takes an Xml file. For better explanation see the below example.

// Now create an object for the XmlDataDocument

XmlDataDocument doc = new XmlDataDocument( );

// Now using Load method load the xml file in to the document

doc.Load(“c:\\ Employees.xml”);

Now see how to load xml file using LoadXml

XmlDataDocument doc = new XmlDataDocument( );

doc.LoadsXml (“<Name> Abc </ Name>”);

How to Load Data Using a DataSet

Now we will see how to load data using DataSet. Dataset object has methods to read XML documents. These methods to read Xml documents are ReadXmlSchema and LoadXml.

See the below example how to read xml schema and how to read xml file

First create object for the XmlDataDocument

XmlDataDocument doc = new XmlDataDocument( );

doc.DataSet.ReadXmlSchema (“test. Xsd”);

Or

doc.DataSet.ReadXml (“<Name> Abc </ Name >”);

How to Displaying XML Data In a DataSet Format?

Now you can get DataSet object from an XmlDataDocument object by using its DataSet property. In order to read XML document in a dataset, first of all we have to read data into document. We can read a document using the ReadXml method of the DataSet object. You can see in the below example.

XmlDataDocument xmlDocument = new XmlDataDocument();

xmlDocument.DataSet.ReadXml (“c:\\ document.xml”);

// In order to see the data in the dataset just bind the xmldocument to the gridview by using DefaultViewManager propety in the dataset.

GridView1.DataSource = xmlDocument.DataSet.DefaultViewManager;

If you need the complete code for this you can see below:

Public Data ( )

{

//Now create an XmlDataDocument object and read an XML file

XmlDataDocument xmldoc = new XmlDataDocument();

xmldoc.DataSet.ReadXml(“C:\\books.xml”);

// Create a DataSet object and fill with the dataset

// of XmlDataDocument

DataSet ds = new DataSet(“Books DataSet”);

ds = xmldoc.DataSet;

// Attach dataset view to the GridView control

GridView1.DataSource = ds.DefaultViewManager;

}

Now See how to Save Data from DataSet to XML ?

Now you will see how to save data form dataset to xml. In this you can save data of a DataSet as an XML document using the Save method of XmlDataDocument. Actually the XmlDocument class defines the Save method.

Now create a DataSet object and fill it using a DataAdapter. The below shown example reads the Employees table from the Northwind Oracle database and fills the data into the DataSet.

// write the select query which details you want to retrive from database

string query = “SELECT * FROM Employees”;

// Give the connection string i.e connection to the database

string connString =

“Data Source=NorthWind.mdb; User ID=scott; Password=tiger”;

// Create an object for the oracle data adapter and pass the quey and the connection string to the adapter

OracleDataAdapter objAdapter = new OracleDataAdapter(query, connString);

// Now the adaptor will get the data and whe have to store the data

//So create a new dataset object and fill the dataset by using data adapter’s fill method

DataSet ds = new DataSet();

objAdapter.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:\\XmlData.xml”);

Thats it and happy coding…………………….

1 comment