CODEWITHSUNDEEP
ado.netdatareader
Jacob Sebastian
Jacob Sebastian

Reputation: 51

Does DataReader.Close() method close the connection object when using Microsoft Application Blocks for Data Access?

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

Upvotes: 5

Views: 16853

Answers (3)

Hui Tan
Hui Tan

Reputation: 103

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

Upvotes: 1

Miguel Angel Utiel Peñaranda
Miguel Angel Utiel Peñaranda

Reputation: 185

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.

Upvotes: 2

Ryan Ternier
Ryan Ternier

Reputation: 8814

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();
}

Upvotes: 1

Related Questions