Introduction
If you have used Microsoft Enterprise Library Data Access Application Block (version 5.0 or previous) before, you are familiar with the ExecuteReader() method.
The Enterprise Library 5.0 documentation describes one of its overloads as:
ExecuteReader(DbCommand): Executes the command and returns an IDataReader through which the result can be read. It is the responsibility
of the caller to close the reader when finished.
This indicates that, first of all, the method returns an IDataReader, an interface, but not a particular type of DataReader, such as a SqlDataReader
which is often used in ADO.NET and SQL Server databases. Secondly, the reader has to be closed if used in an application, either explicitly by calling reader.Close() or implicitly
by placing the reader's code in a using{} block in order to release the database connection that the reader holds.
The use of an IDataReader in an ASP.NET application seems to be straightforward. However, you'll see in this article that some behaviors may not be what
you expect and are not well documented. A demo application has been prepared to illustrate the behaviors (using Visual Studio 2010 and SQL Express 2008R2).
In the demo, data access using the Data Access Application Block is encapsulated in a static class: DataObject in the namespace DataAccessEntLib,
which provides several methods for retrieving data from a SQL Server database as well as for performing data/object conversions. The default.aspx page presents five examples
of using IDataReader. Some work but some don't. Let's take a look at each one of them.
Read Data from an IDataReader
With the Enterprise Library 5.0 Data Access Application Block, the interaction between an application and a SQL Server database becomes relatively simple. There is much less
code to write and the code is easy to read and to maintain. In the DataObject class, an IDataReader is returned using the following code:
public static IDataReader GetIDataReader(string connectionString, string sqlQuery)
{
SqlDatabase db = new SqlDatabase(connectionString);
DbCommand cmd = db.GetSqlStringCommand(sqlQuery);
return db.ExecuteReader(cmd);
}
There are only three lines of code: declare a SqlDatabase, declare a DbCommand, and then call the ExecuteReader() method
to return an IDataReader.
In the first example on the default.aspx page, the application is to directly read data from an IDataReader and display the results on the page.
Here is the code listing that does the work.
using (IDataReader IReader =
DataObject.GetIDataReader(ConnectionString, "select * from Customers"))
{
bool HeaderExists = false;
int NumberOfCols = 5;
while(IReader.Read())
{
if (!HeaderExists) {
TableRow r0=new TableRow();
for (int i = 0; i < NumberOfCols; i++)
{
TableCell c = new TableCell();
c.Controls.Add(new LiteralControl(IReader.GetName(i).ToString()));
r0.Cells.Add(c);
}
tblResults.Rows.Add(r0);
HeaderExists = true;
}
TableRow r = new TableRow(); for (int i = 0; i < NumberOfCols; i++)
{
TableCell c = new TableCell();
c.Controls.Add(new LiteralControl(IReader[i].ToString()));
r.Cells.Add(c);
}
tblResults.Rows.Add(r);
}
}
The application calls the DataObject.GetIDataReader() method to retrieve an IDataReader from the database, and then loops through the reader with
a while statement, and creates rows and cells to display the data through an ASP.NET Table server control. The output is shown in the screenshot below.
It works. As you can see, the application is able to read data from the IDataReader just like reading data from a SqlDataReader. The code is contained
in a using{} block to ensure that the IDataReader is automatically closed when the end of data is reached.
Bind an IDataReader to a GridView
So far so good. We are able to read data from an IDataReader. But can we bind the IDataReader to a GridView server control?
Here is a description and a code sample pulled from the Enterprise Library 5.0 documentation:
From the documentation, it seems that we are able to do it. Let's have a try and see how it goes. In our default.aspx page, the code listed below is used to bind
an IDataReader to a GridView:
using (IDataReader IReader = DataObject.GetIDataReader(ConnectionString,
"select * from Customers"))
{
GridView1.DataSource = IReader;
GridView1.DataBind();
}
The method DataObject.GetIDataReader() is again called to obtain an IDataReader, which is then assigned to GridView1
on the page as its data source. Upon execution, we are not seeing results but an error message:
It does not work. The error indicates that IDataReader is not a valid type to be used as the data source for GridView1.
Obviously, it needs to be converted to a valid type before it is passed onto the GridView. As we already know from our experience, the ADO.NET
SqlDataReader is certainly a valid type since it is frequently used for data binding. Let's convert the IDataReader to a SqlDataReader.
Directly Cast an IDataReader to a SqlDataReader
It is common in .NET Framework that one data type/object can be directly cast to another using the "()" operator with a syntax like:
double a = 1234.5;
int b = (int) a;
The above code casts a double to an int. We'll do a similar operation to cast an IDataReader to a SqlDataReader.
In the DataObject class, a method DataObject.GetSqlDataReader() is provided, in which the first line of code creates an IDataReader
by calling the GetIDataReader() method and the second line returns a SqlDataReader by directly casting the IDataReader
to a SqlDataReader. See the code listing below.
public static SqlDataReader GetSqlDataReader(string connectionString, string sqlQuery)
{
IDataReader IReader = GetIDataReader(connectionString, sqlQuery);
return (SqlDataReader) IReader;
}
To test, we simply assign the SqlDataReader returned to GridView2 on the default.aspx page:
using (SqlDataReader MySqlDataReader=DataObject.GetSqlDataReader(
ConnectionString, "select * from Customers"))
{
GridView2.DataSource = MySqlDataReader;
GridView2.DataBind();
}
We get another error message, different from the previous one:
It does not work. This error message is, in fact, quite confusing because a new type of data reader, RefCountingDataReader, shows up while we think
we are trying to cast an IDataReader to a SqlDataReader. You would wonder where this reader comes from. Based on MSDN documentation,
it turns out that the RefCountingDataReader is the default implementation of the IDataReader interface. Therefore, we need to look into
the RefCountingDataReader to find a way to convert.
As a side note, in Enterprise Library version 3.1, the direct cast from an IDataReader to a SqlDataReader does work. If you are
on one of the previous versions of Enterprise Library and plan to upgrade, it might be a good idea to thoroughly test your application under the Enterprise Library 5.0 environment
before deploying it to a server.
Properly Cast an IDataReader to a SqlDataReader
Online search and documentation study reveal that a RefCountingDataReader, the default implementation of an IDataReader,
has a property called InnerDataReader, "The actual raw IDataReader we're wrapping", as quoted from the Enterprise Library 5.0 documentation.
The InnerDataReader can be cast to a SqlDataReader without any issues. Therefore, we need to first cast the IDataReader
returned from the ExecuteReader() method call to a RefCountingDataReader, and then cast the InnerDataReader
of the RefCountingDataReader to a SqlDataReader. It is a two-step conversion, as presented in the method CastToSqlDataReader(IDataReader)
in the DataObject class.
public static SqlDataReader CastToSqlDataReader(IDataReader iReader)
{
return (SqlDataReader)((RefCountingDataReader)iReader).InnerReader;
}
On the default.aspx page, we again obtain an IDataReader first, and then pass the IDataReader into the DataObject.CastToSqlDataReader()
method to properly convert the IDataReader to a SqlDataReader which is assigned to GridView3 as its data source.
using (IDataReader IReader = DataObject.GetIDataReader(ConnectionString, "select * from Customers"))
{
GridView3.DataSource = DataObject.CastToSqlDataReader(IReader);
GridView3.DataBind();
}
Here is the screenshot for the results:
It works. It should be noted that it is important to wrap the original IDataReader in a using{} block instead of wrapping
the InnerDataReader. This is to ensure that the database connection is properly managed, because closing the InnerDataReader alone
does not release the connection that the outer IDataReader holds.
Use Extension Method
The above implementation works fine. But we could do it more elegantly. In ASP.NET, the most frequently used method, in my opinion, is:
SomeObject.ToString()
Can we do a similar thing for the IDataReader conversion, something like IDataReader.ToSqlDataReader()? Yes, we can, utilizing an Extension Method.
There is plenty of information about Extension Methods on MSDN which you may explore. In our case, it is a really simple static method to write in the namespace
DataAccessEntLib, as listed here.
public static class DataAccessEntLibExtension
{
public static SqlDataReader ToSqlDataReader(this IDataReader reader)
{
return (SqlDataReader)((RefCountingDataReader)reader).InnerReader;
}
}
The this keyword, plus the parameter of IDataReader in the method, magically makes the method a member of the IDataReader,
upon adding the using directive for the namespace of DataAccessEntLib to the default.aspx code page. With the Extension Method in place,
we are able to bind data to GridView4 on the page using code like this:
using (IDataReader IReader = DataObject.GetIDataReader(
ConnectionString, "select * from Customers"))
{
GridView4.DataSource = IReader.ToSqlDataReader();
GridView4.DataBind();
}
Again, it works. We have the results displayed on the page as shown in the following screenshot.
Summary
It is not clear that the behaviors of the IDataReader in Enterprise Library 5.0 illustrated in this article are by design or by accident.
Our experiments above show that it is fine if an application directly reads the data from an IDataReader in a loop. However, if the application needs
to bind an IDataReader to an ASP.NET data server control, the tips presented here may be what have to be used. In my opinion, a .NET data server control
should be able to directly use the IDataReader as its data source without requiring any modification. It defeats the purpose otherwise.
It is fun finding a solution for a small problem like this.
Reference
