.NET Tips & Tricks Community RSS

Those who prefer reading this site through RSS now can also subscribe for updates to .NET Tips & Tricks Community.

When returning DataReader from a function, specify CommandBehavior.CloseConnection

When you create an ADO.NET DataReader object, specify the CommandBehavior.CloseConnection enumeration in your call to ExecuteReader. This ensures that when you close the DataReader, the SQL connection is also closed. This is especially helpful when you return a DataReader from a function, and you do not have control over the calling code. If the caller forgets to close the connection but closes the reader, both are closed when the DataReader is created by using CommandBehavior.CloseConnection. This is shown in the following code fragment.

    public SqlDataReader CustomerRead(int CustomerID)
    {
        //... create connection and command, open connection
        return myCommand.ExecuteReader(CommandBehavior.CloseConnection);
    }
 
    //... client code
    SqlDataReader myReader = CustomerRead(10248);
    //... read some data
    myReader.Close(); // reader and connection are closed