Commit multiple SqlCommands with SqlTransaction

Question

I am trying to pass a list of SqlCommand into a member function that holds the connection to the database.

public void CommitAsTransaction(List<SqlCommand> commands) {
    SqlTransaction transaction = null;
    SqlConnection connection = null;

    try {
        connection = this.CreateSqlConnection();
        connection.Open();

        transaction = connection.BeginTransaction("TransactionID");

        foreach (SqlCommand cmd in commands) {
            cmd.Transaction = transaction;
            cmd.Connection = connection;
            cmd.ExecuteNonQuery();
        }

        transaction.Commit();
    }
    catch (Exception ex) {
        transaction.Rollback();
    }

    connection.Close();
}

This is what I currently have. The error occurs because the command seems to be being executed as in place and the transaction.Commit(); is never reached. I have seen many people doing it like this and am not sure what I am doing wrong.

PS: The issue is that the stored procedures that will be getting executed MUST all be run within a single transaction, I do not control these and they're encrypted, the reason they must be run in a transaction is because they create temp records in a table that has a PK requirement.

Answer 1

Thanks so much. I ended up figuring it out on my own based on other peoples combined answers, as a thank you here is the code I used:

public List<Models.eConnectModels.eConnStatus> CommitAsTransaction(List<SqlCommand> commands) 
{
    SqlTransaction transaction = null;
    SqlConnection connection = null;
    List<eConnStatus> ErrorList = new List<eConnStatus>();
            
    try 
    {
        connection = this.CreateSqlConnection();
        connection.Open();
                
        transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted, "TransactionID");

        foreach (SqlCommand cmd in commands) 
        {
            eConnStatus curErr = new eConnStatus();
            cmd.Transaction = transaction;
            cmd.Connection = connection;
            
            SqlParameter errorString = cmd.Parameters.Add("@oErrString", SqlDbType.VarChar);
            errorString.Direction = ParameterDirection.Output;
            errorString.Size = 8000;

            SqlParameter errorStatus = cmd.Parameters.Add("@O_iErrorState", SqlDbType.Int);
            errorStatus.Direction = ParameterDirection.Output;

            cmd.ExecuteNonQuery();
            curErr.ErrorState = (int)cmd.Parameters["@O_iErrorState"].Value;
            curErr.ErrorMessage = (string)cmd.Parameters["@oErrString"].Value;
            ErrorList.Add(curErr);
        }

        transaction.Commit();
    }
    catch (Exception ex) 
    {
        transaction.Rollback();
        connection.Close();
        throw ex;
    }
    
    connection.Close();
    return ErrorList;
}