5

I am reviewing an application that uses Microsoft Application Blocks for Data Access to interact with the database. The application calls a function and pass a query into it. The function creates a DataReader object using Data Access application blocks (for the given query) and returns the DataReader to the caller.

The application closes DataReader object when it is done. The question I have is, whether closing the DataReader object automatically closes the underlying connection object or not. It is not clear to me whether the Data Access Application Block opens the command object with the "CommandBehavior.CloseConnection" flag.

Questions:

  1. When the Close() method is called on a DataReader object, does it also close the underlying connection object (the DataReader is created using Microsoft Application Blocks - Database.ExecuteReader() method
  2. If not, what is the recommended method to ensure that the connection is freed after we are done with the data reader?

Thanks in advance

Jacob Sebastian
  • 51
  • 1
  • 1
  • 3

3 Answers3

2

http://msdn.microsoft.com/en-us/library/system.data.commandbehavior.aspx

CloseConnection: When the command is executed, the associated Connection object is closed when the associated DataReader object is closed.

Miguel Angel Utiel Peñaranda
  • 185
  • 1
  • 9
  • Does this answer the question? I ask because it's not clear whether `CommandBehaviour.Default` means `CommandBehaviour.CloseConnection`. I guess not, then the answer to OP's question was: no, by default it does not close the connection. – Tim Schmelter Jul 11 '18 at 09:32
1

Miguel Angel Utiel's answer is right, if you use "CommandBehavior.CloseConnection", the connection will close after reader closed. just like this:

 OracleDataReader odr = oc.ExecuteReader(CommandBehavior.CloseConnection);
 odr.Close();
 if (oc.Connection.State == System.Data.ConnectionState.Closed) {
    System.Diagnostics.Debug.WriteLine("connection is closed");
 }

if your ExecuteReader() with no parameter,just like 

OracleDataReader odr = oc.ExecuteReader();
odr.Close();
if (oc.Connection.State != System.Data.ConnectionState.Closed) {
    System.Diagnostics.Debug.WriteLine("connection is openning");
}

read:http://msdn.microsoft.com/en-us/library/system.data.commandbehavior.aspx

Hui Tan
  • 95
  • 1
  • 5
1

It is always a good practice to close and dispose of your objects manually. The GC will eventually collect them, but you don't know when, and when dealing with a database you don't want open connections hanging around eating up resources.

So to answer your questions:

1) Yes, DataReader.Close() will close the connection. This is relevant when dealing with any connected object.

As soon as you close the reader, the connection it was using is then closed automatically as well. Because Readers are connected objects (the need an open connection to function correctly), you can't close the Connection before you are done with the reader. http://p2p.wrox.com/book-beginning-asp-net-1-0/11037-sqldatareader-close-connection.html

also look at: http://msdn.microsoft.com/en-us/magazine/cc188705.aspx

2) I would recommend putting the connection in a using statement: **these are done using regular SQLClient objects, but they will work with all objects that inherit from the System.Data.Common.DbCommant, System.Data.Common.DbConnection etc. classes.

using(SqlConnection con = new SqlConnection(....))
{
   //blah blah blah
   con.close();
}

Dispose will be called at the end of the using, which will call close, but I still like to implicitly call it.

or, you can put it in a try/catch statement:

SQLConnection con = new SqlConnection(...);
try
{
  con.open();
}
catch(Exception ex)
{
}
finally
{
   //depending on the version of .NET you might want to do:
   //if(con.State != System.Data.ConnectionState.Closed)
   //    con.Close();
   con.close();
}
Ryan Ternier
  • 8,714
  • 4
  • 46
  • 69
  • Thanks Ryan for the comment. My question was specifically on Microsoft Data Access Application blocks. In this case it is the Application Blocks Library which opens the connection and manages it. I was reviewing an application that leaves a large number of connections open. When I looked at it, It looked like Application blocks does not close the connection when the application closes the data reader object. So I wanted to get some insights into how it works inside MAB. – Jacob Sebastian Sep 09 '11 at 05:14
  • No problem for the comment. Microsoft has done this functionality with all their datareader objects. If you look at: http://msdn.microsoft.com/en-us/magazine/cc188705.aspx you will see that when the reader is closed, the underlying connection is closed too. That deals with DAAB. – Ryan Ternier Sep 09 '11 at 15:27
  • if one likes to explicitly call it I personally prefer the second example – Martin Meeser May 19 '15 at 15:46