Hi ajs916 and thanks for your reply. You are right, when I check IsClosed in the way that you described, I get a true and so I should have realeased the connection back into the pool (I have a pool of 100 available connections). I am using CommandBehavior.CloseConnection in the ExecuteReader methods like you described (because that's how it was coded in the starter kit), for example:
Public Function GetProductCategories(ByVal LanguageCode as String) As SqlDataReader
' Create Instance of Connection and Command Object
Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As SqlCommand = New SqlCommand("CONC_ProductCategoryList", myConnection)
' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure
' Add Parameters to SPROC
Dim parameterLanguageCode As SqlParameter = New SqlParameter("@LanguageCode", SqlDbType.Char, 2)
parameterLanguageCode.Value = LanguageCode
myCommand.Parameters.Add(parameterLanguageCode)
' Execute the command
myConnection.Open()
Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
' Return the datareader result
Return result
End Function
Also, my connection string is like so (in the web.config file) and is used everywhere with SQL Native security:
<appSettings>
<add key="ConnectionString" value="server=localhost;database=Commerce;User ID=(user);Password=(password);Connection Lifetime=300;" />
</appSettings>
I have tried to keep track of connections by counting them in SQL Server like this:
select count(*) from sysprocesses
WHERE LOGINAME='(loginname)'
and sure enough, the site gives up after I reach 100, which is really quick. Any insight that you could have is really appreciated. I have gone through the whole code and I don't believe that there are any places where I forget to close or use CommandBehavior.CloseConnection.
Maybe you were experiencing the same problem?
Thanks!