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


16 responses

  1. Avatar for zihotki

    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 23 September 2009
  2. Avatar for Steve

    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.

    Steve 23 September 2009
  3. Avatar for Olivier

    Chris Brandsma had a similar idea, yet wrapped in a little more code called FluentADO

    Olivier 23 September 2009
  4. Avatar for pete w

    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.\n\rAlso, 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.\n\rAlso check out BLToolKit, which makes ORM dead simple for basic queries.

    pete w 23 September 2009
  5. Avatar for Damien Guard

    @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 Guard 23 September 2009
  6. Avatar for Damien Guard

    @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 Guard 23 September 2009
  7. Avatar for Steve

    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.

    Steve 23 September 2009
  8. Avatar for Rob Conery

    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 23 September 2009
  9. Avatar for Kevin Babcock

    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 23 September 2009
  10. Avatar for Rob

    isn't ToList already an extension method in System.Linq.Enumerable?

    Rob 23 September 2009
  11. Avatar for Mike Levy

    Give IBatis.Net a quick look

    Mike Levy 24 September 2009
  12. Avatar for Matt Burton

    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 24 September 2009
  13. Avatar for Damien Guard

    @Matt Yes, AutoMapper might well be worth trying once the prototype gets a bit bigger :)

    Damien Guard 24 September 2009
  14. Avatar for Jimmy Zimmerman

    The map function should be typed to a Func 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 Zimmerman 29 September 2009
  15. Avatar for Damien Guard

    @Jimmy: Good call, switched to IDataRecord :)

    Damien Guard 1 October 2009
  16. Avatar for Jimmy Zimmerman

    Yeah I actually hooked this guy up with another Ex Method from Alex (http://www.base4.net/blog.aspx?ID=409 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 Zimmerman 5 October 2009