Conection remains open on MS SQL Server 2000

I am using .NET SDK version 1.1. I have executed the following C# code (I have used namespace System.Data.SqlClient as database server is MS SQL Server 2000) : // start of code string connString = "Server=TestServer; Database=TestData; uid=tester; pwd=testpass"; using (SqlConnection conn = new SqlConnection (connString )) { conn.Open (); MessageBox.Show ("Connection is open.", "Connection status"); } if (conn.State == ConnectionState.Closed) { MessageBox.Show ("Connection to server closed.", "Connection status"); } // end of code I have open the "SQL Profiler" application of "MS SQL Server" and start the "Trace" window to monitor the Audit Login and Audit Logout traces on the SQL Server end. This is to check the status of the connection at SQL Server end. Observation: 1. When executed the above C# code, the application displays message indicating connection is open. The "SQL Profiler Trace" window displays two "Audit Login" traces indicating two connections are made to SQL Server. 2. Dismissed the message box by clicking OK button. The application then executes conn.Close() method and displays next message as "Connection to Server closed". 3. But even though the application has called the conn.Close() method, SQL Profiler did not display "Audit Logout" trace. This indicates that at SQL Server end, the connections are still open. 4. When exited the application, then only SQL Profiler displayed two "Audit Logout" traces indicating both connections are closed at Server end. I have following queries w.r.t. above observations: 1. In first place why two connections are open at SQL Server instead of only one. 2. Even if the conn.Close() method is executed in application, both connections are remained open at SQL Server. Why the connections are not closed at Server end? Is this the connection pooling that ADO.NET does? 3. What I need to do in code to make sure that the connection will be actually closed at SQL Server end whenever I call the conn.Close() method in my code? Please answer. Thanks and regards, Prashant.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

tomar_prashant0922y ago
I have got the answers to all of these queries in the MSDN Library documentation. The answers are as follows: 1. A connection pool is created for each unique connection string. When a pool is created, multiple connection objects are created and added to the pool so that the minimum pool size requirement is satisfied. Connections are added to the pool as needed, up to the maximum pool size. 2. This is because connection pooling is "true" by default. 3. To achieve this, modify the connection string to include the key/value pair "Pooling=false" as follows: "Server=TestServer; Database=TestData; uid=tester; pwd=testpass; Pooling= false" Regards, Prashant. PS: Web Admin can delete this thread.
+1