SQL Server batch problem with error handling

Question

I have a problem regarding SQL batches in SQL Server.

Assume I execute a SqlCommand using the following code:

 private void Example(SqlConnection connection, SqlTransaction transaction)
  {
    using (SqlCommand cmd = new SqlCommand("select * from T1; EXECUTE('update T2 set 1=2')", connection, transaction))
    {
      SqlDataReader reader = cmd.ExecuteReader();
      ..
      ..
    }
  }

Now, since the first statement within the batch succeded, I get NO EXCEPTION and a RESULT eventhough the EXECUTE(''')-statement clearly is in error.

If I reverse the statements, I get an exception since a resultset isn't generated before the first error.

Now, suppose that I would like to detect the error in both cases, how do I do it without generating "redundant" code as in:

 private void Example(SqlConnection connection, SqlTransaction transaction)
  {
    using (SqlCommand cmd = new SqlCommand("select INTO #T1 from T1; EXECUTE('update T2 set 1=2'); select * from #T1", connection, transaction))
    {
      SqlDataReader reader = cmd.ExecuteReader();
      ..
      ..
    }
  }

No this question may seem strange but still... I would like to know the answer, please don't ask why :)

Answer 1

You need to read the SqlDataReader till it no longer returns results (Read() loop then NextResult() and repeate). The exception is there further down on the TDS stream and the SqlClient did not yet got to parse it.

Answer 2

I tried, but using the example below only renders me an exception saying: "There is already an open DataReader associated with this Command which must be closed first."

If I close the reader prior to NextResult(), I get another exception stateing "Invalid attempt to call NextResult when reader is closed."

Code:

private void Example(SqlConnection connection, SqlTransaction transaction)

{ using (SqlCommand cmd = new SqlCommand("select * from T1; EXECUTE('update T2 set 1=2')", connection, transaction)) { SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { }

  if (reader.NextResult())
  {


  }    
}

}

Answer 3

Isn't that what I'm doing in the post above your comment?

Or do you suggest something else? (then please write the code, words always fail :) )

regards, Jens