| Author |
Topic |
|
BigRetina
Posting Yak Master
144 Posts |
 Posted - 2003-01-21 : 07:25:37
|
| Salute.. I have a HUGE problem. SQL connections are not CLOSING in my VB.NET app. I performed a small test. I opened a new project with two buttons btnOpen and btnClose. btnOpen opens a SQLConnection and btnClose closes it as seen below!..put simply the performance counter on the SQL 2000 Server and the sp_who stored procedure(that shows the processes) show that the SQL connection is NOT being CLOSED!!. By the way..If i check the Cn.State property after Cn.Close it says "CLOSED"!!!!!! If I close the whole application every connection that was open is closed and everything goes back to normal!..I tried it on two machines and it is not working! WHAT IS WRONG? Thanks In Advance!.. Dim Cn As New SqlClient.SqlConnection() Dim CnStr As String = "..." Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Cn.ConnectionString = CnStr End Sub Private Sub btnOpen_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOpen.Click Cn.Open() End Sub Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click Cn.Close() End Sub |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-01-21 : 08:01:48
|
| connection pooling keeps the connections open ... this is for increased performance since it does not have to negotiate with the server a new connection each time... it will close the connections in the pool after a set timeout of inactivity... don't worry about it ... its ok ... if you open and close, open and close, open and close you should only have 1 connection ... but if you run the app at the same time and open both of them you should have 2... there may be more depending upon your connection pooling settings (it may initialize a set number of connections when it is used the first time) |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-01-21 : 08:22:37
|
| If you really want to close the connection and clear it from the pool, use:Connection.Dispose()This will destroy the connection object and remove it from the poolOS |
|
|
|
BigRetina
Posting Yak Master
144 Posts |
Posted - 2003-01-21 : 08:32:15
|
| First of all thanks to U all.Onamuji..How can I manipulate the connection pool settings?..and shouldnt the connection pooling use ONLY one connection to connect to SQL server if the connection string was EXACTLY the same?...which is my case...otherwise..what is it pooling ???.mohdowais..I used Cn.Dispose and it didnt close the connection as well!.. I can still see it in the Performance Counter and the sp_Who.More ideas??..I would love to hear more.Thanks Again.. |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-01-21 : 09:21:50
|
| In the connection that you use to initialize the connection, you can specify the following options:- Pooling (True/False)- Max Pool Size (0-100 i think)- Min Pool SizeetcOS |
|
|
|
BigRetina
Posting Yak Master
144 Posts |
Posted - 2003-01-21 : 09:37:39
|
| mohdowais.There are NO such properties in ADO.NET SqlConnection !!..can U elaborate more please?? |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-01-21 : 09:55:33
|
| its part of the connection string ... not the object |
|
|
|
BigRetina
Posting Yak Master
144 Posts |
Posted - 2003-01-21 : 10:28:05
|
| I see..How can I see the default values of the connection pooling settings?Thanks AGAIN! |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
|
|
BigRetina
Posting Yak Master
144 Posts |
Posted - 2003-01-22 : 01:59:48
|
Thanks Again.What worries me is the following..(i might be wrong)..I know that connections are valuable resources on the SQL server and having too much opened for no reason is a waste!.. I know that connection pooling is a mechanism to increase performance but i see TOO many connections opened!..Second thing..if two forms are opening two connections but with the SAME connection string..shouldnt the pooler open ONLY one physical connection to the server?.THIRD..there is a limit on the SQL server for concurrent connections..For example..I have 20 concurrent user connections..the connections are getting over 20 but no EXCEPTION is being raised!..why is that?that used to happen with SQL server 7.0..but now in SQL 2000 the concurrent connections are getting over 20 and no error!..any explanation?..basically those things what worry me!..otherwise..what the hey..let the connections BE OPEN!.. |
|
|
|
|
ADO.NET SQLConnection PROBLEM