c# - 哪种模式适合SqlConnection对象？

哪种模式更适合SqlConnection对象？哪个表现更好？
你们提供其他图案吗？

class DataAccess1 : IDisposable
{
    private SqlConnection connection;

    public DataAccess1(string connectionString)
    {
        connection = new SqlConnection(connectionString);
    }

    public void Execute(string query)
    {
        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandText = query;
            command.CommandType = CommandType.Text;
            // ...

            command.Connection.Open();
            command.ExecuteNonQuery();
            command.Connection.Close();
        }
    }

    public void Dispose()
    {
        connection.Dispose();
    }
}

VS

class DataAccess2 : IDisposable
{
    private string connectionString;

    public DataAccess2(string connectionString)
    {
        this.connectionString = connectionString;
    }

    public void Execute(string query)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = connection.CreateCommand();
            command.CommandText = query;
            command.CommandType = CommandType.Text;
            // ...

            command.Connection.Open();
            command.ExecuteNonQuery();
            command.Connection.Close();
        }
    }

    public void Dispose()
    {
    }
}

最佳答案

建议使用DataAccess2。不过这是个人喜好。有些甚至可能建议您的班级是static。很难说一个比另一个更出色。您正在使用IDisposable的路径，这很棒。

我很乐意阅读并维护上面在您的问题中显示的两种样式。

考虑让您的DAL也能够从.config读取连接字符串，而不是专门允许在构造函数中传递值。

public DataAccess2(string connStr)
{
    this.connectionString = connStr;
}
public DataAccess2()
{
    this.connectionString =
            ConfigurationManager.ConnectionStrings["foo"].ConnectionString;
}

也考虑将SqlCommand包装在using中。

using (var conn = new SqlConnection(connectionString))
{
    using(var cmd = conn.CreateCommand())
    {

    }
}