Problem with datareaders in transactions

12 Dec 2006 8:55 AM

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

12 Dec 2006 1:59 PM

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.

Show quote

"CD" <crb***@yahoo.com> wrote in message
news:1165913736.825554.93790@j72g2000cwa.googlegroups.com...
> 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);
>            }
>