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

Typed SQLDataReader

, 16 Aug 2004
Rate this:
Please Sign up or sign in to vote.
This article would describe how to read column values based on column names, using SQLDataReader.

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”):

Using SQLDataReader (Column Ordinal) Reader.GetInt32(1)
Using SQLDataReader (Column Name) Convert.ToInt32(Reader[“EmpID”])
Using SmartSQLDataReader SmartReader.GetInt32(“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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

S Sansanwal
Architect
Australia Australia
No Biography provided

Comments and Discussions

 
GeneralRock On! Pinmemberdirq5-Jun-07 12:04 

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
Web03 | 2.8.140721.1 | Last Updated 17 Aug 2004
Article Copyright 2004 by S Sansanwal
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid