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()
}

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)
)

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();
             }
         }
     }