Which pattern is better for SqlConnection object?

Question

Which pattern is better for SqlConnection object? Which is better in performance? Do you offer any other pattern?

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

There's no real way to answer this question. The short, canonical answer is that the connection should stay alive for the lifetime of your unit of work. Because we have no way of knowing how DataAccess is used (does it exist for the lifetime of your application, or do you instantiate it and dispose it whenever you do something?), it's impossible to give a concrete answer.

That being said, I would recommend the first pattern, but instantiate and dispose of your DataAccess object as needed; don't keep it around longer than necessary.