Click here to Skip to main content
13,553,325 members
Click here to Skip to main content
Add your own
alternative version


37 bookmarked
Posted 16 Aug 2004

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.


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.


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


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.


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.


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

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


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;
        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);
            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.", 
        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.


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
Australia Australia
No Biography provided

You may also be interested in...


Comments and Discussions

GeneralAccess database from C#.Net via ODBC Pin
Will L Pittenger12-Oct-04 22:48
memberWill L Pittenger12-Oct-04 22:48 
GeneralRe: Access database from C#.Net via ODBC Pin
S Sansanwal21-Oct-04 12:51
memberS Sansanwal21-Oct-04 12:51 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04-2016 | 2.8.180515.1 | Last Updated 17 Aug 2004
Article Copyright 2004 by S Sansanwal
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid