Problem with Timeout and Data Access Blocks

[Adam Flint]

I have a .NET application (actually MCMS, but I think that's irrelevant)
that access a SQL Server database using Microsoft Data Access Blocks (DAB).

Consider the following pseudo-code:

For i = 1 To 10000
SqlHelper.ExecuteNonQuery(strConnection, storedProcType, storedProcName,

params)
Next

This code will cause the classic ODBC timeout error every time after a few
iterations. Now consider this pseudo-code:

Dim oConn as New SqlConnection(strConnection)
oConn.Open
For i = 1 To 10000
SqlHelper.ExecuteNonQuery(oConn, storedProcType, storedProcName, params)
Next

oConn.Close : oConn.Dispose

This works fine. But what's the difference? I've been into the code of DAB
and every call to ExecuteNonQuery does close and dispose the connection.

So my guess is that in the first example, something is not letting go of the

connections fast enough. Yet if I look on the SQL server, there are
virtually no active connections. So where are they being held? What's
causing the timeout?

Now this might seem like an extreme example, but the actual problem I am
having with our application is that in a single HTTP request there is a lot

of database access going on, mostly using the ExecuteNonQuery method in
different functions and event handlers all over the application. The
application is pretty big, it's an Intranet solution for a global financial

services organisation. We've just added a new module that executes every
time an HTTP request is coming in, and we are now getting the timeout errors.

Anyone else experienced this?

[zandries1234]

For some reason MS decided not to close the connections in the code, but to let the garbage collector collect and close the connections used. Due to this the connection stays active for a while after the sql command was completed.
The other thing to be wary of when using the SQLHelper class is that it only uses the default command timeout of around 30sec. This becomes a real pain when you start to run long running queries.
I would suggest to rather use the sqlclient class and code you own wrapper class.
Do it once and you have customizable wrapper class similar to sqlhelper, plus you can step through in debug mode if you wish.