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, Aug 25 '12 at 13:29
@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, Aug 25 '12 at 14:03
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, Aug 25 '12 at 14:08

score 3 · Accepted Answer · edited Oct 09 '19 at 11:22

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.

If StructureMap injects several instances of `IDbConnection` (not sure if/when), could the connection pool max out if they are not disposed in time? — Petrus Theron, Aug 25 '12 at 14:32
I'm no expert on connection pooling or Linq-to-SQL, but see my latest edit — kelloti, Aug 25 '12 at 14:42
Thanks, kelloti. Is `HybridHttpOrThreadLocalScoped` equivalent to `HttpRequestScoped` for web applications? — Petrus Theron, Aug 25 '12 at 14:43
An afterthought - you can't really ever _prevent_ the connection pool from maxing out, unless you share connections across web requests. But using `HttpRequestScope` would reduce that possibility by deterministically disposing connections. — kelloti, Aug 25 '12 at 14:44
Sort of, the name says it all. In production, yes, they're equivalent. In unit tests, it'll use `ThreadLocal` since there is no HTTP context. — kelloti, Aug 25 '12 at 14:45

Injecting connection strings vs IDbConnection

Injected Connection String

Injected IDbConnection

1 Answers1