A quick abstraction of the code to build objects from a dataset

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s