SqlClient equivalent of "BEGIN TRY...END TRY"

Question

We are trying to replicate the functionality of the following SQL, but using SqlClient code in c#.

Consider the following SQL:

BEGIN TRANSACTION
    -- lots of critical SQL that must be atomic

    -----------------------------------------------------
    -- begin non-critical section
    -----------------------------------------------------     
    SAVE TRANSACTION SavePointName;
    BEGIN TRY
        -- lots of SQL that is not critical, and also might fail, such as:
        UPDATE someTable set someField='blah' where LineId=20
    END TRY
    BEGIN CATCH
        --some error handling code
        ROLLBACK TRANSACTION SavePointName;
    END CATCH
    -----------------------------------------------------
    -- end non-critical section
    -----------------------------------------------------     
     
    -- possibly more critical SQL
    ...
    
COMMIT TRANSACTION;

When this SQL runs, if there is a problem with the SQL in the BEGIN TRY...END TRY block, the outer containing (unnamed) transaction is not automatically cancelled and rolled back. This is the desired behaviour.

Now, due to architectural issues (that cannot be changed), we are trying to effect the same behaviour in C# using SqlClient classes. Consider this code:

SqlConnection connection = getOpenConnection();
SqlTransaction transaction = connection.BeginTransaction();
//... lots of critical SQL executed on the connection, within the transaction
////////////////////////////////////////////////////////
// begin non-critical section
////////////////////////////////////////////////////////
transaction.Save("SavePointName");
try
{
    //lots of SQL that is not critical, and also might fail
    SqlCommand cmd = new SqlCommand("UPDATE someTable set someField='blah' where LineId=20"
                                   , connection, transaction );
    cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
    //some error handling code
    transaction.RollBack("SavePointName");
}
////////////////////////////////////////////////////////
// end non-critical section
////////////////////////////////////////////////////////

//possibly more critical SQL
...
transaction.Commit();

This might appear approximately equivalent to the SQL, but it is not. The problem occurs when there is an error executing any SQL in the try-catch block, such as the UPDATE command. In the SQL, the enclosing transaction is not cancelled and rolled back, and we recover cleanly within the BEGIN CATCH block. However, in the C# code, an exception running the "UPDATE" cmd causes the outer enclosed transaction to be cancelled and rolled back - we lose all the critical SQL in the first half, (and the transaction.Commit(); fails).

Is it possible to set up the SqlCommands in the C# try-catch block so that they behave as if they were contained within an SQL try-catch block, so that the C# code behaves similarly to the SQL code at the start?

Thanks.

Edit: Having now read through some of the links in comments below, let me add some other details. We encountered this issue when the "non-critical" SQL, above represented by a simple "UPDATE" command, contained a command that failed with a "too many parameters" (>2100) error. In the link provided in the comment below from @Charlieface, this error is a "class 2" error that dooms any transaction, regardless of how things are done. But we want to capture any types of errors in the "non-critical" section, and roll-back that section, when they happen. Having looked through the link provided by Charlieface below, it seems that handling of errors is quite inconsistent between different error types.

Answer 1

The information in the link above, provided by @Charlieface, is fairly comprehensive and accurate. The upshot of the information is that one cannot always roll back to a point before the non-critical commands. Certain exceptions that may occur when executing the non-critical commands will simply doom any (containing) transaction. (In particular, the exception we encountered, "too many parameters in command", dooms the transaction.) But, these sorts of exceptions doom the transaction even when done with SQL on the server side. The key to replicating the BEGIN TRY...END TRY; BEGIN CATCH ... END CATCH behaviour on the server is indeed judicious use of the XACT_ABORT flag, exactly as suggested by @Charlieface. The following client-side C# code illustrates the salient points required to replicate the server-side behaviour we want (with the caveat pointed out by @Charlieface, that "it also means that some errors will only abort that statement and continue the next statement in the batch"):

    public abstract class DbHandling
    {
        public DbHandling()
        { }

        private SqlConnection m_conn = null;
        //abstract public SqlConnection GetOpenConnection();
        public bool isConnectionBad()
        {
            return m_conn == null || m_conn.State == System.Data.ConnectionState.Closed || (m_conn.State & System.Data.ConnectionState.Broken) != 0;
        }
        public bool isConnectionOpen()
        {
            return m_conn != null && (m_conn.State & System.Data.ConnectionState.Open) != 0;
        }

        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        public abstract object executeScalarNoThrow(string sql);
        public abstract void executeSqlNonQuery(string sql);
        public abstract SqlTransaction getTransaction();

        public void setXactAbort(bool onOff)
        {
            var sql = "SET XACT_ABORT " + (onOff ? "ON" : "OFF");
            executeSqlNonQuery(sql);
        }

        public bool getXactAbort()
        {
            var sql = @"DECLARE @XACT_ABORT VARCHAR(3) = 'OFF';
                        IF ( (16384 & @@OPTIONS) = 16384 ) SET @XACT_ABORT = 'ON';
                        SELECT @XACT_ABORT AS XACT_ABORT;";
            return string.Compare(executeScalarNoThrow(sql).ToString(), "ON", true) == 0;
        }
        public int getXactState()
        {
            var sql = "XACT_STATE();";
            return Convert.ToInt32(executeScalarNoThrow(sql));
        }
        public int getTransactionCount()
        {
            var sql = "select @@TRANCOUNT;";
            return Convert.ToInt32(executeScalarNoThrow(sql));
        }

        public void runNonCriticalCode()
        {
            //if any of this fails, we cannot safely run "non critical code" because we cannot run the recovery code in the catch, so this is outside the try-catch
            var xactAbortIsOn = getXactAbort();
            setXactAbort(false);
            SqlTransaction transaction = getTransaction();
            const string savePointName = "sampleSavePoint";
            transaction.Save(savePointName);

            log.Debug($"xactIsOn: {xactAbortIsOn}");

            try
            {
                ////////////////////////////////////////////////////////////////////////////////////////////////
                //           non critical code
                ////////////////////////////////////////////////////////////////////////////////////////////////
            }
            catch (Exception ex)
            {
                if (isConnectionBad() || getTransactionCount() == 0 || getXactState() == 0)
                {
                    log.Error("Fatal error trying to blah - entire transaction cancelled.", ex);
                    throw;
                }
                transaction.Rollback(savePointName);
                int transCount = 0;
                if (isConnectionBad() || (transCount = getTransactionCount()) == 0 || getXactState() <= 0)
                {
                    log.Error("Fatal error trying to blah, unable to recover - entire transaction cancelled.", ex);
                    if (transCount > 0)
                    {
                        transaction.Rollback(); ///Outer transaction has not been restored to a committable state, we must rollback.  Likely also re-throw.
                    }
                    throw;
                }
                log.Error("Unable to blah.", ex);
            }
            finally
            {
                if (isConnectionOpen())
                {
                    setXactAbort(xactAbortIsOn);
                }
            }
        }
    }