implementing 'reader' goes to catch block

Question

try
{
    sqlTran = conn.BeginTransaction();
    String tblAuto = "Select * from tblAuto_Num where Company_Code='Comp'AND Module='WSS' AND Sub_Module='SVS' AND Doc_Type='ORD'; ";
    SqlCommand cmd = new SqlCommand(tblAuto, conn);
    SqlDataReader reader = cmd.ExecuteReader();

    while (reader.Read())
    {  
    }           

    reader.Close();
    Label1.Text = "ttt";
    sqlTran.Commit();

    conn.Close();
}
catch (Exception ex)
{
    if (sqlTran != null) sqlTran.Rollback();
    Label1.Text = ex.toString();
}

The moment I implementSqlDataReader reader = cmd.ExecuteReader();, it goes to the catch block. Can anyone explain why?

this is the error message:

System.InvalidOperationException: ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized. 
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) 
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) 
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) 
at System.Data.SqlClient.SqlCommand.ExecuteReader() at TicketApp.Default.ButtonAppointment_Click(Object sender, EventArgs e) 
in c:\Users\Farhad\Documents\Visual Studio 2013\Projects\TicketApp\TicketApp\Default.aspx.cs:line 29

It throws an exception or something? Did you debug your code? — Soner Gönül, Jul 17 '14 at 06:58
How about you use a debugger to see the exception that's being thrown? — Dirk, Jul 17 '14 at 06:59
You're not doing anything with the exception, so you can't see what's wrong. Log the exception - or at least look at it in the debugger. That's likely to explain it. — Jon Skeet, Jul 17 '14 at 06:59
Also, please pay more attention to formatting code in your posts - the indentation is all over the place. — Jon Skeet, Jul 17 '14 at 06:59
*Can anyone explain why?* It looks like ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized — ta.speot.is, Jul 17 '14 at 07:13

Dmitry Bychenko · Answer 1 · 2014-07-17T07:46:06.697

2

Use using instead of try..catch when working with IDisposable:

using (var sqlTran = conn.BeginTransaction()) {
  // Format out SQL to be more readable
  String tblAuto = 
    @"select * 
        from tblAuto_Num 
       where Company_Code = 'Comp' and 
             Module = 'WSS' and 
             Sub_Module = 'SVS' and 
             Doc_Type='ORD'; ";

  try {
    using (SqlCommand cmd = new SqlCommand(tblAuto, conn)) {
      cmd.Transaction = sqlTran; // <- Do not forget this

      using (SqlDataReader reader = cmd.ExecuteReader()) {
        while (reader.Read()) {
          ...
        }
      } 
    }

    sqlTran.Commit();
  }
  catch (Exception) {
    try {
      sqlTran.Rollback();
      Label1.Text = "bbb";

      throw; // <- When catch Exception (base class)  do not forget to throw
    } 
    catch (Exception) {
      // when rollback can't be performed
      throw;
    }
  }
}

edited Jul 17 '14 at 07:46

answered Jul 17 '14 at 07:06

Dmitry Bychenko

180,369
20
160
215

Thnanks Mr. Dmitry for an effective answer :) – Farhad Jul 17 '14 at 07:26
if i do not remove `sqlTran.Rollback();throw;` this two line, `This SqlTransaction has completed; it is no longer usable.` error comes up. How can i avoid it? – Farhad Jul 17 '14 at 07:37
@Farhad: http://stackoverflow.com/questions/15293673/sqltransaction-has-completed-error – Dmitry Bychenko Jul 17 '14 at 07:46

implementing 'reader' goes to catch block

1 Answers1