I've heard some talk on twitter, blogs, and at various user groups and conferences for a while now about using the command/query object pattern instead of the repository pattern for a data access layer, and all the benefits of doing so.

So, I decided to explore it for myself. I converted over my Ledger project, since it used repositories, has some complexity, but is not particularly large.

Ledger already uses Dapper, so I started with Dustin Brown's post on using command/query with Dapper. For the most part, I followed what he did exactly (I also had to finally get Ledger using an IoC container, which I had been putting off for a while). These are the four files I used to get started with the pattern:

Note that mine differs from Dustin's only in that his IDatabase contains two methods named Execute, whereas mine contains one named Query and one named Execute. Note that except for IDatabase, these all depend on System.Data.IDbConnection (which is what Dapper uses). So, if you want to use a different DB access library, you'll have to change these.

Now, you need to create queries and/or commands. A query returns data, a command manipulates data. Here's a command that uses Dapper to create a new ledger:

Note that the information for the new ledger is passed via a constructor. And here's an example of a query that returns a single ledger, given an ID number (note that the ID is a constructor parameter).

Here's an example of both of those command/query objects in action inside of a controller.

That's the mechanics of it. It was much simpler to code than it might sound.

I don't know if I'm ready to draw any definitive conclusions about this pattern yet, but here are my thoughts:

• I'm not particulary happy with the ceremony involved when doing this in C#. Compared to repository, it's a bit of extra noise and typing to use Execute + new keyword + constructor parameters + private fields to hold arguments.
• Organizationally, I don't have to think about which method goes to which repository anymore. Organization, then, is strictly a matter of folders and/or namespace, and changing those is not as costly. I like that.
• I also like that instead of 2, 3, or even more repositories, I only need one IDatabase member in my controller.
• I haven't gone hands-on with unit testing, but I don't anticipate it being much harder. I could also forsee that this pattern is less likely to result in broken tests when making changes to a repository's interface.

I am impressed, but I am not entirely convinced yet. I think this may need further research, especially when it comes to unit tests.

Welcome to another "Weekly Concerns". This is a post-a-week series of interesting links, relevant to programming and programmers. You can check out previous Weekly Concerns posts in the archive.

• I've been exploring the Command/Query Objects pattern, specifically with the idea of replacing the repository pattern. I've started with Dustin's Brown post about Command/Query Objects with Dapper.
• How to setup Twitter Cards for your site (specifically WordPress, but blog post is helpful for everyone else too).
• Rob Gibbens posted about using Fody.PropertyChanged (an AOP plugin to the Fody IL rewriting framework) with Xamarin to use the MVVM pattern.
• EconTalk is one of my favorite podcasts. Check out this episode about the "sharing economy", in which they discuss the impact of technology like Uber, AirBnb, and so on, featuring my favorite guest: Michael Munger. Especially interesting to me is the discussion about taxi medallion investments.

If you have an interesting link that you'd like to see in Weekly Concerns, leave a comment or contact me.

I took a long break from ORMs in my career: about 4 years. I was working on a reporting product, and an ORM is just the wrong tool for that. Before that, I worked with ORMs here and there: NHibernate and another ORM I don't even want to mention by name for fear of being associated with it in public. For some of my own projects like the next version of EZRep and this very blog site, I've switched to a so-called micro-ORM, specifically Dapper.

Now that I'm back to consulting, I'm back into the ORM game. This time, it's Entity Framework. Early in this project (that uses EF), I've been experiencing quite a bit of frustration. It seems like I'm swimming upstream while jumping through hoops to accomplish really simple things that wouldn't take nearly as much work in Dapper. Maybe this is just me getting back into the habit, but it's also got me thinking: why do I really need a "full" ORM anymore? Abstraction and indirection are important tools, but perhaps full ORMs aren't the least leaky abstraction anymore in many cases?

Yes, a full ORM is sometimes RTRJ. However, I think micro ORMs (Dapper, PetaPoco, Massive) and document databases (Raven, Couch, Mongo) have taken a big bite out of the pool of possible use cases, and that a lot of full ORM and/or RDBMS usage in projects is really used not because it's the best tool, but because of the sheer momentum of the status quo. Imagine a snapshot of a Venn diagram taken 5 or 10 years ago compared to one I made up to represent a snapshot of today.

There might be missing bubbles like "RDBMS + no ORM" or "Json string in text file", and the exact proportions are open to interpretation. But the point I'm trying to make is this: the number of use cases that are best served by RDBMS + full ORM is shrinking, and will continue to shrink.

In a project that I'm working on, I'm using Dapper for data access and I'm using StructureMap as my IoC containter. It's an ASP.NET MVC project.

I was recently demoing the project to my partner on the staging site (Azure), and I ran into a troubling error having to do with data connections (that I had not experienced running it locally on IIS yet). I immediately thought that it must be an issue with the SQL connections (SqlDbConnection objects that implement the IDbConnection interface, per Dapper) not being closed and/or disposed of correctly.

I remember encountering the same issue when I was developing this very site that you are reading, and the solution is to use the HttpContext scope within StructureMap when configuring it for the IDbConnection interface.

Here's what I started with:

And here's what I refactored it to:

I just added HttpContextScoped (the default is PerRequest scope, see StructureMap docs on Scoping and Lifecycle Management). I also added the ReleaseAndDisposeAllHttpScopedObjects to Global.asax.cs, so that StructureMap would...release and dispose those objects at the end of each request.

However, when I did that, I was still getting problems. I would get exceptions saying that the SqlConnection ConnectionString was the empty string. Something wasn't right. I did some searching around about the issue in the docs, and couldn't really find what I was looking for. I did come across someone with a similar issue on StackOverflow, also trying to understand HttpContextScoped. The top answer from PHeiberg (not the accepted answer) pointed me the right direction.

I used a lambda expression instead of just passing the SqlConnection directly. So now my SqlDbConnections are scoped to the HttpContext, and will be cleaned up after every request. That should squash the issue I was seeing on the staging stie.

When deploying this site for the first time and actually telling people to come here, I learned that I had made a pretty big mistake. Appropriately (or perhaps tragically), I made a mistake in one of the cross-cutting concerns of my own site.

I am using StructureMap to wire up all my dependencies, and 90% of these dependencies are just done WithDefaultConventions. But there is one dependency, IDbConnection which I wired up by hand, like so:

If you can spot the mistake here, then congratulations, you are officially a better developer than me (which is no high accolade). Here's what I should have done:

And then, in Application_EndRequest, I just call ReleaseAndDisposeAllHttpScopedObjects() on ObjectFactory. This means each of the SqlConnections will be bound to a request, and then disposed of when the request is complete. Because I wasn't doing that before, each of the connections would stay open. I didn't notice this when developing the site, because I was continually rebuilding and redeploying.

This may not be strictly speaking AOP, but it's certainly a cross-cutting concern, as almost every request makes use of the IDbConnection to use Dapper, and using StructureMap to manage that dependency saves me from writing a bunch of boilerplate "new SqlConnection" in all my repositories.

Lesson learned: if this site was actually mission critical to a business, I would've been in hot water. I should've been testing with a larger number of requests over a longer running application in an environment similar to production. Fortunately, since I didn't have a bunch of repetitive boilerplate, it was very easy for me to make the fix, once I figured out what the problem was.