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

Question

I do not use LINQ-to-SQL or Entity Framework bits in a web application and currently use 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 problems, since the static initializer is run once per server process, and not once per request.

Does the infrastructure provide a built-in method for handling this, or should I have a function that rewinds database connections each time?

Answer 1

or do I need to have a function that overflows database connections each time?

Yes, do it. Let ADO.NET connection pooling process the data for you. Your goal should be to keep the connection open as short as possible.

The connection pool reduces the number of new connections that must be opened. The machine gun retains ownership of the physical connection. It manages connections, maintaining a live set of active connections for each connection configuration. Whenever a user calls Open when connected, the pool searches for an available connection in the pool. If a merged connection is available, it returns it to the caller instead of opening a new connection. When application calls close the connection, the pool will return it to the combined set of active connections instead of closing it. Once the connection returns 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 in your using statement so that 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 using . They are not created from scratch; they are retrieved from the connection pool . No execution penalty. Actually, that is the best and right way to go.

See my answer about using : stack overflow

Answer 3

Does the infrastructure provide a built-in method for handling this, or should I just have a function that coughs new'd database connections every time?

Both, actually.

The web server is multi-threaded, so each thread requires its own connection to the database. Just create it when necessary.

Actual database connections are merged. When you delete a connection object, the actual connection is not closed, but returned to the pool. If you create a new connection object with the same connection string, it simply reuses the connection from the pool.