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

Question

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

Navigation

#1 by (2 votes)

6

Yesterday I asked a question yesterday about What is the difference between implementations of IDisposable?

I was implementing a class to help get instances of SqlConnection , SqlCommand , SqlDataReader methods ExecuteSql etc. And when I implemented the IDisposable interface I saw some things I do not know about memory management in .Net.

This also reminded me of the fact that I was implementing a class for the same purpose and that I ended up including the Connection String option with Pooling=false because I was having problems doing the Dispose of instances of SqlConnection even after the actual completion of the request cycle.

When I did this I had a class something like this:

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 similar to the other question I created.

I was researching that I found comments about this Connection Pool and that by using Dispose() explicitly SqlConnection we could be causing confusion in the Connection Pool control. Finally, one solution would be to disable the Connection Pool.

Allow connection pooling beneficial to application performance?

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

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

sql-server c# garbage-collector ado.net

asked by anonymous 24.06.2015 / 16:26

1 answer

Capture position of joints on X, Y, Z axes with Kinect V2 How to get the correct HTTP status?

score 2 · Answer 1

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 even did 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 this:

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

You do not have to worry about Dispose because memory is managed and released after calling the method using using { }