DataReader and DB Connection Closing

Discussion in 'ASP .Net' started by rockdale, Sep 21, 2006.

  1. rockdale

    rockdale Guest

    I am using Microsoft.Practices.Enterprise.Library

    When I use IDataReader I saw this line
    "It is the responsibility of the caller to close the connection and
    reader when finished."

    I was wondering if I close the DataReader do I automatic ally close the
    Connection? If not , how can I close the Connection outside the
    ExecuteReader function?

    I code snippet is like following:

    IDataReader oDr =null;

    Database db = DatabaseFactory.CreateDatabase();

    string sqlCommand = "my_stored_proc";
    DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

    oDr = db.ExecuteReader(dbCommand);


    /*I close the DataReader here, do I need to close connection also?*/
    rockdale, Sep 21, 2006
    1. Advertisements

  2. Rockdale,

    Here is some info on closing datareaders.

    I always wrap my code in a try / finally block and put the close into the
    finally so that even if an error is encountered the reader and connection
    are still closed.


    S. Justin Gengo
    Web Developer / Programmer

    Free code library:

    "Out of chaos comes order."
    S. Justin Gengo, Sep 21, 2006
    1. Advertisements

  3. Yes, I would also recommend explicit disposing connection objects. It's a
    got idea to put it in a finally block.
    Eliyahu Goldin, Sep 21, 2006
  4. rockdale

    rockdale Guest

    Hi, Eliyahu

    Thanks for the reply.
    But how can I dispose the connection object as the db connection is
    created and opened in the database factory

    I know I must close the connection if I create and open this connection
    but here as I am using the Data Access block, how do I explicitly close
    the connection and dispose it? Or should I use dataset since "The
    connection is closed by the ExecuteDataSet function" ? (comment from
    Microsoft Enterprise Library)

    Thanks again

    rockdale, Sep 21, 2006
  5. rockdale


    Jun 27, 2011
    Likes Received:
    public IDataReader DsFetch()
    IDataReader Idr = null;

    Database db = DatabaseFactory.CreateDatabase();
    DbCommand dbcommand = db.GetSqlStringCommand("select * from RSSrnd");
    dbcommand.CommandType = CommandType.Text;
    return Idr =db.ExecuteReader(dbcommand);

    catch (Exception ex)
    throw ex;

    deepakkarma, Jun 27, 2011
  6. rockdale

    Rafael Neris

    Jan 31, 2012
    Likes Received:
    Hi Rockdale,

    In fact the ExecuteReader method of the Database class, automatically closes its connection to the DB after the closing of the DataReader returned by the method.

    This is because it uses CommandBehavior.CloseConnection.

    Note the class source Database.cs Microsoft Practices.

    I hope this helps.

    Undo edits

    Source Database.cs:

    public virtual IDataReader ExecuteReader(DbCommand command)
    DbConnection connection = OpenConnection();
    PrepareCommand(command, connection);


    return DoExecuteReader(command, CommandBehavior.CloseConnection);


    Rafael Neris, Jan 31, 2012
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.