AdsConnection not closed?

Question

Consider the following code:

private static void GetData<TConnection, TCommand>( string connectionString, DataTable dataFromDbf, string commandText )
  where TConnection : IDbConnection
  where TCommand : IDbCommand {
  using( IDbConnection oConn = GetConnection<TConnection>( connectionString ) ) {

    oConn.Open( );
    IDbCommand oCmd = oConn.CreateCommand( );
    oCmd.CommandText = commandText;
    dataFromDbf.Load( oCmd.ExecuteReader( ) );
    oConn.Close( );
  }
}

The method is generic because not all customers have a Adavatage DB server and we need to fall back to an OleDbConnection. In most cases however, we use an AdsConnection object (from Advantage.Data.Provider.dll).

The code above closes the connection and the object gets disposed when exiting the using statement.

When we use the Advantage Management Utility to view all connections on the Ads server, we notice that the connections stay open. But not all! We call the method maybe 200 times and only a few stay open, sometimes exceeding the clients MAX_CONNECTIONS. We can work around that, thanks to Jens Mühlenhoff.

Question is:

Why doesn't the connection close when calling the oConn.Close()? Anybody any ideas?

Answer 1

Do you use connection pooling?

According to the ADS online help it's possible that Close() doesn't really close the connection, but returns it to the connection pool.

In that case you either disable pooling or call FlushConnectionPool(). Beware that this flushes the connection pool for the entire application.

http://devzone.advantagedatabase.com/dz/webhelp/Advantage10.1/dotnet_adsconnection_close.htm

http://devzone.advantagedatabase.com/dz/webhelp/Advantage10.1/dotnet_adsconnection_flushconnectionpool_.htm