DataTableReader is a very useful ADO.NET class that can be used to retrieve / pass the data between layers in readonly and forward only format. In this article, I shall discuss about the DataTableReader class. Its very useful but perhaps rarely used in everyday programming even if it is best suitable in passing data between layers in place of DataTable.
Introduction
DataTableReader is a very useful class in ADO.NET that can be used as a placeholder for ReadOnly and ForwardOnly one or more result sets. Though you may say that this can be done using DataReader (SqlDataReader or OledbDataReader) but the major problems with the DataReader is that they are always connected to the database unless you close the reader. This means irrespective of you are using the DataReader as placeholder, you will have to keep your database connection alive if you want to use it, keeping database connection alive is a costly affair.
Video of this article
You can watch the demo and video of this article here.
Benefits of DataTableReader
DataTableReader comes with the all the benefits of DataReader and on top of that it works in the disconnected mode so you do not need to keep your database connection alive. You can very well close the connection and work with your DataTableReader.
How to Create DataTableReader
DataTableReader maintains the same structure of the DataTable from which it was created. The DataTableReader can be created using DataTable, DataSet or by instantiating the new DataTableReader class and specifiying the DataTable or array of the DataTables as constructor parameter.
Creating DataTableReader using DataTable
DataTable table = new DataTable();
using (DataTableReader dReader = table.CreateDataReader())
{
// work with your DataTableReader here
}
Creating DataTableReader using DataSet
DataSet dSet1 = new DataSet();
using (DataTableReader reader = dSet1.CreateDataReader())
{
// work with your DataTableReader here
}
Notice that in case your DataSet contains multiple DataTables, DataTableReader will have multiple result set in the same order as it was in your DataSet.
Get solutions of .NET problems with video explanations, .pdf and source code in .NET How to's.
Creating DataTableReader using its Constructor
DataTable table = new DataTable();
DataTableReader r = new DataTableReader(table);
How DataTableReader works?
Lets see the complete code of the sample I have attached with this article and try to understand how it works. For this example, I have taken a simple database table wtih AutoId (autoincrement), Name, Adddress, Phone, City as fields of the SampleForTutorials table.
<body>
<h2>Working with DataTableReader class</h2>
<form id="form1" runat="server">
<asp:Label ID="lblError" runat="server" EnableViewState="false" ForeColor="Red" />
<div>
<asp:GridView ID="GridView1" runat="server" EnableViewState="false" EmptyDataText="No record found." />
</div>
<h3>Records from the database</h3>
<asp:Label ID="lblData" runat="server" EnableViewState="false" />
</form>
</body>
.aspx page
/// <summary>
/// Binds my grid.
/// </summary>
private void BindMyGrid()
{
// sql for paging. In production write this in the Stored Procedure
string sql = "SELECT * FROM SampleForTutorials ORDER BY AutoId DESC;" +
"SELECT * FROM SampleForTutorials WHERE [Name] like 'Sheo%' ORDER BY AutoId DESC";
// DataTable table = new DataTable();
DataSet dSet = new DataSet();
try
{
// get the from database first
using (SqlConnection conn = new SqlConnection(_connStr))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.CommandType = CommandType.Text;
conn.Open();
using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
{
// For single select statement you can use DataTable
// ad.Fill(table);
// as I am expecting multiple result set (because of mutilple select statement) so I use DataSet
ad.Fill(dSet);
}
conn.Close();
}
}
// Now form the DataTableReader, this data reader is the disconnected reader and forward only
using (DataTableReader reader = dSet.CreateDataReader())
{
// Lets bind the 1st result set with the GridView
GridView1.DataSource = reader;
GridView1.DataBind();
// As the 1st result set is already bounded to the GridView, the next loop will return no records (DataTableReader is forward only)
while (reader.Read())
{
// loop through the DataReader
lblData.Text += "AutoID: " + reader.GetValue(reader.GetOrdinal("AutoID")) + ", Name: " + reader["Name"] + ", Address: " + reader["Address"] + "<br />";
}
// to manually move to the NextResult, call reader.NextResult() method.
reader.NextResult();
lblData.Text += "<h4>Next resultset records</h4>";
while (reader.Read())
{
// loop through the DataReader
lblData.Text += "AutoID: " + reader.GetValue(reader.GetOrdinal("AutoID")) + ", Name: " + reader["Name"] + ", Address: " + reader["Address"] + "<br />";
}
}
}
catch (Exception ee)
{
lblError.Text = ee.ToString();
}
finally
{
dSet.Dispose();
}
}
Code behind
In the above method, I have speicified two Sql satements in the sql variable as I wanted to return two result sets in the DataSet. Then I used SqlConnection, SqlCommand and SqlDataAdapter objects to fetch the data from database into DataSet (This is a normal procedure we follow to retrive the data from database).
Once I have the DataSet filled with result sets (DataTables), I have called the CreateDataReader() method of the DataSet to get the DataTableReader out of DataSet. This DataTableReader will have two restul sets as my DataSet has two DataTables.
I have bounded my GridView using the first result set by specifying the DataSource as reader to the GridView. Next I have specified a while loop withreader.Read() method. As the DataTableReader is the forward only result sets and my 1st result set is already bounded to the GridView so this while statement will not even generate a single line on the page.
After the first while loop, I have called reader.NextResult() method that will move the reader focus to the next result set of the DataTableReader and when the while loop will be called, it will loop through the records of the second result set and write the details into lblData (Label) as you can see the in the below picture.
How to retrieve the data from DataTableReader?
If you have already worked with DataReader, you will not find any difference working with DataTaleReader as it is same. You can retrive the reader columns data using following ways
string name = reader["Name"].ToString(); // using name of the column
string name1 = reader[1].ToString(); // using column position
string name2 = reader.GetString(1); // using GetString() method
string name3 = reader.GetValue(1).ToString(); // using GetValue() method
string name4 = reader.GetString(reader.GetOrdinal("Name")); // getting string using GetString() and GetOrdinal() method
int autoId = reader.GetInt32(reader.GetOrdinal("AutoId")); // getting integer using GetString() and GetOrdinal() method
Despite the fact the last 2 statements (name4 and autoId) might be looking ugly and more complicated than others, they are better. DataTableReader object has all corresponding methods to retrive the data of different data types either in the form of string, integer, double, datatime, char, boolean etc. and you should use them to retrieve the column values as they are considered to be good in terms of performance.
Output : DataTableReader output based on above code snippets
What about the peformance?
Performance of the DataTableReader is much much better than the DataTable, it is almost similar to the DataReader (SqlDataReader or OledbDataReader) as it works in the same way as the DataReader works. DataTableReader has almost the same methods and properties exposed as the DataReader has and using them is similar to DataReader. So next time when you need to pass the forwardonly readonly resultsets between different layers and you want to use DataTable, give a shot to DataTableReader and you will be happier.
Constraints / Limitations of DataTableReader
There are certain limitations of using DataTableReader because of the way it has been designed. You should not use DataTableReader in following scenarios
- When you need to specify the DataSource of multiple data controls - As DataTableReader is forward only so as soon as you specified it as source of one data controls, its done; even if you specify the same reader to another data controls it will not work.
- You are intending to close the DataTableReader before calling the DataBind() mehtod of the data source control - If you ever try to do that you will get error as even if your reader object exists, you have closed the reader and it will not be able to navigate through different records.
In scenario like above, you can use either DataTable or Generic Collection objects.
Conclusion
In this article, I described the less talked about but very good ADO.NET object DataTableReader that can be used to store forwardonly, readeonly result sets that is light weight and much much better than DataTable in terms of performance and usability.
I hope you liked this article. Thank you for reading, you can rss subscribe to different sections to get any new posting directly to your inbox..