Click here to Skip to main content
15,868,016 members
Articles / Web Development / ASP.NET

Enterprise Library 5.0 Data Access Application Block: Fun with IDataReader

Rate me:
Please Sign up or sign in to vote.
4.79/5 (6 votes)
12 Jan 2012CPOL7 min read 68.4K   2.9K   25   4
Explore the behaviors of IDataReader in the Enterprise Library 5.0 data access application block.

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:

C#
//return an IDataReader        
//Param: connectionString - the actual connectionstring to database
//Param: sqlQuery - Sql select statement for retrieving data from database
public static IDataReader GetIDataReader(string connectionString, string sqlQuery)
{  
     SqlDatabase db = new SqlDatabase(connectionString);
        DbCommand cmd = db.GetSqlStringCommand(sqlQuery);
        //return an IDataReader.
        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.

C#
using (IDataReader IReader = 
       DataObject.GetIDataReader(ConnectionString, "select * from Customers"))
{
  bool HeaderExists = false;
  int NumberOfCols = 5;
  while(IReader.Read())
  {
    if (!HeaderExists)//add table header
    {
        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(); //add data
    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.

a_ReadIDataReader.JPG

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:

doc_usingReader.JPG

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:

C#
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:

b_BindIDataReader.JPG

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:

C#
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.

C#
//return a SqlDataReader
//Param: connectionString - the actual connectionstring to database
//Param: sqlQuery - Sql select statement for retrieving data from database
public static SqlDataReader GetSqlDataReader(string connectionString, string sqlQuery)
{
    IDataReader IReader = GetIDataReader(connectionString, sqlQuery);
    //return a SqlDataReader by directly casting the IDataReader to a SqlDateReader
    return (SqlDataReader) IReader;
}

To test, we simply assign the SqlDataReader returned to GridView2 on the default.aspx page:

C#
using (SqlDataReader MySqlDataReader=DataObject.GetSqlDataReader(
                     ConnectionString, "select * from Customers"))
{
  GridView2.DataSource = MySqlDataReader;
  GridView2.DataBind();
}

We get another error message, different from the previous one:

c_CastToSqlDataReader.JPG

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.

C#
public static SqlDataReader CastToSqlDataReader(IDataReader iReader)
{
    //Cast IDataReader to RefCountingDataReader first, 
    //and then cast the innerReader of the RefCountingDataReader to SqlDataReader
    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.

C#
using (IDataReader IReader = DataObject.GetIDataReader(ConnectionString, "select * from Customers"))
{
  GridView3.DataSource = DataObject.CastToSqlDataReader(IReader);
  GridView3.DataBind();
}

Here is the screenshot for the results:

d_ConvertToSqlDataReader.JPG

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:

C#
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.

C#
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:

C#
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.

e_ExtensionMethod.JPG

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
United States United States
Web & Database Developer. Design and implement web and database applications utilizing Microsoft and other development tools.

Comments and Discussions

 
QuestionUse DataSet instead Pin
JaiderNet15-May-14 3:57
professionalJaiderNet15-May-14 3:57 
I just simply avoid such complex casting and use [DataSet].
C#
public static DataSet GetDataSet(string connectionString, string sqlQuery)
{
    SqlDatabase db = new SqlDatabase(connectionString);
    DbCommand cmd = db.GetSqlStringCommand(sqlQuery);
    //return an DataSet.
    return db.ExecuteDataSet(cmd);
}

And then in your page, you can do this:
C#
using (var ds = DataObject.GetDataSet(ConnectionString, "select * from Customers"))
{
  GridView3.DataSource = ds;
  GridView3.DataBind();
}

That work fine and you will get the same result!
AnswerRe: Use DataSet instead Pin
Tomz_KV15-May-14 4:26
Tomz_KV15-May-14 4:26 
GeneralRe: Use DataSet instead Pin
Philippe Mori16-Aug-14 2:54
Philippe Mori16-Aug-14 2:54 
GeneralMy vote of 5 Pin
rht34126-Mar-12 8:44
rht34126-Mar-12 8:44 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.