Which template is better for the SqlConnection object?

Question

Which template is best for the object SqlConnection? What is better in performance? Do you offer any other template?

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()
    {            
    }
}

Answer 1

Offer to go to DataAccess2. This is a personal preference. Some may even offer your class static. It would be difficult to say that he is more perfect than another. You are on the way IDisposable, which is great.

I would be happy to read and keep both styles shown above in your question.

Consider that your DAL will be able to read the connection string from .config, and not only allow the transfer of values ​​in the constructor.

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())
    {

    }
}

Answer 2

. , , . , DataAccess ( , , - ?), .

, DataAccess ; , .

Answer 3

, , DataAccess, "using", .

, sql Execute, , DataAccess.

, sql , , , , .

Answer 4

, . , .

I agree with the above statements that it depends on the use case to solve the problem associated with the first. I have a shell that should use such a template, so I set the value of the boolean field to show that the command is being executed on the connection, and then the "queue" of the next command to execute.

Of course, there will be situations where you can use multiple connections ...