Thursday, 9 October 2008

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;
}


No comments: