Is there a generic way to fill objects from database?

Question

There's this common pattern in my software where a database query is done and a list of objects is created from that query where the objects are constructed from a SqlDataReader.

For example:

public List<Authorization> ReadAuthorizations() {
  List<Authorization> authorizations = new List<Authorization>();

  using (SqlConnection connection = GetConnection(mConnString)) {
    using (SqlDataReader authReader = CmdFactory.Instance.ReadAuthorizations(connection))                                   {
      while (authReader.Read()) {
        Authorization auth = new Authorization(authReader);
        authorizations.Add(auth);
      }
    }
 }

 return authorizations;
}

You can replace Authorization with any kind of object but the pattern is the same every time. The only variables are the object types and the function that is used to query the database.

List<Authorization> ReadAuthorizations()
List<Login> ReadLogins()
List<Batch> ReadBatches()
// etc

Now I was thinking that this just screams for a generic function and I came up with this:

public List<T> Read<T>(Func<SqlConnection, SqlDataReader> func) where T : new()
{
    List<T> objects = new List<T>();

    using (SqlConnection connection = GetConnection(_ropsString))
    {
        using (SqlDataReader reader = func(connection))
        {
            while (reader.Read())
            {
                T obj = new T(reader);
                objects.Add(obj);
            }
        }
    }

    return objects;
}

This ALMOST works except that the object to construct is not allowed to take parameters, VS2k10 says:

'T': cannot provide arguments when creating an instance of a variable type

Is there a way to solve this so the variable type can get a constructor argument?

Answer 1

There are many ways to skin this cat, but to keep you going in the direction you have chosen to explore:

Use the generic constraint to specify where T : IPopulateFromReader, new() and move the reader parameter into a method call:

T obj = new T();
obj.PopulateFromReader(reader);
objects.Add(obj);

interface IPopulateFromReader
{
    void PopulateFromReader(IDataReader reader);
}

On a more serious note, perhaps look at ORM solutions like:

• NHibernate
• Entity Framework
• Dapper (honourable mention, used on SO)

The list goes on. The point being, they succeed in providing an abstraction over database access and mitigate the impedance mismatch problem, allowing you to focus on domain model and business logic and ignore boilerplate DAL code. The oftentimes support also managing the database schema (though support for this varies depending on the focus of the library / framework).