how do i Reset Transactionscope and commit after?

Question

how can i achieve this ? is this possible or should i declare another transaction scope?

    using (TransactionScope scope = new TransactionScope())
        {
            using (SqlConnection connection1 = new SqlConnection(connectString1))
            {
                connection1.Open();

                SqlCommand command1 = new SqlCommand(NomProcedure, connection1);
                command1.CommandType = CommandType.StoredProcedure;
                command1.ExecuteNonQuery();

                //Rollback transaction Here 

                SqlCommand command2 = new SqlCommand("dbo.AddHistory", connection1);
                command2.CommandType = CommandType.StoredProcedure;
                command2.ExecuteNonQuery();

            }

            scope.Complete();
        }

i only want to commit the second command

Answer 1

From the moment you start transaction any change to data is not confirmed until you complete transaction.

If you want to rollback, then simply do not call Complete():

using (TransactionScope scope = new TransactionScope())
{
    ...
    // do not call this to rollback what you did before
    scope.Complete();
}

This can be done by throwing exception while having transaction inside try/catch (as msdn suggest) or by calling Complete conditionally (if).

---

If you want to execute command while still being inside rolled back transaction, then it should be like this:

using (SqlConnection connection1 = new SqlConnection(connectString1))
{
    connection1.Open();
    using (TransactionScope scope = new TransactionScope())
    {
        ... // some commands

        // if you need to rollback and execute other command
        if(blabla)
        {
            scope.Dispose(); // without this further command is also rolled back
            ... // command
        }
        else
            scope.Complete(); // everything is fine, confirm transaction
    }
}