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

22 September 2009 .NET SQL C#

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

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
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
Chris Brandsma had a similar idea, yet wrapped in a little more code called FluentADO
Olivier – 23 September 2009
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
@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
@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
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
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
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
isn't ToList already an extension method in System.Linq.Enumerable?
Rob – 23 September 2009
Give IBatis.Net a quick look
Mike Levy – 24 September 2009

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:
```csharp
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:

```csharp
return Mapper.DynamicMap<IDataReader, IEnumerable>(dataReader);
```

Matt Burton – 24 September 2009

@Matt Yes, AutoMapper might well be worth trying once the prototype gets a bit bigger :)
Damien Guard – 24 September 2009
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
Jimmy Zimmerman – 29 September 2009
@Jimmy: Good call, switched to IDataRecord :)
Damien Guard – 1 October 2009
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
Avatar (Required)

Name/alias (Required, displayed)

Website (Optional, displayed)