在 C# 中打开/关闭 SqlConnection 的最佳方法是什么 [英] What is Best Approach for Opening/Closing SqlConnection in C#

问题描述

我想知道用 Sql Server 2008R2 Express Edition 数据库打开 SqlConnection 的最佳方法是什么.这个版本的 Sql 有 RAM 使用和 CPU 使用的限制，所以我们必须采用最好的方法来打开 SqlConnection.

I would like to know what could be best approach to open a SqlConnection with Sql Server 2008R2 Express Edition Database. This Version of Sql has Limitations of RAM Usage and CPU Usage so we must adopt something best to open a SqlConnection.

现在我正在检查每个方法的开始和结束的连接.这是一个例子.

Right Now i am Checking Connection on Start and End of each and every Method. Here is an example of that.

   private void CheckValidId(string Id)
    {
        CheckConnectionStatus();

        try
        {
            sqlConnection.Open();
            sqlCommand = new SqlCommand("select * from ValidId where id=@id", sqlConnection);
            sqlCommand.Parameters.AddWithValue("@id", Id);
            sqlDataReader = sqlCommand.ExecuteReader();
            While (sqlDataReader.Read())
            {
               string Test = sqlDataReader["Id"].toString();
               MessageBox.Show("Value of Id : " , Test);
            }
        }
        catch (Exception exp)
        {
            MessageBox.Show(exp.Message.ToString(), "Exception in CheckValidId");
        }
        finally
        {
            CheckConnectionStatus();
        }
    }

这里是CheckConnectionStatus方法

    private void CheckConnectionStatus()
    {
        if (sqlConnection.State == ConnectionState.Open)
        {
            sqlConnection.Close();
        }
    }

执行此操作的最佳方法是什么.

What is best approach to perform this operation.

谢谢

推荐答案

您的打开和关闭模式是正确的.但是您必须注意，这不会打开和关闭与 SQL Server 的连接，因此并不能真正解决您对内存使用和 CPU 的担忧 - 事实上它不会产生任何影响.

Your pattern for open and close is correct. However you must note that this doesn't open and close the connection to the SQL Server so doesn't really address your concerns over memory usage and CPU - in fact it wont make any difference.

Open 和 Close 的作用是租用连接并将连接返回到客户端 PC 上的 ADO 连接池.这意味着不保证关闭 ADO 连接(并且在大多数情况下不会)关闭并释放与 SQL Server 的连接.这是因为建立和验证连接的成本相对较高且速度较慢，因此 ADO 连接池将您的连接保持在一个池中，仍然处于打开状态，以防万一您想重新建立连接.

What Open and Close does is lease and return a connection to the ADO Connection Pool on the client PC. This means that Closing an ADO connection is not guaranteed (and in most cases will not) close and release the connection to the SQL Server. This is becasue establishing and authenticating a connection is relatively expensive and slow, so the ADO connection pool keeps your connections in a pool, still open, just in case you want to re-establish a connection.

SQL Server 的不同之处在于它需要执行的并发查询数量 - 以及查询的数据集大小，以及数据库中数据的总大小.

What makes the difference to SQL Server is the number of concurrent queries it needs to execute - and the dataset size of the queries, and the total size of the data in the database.

并发查询会占用 CPU，而返回的数据集会占用可用的 RAM.显然，您的数据库越大，RAM 中的缓存就越少，因此您在查询时获得缓存命中的可能性就越小.

Concurrent queries squeeze CPU, and the datasets returned squeeze the RAM available. Obviously the bigger your database the less can be cached in RAM and so the less likely you are to get a cache hit when querying.

在实践中，我使用 SQL Express 版本的经验是，除非您正在做一些非常具体的事情，否则您不会注意到它与 SQL Server 完整版之间的任何区别；

In practice my experience with SQL Express editions is that you wont notice any difference between it and the full edition of SQL Server unless you are doing some very specific things;

1) 编写允许用户构建用户定义或用户范围查询的 BI 风格工具.2) 编写糟糕的 SQL——大 SQL"可能会掩盖你糟糕的查询语法，但 Express 无法做到，因为它可用的 RAM 较少.

1) Writing a BI style tool which allows the user to construct user-defined or user-scoped queries. 2) Writing terrible SQL - "big SQL" may mask your bad query syntax, but Express wont be able to because it has less available RAM to play with.

如果您编写高效、受约束的 SQL，您可能实际上不会遇到 SQL Express 的任何限制.

If you write efficient, constrained SQL, you probably wont actually ever hit any of SQL Express's limitations.

这篇关于在 C# 中打开/关闭 SqlConnection 的最佳方法是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！