none
C# SQL Server Connection Pooling

    Question

  • Hello,

    I'm using SQL Server 2005 and Visual Studio 2008.  I've been reading up on connection pooling and I can see that it would make quite a difference in the application we use.  I'm not from a C# background, I'm a DBA.  I've been using SQL Profiler on the inhouse application that was created and notice that it opens and closes a connection to SQL Server for every task.  I thought connection pooling is enabled by default.

    I went to the Microsoft page about connection pooling (http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx).  I copied the example into a new C# project.  I created a simple Windows Forms application with a button on it, when clicked it runs the following code...

    using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Database1"))
    
    {
    
      connection.Open();
    
      // Pool A is created.
    
    }
    
    
    
    using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Database2"))
    
    {
    
      connection.Open();
    
      // Pool B is created because the connection strings differ.
    
    }
    
    
    
    using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Database1"))
    
    {
    
      connection.Open();
    
      // The connection string matches pool A.
    
    }
    
    
    That's fine, when I run profiler it only makes two login events in SQL Server Profiler no mater how many times I click the button to run the above code.  However when I put the following code in there to actually return data...

    using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Database1"))
    
    {
    
      SqlCommand myCommand = connection.CreateCommand();
    
      myCommand.CommandType = System.Data.CommandType.Text;
    
      myCommand.CommandText = "SELECT * FROM dbo.activities";
    
    
    
      connection.Open();
    
      myCommand.ExecuteReader();
    
      myCommand.Dispose();
    
      // Pool A is created.
    
    }
    
    
    
    using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Database2"))
    
    {
    
      connection.Open();
    
      // Pool B is created because the connection strings differ.
    
    }
    
    
    
    using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Initial Catalog=Database1"))
    
    {
    
      connection.Open();
    
      // The connection string matches pool A.
    
    }
    
    
    I see the following in SQL Server Profiler...

    Button Click One

    1.  Audit Login
    2.  Select Statement
    3.  Audit Login

    Button Click Two

    1.  Audit Logout
    2.  Audit Login
    3.  Select Statement

    What is happening is that when I click the button a second time to run the second code block above Profiler shows that it logs out of SQL Server then logs back in.  I thought connection pooling was meant to re-run that connection?

    Again, appoligies I'm not a programmer but a DBA however I'd love to know how to get connection pooling to work.

    Kind regards,

    Chris
    Thursday, August 20, 2009 9:14 PM

Answers

All replies