Here is an example of my code in a DAL. All calls to the database's Stored Procedures are structured this way, and there is no in-line SQL.
Friend Shared Function Save(ByVal s As MyClass) As Boolean
Dim cn As SqlClient.SqlConnection = Dal.Connections.MyAppConnection
Dim cmd As New SqlClient.SqlCommand
Try
cmd.Connection = cn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "proc_save_my_class"
cmd.Parameters.AddWithValue("@param1", s.Foo)
cmd.Parameters.AddWithValue("@param2", s.Bar)
Return True
Finally
Dal.Utility.CleanupAdoObjects(cmd, cn)
End Try
End Function
Here is the Connection factory (if I am using the correct term):
Friend Shared Function MyAppConnection() As SqlClient.SqlConnection
Dim cn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ToString)
cn.Open()
If cn.State <> ConnectionState.Open Then
' CriticalException is a custom object inheriting from Exception.
Throw New CriticalException("Could not connect to the database.")
Else
Return cn
End If
End Function
Here is the Dal.Utility.CleaupAdoObjects() function:
Friend Shared Sub CleanupAdoObjects(ByVal cmd As SqlCommand, ByVal cn As SqlConnection)
If cmd IsNot Nothing Then cmd.Dispose()
If cn IsNot Nothing AndAlso cn.State <> ConnectionState.Closed Then cn.Close()
End Sub
I am getting a lot of "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." error messages reported by the users. The application's DAL opens a connection, reads or saves data, and closes it. No connections are ever left open - intentionally!
There is nothing obvious on the Windows 2000 Server hosting the SQL Server 2000 that would indicate a problem. Nothing in the Event Logs and nothing in the SQL Server logs.
The timeouts happen randomly - I cannot reproduce. It happens early in the day with only 1 to 5 users in the
system. It also happens with around 50 users in the
system. The most connections to SQL Server via Performance Monitor, for all databases, has been about 74.
The timeouts happen in code that both saves to, and reads from, the database in different parts of the application. The stack trace does not point to one or two offending DAL functions. It's happened in many different places.
Does my ADO.NET code appear to be able to leak connections? I've goolged around a bit, and I've read that if the connection pool fills up, this can happen. However, I'm not explicitly setting any connection pooling. I've even tried to increase the Connection Timeout in the connection string, but timeouts happen long before the 300 second (5 minute) value:
<add name="MyConnectionString" connectionString="Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI;Connection Timeout=300;"/>
I'm at a total loss already as to what is causing these Timeout issues. Any ideas are appreciated.
