Hi,

I recently created a web service with various functions calls to a Stored Procedure via the RetrievalProcedures (generated class in the SelfServicing template). This web service is getting multiple calls a minute.

With the first code block below, the connections remain open even after they are closed:

Code Block 1:

IRetrievalQuery query = RetrievalProcedures.GetSPNAMECallAsQuery(Convert.ToDecimal(10), 0);
query.Connection = DbUtils.CreateConnection();
query.Connection.Open();
IDataReader reader = query.Execute(CommandBehavior.Default);

string res = BuildXMLHeader() + BuildXML(reader, Language, true) + BuildXMLFooter();
query.Connection.Close();

Now, I modified the above code to this:

Code Block 2:

IRetrievalQuery query = RetrievalProcedures.GetPsbAvalanchePkgPsbAvaBullInternetGetCallAsQuery(Convert.ToDecimal(BulletinID), 0);
query.Connection = DbUtils.CreateConnection();
query.Connection.Open();
IDataReader reader = query.Execute(CommandBehavior.Default);

string res = BuildXMLHeader() + BuildXML(reader, Language, true) + BuildXMLFooter();

reader.Close();
reader.Dispose();
query.Connection = null;
query.Dispose();

reader = null;
query = null;

and I still had connections opened after calling all possible Close() and Dispose() functions.

I had to add the following call to code block 2 so that the DB connections are released:

GC.Collect();

Anyone seen this before?

Thanks,

-- Joel

This looks like it may be ADO.net keeping the connections around in the background so that they can be reused for connection pooling. Although you say they "leak" does the number of open connections steadily increase, or does it settle down to a steady figure...?

Matt

Matt,

The number of connection steadily increases. They never get released until the garbage collector runs. That's why I am forcing the GC to run after each call. I haven't seen this when using direct table calls (TableNameCollection object).

-- Joel

First of all, Why are you opening and closing the connections? Why not just:

DataTable resultSet = RetrievalProcedures.CustOrderDetail(10254);

Second of all, if you see the number of connection grows you should check your code carefully. Could be that in some cases the runtime never reach the .Close() line. So a detailed investigation from you could bring light to this.