Child Parent Transactions roll back

Question

I have a scenario in which I have to process multiple .sQL files, every file contains 3-4 insert or Update queries, now when any query in a file fails I do rollback whole transaction means whole file we be rolled back , and all other files executed before that file will get committed, I want an option where user can rollback entire transaction means all queries in a file executed and all files executed before that particular file containing error, and if user wants to skip that particular file with error we will just rollback single file which contains error all other files will get committed, I am using SQL Transaction right now , no TransactionScope but obviously I can switch too TransactionScope() if needed and possible, Currently pseudo for my code (what i want) is as follows

Var Files[]
for each (string query in Files)
{
  Execute(Query)
IF(TRUE)
CommitQuery()
Else
result=MBOX("IF You want to abort all files or skip this one")
if(result=abort)
rollbackall()
else
QueryRollBack()
}

Trimming

Answer 1

It seems you are looking for SavePoints, i.e. the option to partially roll back and then resume a larger transaction. AFAIK TransactionScope doesn't support SavePoints so you'll need to deal directly with the native provider (e.g. SqlClient if your RDBMS is Sql Server). (i.e. you cannot leverage the ability of TransactionScope to implement DTC equivalent of SavePoints, e.g. across distributed databases, disparate RDBMS, or parallel transactions)

That said, I would suggest a strategy where the user elects to skip or abort up front, before transactional processing begins, as it will be expensive awaiting UI response while a large number of rows are still locked - this will likely cause contention issues.

Edit

Here's a small sample of using SavePoints. Foo1 and Foo3 are inserted, Foo2 is rolled back to the preceding save point.

using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Foo"].ConnectionString))
{
    conn.Open();
    using (var txn = conn.BeginTransaction("Outer"))
    {
        txn.Save("BeforeFoo1");
        InsertFoo(txn, "Foo1");

        txn.Save("BeforeFoo2");
        InsertFoo(txn, "Foo2");
        txn.Rollback("BeforeFoo2");

        txn.Save("BeforeFoo3");
        InsertFoo(txn, "Foo3");
        txn.Commit();
    }
}

Where InsertFoo is:

private void InsertFoo(SqlTransaction txn, string fooName)
{
    using (var cmd = txn.Connection.CreateCommand())
    {
        cmd.Transaction = txn;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "INSERT INTO FOO(Name) VALUES(@Name)";
        cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar)).Value = fooName;
        cmd.ExecuteNonQuery();
    }
}

And the underlying table is:

create table Foo
(
    FooId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name NVARCHAR(50)
)

Austere

thanks for suggesting that, i first considered that option but unfortunately i can not go for this option as i have 90+ .SQL files and every file contains more than 30 Queries , it will kill my UI – Trimming

I just wanted to make the point that every time that the messagebox pops up waiting for the user to make a decision, that all rows locked by the "outer" transaction will still be locked, and possibly escalated to e.g. Table Locks. This won't be good for concurrent access. If there is concurrent activity, you'll also be at risk of your whole batch being terminated as the victim of a deadlock. – Austere

Exactly , That is why i have provided the user an option to suppress message box in that case whole process will rollback without any user interruption , i just want to achieve skip or abort scenario, and also i will be running this code for newly created DB so there will be no pending transactions from anywhere for that DB (No one will be using that DB untill i am done with it) – Trimming

I've updated with a sample of SavePoints. If at any point your user decides to Rollback the whole transaction, txn.RollBack() will roll everything back, otherwise, RollBack("savePointX") will roll back to savePointX – Austere

Answer 2

Keep all insert, update queries in a try{..}catch(..){..} and if any exception occurs, in the catch roll the db transaction back.

     private void InsertFoo(SqlTransaction txn, string fooName)
     {
         using (var cmd = txn.Connection.CreateCommand())
         {
             try
             {
                 do your process here...
                 cmd.Commit();
             }
             catch(Exception ex)
             {
                cmd.Rollback();
             }
         }
     }

Flavescent