How should one maintain a database connection in an ASP.NET MVC application?

Question

I'm not using LINQ-to-SQL or Entity Framework bits in a web app, and have currently been using something like this (this is for a class project):

using System.Data;
using System.Data.SqlClient;

namespace StackOverflowClone.Models
{
    public class Database
    {
        public static SqlConnection ActiveConnection { get; private set; }

        static Database()
        {
            ActiveConnection = new SqlConnection(
                "Data Source=********.database.windows.net;" +
                "Initial Catalog=EECS341;Uid=*****;Pwd=*******;" + 
                "MultipleActiveResultSets=True;");
            ActiveConnection.Open();
        }
    }
}

However this seems to cause threading issues because the static initializer runs once per server process, rather than once per request.

Does the framework provide a built in method of handling this or should I just have a function that coughs up database connections new'd up each time?

Answer 1

or should I just have a function that coughs up database connections new'd up each time?

Yes, do this. Let ADO.NET connection pooling handle the details for you. Your goal should be to keep the connection open for as short a period of time as possible.

Connection pooling reduces the number of times that new connections must be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.

So, create a static GetConnection() method that returns a new open connection. Use this within a using statement so it can be closed and returned to the connection pool as soon as possible.

using(var cn = Database.GetConnection())
{
    //query your data here, Dapper example below
    cn.Execute("update MyTable set MyField = @newValue", new {newValue});
}

Answer 2

Always create new connections and destroy them with using. They are not really created from scratch, they are fetched from a connection pool. There is no performance penalty. Actually that's the best and correct way to go.

See my answer about using: https://stackoverflow.com/a/9811911/290343