DamienG

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

[)amien

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

  1. Avatar for

    Information is only used to show your comment. See my Privacy Policy.

  2. Avatar for Jimmy Zimmerman
    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.

  4. Avatar for Jimmy Zimmerman
    Jimmy Zimmerman

    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

  5. Avatar for Damien Guard
    Damien Guard

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

  6. Avatar for Matt Burton
    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);
  8. Avatar for Rob
    Rob

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

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

  10. Avatar for Rob Conery
    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 :).

  11. Avatar for Steve
    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.

  12. Avatar for Damien Guard
    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.

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

  14. Avatar for pete w
    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.

  16. Avatar for Steve
    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.

  17. Avatar for zihotki
    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.