Creating a DataTableReader with more than one DataTables

One of the nice features of DataTableReader is that it can contain more than one DataTables, as read-only and forward-only recordsets. When you load more than one DataTables in a DataTableReader, it is really faster to iterate and it will automatically deals with the unwanted records during the iteration. You can load bunches of DataTables by creating an object of DataTableReader to contain an array of DataTables. Let us see the sample code to create it.

private void FetchTwoDataTablesInDataTableReader()
{
string sql = "Select * from Customers";
SqlDataAdapter da = new SqlDataAdapter(sql,”YourConnectionString”);
DataTable dtCus = new DataTable();
da.Fill(dtCus);

string sql1 = "Select * from Country";
SqlDataAdapter da1 = new SqlDataAdapter(sql1,”YourConnectionString”);
DataTable dtCountry = new DataTable();
da1.Fill(dtCountry);

DataTableReader dtr = new DataTableReader(new DataTable[] {dtCus, dtCountry});
if (dtr.HasRows)
{
do
{
while (dtr.Read())
{
Response.Write(dtr[1].ToString() + "
");
}
} while (dtr.NextResult());
}
else
Response.Write("No Data");
}

Binding DataTableReader with GridView control

Another great feature of the DataTableReader class is that you can use it as the data source to populate Dataset or DataTable information into an Asp.Net 2.0 GridView control in a very easy way. This is done by using the Load method of the DataTableReader. Let us see the sample code for this.


private void LoadGridViewWithDataTableReader()
{
DataSet ds = new DataSet();

string sql = "Select * from Customers";
SqlDataAdapter da = new SqlDataAdapter(sql,”YourConnectionString”);
DataTable dtCus = new DataTable();
da.Fill(dtCus);
ds.Tables.Add(dtCus);

string sql1 = "Select * from Country";
SqlDataAdapter da1 = new SqlDataAdapter(sql1,”YourConnectionString”);
DataTable dtCountry = new DataTable();
da1.Fill(dtCountry);
ds.Tables.Add(dtCountry);

DataTable dtGrid = new DataTable();
DataTableReader dtr = new DataTableReader(ds.Tables[0]);
dtGrid.Load(dtr);

GridView1.DataSource = dtGrid;
GridView1.DataBind();
}