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

Extension Method for Generic List<T> to Comma Seperated Values

Extension methood can be implemented as follows to convert List of type T values to a comma seperated string. (CSV)

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

/// Summary description for MyExtensions
/// 
public static class MyExtensions
{
   public static string ToCSV<T>(this List<T> lst)
 {
  StringBuilder sb = new StringBuilder();

  foreach (T val in lst)
     sb.Append("," + val.ToString());

  if (sb.Length > 0)
     return sb.ToString().Substring(1);
  else
     return null;

}

}

You will get extension method for any List
Click to enlarge


Usage is as follows

List<short> lst = new List<short> { 1, 5, 7 };

string str = lst.ToCSV<short>();// str will assigned with "1,5,7"

User Control Properties with Enum Types

Rather than specifying web user control properties with string type we can have much controlled properties using enums. For example.. I need to create a user control with two command buttons which I should be able to use in few places in a page. Two command buttons should appear horizontally or vertically depending on a property (DrawMode) in the user control.

I have following layout in html

Click to enlarge

Then I can write property as follows in the user control
Click to enlarge
The user of this user control must specify the "Verticle" correctly for this to work. There is no control over the value you can set to the property.

You can enhance this code using an enum as follows


Click to enlarge


The advantage is that you have the total control over the property values that can be assigned.

Click to enlarge