Skip to content

When an object-relational mapper is too much, DataReader too little  

I fired up Visual Studio this evening to write a proof-of-concept app and found myself wanting strongly typed domain objects from a database but without the overhead of an object-relational mapper  (the application is read-only).

One solution is to write methods by hand, another is to code generate them but it would be nice to be able to do:

var customers = new SqlCommand("SELECT ID, Name FROM Customer", connection)
  .As(r => new Customer { CustomerID = r.GetInt32(0), Name = r.GetString(1) }).ToList();

So for any DbCommand object you can turn it into a bunch of classes by specifying the new pattern.

The tiny helper class to achieve this is:

public static class DataHelpers {
  public static List<T> ToList<T>(this IEnumerable<T> enumerable) {
    return new List<T>(enumerable);

  public static IEnumerable<T> As<T>(this DbCommand command, Func<IDataRecord, T> map) {
    using (var reader = command.ExecuteReader())
      while (reader.Read())
            yield return map(reader);

It might even be possible to do some cool caching/materialization. I should look into that :)


19 responses  

  1. I don’t think that usage of reader with yield is a good idea. Readers should be released as soon as possible because they blocks connection to SQL server (without MARS enabled). Be careful with this approach.

    zihotki – September 23rd, 2009
  2. I still find this ability to gaffer tape new methods on to existing classes from completely unrelated source code rather unpalatable. In practice it’s like using a bunch of uncorrelated global utility functions, except that it masquerades as an OO technique. I dunno, it just seems dishonest about what it actually means structurally (rather than syntactically) is which is likely to invite abuse, IMO.

    SteveSeptember 23rd, 2009
  3. Chris Brandsma had a similar idea, yet wrapped in a little more code:

    OlivierSeptember 23rd, 2009
  4. pingback

    […] When an object-relational mapper is too much, DataReader too little (Damien Guard) […]

    Dew Drop – September 23, 2009 | Alvin Ashcraft's Morning DewSeptember 23rd, 2009
  5. I understand your enthusiasm, I do a similar trick very often.
    It is also easy to write some generic code that converts a DataTable to a collection of objects and vice versa.

    Also, be sure to check out iBatis (think of it as the data-mapping functionality of NHibernate without all the session and reference stuff), this is good for building an ORM with legacy databases.

    Also check out BLToolKit, which makes ORM dead simple for basic queries.

    pete wSeptember 23rd, 2009
  6. Check out this project:

    TravisSeptember 23rd, 2009
  7. @Steve I had those concerns about extension methods originally but if you accept the fact that just because there is a dot there doesn’t mean it is necessarily a member of that class and use “go to definition” or hover to find out.

    It really helps with discoverability – no more what class is that static method on that takes a string and does x with it? Well, it appears alongside x and it doesn’t break encapsulation as it still only has access to the public interface.


    Damien GuardSeptember 23rd, 2009
  8. @zihotki – the reason I yield from each result there is I am actually foreach overing them and structuring them into a custom hierarchy at the other end – if I force them through a list then I’d be building two structures. In a couple of places I want that – hence the ToList method as well.

    FluentADO sounds like a nice idea but it is still too large for this little prototype and these methods are working a treat.


    Damien GuardSeptember 23rd, 2009
  9. re extension methods: yeah, from a user perspective I can see it’s useful not to have to hunt for non-member utility methods. I just have an old-fashioned resistance to having to totally rely on ‘go to definition’ to navigate your codebase effectively, and I think it encourages a lack of design cohesion; like I say they’re basically global utility functions, and if you did most things like that it wouldn’t be considered a decent design approach (we did that in C years ago!). Hiding a global function by pretending it’s a member is potentially fooling yourself into thinking that you have an OO design when you don’t.

    SteveSeptember 23rd, 2009
  10. Well I was going to tell you that you could use SubSonic for this (sans code generation):

    var customer = new CodingHorror("SELECT * FROM Customer WHERE ID=@1",1).ExecuteSingle();

    But if you think FluentADO is “too big” then you might think SubSonic is as well :).

    Rob Conery – September 23rd, 2009
  11. While I like the approach, I agree with Zihotki: DbDataReader object should be closed as early as possible. This approach could hurt scalability.

    Kevin Babcock – September 23rd, 2009
  12. isn’t ToList already an extension method in System.Linq.Enumerable?

    Rob – September 23rd, 2009
  13. Give IBatis.Net a quick look:

    Mike Levy – September 24th, 2009
  14. Another option is Jimmy Bogard’s AutoMapper – has support for IDataReader and IDataRecord built-in now. One line to map IDataReader to your type: Mapper.CreateMap();

    And another to do the map:

    var customers = Mapper.Map<IDataReader, IEnumerable>(dataReader);

    Could probably bake that into your extension method style quite nicely:

    public static class DataHelpers {
        public static IEnumerable<Customer> As<T>(this DbCommand command) {
            using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection)) {
                return Mapper.Map<IDataReader, IEnumerable>(dataReader);

    Or heck, skip the CreateMap step if you don’t need anything fancy (i.e. your domain object properties are 1-1 with column names) and change that line in the extension to this:

    return Mapper.DynamicMap<IDataReader, IEnumerable>(dataReader);
    Matt Burton – September 24th, 2009
  15. @Matt Yes, AutoMapper might well be worth trying once the prototype gets a bit bigger :)


    Damien GuardSeptember 24th, 2009
  16. The map function should be typed to a Func <IDataRecord> interface instead of the abstract DbDataReader class. One, the DbDataReader really is a bootstrap implementation of IDataReader and since you are really trying to perform reification of an object against a single “row”, you only need to expose the IDataRecord interface. I totally support the whole caveat emptor concept and that if someone does something stupid in the map delegate, like calling IDataReader.Read(), then that’s their fault. However, I also strongly believe in the intention revealing interface. Basically if I see a parameter of type Foo in a delegate parameter definition, in theory all the operations available defined on Foo should be callable by me. That’s not really the case here, you only want the IDataRecord members called (i.e.GetString(), GetInt32(), etc). Just my 2 cents.


    Jimmy ZimmermanSeptember 29th, 2009
  17. @Jimmy: Good call, switched to IDataRecord :)

    Damien GuardOctober 1st, 2009
  18. Yeah I actually hooked this guy up with another Ex Method from Alex ( which appears now to be DOA). Which was something along the lines of:

    public static IEnumerable Enumerate(this IDataReader reader) {
      using (reader) 
        while (reader.Read())
          yield return reader;

    It seems logical that we really have two responsibilities here. One to enumerate a sequence of IDataRecord instances and another to coordinate with a mapping delegate and our friend the ExecuteReader() method.

    Jimmy ZimmermanOctober 5th, 2009
  19. pingback

    […] Guard had a short post “When an object-relational mapper is too much, DataReader too little” along these lines and that’s what i have used as the basis for my dynamic abuse. In […]

    Abusing dynamic for no good reason « Searching, Seek and DeployFebruary 28th, 2010

Respond to this