Connection pool with ADO.NET and Sqlconnection Dispose, what is correct to do?

Question

I asked a question yesterday about What is the difference between Idisposable implementations?

I was implementing a class to help obtain instances of SqlConnection, SqlCommand, SqlDataReader methods ExecuteSql etc. And when implementing the interface IDisposable I’ve seen some things I don’t know about memory management on . Net.

It also reminded me of one time that I was implementing a class for the same purpose and that I ended up including in Connection String the option Pooling=false because he was having problems performing the method Dispose of the instances of SqlConnection even after the actual end of the request cycle.

When I did that I had a sort of class:

public class DbConnection : IDisposable
{
    private SqlConnection connection;

    public DbConnection()
    {
        connection = new SqlConnection("string de conexão");
    }

    #region outros métodos ...

    ~DbConnection()
    {
        Dispose();
    }

    public void Dispose()
    {
        if (connection != null && connection.State == ConnectionState.Open)
        {
            connection.Dispose();
            connection = null;
        }
        GC.SuppressFinalize(this);
    }
}

This is also similar to the other issue I created.

It was researching that I found comments on the Pool of Connections and that when using Dispose() explicitly of SqlConnection that we could be causing confusion in the control of the Connection Pool. Finally, that a solution would be to disable the Connection Pool.

Enabling the Connection Pool is beneficial for application performance?

Allowing the Connection Pool, which is the correct way to manage instances of SqlConnection, not effecting the Dispose()?

By default Sqlconnection allows only one Sqldatareader per instance of Sqlconnection, right, to get more than one you need to add the option MultipleActiveResultSets=True; in Connection String, this causes performance loss in the application?

Answer 1

Dude I have a very optimized and good class that I use in the projects here, using Generics you can even choose which type of Data Access Class you want to use, I did even performance tests:

private static readonly string ConnectionString = ConfigurationManager.AppSettings["SqlServerConnection"];

private static DataTable Read<TS, T>(string query) where TS : IDbConnection, new() where T : class, IDbDataAdapter, IDisposable, new()
{
    using (var conn = new TS())
    {
        using (var da = new T())
        {
            using (da.SelectCommand = conn.CreateCommand())
            {
                da.SelectCommand.CommandText = query;
                da.SelectCommand.Connection.ConnectionString = ConnectionString;
                var ds = new DataSet();
                da.Fill(ds);
                return ds.Tables[0];
            }
        }
    }
}

You can use it like this:

var result = Read<SqlConnection, SqlDataAdapter>("SELECT * FROM [SuaTabela]");

No need to worry about Dispose, as the memory is managed and released after the method call using the using { }