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.
