title: ASP.NET application opening WAY too many SQL connections
body: <p>I have an issue on a codebit with an ASP.NET Webforms 4 application.
  I am using SQL server 2008 R2, IIS 7, the website is running on Windows Server 2008 R2 in separate Application pool (integrated mode, .NET4, with support for 32bit assemblies).</p>
  
  <p>The following code is posing problems:</p>
  
  <pre><code>Dim sqlCmd = New SqlClient.SqlCommand
  With sqlCmd
      Using sqlConnectionToUse As SqlClient.SqlConnection = GetActiveConnexion(pstrConnectString), _
          vAdaptor As New SqlClient.SqlDataAdapter
  
              .Connection = sqlConnectionToUse
  
              .CommandText = pstrSQL 'function parameter
  
              vAdaptor.SelectCommand = sqlCmd
              'query1: SELECT somecolumn FROM table WHERE somecolumn '' ==&gt; opens a new connection in SQL Server
              'query2: SELECT someothercolumn FROM anothertable WHERE someothercolumn 23 ==&gt; uses my WebSite process active connection
              vAdaptor.Fill(vDataSet)
      End Using
  End With
  </code></pre>
  
  <p><strong>UPDATE:</strong> the GetActiveConnexion() method simply does the following code in my case:</p>
  
  <pre><code>Return New SqlClient.SqlConnection("my connection string obtained from the web.config file")
  </code></pre>
  
  <p>When I run the query2, everything goes smoothly, the ASP.NET application uses the openned connection of the application pool and I get my results in the dataset.</p>
  
  <p>However, whenever I run the query1, a NEW connection is openned in SQL server (I can see it show up in the SSMS's Activity Monitor) and this one remains openned. The problem is that If I run this query1 100 times, I reach the connection pool's limit and very bad things happens. I still gets the results in the dataset, can use them etc...
  The new connection is created on the call of vAdaptator.Fill().</p>
  
  <p>Any idea on what's wrong ?</p>
  
  <p>Thanks a lot for your time.
  (PS: sorry for the bad english).</p>
  
  <p>Here is the code in C# for those who prefer:</p>
  
  <pre><code>object sqlCmd = new SqlClient.SqlCommand();
  using (SqlClient.SqlConnection sqlConnectionToUse = GetActiveConnexion(pstrConnectString)) {
      using (SqlClient.SqlDataAdapter vAdaptor = new SqlClient.SqlDataAdapter()) {
  
          sqlCmd.Connection = sqlConnectionToUse;
  
          sqlCmd.CommandText = pstrSQL; //function parameter
  
          vAdaptor.SelectCommand = sqlCmd;
          //query1: SELECT F10_ID FROM FIN_MONTANT_TT_F10 WHERE F10_ID_TT_F19 = '' ==&gt; opens a new connection in SQL Server
          //query2: SELECT A48_ID FROM ADH_EPARTICIPANT_ADMIN_A48 WHERE A48_ID=23 ==&gt; uses my WebSite process active connection
          vAdaptor.Fill(vDataSet);
      }
  }
  </code></pre>