connection don't close immediately

A

auxcom

Well-known member
Joined
Apr 1, 2004
Messages
49
Im using SQL Profiler to monitor connection created by my Web Application. I noticed that calling the Close method didnt close immediately the connection, it takes 1 to 5 minutes before the connection will be closed in the SQL Profiler.

I think SQL Profiler is checking connection in real time, Is there a way to make the connection close immediately? Even using this simple code below will make the SQLConnection behave the same.

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
TestConn()
End Sub

Sub TestConn()
Dim cn As New SqlClient.SqlConnection

cn.ConnectionString = Utility.ConfigReader.ConnectionString
cn.Open()

System.Threading.Thread.Sleep(5000)

cn.Close()
cn.Dispose()
End Sub

Thanks!
 
ASP.Net implements connection pooling for any database whose provider supports it. Under normal running conditions this is a pretty good trick to speed things up as it avoids unnecessary connections being created / destroyed.
Are you looking at preventing the connection remaining open during testing or in production?
 
mmmm I missed the web application part, why I always think ppl develop only in WIndows Forms? lol, sorry about that, yeah connection pooling to speed up things is what .NET implements in web applications. Thanks PlausiblyDamp :P today is not my day... too much stuff to check in so little time.
 
PlausiblyDamp said:
ASP.Net implements connection pooling for any database whose provider supports it. Under normal running conditions this is a pretty good trick to speed things up as it avoids unnecessary connections being created / destroyed.
Are you looking at preventing the connection remaining open during testing or in production?
Click to expand...
Hi PlausiblyDamp,

I read somewhere on the web that calling connection.Dispose will destroy the connection entirely and doesnt return it to the pool. Correct?

Hi auxcom,

If you want to disable connection pooling, just set Pooling=false in your connection string.
 
iebidan said:
What happens if you remove this??? youre telling your current thread to wait / sleep for 5 minutes.
Click to expand...

Its only 5 seconds.
 
Last edited by a moderator:
PlausiblyDamp said:
Are you looking at preventing the connection remaining open during testing or in production?
Click to expand...

Obviously in production. I have experienced this error in my old web app project.

"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"

Id to avoid this to happen in my current project.
 
michael_hk said:
If you want to disable connection pooling, just set Pooling=false in your connection string.
Click to expand...

Hmmm... this seem do the trick. In the SQL Profiler, I see the Audit Login and Audit Logout items immediately, showing connection has been close immediately.

But connection pooling is recommend for ADO.NET.
 
auxcom said:
Hmmm... this seem do the trick. In the SQL Profiler, I see the Audit Login and Audit Logout items immediately, showing connection has been close immediately.

But connection pooling is recommend for ADO.NET.
Click to expand...
You may consider raising "Max Pool Size" to alleviate the timeout problem.
 
michael_hk said:
Hi PlausiblyDamp,

I read somewhere on the web that calling connection.Dispose will destroy the connection entirely and doesnt return it to the pool. Correct?
Click to expand...

It depends on the provider. IBM provider does this to an extent on the AS400, SQL Provider will return to the pool...I believe thats the case, unfortunately I work with IBM more than I do the SQL server; each provider works differantly. The main thing all programmers should do, if they dont know where to research this, is to let the provider do what it does by default, its probably the best method.
 
Back
Top