Microsoft Enterprise DAAB and Closing Connections

Question

I noticed one of our internal apps was using DAAB, and that it appears to have some sort of connection leak. I thought I remember reading somewhere that when you used the following code:

Database db = DatabaseFactory.CreateDatabase("ConnectionString");
IDataReader rdr = db.ExecuteReader("sproc")

That the connection was closed for you, so I investigated and it doesn't look to be the case. I'm not too familiar with DAAB, does anyone know for sure what happens and when? Also, the same app has a bunch of classes like:

public class Example
{
   private Database db;
   public Example ()
   {
      db = DatabaseFactory.CreateDabase("ConnectionString");
   }

   public void Update(object o)
   {
      try
      {
         db.ExecuteNonQuery("sproc", o.parameter);
      }
      catch...
      }
      }
   }
 }

This at first seems kind of cringeworthy, but like I said I'm not familiar with DAAB. Could these classes be causing the leak if the connections aren't closing themselves within DAAB?

Answer 1

I'm investigating a leak too using DAAB on an Oracle database - everything seems Ok when using the Microsoft driver but I'm seeing problems when using the Oracle Odp.net provider. Internally the DAAB uses the CommandBehavior enum to control the connection lifetimes for ExecuteReader methods, but something seems not to be right somewhere with the ExecNonQuery methods.

Answer 2

Have you tried disposing the data reader? I always do this and haven't had any lingering connections.

Database db = DatabaseFactory.CreateDatabase("ConnectionString");
using (IDataReader rdr = db.ExecuteReader("sproc")) {
    // Use the data reader
}

Answer 3

There is a problem is ExecuteReader method from the entlib 3.0. It was a fix done to implement Transaction scope but there was an error when handing huge amount of data withing a transaction. So they have done a fix to that and this problem causes as result of that fix. So you'll have to close the connection when ever you use the ExecuteReader method. Better to use it within a using block as mentioned above