如果您重复使用的SqlConnection，SqlDataAdapter的，和的SqlCommand对象？ [英] Should you reuse SqlConnection, SqlDataAdapter, and SqlCommand objects?

问题描述

我与写入类似下面的代码布局的DAL对象的工作。我简化了很多代码，代码只是显示设置

I'm working with a DAL object that is written in a layout similar to the following code. I simplified a lot of the code code just to show the setup.

public class UserDatabase : IDisposable
{
    private SqlDataAdapter UserDbAdapter;
    private SqlCommand UserSelectCommand;
    private SqlCommand UserInsertCommand;
    private SqlCommand UserUpdateCommand;
    private SqlCommand UserDeleteCommand;

    private System.Data.SqlClient.SqlConnection SQLConnection; 

    public UserDatabase()
    {
        this.SQLConnection = new System.Data.SqlClient.SqlConnection(ConnectionString);
        this.UserDbAdapter= new SqlDataAdapter(); 
        this.UserDbAdapter.DeleteCommand = this.UserDeleteCommand;
        this.UserDbAdapter.InsertCommand = this.UserInsertCommand;
        this.UserDbAdapter.SelectCommand = this.UserSelectCommand;
        this.UserDbAdapter.UpdateCommand = this.UserUpdateCommand;
    }

    private bool FillUsers(DataSet UserDataSet, out int numberOfRecords)
    {
        bool success = true;

        numberOfRecords = 0;
        string errorMsg = null;

        this.UserDbAdapter.SelectCommand = this.GetUsersSelectCommand();

        numberOfRecords = UserDbAdapter.Fill(UserDataSet, UsersTableName);

        return success;
    }

    private SqlCommand GetUserSelectCommand()
    {
        if (this.UserSelectCommand==null)
            this.UserSelectCommand= new System.Data.SqlClient.SqlCommand();
        this.UserSelectCommand.CommandText = "dbo.Users_Select";
        this.UserSelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
        this.UserSelectCommand.Connection = this.SQLConnection;
        this.UserSelectCommand.Parameters.Clear();
        this.UserSelectCommand.Parameters.AddRange(new System.Data.SqlClient.SqlParameter[] {
        new System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Variant, 0, System.Data.ParameterDirection.ReturnValue, false, ((byte)(0)), ((byte)(0)), "", System.Data.DataRowVersion.Current, null)});

        return UserSelectCommand;
    }

有是一些用同样的方式重复使用连接其他多个填充类型的功能对象，SqlCommands和SqlDataAdapter的。该SqlDataAdapter的在内部管理中的SqlConnection的开幕式和闭幕式。

There are multiple other Fill type functions that are written the same way reusing the Connection object, SqlCommands, and SqlDataAdapter. The SqlDataAdapter manages opening and closing of the SqlConnection internally.

所以我的问题是多部分。这是设计不好？ ？如果是这样，为什么

So my question is multipart. Is this design bad? If so, why?

如果它是坏的，应该把它改为保持事物更本地范围如下所示：

If it is bad, should it be changed to keeping things in a more local scope like the following:

    public bool FillUsers(DataSet UserDataSet)
    {
        using (SqlConnection conn = new SqlConnection(ConnectionString))
        {
            using (SqlCommand command = GetUsersSelectCommand())
            {
                using (SqlDataAdapter adapter = new SqlDataAdapter(command, conn))
                {
                    adapter.Fill(UserDataSet, UsersTableName);
                }
            }
        }
    }

这将对所有这似乎是创建，处理，然后改造会比保持周围的物品更糟糕的功能来完成。然而，这似乎是设置我看到网上随处可见。

This would have to be done for all the functions which seems like creating, disposing, and then remaking would be worse than keeping the items around. However this seems to be the setup I see everywhere online.

推荐答案

没有，没有什么不对的。你应该配置你的对象，一旦你与他们所做的实施的IDisposable 。

No, there isn't anything wrong with that. You should dispose your objects that implement IDisposable as soon as you are done with them.

给定一个的SqlConnection ，在废弃的连接，底层的连接将简单地返回到池中。它不一定是封闭像你想象的。最好是让连接池去做的工作。 这里是ADO.NET连接池MSDN上的链接。试图让它做的事情是不是为设计（有些人把这种优化，令人惊讶的）通常是下跌的兔子洞之旅。

Given a SqlConnection, when you dispose of the connection, the underlying connection will simply be returned to the pool. It's not necessarily "closed" as you might think. It's best to let the connection pool do it's job. Here is a link on MSDN to ADO.NET connection pooling. Trying to make it do things it wasn't designed for (some people call this optimizing, surprisingly) is usually a trip down the rabbit hole.

此外，请确保您实际上测量并试图对其进行优化之前观察到的一个问题。 （我不恶劣的方式是说这个，只有节省您的时间）。

Also, make sure you have actually measured and observed a problem before trying to optimize it. (and I don't mean this in a harsh way, only to save you time).

这篇关于如果您重复使用的SqlConnection，SqlDataAdapter的，和的SqlCommand对象？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！