我在一个C＃函数适当地关闭这个SQL连接？ [英] Am I closing this SQL connection in a C# function appropriately?

问题描述

尝试关闭我的问题对连接保持打开和超过最大池，我正在尝试重写用于连接到我们的数据库的函数。

In an attempt to close my question on connections remaining open and exceeding the maximum pool, I'm trying tor rewrite the function that is used to connect to our database.

该函数存在于本地编译的库中。使用反射器我可以看到代码看起来像这样：

The function exists within a homegrown compiled library. using reflector I can see the code looks like this:

public SqlProvider([Optional, DefaultParameterValue("")] string StrConnection)
{
    string str;
    if (StrConnection == "")
    {
        str = ConfigurationSettings.AppSettings["ConStr"];
    }
    else
    {
        str = StrConnection;
    }
    SqlConnection connection = new SqlConnection(str);
    connection.Open();
    this.MyCommand = new SqlCommand();
    SqlCommand myCommand = this.MyCommand;
    myCommand.Connection = connection;
    myCommand.CommandType = CommandType.Text;
    myCommand = null;
    this.MyDataAdapter = new SqlDataAdapter(this.MyCommand);
    this.MyCommandBuilder = new SqlCommandBuilder(this.MyDataAdapter);
    this.MyDataSet = new DataSet();
}

我打算修改此读取

public SqlProvider([Optional, DefaultParameterValue("")] string StrConnection)
{
    string str;
    if (StrConnection == "")
    {
        str = ConfigurationSettings.AppSettings["ConStr"];
    }
    else
    {
        str = StrConnection;
    }

    using (SqlConnection connection = new SqlConnection(str))
    {
        connection.Open();
        this.MyCommand = new SqlCommand();
        SqlCommand myCommand = this.MyCommand;
        myCommand.Connection = connection;
        myCommand.CommandType = CommandType.Text;
        myCommand = null;
        this.MyDataAdapter = new SqlDataAdapter(this.MyCommand);
        this.MyCommandBuilder = new SqlCommandBuilder(this.MyDataAdapter);
        this.MyDataSet = new DataSet();
    }
}

，然后重新编译dll。
假定 SQLProvider（）的实例通常在公共类的顶部创建，然后在类成员中使用该实例（例如：

and then recompiling the dll. Given that an instance of SQLProvider() is typically created at the top of a public class, and then that instance is used within class members (eg:

public class Banner
{
    DSLibrary.DataProviders.SqlProvider db = new DSLibrary.DataProviders.SqlProvider(Defaults.ConnStr);
    public Banner()
    {    
    }

    public DataTable GetBannerImages(string bannerLocation,int DeptId)
    {
        using (DSLibrary.DataProviders.SqlProvider db = new DSLibrary.DataProviders.SqlProvider(Defaults.ConnStr))
        {
            DataTable dt = new DataTable();

            //Add Parameter @BannerLocation for Banner of Specific Location 
            //Call proc_getBannerImages Stored procedure for Banner Images
            db.AddStoredProcParameter("@BannerLocation", SqlDbType.VarChar, ParameterDirection.Input, 100, bannerLocation);
            db.AddStoredProcParameter("@DeptId", SqlDbType.Int, ParameterDirection.Input, 0, DeptId);
            dt = db.ExecuteStoredProcedure("proc_getBannerImages");
            return dt;
        }
    }
}

正确的方法？在我看来，连接将在数据被实际检索之前被处理。此外，Visual Studio告诉我 SQLProvider（）必须隐式转换为 System.IDisposable - 我将如何去实现这个？

am I going about this the right way? It seems to me the connection will be disposed of before the data has actually been retrieved. Also, Visual Studio tells me that SQLProvider() must be implicitly convertible to System.IDisposable - how would I go about implementing this?

我试着用 class Banner 但是intellisense然后在类，结构或接口成员声明中显示一个Invalid tokenusing'in the class，struct，or interface member declaration错误。

I tried wrapping all the members of class Banner in a using (DSLibrary.DataProviders.SqlProvider db = new DSLibrary.DataProviders.SqlProvider(Defaults.ConnStr)){} statement but intellisense then displays a "Invalid token 'using' in class, struct, or interface member declaration" error.

这是最好的方法是什么？

What is the best way to go about this?

UPDATE
我试过反汇编调整和重新编译的DSLibrary，但正如CHris_Lively所说，我认为对我没有什么。更改有问题的实例到我猜想是一个更标准的格式到目前为止：

UPDATE I've tried disassembling adjusting and recompiling the DSLibrary, but as CHris_Lively says, I thinkit's doing nothing for me. Changing the instance in question to what I preceive to be a more standard format works so far:

public DataTable GetBannerImages(string bannerLocation,int DeptId)
{
    using (SqlConnection conn = new SqlConnection(Defaults.ConnStr))
    {
        SqlCommand cmd = new SqlCommand("proc_getBannerImages", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@BannerLocation", bannerLocation));
        cmd.Parameters.Add(new SqlParameter("@DeptId", DeptId));

        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        DataTable dt = new DataTable();

        da.Fill(dt);
        return dt;
    }
}

我将要查看Enterprise库，似乎可能是向前的方式。

I'm about to look into the Enterprise library, seems like it might be the way forward.

推荐答案

但是，有几个问题。

首先，它看起来像整个DSLibrary不买你任何东西。

First, it looks like that whole DSLibrary isn't buying you anything at all.

进行数据访问时，您通常希望在获取连接和执行命令时采用相同的功能。你的方法应该只返回操作的结果。这样，您可以干净地使用连接，命令和阅读器的IDisposable接口。

When doing data access you typically want to structure it where acquiring the connection and executing the command are in the same function. You're methods should only return the result of the operation. This way you can cleanly use the IDisposable interface of the connection, command, and reader.

以下示例使用 Enterprise Library 。注意，Db没有using子句。它不实现IDisposable。相反，当命令超出范围时，命令负责释放连接。

The following example uses Enterprise Library. Note that the Db doesn't have a using clause. It doesn't implement IDisposable. Instead, the command is responsible for letting go of the connection when it goes out of scope:

    public static DataTable GetBannerImages(String bannerLocation, Int32 departmentId)
    {
        DataTable result = new DataTable();
        result.Locale = CultureInfo.CurrentCulture;

        Database db = DatabaseFactory.CreateDatabase("NamedConnectionStringFromConfig");

        using (DbCommand dbCommand = db.GetStoredProcCommand("proc_getBannerImages"))
        {
            db.AddInParameter(dbCommand, "BannerLocation", DbType.String, bannerLocation);
            db.AddInParameter(dbCommand, "DeptId", DbType.Int32, departmentId);

            using (IDataReader reader = db.ExecuteReader(dbCommand))
            {
                SopDataAdapter dta = new SopDataAdapter(); // descended from DbDataAdapter

                dta.FillFromReader(result, reader);
            } // using dataReader
        } // using dbCommand

        return result;
    } // method::GetBannerImages

您可能已经有一些东西会转换读者到一个数据表，如果不只是看看子类的System.Data.Common.DbDataAdapter类

You probably already have something that will convert a reader to a datatable, if not just look into subclassing the System.Data.Common.DbDataAdapter class

我已经取得了巨大的成功与企业库。它是快速，高效，当我走这条路线我从来没有内存泄漏或数据库连接问题。

I've had tremendous success with the Enterprise Library. It's fast, efficient, and when going this route I've never had memory leaks or db connection issues.

这篇关于我在一个C＃函数适当地关闭这个SQL连接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！