Sunday, September 12, 2010

TEAM.Commons: Infrastructure for the Query part of CQRS (or just for querying)

This is the 2nd of several posts about TEAM.Common, a set of functionality I use in every project and that I'd like to share. The index is here: http://rodolfograve.blogspot.com/2010/09/teamcommons-introduction.html

It all started with some CQRS reading (Command and Query Responsibility Segregation).

The idea of having a separated model only for querying seemed very valuable even outside a "classic" CQRS implementation, so we started applying it everywhere using an architecture like:

At this point, the implementation of the SqlSomeModelQueries was always something like this:
public IEnumerable<somemodel> GetAllWithCondition(string condition1, int condition2)
{
  using (var connection = new SqlConnection(ConnectionString))
  {
    var cmd = connection.CreateCommand("select A, B, C, ... from T1 inner join T2 ... where ...");
    using (var reader = cmd.ExecuteReader())
    {
      while (reader.Read())
      {
        SomeModel result = new SomeModel()
        {
          // Missing null checkings, safe conversions, etc
          A = Convert.ToInt32(reader["A"].GetValue()), 
          B = Convert.ToString(reader["B"].GetValue())
          // ... More properties
        };
        yield return result; // We don't want to keep all the items in memory.
      }
    }
  }
}

Obviously, after implementing this 3 times I decided I needed some infrastructure that will save us from repeating all this code over and over, with all the benefits that come with it.

You can find the complete implementation in https://bitbucket.org/rodolfograve/team.commons. Check the MapperExtensions and DbConnectionExtensions classes. Using this infrastructure the code above is:


public IEnumerable<SomeModel> GetAllWithCondition(string condition1, int condition2)
{
  using (var connection = new SqlConnection(ConnectionString))
  {
    foreach (var item in connection.GetAllWithStreaming<SomeModel>("select A, B, C, ... from T1 inner join T2 ... where ..."))
    // There is an overload to pass an SqlCommand in case you need to use an SqlCommand with parameters.
    {
      yield return item;
    }
  }
}

There are many details you must take into account if you want to do this by yourself:
  • Reflection is always very tricky to get right.
  • Reflection is slow, so use something like Fasterflect and its cache features.
  • There are a lot of special cases.
  • You should add as much information as possible in the exception messages.

Remember, you can get all this code for free at bitbucket: http://bitbucket.org/rodolfograve/team.commons/overview

Check it out to get ideas, or simply use it as it is. It's working out for me and my team.

No comments: