Connection pooling (?) troubles

[phbrady]

I have an app that I'm developing on my PC and testing on a server. I can run the app in the Visual Studio IDE all day long on my PC and have no trouble with the SQL connection. When I installed the app on a server, the connections don't get dropped, so when I run the app I get the following error:
----------------
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. at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open()
----------------
The funny thing is, I can still run the app from the VS IDE on my PC and have no trouble getting a SQL connection! Maybe the .Net garbage collector works differently when you run the app from the IDE?
What would happen if I didn't use connection pooling? Would the connections be closed more efficiently? Should the SQL server itself be closing the connections after some period of time, or is that all handled by .Net?
This is an intranet-based app and I don't expect a very large user base.

Below is a sample of a typical function in the app:
----------------
Public Sub DisableUser(ByVal intUserID As Integer)
Dim objCommand As New SqlCommand(SP_DISABLE_USER, objConnection)

Try
objCommand.CommandType = CommandType.StoredProcedure
objCommand.Parameters.Add("@UserID", intUserID)

objCommand.ExecuteNonQuery()
Catch ex As Exception
Err.Raise(ERR_DISABLE_USER, , "Error Disabling User (clsData.DisableUser): " & ex.Message & " " & ex.StackTrace)
Finally
objConnection.Dispose()
objCommand.Dispose()
End Try

End Sub
----------------

As you can see, I'm explicitly closing everything in my Finally block.

Thanks in advance for your help!

 

[whatsthehampton]

Hi,

How are you calling your 'objConnection' ?

j

 

[phbrady]

Here's the function:
--------------------
Public Function GetSQLConn() As SqlConnection
Dim strConnString As String = ConfigurationSettings.AppSettings("SQLConnectionString")
Dim objConn As New SqlConnection(strConnString)
RaiseEvent Conneting("Opening Database..")

Try
objConn.Open()
RaiseEvent Connected("Connected to Database.")
Return objConn
Catch ex As Exception
Err.Raise(ERR_GET_SQL_CONN, , "Error Opening Database " & ex.Message & " " & ex.StackTrace)
End Try

End Function

--------------------

The connection string is simple:

server=<server>;Initial Catalog=ProjectMgt;uid=<userid>;pwd=<password>

 

[whatsthehampton]

Hi,

Well I can see objConn in your function and objConnection in your sub?
I also can't see how you have declared your Sproc, so I put it in quotes.

Try this:-

Public Sub DisableUser(ByVal intUserID As Integer)

Dim strConnString As String = ConfigurationSettings.AppSettings("SQLConnectionString")
Dim objConn As New SqlConnection(strConnString)

Dim objCommand As New SqlCommand("SP_DISABLE_USER", objConn)

objCommand.CommandType = CommandType.StoredProcedure
objCommand.Parameters.Add("@UserID", intUserID)

objConn.Open()

objCommand.ExecuteNonQuery()

objConn.Close()

End Sub

Let me know how you get on...

j

 

[phbrady]

I think I fixed the problem. There were a couple of functions where we weren't explicitly closing the connection and disposing of our command object.
I failed to mention that the connection is actually opened when the class is instantiated, as follows:

--------
Public Sub New()
objConnection = GetSQLConn()
End Sub
--------