c# - 应该如何维护 ASP.NET MVC 应用程序中的数据库连接？

Question

我没有在 Web 应用程序中使用 LINQ-to-SQL 或 Entity Framework 位，并且目前一直在使用类似这样的东西(这是一个类项目):

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

然而，这似乎会导致线程问题，因为静态初始化程序每个服务器进程运行一次，而不是每个请求运行一次。

框架是否提供了一个内置的方法来处理这个问题，或者我是否应该有一个函数来处理每次新的数据库连接？

Answer 1

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

是的，这样做。让ADO.NET connection pooling为您处理细节。您的目标应该是保持连接打开的时间尽可能短。

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.

因此，创建一个返回新打开连接的静态 GetConnection() 方法。在 using 语句中使用它，以便它可以尽快关闭并返回到连接池。

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