Is it possible to use DependentTransaction or multiple DependentTransactions with TPL/Parallelism?

I haven't been able to find anything remotely similar to what I am doing and due to budget concerns, I have to turn to you all for some a开发者_StackOverflow社区ssistance. I am currently writing an import application that can import a variable amount of records. More than likely, this variable number will be very high, potentially in the millions. We utilize WCF streaming to send chunks of data to the server for processing. On the server end, we dump the data into a temp SQL table for validation. From there, using TPL to extract a set number of records and then insert them into the permanent SQL storage repository. There's a lot more involved, but in a nutshell that is the bulk of it. The only outstanding issue at this time is that we allow the user to cancel the import at anytime. This means that the cancel could occur during the saving of these records. We need to be able to rollback all of the inserted/updated records. Since we are using TPL to process the records, I am having an extremely difficult time sharing the transactions. I am using the DependentTransaction approach outlined on MSDN (very similar, http://msdn.microsoft.com/en-us/library/system.transactions.dependenttransaction(v=VS.100).aspx). Here are some code snippets:

var currentTransaction = System.Transactions.Transaction.Current;

...

// DataTable is simply a placeholder for X amount of records from the SQL temp table
var partitions = Partitioner.Create(0, DataTable.Rows.Count, 100);
Parallel.ForEach(partitions, (partition, state) =>
                    {
                        using (var parallelTransaction = new TransactionScope(currentTransaction.DependentClone((DependentCloneOption.RollbackIfNotComplete)))

Inside this TPL iteration, there are many selects, inserts and updates occurring. I want these all to occur under the same transaction if possible. Or at least be manageable by a TransactionScope so that we can roll everything back. I keep getting the error that the "transaction context is already in use" and then it aborts, pretty much instantly.

Am I not using the DependentTransaction properly? Can my request even be possible? I know that transactions are typically sequential and are not used in this context, but for simplicity and to help us from having to implement some crazy functionality to keep track of inserted records and rolling-back manually, a transaction would be ideal!

Any suggestions?

NOTE: I have tried using the local state approach to the Parallel iteration, but no matter what I do, it all generates the same exception. Also, using RollbackIfNotComplete or BlockCommitUntilComplete has no bearing.

The best place to manage SQL Server transactions is within T-SQL code. Since you already have all the data you want to insert/update/delete in the same SQL Server, I don't see any good reason not to just perform your selects/inserts/updates within a transaction in T-SQL code. Here is a simple template you can use.

    -- REFERENCE: http://aleemkhan.wordpress.com/2006/07/21/t-sql-error-handling-pattern-for-nested-transactions-and-stored-procedures/
    -- ======================================================================================
    -- STANDARD HEADER FOR TRANSACTION LOGIC THAT WILL ALSO HANDLE BEING A NESTED TRANSACTION
    -- FOR SQL 2005 AND UP
    -- ======================================================================================
    SET XACT_ABORT ON;
    BEGIN TRY
    DECLARE @TranStarted bit; SET @TranStarted = 0
    IF( @@TRANCOUNT = 0 ) BEGIN  BEGIN TRANSACTION; SET @TranStarted = 1;  END ELSE SET @TranStarted = 0
    -- ======================================================================================




    -- ==================================================================
    -- ***** SQL CODE FOR SELECTS/INSERTS/UPDATES/DELETES GOES HERE *****
    -- ==================================================================




    -- ======================================================================================
    -- STANDARD FOOTER FOR TRANSACTION LOGIC THAT WILL ALSO HANDLE BEING A NESTED TRANSACTION 
    -- FOR SQL 2005 AND UP
    -- ======================================================================================
    IF( @TranStarted = 1 AND (XACT_STATE()) = 1 ) BEGIN  SET @TranStarted = 0; COMMIT TRANSACTION;  END
    RETURN(0)
    END TRY
    BEGIN CATCH
      DECLARE @ErrorMessage  nvarchar(4000)
             ,@ErrorNumber   int
             ,@ErrorSeverity int
             ,@ErrorState    int;
       SELECT @ErrorMessage  = ERROR_MESSAGE() + CHAR(13) + 'Actual Code Line that took the error: ' + CONVERT(nvarchar, ERROR_LINE())
                                               + CHAR(13) + 'Actual Proc that took the error: ' + CONVERT(nvarchar(126), ERROR_PROCEDURE())
             ,@ErrorSeverity = ERROR_SEVERITY()
             ,@ErrorState    = ERROR_STATE()
             ,@ErrorNumber   = ERROR_NUMBER();

    IF( @TranStarted = 1 AND (XACT_STATE()) = -1 ) BEGIN  SET @TranStarted = 0; ROLLBACK TRANSACTION; END
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
    -- ======================================================================================