Closing SQL Server Connection

[RobHudson]

Hi

Am I missing something when I'm trying to close a connection to SQL Server? My Code:

Try
DBConnectionString = String.Concat("server=", DBServer, ";database=", DBDatabase, ";Integrated Security=SSPI")
DBConnection = New SqlConnection
DBConnection.ConnectionString = DBConnectionString
DBConnection.Open()
Catch ex As Exception
....blah blah blah...
Finally
DBConnection.Close()
DBConnection.Dispose()
DBConnection = Nothing
End Try

For some reason, each connection is left as a sleeping process in the SQL Server activity log. Subsequently, after numerous calls to the DB, I have exceeded the number of connections allowed. Surely Close, Dispose and Nothing are killing it???

Cheers
Rob

 

[ca8msm]

That should be enough to close the connection (although I remember from the last time that I worked with SQL Server that even if the connection between your app and SQL Server has been terminated, there is a connection kind of like a session that SQL Server maintains). Do you use any sort of connection pooling on your SQL Server database? If not you may want to have a look into it.

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

Need help finding an answer?

Try the search facility (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[eb24]

RobHudson:

It seems that your Finally is not executing, thus meaning there is an exception in your Try statement. For a Finally block to execute, control has to leave a Try statement.

Let's try debugging your block by moving your Close() to the end of your Try block. Remove your Dispose() and Nothing() for these are redundant. Also, I would suggest you Catch an SqlException as well to your general Exception.

Also, can you elaborate on what exactly you are trying to do here? Dataset? DataReader? ...?

 

[RobHudson]

Thanks fellas...

I tried connection pooling and was faced with the same problem

I figured that I must be missing the Close() somewhere along the line. The Try..Catch...Fianlly thing got me thinking down those lines. And I found it! On occaision I test for connection state and was testing wrongly - I think I may have wasted your time a little - sorry...