Chapter 13: More about DataTable and DataRow

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.