DataTableReader in ADO.NET 2.0

DataTableReader obtains the contents of one or more DataTable objects in the form of one or more read-only, forward-only result sets. As the name suggests, it is a combination of both DataTable and SqlDataReader. In a DataTable, we can store a single database table records, with all constraints, in a disconnected mode from the database server. And we can perform all sorts of database manipulations in it. A SqlDataReader can contain single database table records, with read-only and forward-only record sets, for which we need an active connection with the database server. And we cannot perform other database manipulations in a SqlDataReader. A DataTableReader can contain more than one DataTable(s), in a disconnected mode, as a read-only and forward-only record sets.

Advantages of using DataTableReader:

SqlDataReader are much faster than DataSet and consume less memory. But the major drawback of using SqlDataReader is that it always required an open connection to operate, that is, it is connection oriented. Hence we needed to explicitly close the database connections when we were done using it.

In ADO.NET 2.0, DataTableReader class has been developed similar to it but with one exception – it works in a disconnected mode. Clearly opening and closing of database server connection is taken care by the DataTableReader itself. The iteration of rows is done from the cache. The cached data can be modified while the DataTableReader is active, and the reader automatically maintains its position.

Creating a simple DataTableReader

DataTableReader can be created from any DataTable’s CreateDataReader method. Let us see the syntax to create a simple DataTableReader and iterate the records in it.

private void FetchDataTableReader()
{
string sql = "Select * from Customers";
SqlDataAdapter da = new SqlDataAdapter(sql,”YourConnectionString”);
DataTable dt = new DataTable(); da.Fill(dt);
DataTableReader dtr = dt.CreateDataReader();
if (dtr.HasRows)
{
while (dtr.Read())
{
Response.Write(dtr[“Cus_Name”].ToString() + "
");
}
}
else
Response.Write("No Data");
}