Dear Mike,
Thanks for your response.
After further research on this issue these days, I'm sorry I made a mistake
in my previous replies.
connection.dispose() is "equal" to connection.close() (just as you have
seen by Reflector).
We need to close the connection only once. (using either close() or
dispose())
There are truly two connection pools (one in SQL Server and another in
ADO.net Application). However, the connection pool in SQL Server is
transparent to .net application.
Connection.dispose() method couldn't release connect to SQL connection
pool.(I'm sorry I explained wrong in my pervious reply).
Both connection.close() and connection.dispose() method return the
connection into the application connection pool.
Please note the CAUTION part in the following article on MSDN website.
http://msdn2.microsoft.com/en-us/library/8xx3tyca(vs.71).aspx
[Connection Pooling for the .NET Framework Data Provider for SQL Server]
CAUTION It is recommended that you always close the Connection when you
are finished using it in order for the connection to be returned to the
pool. 1)*THIS CAN BE DONE USING EITHER THE CLOSE OR DISPOSE METHODS OF THE
CONNECTION OBJECT*. 2)*CONNECTIONS THAT ARE NOT EXPLICITLY CLOSED MIGHT NOT
BE ADDED OR RETURNED TO THE POOL*. For example, a connection that has gone
out of scope but that has not been explicitly closed will only be returned
to the connection pool if the maximum pool size has been reached and the
connection is still valid.
If we FORGET to call close/dispose method, (in turn, dispose() method calls
connection.close() method implicit), the connection will NOT be released to
application pool, even though, the connection has gone out of scope.
We could repro this issue by the following code.
//we created an application connection pool (min=2, max=4).
// we added four connections in one scope and add two
connections out of scope.
//we will receive a time out exception when open the 5th
connection (sc5).
string connectionstring = @"Data Source=.;Initial
Catalog=testDB;Integrated Security=True;Min Pool Size=2;Max Pool Size=4";
{
System.Data.SqlClient.SqlConnection sc1 = new
System.Data.SqlClient.SqlConnection(connectionstring);
System.Data.SqlClient.SqlConnection sc2 = new
System.Data.SqlClient.SqlConnection(connectionstring);
System.Data.SqlClient.SqlConnection sc3 = new
System.Data.SqlClient.SqlConnection(connectionstring);
System.Data.SqlClient.SqlConnection sc4 = new
System.Data.SqlClient.SqlConnection(connectionstring);
sc1.Open();
sc2.Open();
sc3.Open();
sc4.Open();
}
System.Data.SqlClient.SqlConnection sc5 = new
System.Data.SqlClient.SqlConnection(connectionstring);
System.Data.SqlClient.SqlConnection sc6 = new
System.Data.SqlClient.SqlConnection(connectionstring);
sc5.Open();// Error(Time out)
sc6.Open();
Therefore, MSDN document suggest us use "Using()" block to ensure the
connection will be closed timely.
"Using" block will call dispose () method at the end of scope. In turn,
Dispose() method will call close() method of connection object. Therefore,
the connection could be return into application pool timely.
However, if you have called connection.close() before, it is NOT necessary
to dispose the connection again. Close() and Dispose() do the same thing
for connection.
Again, for your initial issues:
1. I often use the pattern for ExecuteReader with the
CommandBehavior.CloseConnection parameter and then return the reader to a
calling routine. The calling routine iterates thru the reader and
ultimately
closes the reader, thus closing the connection. There's no good opportunity
to Dispose the SqlConnection. If Disposing is important, then I don't see
how
to use this pattern.
not to dispose the connection again. However, please make sure we have
closed the connection.
2. There are several posts that say that if a SqlConnection is Disposed,
then it won't be released to the ConnectionPool. If you want the connection
to be pooled, only call Close(). Is there any truth to this?
application connection pool.
3. I think this defeats the whole purpose of CloseConnection. As I
understand it, this allows one to have a helper routine that encapsulates
all the Data related "overhead". The helper returns the resulting reader to
the caller, something like
public SqlDataReader ExecuteReaderHelper(string sql)
{
//using (SqlConnection conn = new SqlConnection...)
SqlConnection conn = new SqlConnection...
{
using (SqlCommand cmd = new SqlCommand...)
{
return cmd.ExecuteReader(CommandBehavior.CloseConnection))
}
}
}
...calling code
using (SqlDataReader rdr = ExecuteReaderHelper(sql))
{
while (rdr.Read())
...do something
}
(CommandBehavior.CloseConnection option). It is not necessary to call
dispose method again.
The connection will be return to connection pool timely.
4. As to your previous message, all of this discussion about two connection
pools is brand new to me. I haven't seen any references to the existence of
two pools in any documentation or posts before. Can you clarify or point me
to more documentation?
Application Connection Pool (Not SQL Connection Pool) by Dispose method.
I'm sorry.
Thanks for your patience.
Please let me know if you have any more concern. It's my pleasure to work
with you.
Best regards,
Wen Yuan
Microsoft Online Community Support
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
