Problem with datareaders in transactions

[CD]

Hello All,

I am having problems using a datareaders within a transaction. I have
attached a stripped down version of my code that exposes the problem.
The problem occurs when I commit the transaction. I get an exception
"There is already an open DataReader associated with this Command which
must be closed first". If I do not use transactions, everything is
fine.

Any clues?

thank you
CD

connection.Open();
SqlCommand command = connection.CreateCommand();
SqlTransaction sqlTran =
connection.BeginTransaction();
command.Transaction = sqlTran;

try
{
command.CommandText = queryString;
SqlDataReader reader = command.ExecuteReader();
sqlTran.Commit(); <<<<<<<<<<<Exception thrown
here
while (reader.Read())
{
Console.WriteLine("\t{0}\t{1}", reader[0],
reader[1]);
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine("Caught exception, rolling
back {0}", ex);
sqlTran.Rollback();
}

connection.Close();
}
}
catch (Exception ex)
{
Console.WriteLine("Caught exception {0}", ex);
}

 

[Marina Levit [MVP]]

You can't commit the transaction before you close the datareader. So either
read through everything before committing, or put the data into a datatable,
commit, and then examine the datatable for the data.

Additionally, your catch is invalid. If an exception is thrown after the
transaction is committed, (the while loop), then there is nothing to
rollback.