SqlDataReader Simplified

• Download source - 15.51 KB
• Download DataReader class - 1.39 KB

Introduction

Welcome to my first article ever. I hope it will be useful to some of you. Let's start... Almost everyday I code software, I use SqlDataReader because it is very fast and simple to use. Since I use it that much, I realized that if I simplify a bit more the process of creating and destroying it, it would reduce the amount of code needed and also increase readability.

Let's see the standard usage of a SqlDataReader:

using (SqlConnection conn = new SqlConnection(ConnectionString))
using (SqlCommand comm = new SqlCommand("Sales by Year"))
{
  conn.Open();

  comm.Parameters.Add("Beginning_Date", 
	SqlDbType.DateTime).Value = DateTime.Parse("1997-01-01");
  comm.Parameters.Add("Ending_Date", 
	SqlDbType.DateTime).Value = DateTime.Parse("1997-12-31");

  comm.CommandType = System.Data.CommandType.StoredProcedure;
  comm.CommandTimeout = 60;

  using (SqlDataReader dr = comm.ExecuteReader())
  {
    while (dr.Read())
    {
      DateTime ShippedDate = (DateTime)dr["ShippedDate"};
      int OrderID = (int)dr["OrderID"];
      Decimal Subtotal = (Decimal)dr["Subtotal"];
      string Year = (string)dr["Year"];
    }
  }
}

You have to use 3 using statements and also not forget to open the connection before executing reader. To improve that, I have created a class named DataReader that does some of the work for you.

Using the Code

Here is what the same code looks like with the DataReader class:

using (DataReader dr = new DataReader("Sales by Year", 
System.Data.CommandType.StoredProcedure, 60))
{
  dr.Parameters.Add("Beginning_Date", 
	SqlDbType.DateTime).Value = DateTime.Parse("1997-01-01");
  dr.Parameters.Add("Ending_Date", 
	SqlDbType.DateTime).Value = DateTime.Parse("1997-12-31");

  while (dr.Read())
  {
    DateTime ShippedDate = dr.GetDateTime("ShippedDate");
    int OrderID = dr.GetInt32("OrderID");
    Decimal Subtotal = dr.GetDecimal("Subtotal");
    string Year = dr.GetString("Year");
  }
}

Now let me explain a little bit what I have done.

First of all, I did not inherit from SqlDataReader since it does not have a constructor defined. Maybe it is possible to instantiate SqlDataReader without calling SqlCommand.ExecuteReader but I did not try it. Instead, I have only implemented IDisposable to let me use using statement.

You can also see in the above code that I am using dr.GetDateTime() with a column name. In the SqlDataReader, it is only possible to use it with the index of the column. I have also added some validation to the Get* functions: when the value is DbNull, then I return the default value of the type. If it is not the behavior that you want, you can still use the syntax dr[ColumnName] or dr[index] to get the DbNull value.

Points of Interest

Since my primary language is not English, I hope I did not make too many errors, but feel free to send good or bad comments so I can learn.

Hope it helps at least one person.

Thank you for reading.

History

• 15^th April, 2011: Initial release