Clearing SQL Connections - passing conn by ref

[RobSchultz]

This is a two part question/problem and I apologize in advance for the rambling. They may be well known facts/issues/features but they are new to me...

Setup: While trying to figure out how to pass a connection by ref to a subroutine I found that I couldn't use the ref keyword (error: Argument '1': cannot convert from 'System.Data.SqlClient.SqlConnection' to 'ref System.Data.SqlClient.SqlConnection'). I came up with the test code below to try to figure it out. Technically, the subroutine I will be calling in real code sets up an approle authentication. Not using ref seems to work fine (per demonstration code) since the initial connection state is closed so it must be passing it by ref internally.

Part 1: I noticed (when not using ref) that a second connection is created on the sql server (sp_who). Is there any way to keep from creating this 2nd connection or is this a "feature" of .NET? Using VB.NET did the same thing but 2 connections were made immediately upon conn.Open() rather than waiting until the SetConn subroutine was called (ByRef worked there though).

Part 2: Using sp_who reveals that the connections to the database are not released until after the program closes. conn.Close() and conn.Dispose() don't seem to release the connection completely. Not a big problem, but kind of irritating to me.

Put breakpoints on each Debug.Writeline and use sp_who.

using System;
using System.Diagnostics;
using System.Data.SqlClient;

namespace TestConnRefCSharp
{
 public class TestRef
 {
  public TestRef()
  {
  }

  public static void Main()
  {
   SqlConnection conn = new SqlConnection();
   conn.ConnectionString = "Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI";
   conn.Open();
   Debug.WriteLine("Before SetConn state:" + conn.State.ToString()); //I see one additional connection here
   SetConn(conn);
   Debug.WriteLine("After SetConn state:" + conn.State.ToString());  //I still see two additional connection here but state is closed
   conn.Dispose(); 
   Debug.WriteLine("After dispose");  //I see two additional connection here
  } //program exits - no additional connections

  private static void SetConn(SqlConnection conn)
  {
   Debug.WriteLine("Got here"); //I see two additional connection here
   conn.Close();
  }
 }
}

Thanks,

Rob

 

[apc2004]

Hi,

1, All objects are passed by ref in the .Net Framework by default so when passing an object it is not necessary to use the keyword "ref".

Example:

private void CallMethod()
{
  SqlConnection sqlConnection  = new SqlConnection();
  // Call 1
  OpenConnection(sqlConnection);

  // Call 2
  OpenConnection(ref sqlConnection);
}

// Call 1 - This is the same
private void OpenConnection(SqlConnection sqlConnection)
{
  sqlConnection.Open();
}

// Call 2 - as this...
private void OpenConnection(ref SqlConnection sqlConnection)
{
  sqlConnection.Open();
}

Hope this is of some help...

 

[litton1]

dot net pools the connection to prevent wasted resource as it is costly to make a completely new connection, or that is what I have read anyway. Hope this helps

Age is a consequence of experience