CODEWITHSUNDEEP
asp.net-mvc-3
db2
db2

Reputation: 517

Clean up database connections at request completion

I'm dabbling in MVC3, and I'm putting together a site that connects to SQL Server (no big surprise there, I'm sure). I'm using a base class called SqlRepository that will be extended by various other repository classes for different business objects. The base class will handle opening a database connection that will be shared among all other instances for the lifetime of the request (a singleton, in other words). It'll look something like this:

public abstract class SqlRepository {
    protected SqlConnection dbconn;

    public SqlRepository() {
        if (HttpContext.Current.Items["dbconn"] == null) {
            dbconn = new SqlConnection(WebConfigurationManager.ConnectionStrings["SqlRepositoryConnection"].ConnectionString);
            dbconn.Open();
            HttpContext.Current.Items["dbconn"] = dbconn;
        } else {
            dbconn = (SqlConnection)HttpContext.Current.Items["dbconn"];
        }
    }
}

The subclasses will implement various interfaces for different repository types, and will be injected via Ninject.

What, then, would be a good way to ensure that the database connection is closed at the end of each request lifecycle? I'd like to do it without a lot of extra dependencies, and without needing to explicitly close the connection from within controllers (because I'll have different build configurations for other dummy repositories, and might not even be using the SQL-based one while developing/debugging the front-end).

I suspect there's a simple solution, but I haven't done enough yet with MVC3 to know where I should handle this.

Upvotes: 1

Views: 705

Answers (1)

Samuel Neff
Samuel Neff

Reputation: 74939

If you don't want to do it in your controllers or in a controller base class, then do it in Global.asax. Not the MVC way, but just 'cause you're using MVC doesn't mean everything has to be pure MVC. These events still work fine:

private void Application_EndRequest(object sender, EventArgs e)
{
    var conn = HttpContext.Current.Items["dbconn"];
    if (conn != null)
    {
        conn.Close();
    }
}

Upvotes: 2

Related Questions