In my post on 09-16-2003 at 10:43 PM, I gave a scenario where the overloaded method called PrepareCommand, but did so with an open connection. So, mustCloseConnection is set to false AND the method that opens the connection does not close it. Test function
that orphans a connection:
The test code calls an ExecuteNonQuery with a connection string and a dynamic SQL statement. A null parameter list is added and then the following method is called:
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
// Create & open a SqlConnection, and dispose of it after we are done
using (SqlConnection connection = new SqlConnection(connectionString))
// This is the connection open that never gets closed. - Comment added by Weston Binford
connection.Open();
// Call the overload that takes a connection in place of the connection string
return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
}
}
Here, the connection is opened, then another ExecuteNonQuery method is called with the newly created connection object. When I traced through this execution path, I eventually called PrepareCommand, BUT mustCloseConnection
was set to false because the method that called it already had an open connection. Thus, I still think it is a bug. In the VB version, the ExecuteNonQuery method shown above opens the connection in a try block and closes it in the finally block. The VB version
has the same mustCloseConnection logic in the PrepareCommand, but also handles the scenario if you pass it a connection string. Please forgive me if the code above is not exactly correct. I ran the test at home last night and recreated the test stub from memory.
I don't have the code available in my current evironment to make sure that I remembered correctly. -Weston
Wow. I finally get it. Yep, you found a path through it that keeps the connection open. I'd call that a bug too. Interesting! And a bummer.... Don
Don Kiely, MCP, MCSD
In the Last Frontier, Interior Alaska
Please post questions and replies to the forum! And remember to MARK AS ANSWER when someone definitively answers a question or resolves a problem!
Okay. It is good to get confirmation. I was worried that I was missing something simple. Actually, this has really helped me understand how the DAAB is built. This is a problem that affects almost every set of methods that use a ConnectionString. However, I
believe it only affects the C# version of the application. The problem is very broad in scope, but appears to be relatively easy to fix in one of two ways. Let me restate the problem: While using the C# version of the DAAB, if you call any of the following
sets of methods that accept a ConnectionString, then the connection will not be closed by the program:
ExecuteNonQuery ExecuteDataSet ExecuteScalar FillDataSet The ExecuteDataReader methods handle connection management completely differently and the
ExecuteXmlDataReader does not have any methods that accept a ConnectionString as a parameter because it does not a method equivalent to cmd.ExecuteDataReader(CommandBehavior.CloseConnection). All of the methods except the
FillDataSet methods funnel the calls to a single method that in turn calls an overloaded method passing a connection object in place of the ConnectionString. For some reason, each of the FillDataSet methods that accept a ConnectionString open a new connection
and pass it to the associated FillDataSet method that accepts a connection object. In any event, the following methods open a connection that never gets closed:
Using the first method as an example, the connection that is created and opened will never be closed. See here:
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
// Create & open a SqlConnection, and dispose of it after we are done
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Comment added by Weston Binford
// There is not a close associated with this open
connection.Open();
// Call the overload that takes a connection in place of the connection string
return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
}
}
I see two solutions to the problem:
The first solution is to wrap the body of the method in a try finally block. This is how it is done in the VB version. So, with added code in red, the new method call would be:
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );
SqlConnection connection = null;
try
{
// Create & open a SqlConnection, and dispose of it after we are done
using (connection = new SqlConnection(connectionString))
{
connection.Open();
// Call the overload that takes a connection in place of the connection string
return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
}
}
finally
{
if (connection != null)
connection.Dispose();
}
}
The second and somewhat intriguing solution comes from the implementation of
GetSpParameterSet. It creates a connection, but does not open it. The connection is eventually opened and closed by
DiscoverSpParameterSet. Using the same technique, the connection.Open() line would be removed from all of the methods identified above. Then, I believe the
PrepareCommand method would set the mustCloseConnection boolean variable correctly and we could depend on the ExecuteNonQuery method that accepts a connection object to close the connection. Assuming it works, this would be the simplest fix, but would
cause a divergence between the VB and C# versions of the code. I have not debugged either solution. The first solution compiles, but I have not traced through the code. I thought I would get some feedback on this issue before I fixed it in my version of the
DAAB. What do you think? Do you have any comments on either solution or have another solution? Anybody still with me? -Weston
You are exactly right. I feel really stupid. For some reason, I thought that using only saved on typing. It also acts as a scope operator and calls the Dispose() method when it reaches the close brace. Nevermind. I agree it is working. Sorry for wasting your
time. -Weston
D'oh! I can't believe I missed that too! I've been doing too much VB.NET lately! Thanks, klenne! Don
Don Kiely, MCP, MCSD
In the Last Frontier, Interior Alaska
Please post questions and replies to the forum! And remember to MARK AS ANSWER when someone definitively answers a question or resolves a problem!
I have the same problem about closing connections but I'm using
public static
SqlDataReader
ExecuteReader(string connectionString,
CommandType commandType,
string commandText)
I see that it not close the connection and when I call it many times the SQL pool connections increase a lot. If I tray to fix it with
using (SqlConnection cn =
new SqlConnection(connectionString)) or with
cn.Close(), the application that use SqlHelper generates error because the SqlDataReader object has the connection closed.
I had to workaround with the use of
DataTableReader
object .
//create & open a SqlConnection
SqlConnection cn = new SqlConnection(connectionString);
cn.Open();
try
{
//call the private overload that takes an internally owned connection in place of the connection string
return ExecuteReader(cn,
null, commandType, commandText, commandParameters,SqlConnectionOwnership.Internal);
}
catch
{
//if we fail to return the SqlDatReader, we need to close the connection ourselves
using (SqlConnection cn =
new SqlConnection(connectionString))
{
cn.Open();
//call the private overload that takes an internally owned connection in place of the connection string
return ExecuteReader(cn,
null, commandType, commandText, commandParameters,
SqlConnectionOwnership.Internal);
}
}
I have VS2005, SQL 2000 and SQL 2005 and framework 2.0.
I find the problem and it is because we needed to explicitly close the database connections when we were done using SqlDataReader. In teory, DAL has to close the connections, but SQLHelper does not do it. I do not want to worry about if I using BLL and DAL.
Weston Binfo...
Member
190 Points
38 Posts
Re: Microsoft Data Access Application Block not closing connections?
Sep 17, 2003 07:45 PM|LINK
private void SampleExecuteNonQuery() { // Pull connection string from web.config for Northwind database string strConn = ConfigurationSettings.AppSettings["NorthwindConnect"]; string strSql = " insert into Northwind" + " (ProductName)" + " values ('New Product')"; SqlHelper.ExecuteNonQuery(strConnectString, CommandType.Text, strSqlStmt); }The test code calls an ExecuteNonQuery with a connection string and a dynamic SQL statement. A null parameter list is added and then the following method is called:public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" ); // Create & open a SqlConnection, and dispose of it after we are done using (SqlConnection connection = new SqlConnection(connectionString)) // This is the connection open that never gets closed. - Comment added by Weston Binford connection.Open(); // Call the overload that takes a connection in place of the connection string return ExecuteNonQuery(connection, commandType, commandText, commandParameters); } }Here, the connection is opened, then another ExecuteNonQuery method is called with the newly created connection object. When I traced through this execution path, I eventually called PrepareCommand, BUT mustCloseConnection was set to false because the method that called it already had an open connection. Thus, I still think it is a bug. In the VB version, the ExecuteNonQuery method shown above opens the connection in a try block and closes it in the finally block. The VB version has the same mustCloseConnection logic in the PrepareCommand, but also handles the scenario if you pass it a connection string. Please forgive me if the code above is not exactly correct. I ran the test at home last night and recreated the test stub from memory. I don't have the code available in my current evironment to make sure that I remembered correctly. -WestonTrason Consulting, Inc.
wbnospam@yahoo.com
(yes, it is a real e-mail address)
donkiely
All-Star
15929 Points
2518 Posts
ASPInsiders
Moderator
MVP
Re: Microsoft Data Access Application Block not closing connections?
Sep 17, 2003 11:09 PM|LINK
In the Last Frontier, Interior Alaska
Please post questions and replies to the forum! And remember to MARK AS ANSWER when someone definitively answers a question or resolves a problem!
Weston Binfo...
Member
190 Points
38 Posts
Re: Microsoft Data Access Application Block not closing connections?
Sep 18, 2003 03:48 AM|LINK
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) public static void FillDataset(string connectionString, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames) public static void FillDataset(string connectionString, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames, params SqlParameter[] commandParameters) public static void FillDataset(string connectionString, string spName, DataSet dataSet, string[] tableNames, params object[] parameterValues)Using the first method as an example, the connection that is created and opened will never be closed. See here:public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" ); // Create & open a SqlConnection, and dispose of it after we are done using (SqlConnection connection = new SqlConnection(connectionString)) {// Comment added by Weston Binford
// There is not a close associated with this open
connection.Open();
// Call the overload that takes a connection in place of the connection string
return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
}
}
I see two solutions to the problem: The first solution is to wrap the body of the method in a try finally block. This is how it is done in the VB version. So, with added code in red, the new method call would be:public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if( connectionString == null || connectionString.Length == 0 ) throw new ArgumentNullException( "connectionString" );SqlConnection connection = null;
try
{
// Create & open a SqlConnection, and dispose of it after we are done
using (connection = new SqlConnection(connectionString))
{
connection.Open();
// Call the overload that takes a connection in place of the connection string
return ExecuteNonQuery(connection, commandType, commandText, commandParameters);
}
}
finally
{
if (connection != null)
connection.Dispose();
}
}
The second and somewhat intriguing solution comes from the implementation of GetSpParameterSet. It creates a connection, but does not open it. The connection is eventually opened and closed by DiscoverSpParameterSet. Using the same technique, the connection.Open() line would be removed from all of the methods identified above. Then, I believe the PrepareCommand method would set the mustCloseConnection boolean variable correctly and we could depend on the ExecuteNonQuery method that accepts a connection object to close the connection. Assuming it works, this would be the simplest fix, but would cause a divergence between the VB and C# versions of the code. I have not debugged either solution. The first solution compiles, but I have not traced through the code. I thought I would get some feedback on this issue before I fixed it in my version of the DAAB. What do you think? Do you have any comments on either solution or have another solution? Anybody still with me? -WestonTrason Consulting, Inc.
wbnospam@yahoo.com
(yes, it is a real e-mail address)
klenne
Member
390 Points
78 Posts
Re: Microsoft Data Access Application Block not closing connections?
Sep 18, 2003 11:37 AM|LINK
Weston Binfo...
Member
190 Points
38 Posts
Re: Microsoft Data Access Application Block not closing connections?
Sep 18, 2003 02:31 PM|LINK
Trason Consulting, Inc.
wbnospam@yahoo.com
(yes, it is a real e-mail address)
donkiely
All-Star
15929 Points
2518 Posts
ASPInsiders
Moderator
MVP
Re: Microsoft Data Access Application Block not closing connections?
Sep 18, 2003 04:25 PM|LINK
In the Last Frontier, Interior Alaska
Please post questions and replies to the forum! And remember to MARK AS ANSWER when someone definitively answers a question or resolves a problem!
twinscythe12...
Member
8 Points
4 Posts
Re: Microsoft Data Access Application Block not closing connections?
Jan 08, 2008 07:17 AM|LINK
ds =
SqlHelper.ExecuteDataset(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString(), CommandType.Text, mystring, param);now the strange thing is, when I run a netstat in the command prompt, I get quite a few connections to sql popping up in "TIME_WAIT"
any reason why this should be happening? or am I just thinking of functionality that isn't supposed to be there?
EDIT:
sorry, forgot to mention that the amount of connections increases as I carry on moving through pages.
Roberto Bedo...
Member
4 Points
2 Posts
Re: Microsoft Data Access Application Block not closing connections?
May 30, 2008 01:54 PM|LINK
hello,
I have the same problem about closing connections but I'm using
public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText)
I see that it not close the connection and when I call it many times the SQL pool connections increase a lot. If I tray to fix it with using (SqlConnection cn = new SqlConnection(connectionString)) or with cn.Close(), the application that use SqlHelper generates error because the SqlDataReader object has the connection closed.
I had to workaround with the use of DataTableReader object .
Original Code:
public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters){
//create & open a SqlConnection SqlConnection cn = new SqlConnection(connectionString);cn.Open();
try{
//call the private overload that takes an internally owned connection in place of the connection string return ExecuteReader(cn, null, commandType, commandText, commandParameters,SqlConnectionOwnership.Internal);}
catch{
//if we fail to return the SqlDatReader, we need to close the connection ourselvescn.Close();
throw;}
}
Code that does not work
public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters){
using (SqlConnection cn = new SqlConnection(connectionString)){
cn.Open();
//call the private overload that takes an internally owned connection in place of the connection string return ExecuteReader(cn, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal);}
}
I have VS2005, SQL 2000 and SQL 2005 and framework 2.0.
Regards,
Roberto
Roberto Bedo...
Member
4 Points
2 Posts
Re: Microsoft Data Access Application Block not closing connections?
Jun 03, 2008 10:56 PM|LINK
Hello,
I find the problem and it is because we needed to explicitly close the database connections when we were done using SqlDataReader. In teory, DAL has to close the connections, but SQLHelper does not do it. I do not want to worry about if I using BLL and DAL.
You can read http://www.aspdotnetcodes.com/Ado.Net_DataTableReader.aspx.
Best regards,
Roberto