I've been looking at the Microsoft Data Access Application Block recently.
I've noticed a problem that maybe someone can help me with, if not this will make you aware of it. It seems most the places I've worked recently use the Data Application Block, and normally use these methods most:
SqlHelper.ExecuteNonQuery(_connstring, CommandType.StoredProcedure, _spName, _arParms)
SqlHelper.ExecuteReader(_connstring, CommandType.StoredProcedure, _SPName, _arParms)
SqlHelper.ExecuteDataset(_connstring, CommandType.StoredProcedure, _spName, _arParms)
Of course passing in the correct values. But specifically passing in the connection string.
The thing I notice is this:
If you look at the DAB's implementation of the method that takes a connection string as the first parameter:
public static int ExecuteNonQuery
(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{ //create & open a SqlConnection, and dispose of it after we are done.
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();
//call the overload that takes a connection in place of the connection string
return ExecuteNonQuery(cn, commandType, commandText, commandParameters);
}}
It's understandable that using the “Using” statement in C# will call dispose automatically on the object after the “Using” Block. Is this the best method? By doing this, the connection isn't closed until the GC needs resources or gets around to it.
Using the built in method with leaving the GC to dispose of the connection object, doesn't return the connection to the pool immediately, and when calling alot of Stored Procedures, I have actually gotten the error:
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.ConnectionPool.GetConnection (Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection (SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() at System.Web.SessionState.SqlStateConnection..ctor(String
sqlconnectionstring)
This means, because I left it upto the GC, I actually ran out of available connections in the connection pool. And yes, if I look in SQL with an sp_who2, my machine has about 30 open connections. I would then have to manually kill my aspnet_wp.exe process to free the tied up connections.
Why not instead control the connection yourself and call the overloaded method to take the connection itself? This way you can actually close and dispose of the connection.
example:
strSQL = "SELECT STATEMENT HERE"
Dim conn As SqlConnection = new SqlConnection(connString)
Try
conn.Open()
objDR = SqlHelper.ExecuteReader(conn, CommandType.Text, strSQL)
If (objDR.Read) Then
'Do Something
End If
objDR.Close()
Finally
If (conn.State = ConnectionState.Open) Then
conn.Close()
End If
conn.Dispose()
End Try
Why would MS make this version of the overloaded method and simply leave the closing of the connection upto the GC? This doesn't seem like a best practice in my opinion. I understand the need for ease of use, but this seems to complicate matters by not closing the connections!
I would appreciate some comments if anyone else has ran into this issue.
Gavin Stevens