Click here to Skip to main content
Click here to Skip to main content

SqlDataReader Simplified

, 15 Apr 2011
Rate this:
Please Sign up or sign in to vote.
Shorten the amount of code needed to use a SqlDataReader

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

  • 15th April, 2011: Initial release

License

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

About the Author

DizZ
Software Developer
Canada Canada
The first ever language I started with was QuickBasic and soon I switched to Borland C++ 3.0. Then I attended computer science school where I learned Cobol, Access and ASP. Now I use VB6, VB.NET, C#, ASP.NET, PHP languages with MSSQL and MySQL databases.

Comments and Discussions

 
GeneralThoughts PinprofessionalPIEBALDconsult4-Oct-13 3:45 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 15 Apr 2011
Article Copyright 2011 by DizZ
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid