DataSet, DataTable, DataRow

DataSet and DataTable are the key components in ADO.NET programming. In simple words, DataSet represents an in memory representation of the database. We can load an entire database into a DataSet and manipulate the data in memory. If you aremore familiar with DataSet, you can Add, Edit and Update data in the dataset and then just call a single method 'AcceptChanges()' whichwill save all the changes back to the database.

A DataSet contains one or more DataTables

A DataTable contains DataRows.

What is DataSet ?

A DataSet is an in memory representation of data loaded from any data source. Even though the most common data sourceis database, we can use DataSet to load data from other data sources including XML files etc. In this article, we will talk about the role of DataSet in manipulating data from database.

In .NET, a DataSet is a class provided by the .NET Framework. The DataSet class exposes several proeprties and methods that can be used to retrieve, manipulate and save data from various data sources.

Just like any other classes in object oriented programming, we have to create an instance of DataSet class to work with data. Typically, we may create a new instance of a DataSet and use other classes provided by .NET Framework to populate the DataSet. See the following example:

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Samples\\Employee.mdb";

OleDbConnection myConnection = new OleDbConnection( connectionString );

string query = "select * from EMPLOYEE_TABLE";

OleDbDataAdapter myAdapter = new OleDbDataAdapter( query, myConnection );

DataSet employeeData = new DataSet();
myAdapter.Fill ( employeeData );

Here we are creating a OleDbConnection object and we are just passing the object to the OleDbDataAdapterobject. Also, we pass the 'select ...' query to the OleDbDataAdapter. Next, we call the '.Fill()' method of the OleDbDataAdapter. This step will populate the dataset ( called 'employeeData' ) with the data retrieved for the sql statement 'select * from EMPLOYEE'.

As you already know, a DataSet can contain a collection of tables. But in the above case, our sql statement will retrieve data from only one table. So, our DataSet will have only one table.

Commonly used properties and methods of DataSet

Property : Tables

The Tables propertly allows us to retrieve the tables contained in the DataSet. This property returns a DataTableCollection object. The following sample code demonstrates iterating through the collection of tables in a data set and print the name of all the tables.

DataSet employeeData = new DataSet();
myAdapter.Fill( employeeData );

// Repeat for each table in the DataSet collection.
foreach ( DataTable table in employeeData.Tables )
{
MessageBox.Show ( table.TableName );
}

Or, you can use the indexer to access any specific table in the collection.

DataSet employeeData = new DataSet();
myAdapter.Fill( employeeData );

// Repeat for each table in the DataSet collection.
for ( int i = 0; i < employeeData.Tables.Count; i++ )
{
DataTable table = employeeData.Tables[i];
MessageBox.Show ( table.TableName );
}

Method : GetXml()

The GetXml() method returns the XML representation of the data from the DataSet.

DataSet employeeData = new DataSet();
myAdapter.Fill( employeeData );

string xmlData = employeeData.GetXml();

Method : WriteXml(...)

The WriteXml() method allows to save XML representation of the data from the DataSet to an XML file. There are many overloaded method available, which takes various parameters. The example shown below takes a file name as parameter and saves the data in DataSet into xml format to the file name specified as parameter. We can optionally save only the data or both data and schema.

DataSet employeeData = new DataSet();
myAdapter.Fill( employeeData );

employeeData.WriteXml( "c:\\MyData.xml" );

Method : ReadXml(...)

The ReadXml() method allows to load the DataSet from an XML representation of the data. There are many overloaded method available, which takes various parameters. The example shown below takes a file name as parameter and loads the data from XML file into the DataSet. This method can be used to load either the data only or both data and schema from the XML.

DataSet employeeData = new DataSet();
employeeData.ReadXml( "c:\\MyData.xml" );

There is another method called 'ReadXmlSchema()', which can be used to load only the schema from a file.

The methods WriteXml() and ReadXml() are useful to save the data from a database into some temporary files, transport to other places or keep it as a local file and load later. Many applications, including the SpiderAlerts tool available for download from this site, uses DataSet to manipulate data and saves/retrieves them from local disk using the WriteXml() and ReadXml() methods.

The SpiderAlerts tool communicates with webservices in our site and retrieves the alerts in the form of a DataSet. Once the Alerts are retrieved, it is saved into local computer using the WriteXml method. (This implementation may be changed soon in the future versions of this tool. We are considering saving(serializing) the DataSet into Isolated Storage (IsolatedStorage is a new feature part of the .NET Framework - it is a kind of hidden file system)

A DataTable is a class in .NET Framework and in simple words a DataTable object represents a table from a database.

DataSet and DataTable are the key components in ADO.NET programming. While DataSet can be used to represent a database as a whole, a DataTable object can be used to represent a table in the Database/DataSet. A DataSet can contain several DataTables.

In typical database oriented applications, DataSet and DataTable are used a lot to manipulate data. DataAdapter or other classes can be used to populate a DataSet. Once a DataSet is populated, we can access the DataTables contained within the DataSet.

Just like any database table contains multiple rows (records), a DataTable can contain multiple DataRows. Each row contains multiple fields representing each column in the table.

The typical process to retrieve records from a database in ADO.NET includes the following steps:

Open a connection to database

Use a data adapter to fill a DataSet.

Access the DataTable contained in the DataSet

Access the DataRows contained in the DataTable.

The following sample code explains these steps. This sample code retrieves data from an MS Access database.

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Samples\\Employee.mdb";
OleDbConnection myConnection = new OleDbConnection( connectionString );

string query = "select * from EMPLOYEE_TABLE";

OleDbDataAdapter myAdapter = new OleDbDataAdapter( query, myConnection );
DataSet employeeData = new DataSet();
myAdapter.Fill( employeeData );

// Repeat for each table in the DataSet collection.
foreach ( DataTable table in employeeData.Tables )
{
 // Repeat for each row in the table.
 foreach ( DataRow row in table.Rows )
 {
  MessageBox.Show( "Employee Number : " + row["EmployeeNumber"].ToString() );
MessageBox.Show( "Name : " + row["Name"].ToString() );
MessageBox.Show( "Address : " + row["Address"].ToString() );
 }
}

How to create a DataTable

In most of the cases, we just access the DataTable in a DataSet. We do not need to create a new instance of the DataTable. When a DataSet is populated from database, the DataTable is created with proper schema and data.

If we explicitely create DataTable, we have to create the proper schema. It is bit confusing if you are not very familiar with the database structure and schema.

Create, Read, Update, Delete - ADO.NET sample

This chapter demonstrates the basic database operations using the ADO.NET classes. The sample code in this chapter uses the OleDb Provider.

Here is some sample code to execute a simple query.

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Samples\\Employee.mdb";

OleDbConnection myConnection = new OleDbConnection( connectionString );
myConnection.Open();

string query = "insert into EMPLOYEE_TABLE (EmployeeID, Name, Address) VALUES (101, 'John', '3960 CliffValley Way')";

OleDbCommand myCommand = new OleDbCommand();
myCommand.CommandText = query;
myCommand.Connection = myConnection;
myCommand.ExecuteNonQuery();

myConnection.Close();

Let us analyze the code. First we have declared a connection string. The connection string points to an MS Access database. Before you execute this code, make sure you have the database in the path specified. Or, change the path accordingly.

In the next step, we are creating a OleDbConnectionobject and passing the connection string to this object. The line 'myConnection.Open();' will open a connection to the MS Access database specified in the connection string. If the database doesn't exists or if it is not able to open a connection for some other reason, the '.Open' call will fail.

Next step is, creating a OleDbCommand object. This command object is used to execute sql statements and uses the connection opened by the OleDbConnection object.

Note that before executing a command, we have to establish a valid connection to the database.

And finally, after we have executed with the command, we will close the connection.

The above sample code executes a sql statement and returns no data from database. We are calling the method 'ExecuteNonQuery()' on the command object. If we have a 'select ...' statement which returns data from database, we cannot use the 'ExecuteNonQuery()' method.

The following sample demonstrates using OleDbDataAdapterObject and DataSet to retrieve data from databbase. 

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Samples\\Employee.mdb";

OleDbConnection myConnection = new OleDbConnection( connectionString );

string query = "select * from EMPLOYEE_TABLE";

OleDbDataAdapter myAdapter = new OleDbDataAdapter( query, myConnection );
DataSet employeeData = new DataSet();
myAdapter.Fill ( employeeData );

Here we are creating a OleDbConnection object and we are just passing the object to the OleDbDataAdapterobject. Also, we pass the 'select ...' query to the OleDbDataAdapter. Next, we call the '.Fill()' method of the OleDbDataAdapter. This step will populate the dataset ( called 'employeeData' ) with the data retrieved for the sql statement 'select * from EMPLOYEE'.

As you already know, a DataSet can contain a collection of tables. But in our case, our sql statement will retrieve data from only one table. So, our DataSet will have only one table.

We can iterate through the table in the dataset and retrieve all the records. See the following code demonstrating this:

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Samples\\Employee.mdb";
OleDbConnection myConnection = new OleDbConnection( connectionString );

string query = "select * from EMPLOYEE_TABLE";

OleDbDataAdapter myAdapter = new OleDbDataAdapter( query, myConnection );
DataSet employeeData = new DataSet();
myAdapter.Fill( employeeData );

// Repeat for each table in the DataSet collection.
foreach ( DataTable table in employeeData.Tables )
{
// Repeat for each row in the table.
foreach ( DataRow row in table.Rows )
{
MessageBox.Show( "Employee Number : " + row["EmployeeNumber"].ToString() );
MessageBox.Show( "Name : " + row["Name"].ToString() );
MessageBox.Show( "Address : " + row["Address"].ToString() );
}
}

The above code retrieves all the records from the employee table and displays all the fields. You can download a sample project demonstrating the basic database operations.