Using datareaders with sqltransactions

Question

I am using SqlTransactions in my code for roll back purpose. Within the transaction I got multiple statements to be executed with may include selects inserts and updates. All these statements are within the scope of the sqltransaction. Everything works fine just for one problem. I am using datareaders for select statements . And these readers are closed once they are used. This forces the connection to be lost and every thing fails. Does any one have a solution on whether I can use datareaders within a sqltransaction??

Answer 1

You should open the SqlConnection by itself.
The SqlDataReader doesn't close the SqlConnection when you close the DataReader

For example:

using(SqlConnection cn = GetConnection())
{
    cn.Open();
    SqlTransaction tr = cn.BeginTransaction("myTransaction");
    .....

    SqlCommand command = new SqlCommand(sqlString, cn);

    using(SqlDataReader reader = command.ExecuteReader())
    {
        .....
    }

    SqlCommand command1 = new SqlCommand(sqlString1, cn);
    using(SqlDataReader reader1 = command1.ExecuteReader())
    {
        .....
    }
    tr.Commit();
}

Answer 2

A DataReader will only close the connection if the CommandBehavior.CloseConnection option was set when calling ExecuteReader.

You should be OK if you avoid setting this option.