SqlConnection.Close()

[Ken H.]

Hi, just wondering when is a connection really closed when I called
the Close() method on the SqlConnection object. The code below runs
without problem even though the SqlConnection is supposedly "closed!"
The DumpTable routine dumps the correct values.


String strConn =
"SERVER=localhost;DATABASE=Northwind;UID=sa;PWD=whatever";
SqlConnection sqlConn = new SqlConnection(strConn);
SqlCommand sqlCmd = new SqlCommand("CustOrdersDetail", sqlConn);
sqlCmd.CommandType = CommandType.StoredProcedure;

SqlParameter cmdParm = sqlCmd.Parameters.Add("@orderId",
SqlDbType.Int, 4);
cmdParm.Value = 10250;

SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCmd);
DataTable sqlTable = new DataTable("orders");
sqlAdapter.Fill(sqlTable);

dumpTable( sqlTable );

// release resources...
sqlAdapter.Dispose();
sqlCmd.Connection.Close();
sqlCmd.Connection.Dispose(); // necessary?

Console.WriteLine( sqlCmd.Connection.State ); // prints "Closed"

cmdParm.Value = 10248;
sqlAdapter = new SqlDataAdapter(sqlCmd);
sqlTable = new DataTable("orders");
sqlAdapter.Fill(sqlTable);

dumpTable( sqlTable );

 

[William Ryan]

It's closed, I can't find the post right now with a link to it, but it's a
bug that makes it look like it isn't.

HTH,

BIll

 

[Angel Saenz-Badillos[MS]]

Ken,
there are a few things happening behind the covers, things that are necesary
for best performance and scalability but that are confusing.

First of all there is connection pooling, when you close a connection with
Pooling=true (default) we do not close the physical connection. The next
time that you ask for a connection we will hand you the pooled (stored)
connection if we have one available, this is a huge performance gain over
going to the server and creating a brand new connection every time.

The second thing that is happening under the covers, and the one that you
are running into here, is that the Adapter will open and close a connection
under the covers whenever the connection it is associated with is closed.
The reason for this is that it is very important when using pooling that the
connection is opened as late as possible and closed as soon as possible, by
building this behavior into the Adapter we avoid code that leaves a
connection open indefinitelly in the off chance that an adapter will need
it.

Hope this helped,

 

[Keyee Hsu]

Thank you! That helps. I guessed I should've seen it from the code
that the adapter is opening the connection. Good to know that it (the
adapter) also closes the connection automatically.