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?

possible duplicate of [Passing arguments to C# generic new() of templated type](http://stackoverflow.com/questions/840261/passing-arguments-to-c-sharp-generic-new-of-templated-type) — Sriram Sakthivel, Oct 24 '13 at 09:13
I had to do this on SEVERAL occassions. I decorated the properties and used reflection to populate them. It's well worth investigating! — Gayot Fow, Oct 24 '13 at 09:14
Isn't that problem solved long time ago?? It's called an **ORM** - object-relational mapper - like NHibernate or Entity Framework. Why reinvent the wheel yet again? — marc_s, Oct 24 '13 at 09:19
This is a problem that would be trivially solved using something like "dapper": `return connection.Query("some command", new {someArg = someValue, someOtherArg = someOtherValue}).ToList();` — Marc Gravell, Oct 24 '13 at 09:21
@Garry if only there were pre-existing tools that could help with that... oh, wait... ;p — Marc Gravell, Oct 24 '13 at 09:22
@MarcGravell, if only there were compliance and security departments that permitted such tools :) — Gayot Fow, Oct 24 '13 at 10:38
@GarryVass if only there were *competent* compliance and security departments ;p — Marc Gravell, Oct 24 '13 at 10:53

Adam Houldsworth · Accepted Answer · 2013-10-24T09:26:10.550

4

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).

edited Oct 24 '13 at 09:26

answered Oct 24 '13 at 09:20

Adam Houldsworth

63,413
11
150
187

+1 for Dapper, I've used it and it should be easily augment what the asker already has. – Binary Worrier Oct 24 '13 at 09:33
it is older code, im going for the populate interface for now – Serve Laurijssen Oct 24 '13 at 09:46
@ServéLaurijssen I can understand that, I battle legacy code on a daily basis. – Adam Houldsworth Oct 24 '13 at 09:47

score 0 · Answer 2 · answered Oct 24 '13 at 09:21

0

I know this is not the prettiest way, but you can put an interface constraint and then try to pass the reader as a property(or construct a method) to the constructed object.

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

answered Oct 24 '13 at 09:21

Anand

14,545
8
32
44

score 0 · Answer 3 · answered Oct 24 '13 at 09:27

I would reconmend Dapper: I use it all the time for this kind of work:

Dapper

    public class Dog
    {
        public int? Age { get; set; }
        public Guid Id { get; set; }
        public string Name { get; set; }
        public float? Weight { get; set; }

        public int IgnoredProperty { get { return 1; } }
    }            

    var guid = Guid.NewGuid();
    var dogs = connection.Query<Dog>("select Age = @Age, Id = @Id", new { Age = (int?)null, Id = guid });

    dogs.Count()
        .IsEqualTo(1);

    dogs.First().Age
        .IsNull();

    dogs.First().Id
       .IsEqualTo(guid);

Is there a generic way to fill objects from database?

3 Answers3