Be careful with ExecuteScalar() and ExecuteReader()

If you use ExecuteScalar() to get a value from a sql command, and updates some records in the same command. (For example, generate a new ID, return the new ID and create the record for this ID). You should always put the select value sentence in the end of the transaction. Otherwise you may get surprised that you can still get the new ID with no exception when actually the record is not created!
There are 2 simplified examples. Believe me, they won’t throw.

Example 1:
string sql = @"select 123
RAISERROR('error msg', 16, 1)";
command.ExecuteScalar(context.MetaDBConnectionString, CommandType.Text, sql);

Example 2:
string sql = @" SET XACT_ABORT ON
select 123
BEGIN TRAN
RAISERROR('error msg', 16, 1)
COMMIT TRAN";
command.ExecuteScalar(context.MetaDBConnectionString, CommandType.Text, sql);

If you change to use ExecuteReader(), there won’t be any exceptions either. But the same sql throws if you use ExecuteNonQuery() or ExecuteDataset().
For ExecuteScalar()/ExecuteReader(), SqlException only throws if you move RAISERROR before the select sentence.

I found some explaination from Internet for this behavior:

> ExecuteReader

Since your proc raises the error after the first result set is returned, you
need to invoke the SqlDataReader.NextResult method before the error is
thrown:

SqlDataReader dr = command.ExecuteReader();
while (dr.Read());
while(dr.NextResult());

> and ExecuteScalar?

ExecuteScalar returns only the first column of the first result set so the
exception is not raised in the app code. If you need the exception raised,
consider using ExecuteReader with NextResult instead. ExecuteScalar is
basically just a convenience method that wraps ExecuteReader and retrieves
only the first result set.