The Dumping Ground

Truthfully I wonder how late I got to this party given the already established ORM and the new Linq technology. A session at a conference I’m at by Mike Pitcher suggested that .net’s generics would allow me to create a quick and easy way to create a strongly typed list of objects based on some SQL.

Here’s what I came up with based on that idea. As you can see there are some other methods not defined in the example but they are just functionality we’ve hidden away elsewhere in the class.

public delegate T ConstructObject<T>(DbDataReader rdr);

public List<T> GetList<T>(string sql, Dictionary<string, object> parameters, ConstructObject<T> constructor)
{
    DbConnection con = Connection;
    List<T> list = null;
    using (DbCommand cmd = con.CreateCommand())
    {
        list = GetList(sql, parameters, constructor, cmd);
    }
    return list;
}

public List<T> GetList<T>(string sql, Dictionary<string, object> parameters, ConstructObject<T> constructor, DbCommand cmd)
{
    List<T> list = new List<T>();
    cmd.CommandText = sql;
    AddParameters(cmd, parameters);
    DbDataReader rdr = cmd.ExecuteReader();
    while (rdr.Read())
    {
        T obj = constructor(rdr);
        list.Add(obj);
    }
    return list;
}

And here is a practical use for the methods,

Dictionary<string, object> p = new Dictionary<string, object>();
p["order"] = cOrderNo;
string sql = "select line, prod, quan, stat from orddt where [order]=:order";
order.Items = GetList<OrderItem>(sql, p, ConstructOrderItem);

And you need a method that is able to turn a reader in to an object of the type you are interesting in.

private static OrderItem ConstructOrderItem(DbDataReader rdr)
{
    OrderItem item = new OrderItem();
    item.Line = rdr["line"] as string;
    item.Product = rdr["prod"] as string;
    item.Quantity = rdr["quan"] as int;
    item.ProductStatus = rdr["stat"] as string;
    return item;
}