Automatically delete Sql connections properly

Question

My application uses 3 levels: DAL / Service / UL.

My typical DAL class is as follows:

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 (by introducing 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 finally, in my user interface layer, here is how I access my service level:

 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(); } } 

It all works well. But, as you can see, I rely on IDisposable inside each layer. The UI places the service object, and then the service object places the DAL object, and then the DAL object deletes the database connection object.

I am sure there must be a better way to do this. I am afraid that users may forget to dispose of their service object (in the user interface), and in the end I will have many open connections to the database or worse. Consult best practices. I need a way to automatically delete my database connections or any other unmanaged resources (files, etc.).

Answer 1

Your question returns to the principle of ownership:

Anyone who has the right to own a resource must dispose of it.

Although ownership may be transferred, you should not usually do so. In your case, the ownership of IDbConnection transferred from ordersService to OrdersRepository (since OrdersRepository uses a connection). But in many cases, the OrdersRepository cannot know whether a connection can be established. It can be reused in the graph of objects. Therefore, in the general case, you should not place objects passed to you through the constructor.

Another thing is that the consumer of dependencies often cannot know whether the dependency needs, because regardless of whether the dependency should be selected, this is an implementation detail. This information may not be available in the interface.

So reformat the OrdersRepository as follows:

 public class OrdersRepository : IOrdersRepository { private IDbConnection _db; public OrdersRepository(IDbConnection db) { _db = db; } } 

Since the OrdersRepository does not receive ownership, IDbConnection does not need to host IDbConnection , and you do not need to implement IDisposable . This explicitly takes responsibility for removing the connection to ordersService . However, ordersService itself does not need an IDbConnection as a dependency; it just depends on the IOrdersRepository . So, why not move the responsibility for plotting the object from ordersService :

 public class OrdersService : IDisposable { private readonly IOrdersRepository _orders; public ordersService(IOrdersRepository ordersRepo) { _orders = ordersRepo; } } 

Since ordersService has nothing to do with itself, there is no need to implement IDisposable . And since it now has only one constructor that accepts the required dependencies, the class has become much easier to maintain.

Thus, this transfers responsibility for creating the object graph in the OrdersController . But we have to apply the same template to the OrdersController :

 public class OrdersController : Controller { private ordersService _orderService; public OrdersController(ordersService o) { _orderService = o; } } 

Again, this class has become much easier to understand, and it does not need to dispose of anything, since it does not have or did not own any resource.

Of course, we simply postponed and postponed our problems, since, obviously, we still need to create our OrdersController . But the difference is that we have now shifted the responsibility for plotting objects to one place in the application. We call this place the root of the composition .

Dependency frameworks can help you make your root composition supported, but even without a DI framework, you can create your object graph fairly easily 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 connect your custom factory controller in the global asax of your MVC application as follows:

 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, all this becomes much easier if you use the Injection Dependency framework. For example, when you use Simple Injector (I am the lead developer for Simple Injector), you can replace all of this with the following 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 some interesting things in the code above. First of all, calls to Register to tell Simple Injector that they need to return a specific implementation should be created when this abstraction is requested. Then Simple Injector allows you to register types with Web Request Lifestyle , which ensures that this instance will be deleted when the web request is completed (as in Application_EndRequest ). By calling RegisterMvcControllers , Simple Injector will register all the controllers for you. By providing SimpleInjectorDependencyResolver using the SimpleInjectorDependencyResolver , we enable MVC to route the creation of the controllers to the Simple Injector (as with the factory controller).

Although this code may be a little more difficult to understand at first, using the Injection Dependency container becomes really valuable when your application starts to grow. The DI container will help you maintain your root composition.

Answer 2

It’s good if you are really paranoid about the fact that you can use a finalizer (destructor) to automatically execute Dispose when an object collects garbage. Check out this link, which basically explains everything you need to know about IDisposable , go to the "Examples" section, if you just want a quick example of how to do this, the Finalizer (destructor) is the MyResource () method.

But in some way, you should always encourage consumers of your libraries to use Dispose correctly. Automatic cleaning with the garbage collector is still a disadvantage. You do not know when the garbage collector will do its job, so if you get a lot of these classes created and used and then forget, in a short time you can still be in trouble.