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