connection don't close immediately

[auxcom]

I'm using SQL Profiler to monitor connection created by my Web Application. I noticed that calling the Close method didn't 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!

[iebidan]

System.Threading.Thread.Sleep(5000)

 

What happens if you remove this??? you're telling your current thread to wait / sleep for 5 minutes.

[PlausiblyDamp]

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?

[iebidan]

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.

[michael_hk]

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?

Hi PlausiblyDamp,

 

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

 

Hi auxcom,

 

If you want to disable connection pooling, just set Pooling=false in your connection string.

[auxcom]

What happens if you remove this??? you're telling your current thread to wait / sleep for 5 minutes.

 

Its only 5 seconds.

Edited June 28, 2005 by auxcom

[auxcom]

Are you looking at preventing the connection remaining open during testing or in production?

 

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"

 

I'd to avoid this to happen in my current project.

[auxcom]

If you want to disable connection pooling, just set Pooling=false in your connection string.

 

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.

[michael_hk]

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.

You may consider raising "Max Pool Size" to alleviate the timeout problem.

[auxcom]

many Thanks! :-)

[bri189a]

Hi PlausiblyDamp,

 

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

 

 

 

It depends on the provider. IBM provider does this to an extent on the AS400, SQL Provider will return to the pool...I believe that's 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 don't know where to research this, is to let the provider do what it does by default, it's probably the best method.

[Magadass]

Add to your connection string Pooling=false