Typed SQLDataReader

• Download source files - 4.84 Kb

Introduction

SQLDataReader provides a way to access the records in forward direction from the database. A typed SQLDataReader would provide an interface to read the column data using friendly column names.

SQLDataReader

.NET has provided a new different ways to access data based on various scenarios. SQLDataReader is one of the fastest ways of accessing a SQL Server database as it reads data in one direction- forward read only.

Limitation

To read column value from SQLDataReader object, one needs to call the function with column ordinal as the input parameter. This is tedious as it requires changes in the column parameter again on any changes in the SQL column sequence.

One could also read column values based on column names, provided user explicitly casts the property to proper data type.

Solution

To counter these issues, I created a new class that would take input as column names and returns the value with appropriate data type same like using column ordinals. This class would also provide functionality to handle null values.

The class SmartDataReader has the same function names but different signatures to access the column values as that of SQLDataReader. SmartDataReader class uses the SQLDataReader object that is initialized in the constructor of this class. The rest of the call handling is done by calling the appropriate method of this object inside the class.

Example:

Different ways to access int column(“EmpID”):

As you could see, to use the SQLDataReader with column names, we need to explicitly perform the proper casting. This is not the case with SmartDataReader which works same way as using column ordinals.

Sample:

public sealed class SmartDataReader
{
private DateTime defaultDate;
public SmartDataReader(SqlDataReader reader)
{
    this.defaultDate = DateTime.MinValue;
    this.reader = reader;
}
 
public int GetInt32(String column)
{
    int data = (reader.IsDBNull(reader.GetOrdinal(column))) 
                            ? (int)0 : (int)reader[column];
    return data;
}
 
public short GetInt16(String column)
{
    short data = (reader.IsDBNull(reader.GetOrdinal(column))) 
                          ? (short)0 : (short)reader[column];
    return data;
}
 
public float GetFloat(String column)
{
    float data = (reader.IsDBNull(reader.GetOrdinal(column))) 
                ? 0 : float.Parse(reader[column].ToString());
    return data;
}
 
public bool GetBoolean(String column)
{
    bool data = (reader.IsDBNull(reader.GetOrdinal(column))) 
                             ? false : (bool)reader[column];
    return data;
}
 
public String GetString(String column)
{
    String data = (reader.IsDBNull(reader.GetOrdinal(column))) 
                           ? null : reader[column].ToString();
    return data;
}
 
public DateTime GetDateTime(String column)
{
   DateTime data = (reader.IsDBNull(reader.GetOrdinal(column))) 
                      ? defaultDate : (DateTime)reader[column];
   return data;
}
 
public bool Read()
{
   return this.reader.Read();
}
private SqlDataReader reader;
}
/// <summary>
/// Read from database using SmartDataReader
/// </summary>
public void ReadData()
{
    SqlConnection connection = new 
        SqlConnection("Initial Catalog=Northwind;Data Source=Test;" + 
        "Integrated Security=SSPI;");
    SqlDataReader reader = null;
    try
    {
        connection.Open();
        string sql = "SELECT EmployeeID, FirstName, BirthDate FROM Employees";
        SqlCommand command = new SqlCommand();
        command.CommandText = sql;
        command.Connection = connection;
        reader = command.ExecuteReader();
        SmartDataReader smartReader = new SmartDataReader(reader);
        while(smartReader.Read())
        {
            int idSmart = smartReader.GetInt32("EmployeeID"); 
            string nameSmart = smartReader.GetString("FirstName"); 
            DateTime dateSmart = smartReader.GetDateTime("BirthDate");
        }
    }
    catch(SqlException e)
    {                   
        Console.WriteLine(String.Format("An exception occurred" + 
                " : {0}. Please contact your system administrator.", 
                e.Message));
    }
    finally
    {
        if(reader != null) reader.Close();
        if(connection != null) connection.Close();
    }
}

This sample opens a database connection to the SQL Server database. A SQLDataReader object is created based on the SQL query, and this object is passed to the constructor of SmartDataReader class object. Once SmartDataReader object is set, all table values are accessed by using SmartDataReader object.