Transaction inside of code

Question

I'm having an issue where I'm preaty not sure how to resolve this and I want to know what is the best approach I should consider in order to achieve this task.

We are developping an application VB.net 2.0 and SQL 2005. Users are allowed to cancel a reception based on a purchase which may contains many received goods. But, during the process of cancellation, some questions are asked to users such as "Do you want to cancel Good #1". If yes, delete. Then, "Do you want to cancel Good #2", no, do not delete and one another question (if received item is issued, a process must be made manualy by the user). And, at the end, if all goods were successfully cancelled, we have to cancel the reception itself. But sometime, if an error occurs or some conditions occurs once asked to user in this process, we want to cancel any actions made from the beginning and make it back to his original state. So I thought about Transaction.

1. I know there is Transaction for SQL which can be used and I know good enough how to use it, but I can't realy use this as user must perform actions which possibly cancel this transaction.

2. I also remembered TransactionScope from .NET 2.X and over which can achieve something similar and I also know as well how to use it. The problem comes with TransactionScope and MSDTC. When using this, we still getting an error which said :

Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.

I've tried what is describe here in another stack post and it works great... until user restard their computer. EVERY time users restart their computer, they must put value back. Plus, per default, no computer have this value set to On. At least on 10 computers bases, none were activated. There is something like 300 computers on which this program is installed so it's surely not the good things to consider neither.

So anyone have an idea of how I can acheive this? Is there anything else doing transaction via code which I can use?

NOTE1 : I know some would say, first ask conditions to user and maintain values in memory. Once done, if everything went well, go with delete. But what if an error occurs when deleting let's say, goods #4? And how can I give to a store procedure a dynamic list of goods to be deleted?

NOTE2 : Sorry for my english, I usualy talk french.

NOTE3 : Any exemple in C# can be provide also as I know both VB and C#.

Answer 1

Assuming you already have similar stored procedure to manage cancelation:

create proc CancelGood (@goodID int)
as
   SET NOCOUNT ON
   SET XACT_ABORT ON

   begin transaction

   update table1 set canceled = 1
    where GoodID = @GoodID

   update table2 set on_stock = on_stock + 1
    where GoodID = @GoodID

   commit transaction

VB code adds a string to some canceledGoods list if user selects 'Oui'. I'm not familiar with VB.Net; in c# it would look like:

canceledGoods.Add (string.Format("exec dbo.CancelGood {0}", goodID));

Than, if there is at least one string in canceledGoods, build and execute batch:

batch = "BEGIN TRANSACTION" +
        " BEGIN TRY " +
        string.Join (Environment.NewLine, canceledGoods.ToArray()) + 
        " END TRY" +
        " BEGIN CATCH " +
        " -- CODE TO CALL IF THERE WAS AN ERROR" +
        "    ROLLBACK TRANSACTION" +
        "    RETURN" +
        " END CATCH" +
        " -- CODE TO CALL AFTER SUCCESSFULL CANCELATION OF ALL GOODS" +
        " COMMIT TRANSACTION"

conn.ExecuteNonQuery (batch);