Injecting connection strings vs IDbConnection

Question

What are the trade-offs for injecting connection strings vs. an instance of IDbConnection?

I use StructureMap to inject various services into my ASP.NET MVC application, most of which require database access for LINQ-to-SQL queries. Injecting an IDbConnection seems more testable and easier to configure for IoC than a generic connection string parameter, but I'm worried about open connections hanging around if I don't explicitly wrap the connection in a using block.

Are there any connection pooling advantages or disadvantages I should be aware of?

Injected Connection String

using (var con = new SqlConnection(InjectedConnectionString))
{
    con.Execute("INSERT INTO Logs (...) VALUES (...)");
    using (var db = new MyDataContext(con))
    {
        var records = from p in db.Products
                      select p;
    }
}

Injected IDbConnection

con.Execute("INSERT INTO Logs (...) VALUES (...)");
using (var db = new MyDataContext(InjectedConnection))
{
    var records = from p in db.Products
                  select p;
}

Why should you be concerned about open connections? You're using a using block which ensures that the connection will always be closed. — Dai
@David, only if I inject the connection string and explicitly use a using. When injecting an IDbConnection, I don't and this is part of the difference. I'll make it clearer in my question. — Petrus Theron
Ah, I see. In the injected IDbConnection case the DataContext will not dispose of your connection (it only disposes of connections if it created it from a connection string itself), so it's your responsibility. But connections are cheap with Connection Pooling, so personally I'd go with injecting the connection string and avoid connection re-use (unless you're using a database system that doesn't use pooling). — Dai

kelloti kelloti · Accepted Answer · 2012-08-25T14:23:10

A feature of any moderately sophisticated IoC container (structuremap) is being able to control the lifetimes of objects. By default, structuremap uses a Transient lifetime. This means it creates a new instance per object graph. In practice, this often is the same as per-web-request (unless you sprinkle your code with usages of container.GetInstance<T>()).

By using structuremap to inject precious resources like database connections you gain control over how long they live. A single resource can (if you choose) be reused throughout an entire web request, or created fresh for every usage.

Furthermore, these choices (as well as configuration) are now externalized into the registry instead of sprinkling them through your code. If you have to change how the connection is created, you only have to look one place. Classes with a single responsibility are always preferred.

As far as your connection pooling concerns, no IoC container will involve itself in details like connection pooling. They do, however, help with lifetimes. Structuremap will call Dispose() on any IDisposable object (well, it's actually the interpreter that calls it).

Edit: Again on connection pooling, each lifetime carries its own rules for how and when objects are disposed. Transient relies on the CLR to dispose, however HttpRequestScoped deterministically disposes objects at the end of each request. Using HttpRequestScoped would prevent you from maxing out the number of connections.

Injecting connection strings vs IDbConnection

Injected Connection String

Injected IDbConnection

1 Answers