我是不是在关闭一个适当的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();
}

我打算修改这一读

I'm planning on amending this to read

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?

我试过包装的类横幅所有成员在使用（ DSLibrary.DataProviders.SqlProvider DB =新DSLibrary.DataProviders.SqlProvider（Defaults.ConnStr））{} 语句，但智能感知然后显示无效令牌类'使用'，结构或接口成员声明错误。

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?

更新
我试着拆卸调整和重新编译DSLibrary，但作为CHris_Lively说，我信利的无为我。有问题的情况下改变什么，我preceive是一个比较标准格式的作品至今：

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;
    }
}

我要看看企业库，好像它可能是前进的方向。

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

推荐答案

您正在接近。然而，一对夫妇的问题。

You're close. However, a couple issues.

首先，它看起来像整个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.

下面的例子使用的企业库。需要注意的是分贝不具有使用条款。它没有实现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

您可能已经拥有的东西，将转换成一个阅读器到DataTable，如果不只是考虑继承了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

我已经与企业库巨大的成功。它的快速，高效，走这条路我从来没有内存泄漏或DB连接问题时。

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屋！