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?

@TomasVoracek: The problem is there can be more than one user being served at a time, and the connection occasionally gets confused and fails a query because a different thread is still reading the results from a previous query. — Billy ONeal, Mar 21 '12 at 20:04

D'Arcy Rittich · Accepted Answer · 2012-03-21T20:08:16.677

12

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

edited Mar 21 '12 at 20:08

answered Mar 21 '12 at 19:57

D'Arcy Rittich

167,292
40
290
283

Connection Pooling? What's that? – Billy ONeal Mar 21 '12 at 19:58
2

@BillyONeal: It sounds like it's a Pool of Connections... Maybe Google knows more. – H H Mar 21 '12 at 20:00
@Henk: I know that. I mean in the specific context of ASP.NET MVC. Is that provided by SQL Server? By ASP.NET? By something else? – Billy ONeal Mar 21 '12 at 20:00
1

@BillyONeal see my answer. It's an ADO.NET mechanism. – Ofer Zelig Mar 21 '12 at 20:03
@TomasVoracek: Ok, then the answer would be "it's provided by SQL server itself." I don't think that's an unreasonable question to ask given the massive amount of docs about databases in ASP.NET's MSDN archives (which unfortunately seem to be almost all about Linq to SQL or Entity Framework). – Billy ONeal Mar 21 '12 at 20:07
1

@BillyONeal "it's provided by SQL server itself." - no, it's not. Connection pool is handled by ADO.NET, not SQL Server. See http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx – Tomas Voracek Mar 21 '12 at 20:09
@TomasVoracek: Derp. Sorry 'bout that :) – Billy ONeal Mar 21 '12 at 20:11

score 4 · Answer 2 · edited May 23 '17 at 10:28

4

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

edited May 23 '17 at 10:28

Community

1
1

answered Mar 21 '12 at 19:58

Ofer Zelig

17,068
9
59
93

:sigh: yet another level deep of using to run a simple query. We've already got one for the SqlCommand, one for the transaction, one for the reader, and one for the loop reading records. I guess if it's what we've gotta do it's what we've gotta do, but it seems painful to be doing this every time. – Billy ONeal Mar 21 '12 at 20:02
@BillyONeal depends on what API you use to access the database. I recommend you check out [Dapper](http://www.google.ca/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&ved=0CCsQFjAA&url=http%3A%2F%2Fcode.google.com%2Fp%2Fdapper-dot-net%2F&ei=PjRqT76cMqLf0QGBwozxAQ&usg=AFQjCNHDo-0KnatIhtMQ4pvQ0QLhup59Wg). Extremely simple. – D'Arcy Rittich Mar 21 '12 at 20:04
@BillyONeal: You can stack using statements... If I have multiple, I avoid putting a brace on the next line. Makes it a lot cleaner. – Bryan Crosby Mar 21 '12 at 20:04
1

That's the way to go... Better than handling `Dispose` yourself. We're programmers. One more level of curly brackets doesn't need to scare us. – Ofer Zelig Mar 21 '12 at 20:05
@BryanCrosby: Only if you don't have things that have to be done in between the statements. Like setting `SqlCommand.CommandText`. – Billy ONeal Mar 21 '12 at 20:05

score 1 · Answer 3 · answered Mar 21 '12 at 20:00

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?

Both, actually.

The web server is multi threaded, so each thread needs its own database connection. Just create one when needed.

The actual connections to the database are pooled. When you dispose a connection object, the actual connection isn't closed, but returned to the pool. If you create a new connection object with the same connection string, it will just reuse a connection from the pool.

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

3 Answers3

Linked