I was implementing some data access code for a personal project using the Enterprise Library's Data Access Application Block (DAAB). I was running some code that checked for the existence of a row prior to issuing an insert. Here is the original code:
SqlDatabase db = DatabaseFactory.CreateDatabase("mainDB") as SqlDatabase;
string sqlCommand = "ar_GetArrestReportItemByCaseNumber";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "reportId", DbType.Int32, reportID);
db.AddInParameter(dbCommand, "caseNumber", DbType.String, caseNumber);
IDataReader dataReader = db.ExecuteReader(dbCommand);
exists = dataReader.Read();
After running through on some test data, I gave it a run against some real world data. And it worked fine until the data's batches exceeded 100 rows. Then I got the error message:
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.
I checked SQL Manager and saw 100 connections / processes accessing the table in question. I am using the DAAB. The Database class is supposed to take care of this sort of thing for me. There was a bug in VS 2003, but this was fixed in VS 2005. Where is the connection being left open... the DataReader. I forgot to close the DataReader. I promptly re-educated myself on some ADO and IDisposable. Whenever an object implements IDisposable, there is probably a good reason for it... like say... releasing a connection object back to the connection pool. Corrected code and much better performance:
SqlDatabase db = DatabaseFactory.CreateDatabase("mainDB") as SqlDatabase;
string sqlCommand = "ar_GetArrestReportItemByCaseNumber";
using (DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand))
{
db.AddInParameter(dbCommand, "reportId", DbType.Int32, reportID);
db.AddInParameter(dbCommand, "caseNumber", DbType.String, caseNumber);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
exists = dataReader.Read();
}
}
Moral of the story... if IDisposable is implemented, chances are you should call it.
ADDENDUM
----------
For those who are not familiar, IDisposable is an interface for the implementation of the Dispose Pattern. This is very useful in programming languages that have Garbage Collection (ie. .Net). When you have Garbage Collection, you have what is called a non-deterministic destructor. In other words, when you are done using an object, you have no idea when it's resources will be freed. Garbage Collection occurs at an interval that is determined by the system. By implementing IDisposable, you ad a Dispose method to your class where high contention resources can be freed (ie. File Handles, Database Connections, etc.). The implementing of IDisposable signals the user that there is something valuable to be cleaned up ASAP that cannot wait for Garbage Collection. So, even if Dispose is called, the Garbage Collector will still clean up the object's resources eventually. But in the mean time, all of the high value resources have been released.