如何解决这个code SQL连接泄漏？ [英] How to fix SQL connection leaks in this code?

问题描述

我得到这个错误在我的网站：

I am getting this error on my website:

超时过期。超时时间已过获取之前
  从池中的连接。出现这种情况可能是因为所有池
  连接正在使用，并且达到最大池大小。

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

说明：在执行过程中发生未处理的异常
  当前Web请求。有关详情，请堆栈跟踪
  有关错误的信息以及它起源于code。

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

异常详细信息：System.InvalidOperationException：超时过期。
  超时时间已过获取从一个连接之前
  池。出现这种情况可能是因为所有池连接均在
  使用，达到最大池大小。

Exception Details: System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

这是正在到处重复使用C＃类（我认为泄漏是在这里）：

This is the C# class that is being reused everywhere (I think the leak is here):

public class Generator
{

SqlConnection cn = null; // new SqlConnection(connectionString);

    public SqlConnection Connection {
        get {
            if (cn == null) {
            cn = new SqlConnection("Server=xxxxx,1433;Database=xxxxx;User ID=xxxxx;Password=xxxxx;Trusted_Connection=False;Encrypt=True;");
            }
            if(cn.State != ConnectionState.Open)
                cn.Open();

            return cn;
        }
    }

}

然后在我的方法，我用这样的：

Then in my methods I use it like this:

        var cmd = _generator.Connection.CreateCommand();
        cmd.CommandText = "SELECT * FROM SomeTable";
        var reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            //DO SOMETHING
        }
        reader.Close();

编辑：在类的我的方法我尝试共享像这样跨越多个方法相同的SQL连接：

In the class of my methods I attempt to share the same SQL connection across multiple methods like this:

private Generator _generator;

public HomeController()
{
    InitializeConnection();
}

private void InitializeConnection()
{
    _generator = new Generator();
}

有人能看到我怎么能修复泄漏或者是什么原因造成我的最大池问题？

Can anyone see how I can fix the leak or what is causing my max pool issue?

推荐答案

您需要使用块，以确保物品设置。

You need using blocks to make sure the objects are disposed.

using (var cmd = _generator.Connection.CreateCommand()) {
    cmd.CommandText = "SELECT * FROM SomeTable";
    using (var reader = cmd.ExecuteReader()) {

            while (reader.Read())
            {
                //DO SOMETHING
            }
    }
}

我也不会用连接属性。这是更好地打开，并根据需要紧密的联系，并允许连接池来完成其工作。

I also wouldn't use a Connection property. It's better to open and close connections as needed and allow connection pooling to do its job.

这篇关于如何解决这个code SQL连接泄漏？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！