Auto Dispose Sql Connections properly

Question

My application is using 3 layers: DAL / Service / UL.

My typical DAL class looks like this:

public class OrdersRepository : IOrdersRepository, IDisposable
{
    private IDbConnection _db;

    public OrdersRepository(IDbConnection db) // constructor
    {
        _db = db;
    }

    public void Dispose()
    {
        _db.Dispose();
    }
}

My service calls the DAL class like this (injecting a database connection):

public class ordersService : IDisposable
{
    IOrdersRepository _orders;

    public ordersService() : this(new OrdersRepository(new Ajx.Dal.DapperConnection().getConnection()))
    {
    }

    public ordersService(OrdersRepository ordersRepo)
    {
        _orders = ordersRepo;
    }

    public void Dispose()
    {
        _orders.Dispose();
    }
}

And then finally within my UI layer, this is how I access my service layer:

public class OrdersController : Controller, IDisposable
{
    //
    // GET: /Orders/
    private ordersService _orderService;

    public OrdersController():this(new ordersService())
    {
    }

    public OrdersController(ordersService o)
    {
        _orderService = o;
    }

    void IDisposable.Dispose()
    {
        _orderService.Dispose();
    }
}

This all works good. But as you can see, I am relying on IDisposable within every layer. UI disposes service object and then service object disposes DAL object and then DAL object disposes the database connection object.

I am sure there has to be a better way of doing it. I am afraid users can forget to dispose my service object (within UI) and I will end up with many open database connections or worse. Please advise the best practice. I need a way to auto-dispose my database connections OR any other unmanaged resources (files etc).

Answer 1

Your question comes back to the principle of ownership:

he who has ownership of the resource, should dispose it.

Although ownership can be transferred, you should usually not do this. In your case the ownership of the IDbConnection is transferred from the ordersService to the OrdersRepository (since OrdersRepository disposes the connection). But in many cases the OrdersRepository can't know whether the connection can be disposed. It can be reused throughout the object graph. So in general, you should not dispose objects that are passed on to you through the constructor.

Another thing is that the consumer of a dependency often can't know if a dependency needs disposal, since whether or not a dependency needs to be disposed is an implementation detail. This information might not be available in the interface.

So instead, refactor your OrdersRepository to the following:

public class OrdersRepository : IOrdersRepository {
    private IDbConnection _db;

    public OrdersRepository(IDbConnection db) {
        _db = db;
    }
}

Since OrdersRepository doesn't take ownership, IDbConnection doesn't need to dispose IDbConnection and you don't need to implement IDisposable. This explicitly moves the responsibility of disposing the connection to the OrdersService. However, ordersService by itself doesn't need IDbConnection as a dependency; it just depends on IOrdersRepository. So why not move the responsibility of building up the object graph out of the OrdersService as well:

public class OrdersService : IDisposable {
    private readonly IOrdersRepository _orders;

    public ordersService(IOrdersRepository ordersRepo) {
        _orders = ordersRepo;
    }
}

Since ordersService has nothing to dispose itself, there's no need to implement IDisposable. And since it now has just a single constructor that takes the dependencies it requires, the class has become much easier to maintain.

So this moves the responsibility of creating the object graph to the OrdersController. But we should apply the same pattern to the OrdersController as well:

public class OrdersController : Controller {
    private ordersService _orderService;

    public OrdersController(ordersService o) {
        _orderService = o;
    }
}

Again, this class has become much easier to grasp and it doesn't needs to dispose anything, since it doesn't has or took ownership of any resource.

Of course we just moved and postponed our problems, since obviously we still need to create our OrdersController. But the difference is that we now moved the responsiblity of building up object graphs to a single place in the application. We call this place the Composition Root.

Dependency Injection frameworks can help you making your Composition Root maintainable, but even without a DI framework, you build your object graph quite easy in MVC by implementing a custom ControllerFactory:

public class CompositionRoot : DefaultControllerFactory {
    protected override IController GetControllerInstance(
        RequestContext requestContext, Type controllerType) {
        if (controllerType == typeof(OrdersController)) {
            var connection = new Ajx.Dal.DapperConnection().getConnection();

            return new OrdersController(
                new OrdersService(
                    new OrdersRepository(
                        Disposable(connection))));
        } 
        else if (...) {
            // other controller here.
        } 
        else {
            return base.GetControllerInstance(requestContext, controllerType);
        }
    }

    public static void CleanUpRequest() }
        var items = (List<IDisposable>)HttpContext.Current.Items["resources"];
        if (items != null) items.ForEach(item => item.Dispose());
    }

    private static T Disposable<T>(T instance) 
        where T : IDisposable {
        var items = (List<IDisposable>)HttpContext.Current.Items["resources"];
        if (items == null) {
            HttpContext.Current.Items["resources"] =
                items = new List<IDisposable>();
        }
        items.Add(instance);
        return instance;
    }
}

You can hook your custom controller factory in the Global asax of your MVC application like this:

public class MvcApplication : System.Web.HttpApplication
{
    protected void Application_Start()
    {
        ControllerBuilder.Current.SetControllerFactory(
            new CompositionRoot());
    }

    protected void Application_EndRequest(object sender, EventArgs e)
    {
        CompositionRoot.CleanUpRequest();
    }
}

Of course, this all becomes much easier when you use a Dependency Injection framework. For instance, when you use Simple Injector (I'm the lead dev for Simple Injector), you can replace all of this with the following few lines of code:

using SimpleInjector;
using SimpleInjector.Integration.Web;
using SimpleInjector.Integration.Web.Mvc;

public class MvcApplication : System.Web.HttpApplication
{
    protected void Application_Start()
    {
        var container = new Container();

        container.RegisterPerWebRequest<IDbConnection>(() =>
            new Ajx.Dal.DapperConnection().getConnection());

        container.Register<IOrdersRepository, OrdersRepository>();
        container.Register<IOrdersService, OrdersService>();

        container.RegisterMvcControllers(Assembly.GetExecutingAssembly());

        container.Verify();

        DependencyResolver.SetResolver(
            new SimpleInjectorDependencyResolver(container));
    }
}

There are a few interesting things going on in the code above. First of all, the calls to Register tell Simple Injector that they need to return a certain implementation should be created when the given abstraction is requested. Next, Simple Injector allows registering types with the Web Request Lifestyle, which makes sure that the given instance is disposed when the web request ends (just as we did in the Application_EndRequest). By calling RegisterMvcControllers, Simple Injector will batch-register all Controllers for you. By supplying MVC with the SimpleInjectorDependencyResolver we allow MVC to route the creation of controllers to Simple Injector (just as we did with the controller factory).

Although this code might be a bit harder to understand at first, the use of a Dependency Injection container becomes really valuable when your application starts to grow. A DI container will help you keeping your Composition Root maintainable.