CODEWITHSUNDEEP
sql-serverado.netconnection-poolingdatareaderconnection-timeout
Abe
Abe

Reputation: 6514

Does the SQL connection not get closed if you put the datareader in a using block?

So, I recently inherited a large project that uses the following data access pattern; unfortunately, this is resulting in a massive number of timeout exceptions related to connection pooling.

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached"

It clear that the connections are leaking and not getting closed properly. So, the framework has a DataAccess class with the method GetDataReader.

When the data reader is referenced, it is placed inside a using block, but connections are still leaking.

Does the fact that the connection is not explicitly closed or placed in a using block the reason why the connections are getting leaked?

Normally, I would wrap the connection in a using block AND wrap the data reader in a using block.

Obviously, this framework is very flawed, but would somehow using the option CommandBehavior.CloseConnection for the data reader resolve this issue?

None the external code accesses the SqlConnection directly and has to go through this DataAccess class.

public IDataReader GetDataReader(QueryDto dto)
{
    DateTime current = DateTime.Now;
    Database db = DatabaseFactory.CreateDatabase(dto.DatabaseName);

    DbCommand cmd = db.GetStoredProcCommand(dto.StoredProcedureName);

    if (dto.Params.Length > 0)
    {
        cmd = db.GetStoredProcCommand(dto.StoredProcedureName, dto.Params);
    }

    dto.Command = cmd;

    cmd.CommandTimeout = dto.Timeout;
    cmd.Connection = db.CreateConnection();

    try
    {
        cmd.Connection.Open();
    }
    catch (SqlException ex)
    {
        // Handle Exception here...
        throw;
    }

    return rdr;
}

Usage in some static repository class:

var query = new QueryDto
{
    DatabaseName = "SomeDatabase",
    Params = parms,
    StoredProcedureName = "StoredProcedureName"
};

using (IDataReader dr = dataAccess.GetDataReader(query))
{
    while (dr.Read())
    {
        // do stuff here
    }
}

Upvotes: 0

Views: 319

Answers (1)

Brian Maupin
Brian Maupin

Reputation: 745

I think your problem is that the using statement is around a function that has open resources embedded in it. The using will not dispose of the connection that is opened inside GetDataReader. I think your are correct that the Connection itself needs to be in a using block. The using statement only calls Dispose on the object that is passed in, not any nested resources.

Upvotes: 1

Related Questions