XML and ADO.NET

The C# Column - Yashawant Kanetkar

A powerful feature of ADO.NET is its ability to convert the data stored in a data source in XML and vice-versa. In this article we will read records from a data source and display them in a DataGrid control. At the same time we will write the records in an XML file. The contents of the XML file get displayed in a text box where we can modify it. The modified contents get added to the data set and would get displayed in the DataGrid control. We are going to use OLEDB for accessing the database.

Let us first understand a few basic issues in the ADO.NET technology. The System.Data namespace is the root namespace of the ADO.NET API. The primary namespaces of ADO.NET, System.Data.OleDb and System.Data.SqlClient contain classes that enable programs to connect with and modify the datasources. A datasource can be any file containing records such as a ‘.mdb’ file. The namespace OleDb can be used to work with any datasource. On the other hand the SqlClient namespace is used for working with MS SQL Server 2000 datasources.

ADO.NET has two major components—DataSet and .NET Data Providers. A .NET Data Provider is used for connecting to a database, executing commands, and retrieving results.

DataSet is used to cache the data read from the datasource in local memory. The data stored in the memory can be modified and written back to the datasource. The System.Data.DataSet class is provided to take care of the DataSets.

Let us move on and write a program using ADO.NET. Create a Windows application and design the form as shown in the following figure.

The dark grey coloured control is the DataGrid control. The controls, their names and handlers are given in the following table.

First of all, we will look at the Click event handler for the ‘Write’ button.

private void wb_Click ( object sender, System.EventArgs e )
{

String constr = @”Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\book.mdb”;
OleDbConnection con = new OleDbConnection ( constr );
con.Open( );
String comstr = “SELECT Name, Email, Phone FROM addressbook”;
OleDbCommand com = new OleDbCommand ( comstr, con );
OleDbDataAdapter adapt = new OleDbDataAdapter ( com );
adapt.Fill ( dset, “addressbook” );
dg.SetDataBinding ( dset, “addressbook” );
dset.WriteXml ( “addressbook.xml” );
xmltext.Text = dset.GetXml( );
con.Close( );

}

Interacting with a database using ADO.NET involves connection and command objects. Since we have used OLEDB to access the database, the connection must be established between the database and OLEDB .NET Provider. The class OleDbConnection is used for this job. So, in this method we have firstly created an object of this class passing to it the connection string.

The connection string contains the name of the OLEDB .NET Provider and that of the datasource. Our datasource is ‘book.mdb’ that maintains an address book. This file is already created using Microsoft Access. It contains details like names, e-mail IDs and phone numbers.

Only creating an object of connection class does not physically open a connection to the database. Calling the Open( ) method does. So we have called the Open() method in the next statement.

The Command object allows us to execute an SQL statement or a stored procedure in a data source. We have created the command object using the OleDbCommand class and are passing to it the command string. The command string contains the SQL statement to select all the records from the
datasource.

The command object is used to connect the connection object to a DataAdapter object. A DataAdapter is used to fill data from the database into the DataSet object. To read the records into the DataSet we have used the OleDbDataAdapter.Fill( ) method. To this method we have passed the DataSet object dset and the table name. Add the data member dset of type DataSet to the form class. Initialise the data set object in the constructor as

dset = new DataSet( );

Next, we have called the DataGrid. SetDataBinding( ) method to bind the grid control with the datasource. We have passed the DataSet object as the first parameter to specify whose data the grid control should display. The second parameter is the name of the table within the datasource we want to bind to the control. When this function call is executed, the data grid is filled with the records in the DataSet.

Next comes the main part of the program, that is, writing records to an XML file. The DataSet.WriteXml( ) method generates the XML representation of the data contained in the DataSet object and writes it to the specified file. We have displayed the same XML data that we have just written to the file in the text box. For this, we have called the DataSet.GetXml( ) method.

The OleDbConnection.Close( ) method closes the connection with the datasource.

A part of the XML file is given below:

<NewDataSet>
<addressbook>
<Name>Meghana</Name>
<Email>Meg2003@hotmail.com</Email>
<Phone>2242567</Phone>
</addressbook>
</NewDataSet>

On clicking the ‘Clear’ button, the grid control should get cleared so that we can display modified contents in it. The code to clear the control is given in the cb_CLick( ) event handler. The handler is given below.

private void cb_Click ( object sender, System.EventArgs e )
{

dset.Clear( ) ;
dg.Update( ) ;

}

Since the grid control is attached to the datasource represented by dset, clearing dset and updating the grid control clears the contents from it.

Now add the following node to the XML data displayed in the text box.

<addressbook>
<Name>Sanjay</Name>
<Email>sanju@hotmail.com</Email>
<Phone>227257</Phone>
</addressbook>

Click the ‘Read’ button. The following handler would get called:

private void rb_Click ( object sender, System.EventArgs e )
{

StreamWriter sw = new StreamWriter ( “addressbook.xml”, false );
sw.Write ( xmltext.Text );
sw.Close( );
dset.ReadXml ( “addressbook.xml” );
dg.SetDataBinding ( dset, “addressbook” );

}

Here, we have used the StreamWriter class to write the contents from textbox to the XML file. To read new contents of the file and fill the DataSet with it we have used the ReadXml( ) method. Again to fill the grid control we have called the SetDataBinding( ) method. The result is shown in the following figure.

Note that we must add the following declarations at the beginning of the program.

using System.Data.OleDb;
using System.IO;

Yashavant Kanetkar, one of the first Express Computer columnists, is an established software expert, speaker and author with several best-sellers to his credit, including titles like “Let Us C” and the “Fundas” series. Contact him at kanet@nagpur.dot.net.in