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

问题描述

我想知道什么是最好的方法用打开 SqlConnection Sql Server 2008R2 Express Edition 数据库。此版本的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 方法

Here is CheckConnectionStatus Method

    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.

打开和关闭是租用并返回到客户端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.

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