DataReader Extension to Get Typesafe Database Values

This extension to IDataReader can be used to get Typesafe values from a reader. This is furthur enhanced to get even Enum types. Compile this in to a dll and reference it from your Data Access logic. There will be a extension method available on DataReaders(eg: SqlDataReader ...etc).

Click to enlarge

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Data;


public static class DataReaderExtensions
{
  public static T GetDBValue<T>(this IDataReader dr, int ordinal)
{
    if (dr.IsDBNull(ordinal)) //Value in the DB is null
        return default(T); //Returns the default type of the value (string->null,int->0,int?->null)
    if (typeof(T).BaseType.Name == "Enum") //Check for enum types
        return (T)Enum.Parse(typeof(T), dr.GetValue(ordinal).ToString(), true);//Parse and return enum value

    return (T)dr.GetValue(ordinal); //not Enum then cast to the relevant type and return
}


  public static T GetDBValue<T>(this IDataReader dr, string ColumnName)
{
    try
{
  int ordinal = dr.GetOrdinal(ColumnName);//Get the ordinal using column name
  return dr.GetDBValue(ordinal);
}
    catch (IndexOutOfRangeException exception)
{
       throw new ApplicationException("'" + ColumnName + "' is invalid", exception);
}
}

}

You can use this as following sample

Business Entities

public enum ItemType {NonPerishable, Perishable }

public class Item 
{
  public int ItemNo{get;set;}
  public string Description{get;set;}
  public decimal UnitPrice{get;set;}
  public int StockQuantity{get;set;}
  public ItemType Type{get;set;}//Enum
  public int? AlternativeItemNo {get;set;} //Nullable Type
}

public class Items : List<Item> { };

You can write your data access logic as follows

private Item FillItem(SqlDataReader Reader)
 {
     if (Reader.Read())
     {
         Item objItem = new Item()
         {
             ItemNo = Reader.GetDBValue<int>("ItemNo"),
             Description = Reader.GetDBValue<string>("Description"),
             StockQuantity = Reader.GetDBValue<int>("StockQuantity"),
             Type = Reader.GetDBValue<ItemType>("ItemType"),
             UnitPrice = Reader.GetDBValue<decimal>("UnitPrice"),
             AlternativeItemNo = Reader.GetDBValue<int?>("AlternativeItemNo")

         };
      
         return objItem;
     }
     else
            return null;
 }


 public Items GetAllItems()
 {
     Items objItems = new Items();
     using (SqlConnection con = new SqlConnection("Data Source=DOTNETDBSRV;Initial Catalog=SimpleApp;Persist Security Info=True;User ID=sa;Password=sa"))
     {
         using (SqlCommand cmd = new SqlCommand("Item_GetAll", con))
         {
             cmd.CommandType = CommandType.StoredProcedure;
             try
             {
                 con.Open();
                 using (SqlDataReader rdr = cmd.ExecuteReader())
                 {
                     while (true)
                     {
                         Item objItem = this.FillItem(rdr);
                         if (objItem == null) break;
                         objItems.Add(objItem);
                     }

                     rdr.Close();
                 }
             }
             catch (Exception ex) { throw new ApplicationException("Get Items failed", ex); }
         }
         con.Close();
     }
     return objItems;
 }