Microsoft Data Access Application Block not closing connections? RSS

• • Reply
  

  Weston Binfo...

  Member

  190 Points

  38 Posts

  Microsoft Data Access Application Block not closing connections?

  Sep 16, 2003 08:50 PM|LINK

  I have run into a scenario that I have not seen posted here. If I missed something, please let me know. It appears that the code for the ExecuteNonQuery (and ExecuteDataSet for that matter) does not close the connection if you call it using a connection string instead of a Connection object. If I understand the code correctly, the following method (I apologize for the word wrap): ExecuteNonQuery( string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) creates a connection using the connectionString parameter, then calls: ExecuteNonQuery(connection, commandType, commandText, commandParameters); However, it never closes the connection. Is that correct? Did I miss something? If not, is this a bug or is it working as designed? Do you have to depend on garbage collection if you use SqlHelper with connection strings instead of connections? I have seen the threads regarding the connection leaking using DataReaders (fixed in the sample code for DAAB 2.0, but I have not seen a reference to this issue. Thanks in advance for your assistance. -Weston
  Weston Binford
  
  
  Trason Consulting, Inc.
  
  
  wbnospam@yahoo.com
  
  
  (yes, it is a real e-mail address)
• • Reply
  

  donkiely

  All-Star

  15929 Points

  2518 Posts

  ASPInsiders

  Moderator

  MVP

  Re: Microsoft Data Access Application Block not closing connections?

  Sep 16, 2003 09:47 PM|LINK

  Hi Weston, Do you have it backwards? The DAAB is supposed to close the connection when you pass a string, but not when you pass a connection object. This is because when you pass a string, the DAAB takes control of the entire connection. If you do mean that it isn't getting closed when you use a string, that is a problem. How do you know that it isn't closed? Are you monitoring connections? Or looking at the source code? 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!
• • Reply
  

  Weston Binfo...

  Member

  190 Points

  38 Posts

  Re: Microsoft Data Access Application Block not closing connections?

  Sep 16, 2003 10:01 PM|LINK

  That is what I was trying to communicate. I understood the DAAB to handle opening and closing the connection if you passed it a connection string. The connection is not getting closed. However, I thought it might be a connection pooling issue, so I looked at the code. In the source code for SqlHelper, the method call for ExecuteNonQuery that takes a connection string appears to work as follows: Open connection. Call the ExecuteNonQuery method that takes a connection passing the connection just created. The original method (ExecuteNonQuery with a connectionString) never closes the connection. -Weston
  Weston Binford
  
  
  Trason Consulting, Inc.
  
  
  wbnospam@yahoo.com
  
  
  (yes, it is a real e-mail address)
• • Reply
  

  donkiely

  All-Star

  15929 Points

  2518 Posts

  ASPInsiders

  Moderator

  MVP

  Re: Microsoft Data Access Application Block not closing connections?

  Sep 16, 2003 10:42 PM|LINK

  Well, I was hoping that wasn't what you meant! But I just looked at the code for ExecuteNonQuery. That method is heavily overloaded, and all of the versions that take the connection string as the first parameter, as opposed to a connection object, close the connection. It may not do it itself, but calls another version that does it. Working in the source from top to bottom: ExecuteNonQuery(ByVal connectionString As String, _ ByVal commandType As CommandType, _ ByVal commandText As String) As Integer Neither opens nor closes the connection, but calls a version that does. ExecuteNonQuery(ByVal connectionString As String, _ ByVal commandType As CommandType, _ ByVal commandText As String, _ ByVal ParamArray commandParameters() As SqlParameter) As Integer Disposes the connection object in the Finally block. ExecuteNonQuery(ByVal connectionString As String, _ ByVal spName As String, _ ByVal ParamArray parameterValues() As Object) As Integer Calls a version of the method that handles closing the connection. The rest of the overloaded methods either use transactions or take a connection object as a parameter. So, it seems to take care of closing the connections. What am I missing? 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!
• • Reply
  

  Weston Binfo...

  Member

  190 Points

  38 Posts

  Re: Microsoft Data Access Application Block not closing connections?

  Sep 16, 2003 10:52 PM|LINK

  You are exactly right... for the VB version. It appears that the C# version does not close the connections. If you have access to the C# version, can you confirm that I am not crazy (or at least that I am crazy so that I can seek the appropriate professional help ;-) ). -Weston
  Weston Binford
  
  
  Trason Consulting, Inc.
  
  
  wbnospam@yahoo.com
  
  
  (yes, it is a real e-mail address)
• • Reply
  

  donkiely

  All-Star

  15929 Points

  2518 Posts

  ASPInsiders

  Moderator

  MVP

  Re: Microsoft Data Access Application Block not closing connections?

  Sep 16, 2003 11:26 PM|LINK

  Ah. Why dindya say so??? :-D Same result, different process. In the C# version, the connection management is handled by the PrepareCommand method. If the method gets a connection that's open, it leaves it open. If not, it closes it when it's done with it. This eliminates the need for managing it in the various Execute methods. Why is this different from the VB.NET version, you ask? Sorry I don't do why questions when they're about Microsoft. :-D I suspect that you could dig into the VB.NET code and figure out why it is that way, but I'll leave that as an exercise for another day. 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!
• • Reply
  

  Weston Binfo...

  Member

  190 Points

  38 Posts

  Re: Microsoft Data Access Application Block not closing connections?

  Sep 17, 2003 03:43 AM|LINK

  I see what you are saying, but that only affects connections that are passed to ExecuteNonQuery, but have not been opened (a scenario that I have not seen implemented). Call the overloaded ExecuteNonQuery method that takes a connection string, a Command.Text, a sql statment string, and a param array. It opens a connection and calls the overloaded ExecuteNonQuery method with the same signature except a SqlConnection substitutes for the connection string. This method calls PrepareCommand. Had a closed connection been passed, then the mustCloseConnection boolean variable would be set to true in PrepareCommand. However, since the connection was already open, mustCloseConnection is set to false and the connection is never closed. The VB version of DAAB also has the mustCloseConnection boolean logic in PrepareCommand and I believe it works in the same manner. -Weston
  Weston Binford
  
  
  Trason Consulting, Inc.
  
  
  wbnospam@yahoo.com
  
  
  (yes, it is a real e-mail address)
• • Reply
  

  donkiely

  All-Star

  15929 Points

  2518 Posts

  ASPInsiders

  Moderator

  MVP

  Re: Microsoft Data Access Application Block not closing connections?

  Sep 17, 2003 05:24 PM|LINK

  Correct. I'm not saying it's a good or bad way to do it, just that's how it is. I would hope that further delving into the source would reveal the reason for this design choice, but that will have to wait for another day. 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!
• • Reply
  

  Weston Binfo...

  Member

  190 Points

  38 Posts

  Re: Microsoft Data Access Application Block not closing connections?

  Sep 17, 2003 05:59 PM|LINK

  So, I still have my problem. If I call ExecuteNonQuery with a connection string and a dynamic sql statement, then the connection never gets closed. Right? -Weston
  Weston Binford
  
  
  Trason Consulting, Inc.
  
  
  wbnospam@yahoo.com
  
  
  (yes, it is a real e-mail address)
• • Reply
  

  donkiely

  All-Star

  15929 Points

  2518 Posts

  ASPInsiders

  Moderator

  MVP

  Re: Microsoft Data Access Application Block not closing connections?

  Sep 17, 2003 06:41 PM|LINK

  No. Because as near as I can tell all ExecuteNonQuery overloads use the PrepareCommand method, they all create an unopened connection object. And PrepareCommand takes care of closing it. It keeps track of whether it needs to close it through this code:

  if (connection.State != ConnectionState.Open)
  {
  	mustCloseConnection = true;
  	connection.Open();
  }
  else
  {
  	mustCloseConnection = false;
  }

  mustCloseConnection is an output parameter, passed back to the ExecuteNonQuery that called PrepareCommand. Then after the query is run ExecuteNonQuery checks whether to close it this way:

  if( mustCloseConnection )
      connection.Close();

  Assuming that Microsoft carefully implemented this code to take care of all the paths through the various overloaded methods, the connection is closed. If you really want to make sure, use Performance Monitor to watch the connections to the database and verify that you're not left with any open connections. 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!