如何在 C# 中保持单个 SQL Server 连接实例为多个请求打开? [英] How to keep single SQL Server connection instance open for multiple request in C#?

问题描述

我有一个 Web API，其中包含 C# 中的数据库插入逻辑 (ado.net).当多个用户(例如 100 个用户)调用 Web API 时，每次为多个请求打开和关闭 SQL Server 连接时.它会降低性能.

I have a Web API which contains database insert logic (ado.net) in C#. When multiple users (e.g. 100 users) call the Web API, every time a SQL Server connection is opened and closed for multiple requests. It slows down performance.

如何让单个 SQL Server 连接同时处理多个请求?我必须保持 SQL 连接只打开一次并在一段时间后关闭，以便在此期间它应该考虑多个请求并在数据库中插入记录.

How can I keep a single SQL Server connection live for multiple requests? I have to keep SQL connection open only once and close after some time so that during that time it should consider multiple request and insert records in database.

请提出建议.

推荐答案

ADO.NET 的 SqlConnection 正在实现一个连接池.这意味着当您关闭或释放 SqlConnection 的实例时，底层连接只是返回到池中.当 SqlConnection 的另一个实例打开，并且连接池中有可用的连接时，将使用该连接.
事实上，SQL 上的 Microsoft 文档页面服务器连接池明确指出:

ADO.NET's SqlConnection is implementing a connection pool. This means that when you close or dispose an instance of SqlConnection, the underlying connection simply returns to the pool. When another instance of SqlConnection is opened, and a connection is available in the connection pool, that connection will be used.
In fact, Microsoft docs page on SQL Server Connection Pooling clearly states:

注意
我们强烈建议您在使用完连接后始终关闭连接，以便将连接返回到池中.您可以使用 Connection 对象的 Close 或 Dispose 方法，或者通过在 C# 中的 using 语句或 Visual Basic 中的 Using 语句中打开所有连接来执行此操作.未明确关闭的连接可能不会添加或返回到池中.有关详细信息，请参阅使用语句或如何:处置 Visual Basic 的系统资源.

Caution
We strongly recommend that you always close the connection when you are finished using it so that the connection will be returned to the pool. You can do this using either the Close or Dispose methods of the Connection object, or by opening all connections inside a using statement in C#, or a Using statement in Visual Basic. Connections that are not explicitly closed might not be added or returned to the pool. For more information, see using Statement or How to: Dispose of a System Resource for Visual Basic.

这意味着使用 SqlConnection 的最佳实践方式是这样的:

This means that the best practice way of using SqlConnection is this:

using(var con = new SqlConnection(connectionString))
{
    // your sql stuff goes here...
}

BTW，SqlCommand，SqlDataReader 和 SqlDataAdapter 也实现了 IDisposable 接口，所以它们也需要在 using 语句的上下文中使用:

BTW, SqlCommand, SqlDataReader and SqlDataAdapter also implements the IDisposable interface, so they too needs to be used in the context of the using statement:

using(var con = new SqlConnection(connectionString))
{
    using(var cmd = new SqlCommand(sql, con))
    {
        // prepare command here - parameters and stuff like that

        // either
        using(var reader = cmd.ExecuteReader())
        {

        }

        // or 
        using(var adapter = new SqlDataAdapter(cmd))
        {

        }

    }
}

这篇关于如何在 C# 中保持单个 SQL Server 连接实例为多个请求打开?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！