1

I am Using Transaction Scope for Performing the Insert In Multiple Tables using Try and Catch. But when i am Getting the Error Within Transaction Scope it's not allowing me to Save the Data in catch also.

My Code

using (var transaction = new TransactionScope())
{
   try
   {
     //Insert in Table1
     //Insert in Table2
     //Insert in Table3

     transaction.Complete();    
     transaction.Dispose();    
   }
   catch(Exception ex)
   {
     transaction.Dispose();
     //Insert in ErrorHandlerTable (Independent Table)
   }
}

Now The Problem is whenever i am getting the error in try block for foreign key constraints i am unable to insert into ErrorHandlerTable (Independent Table). Always Getting Following Exception:

{"The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_Table1_PkId\". The conflict occurred in database \"MyTransactionDatabase\", table \"dbo.Table2\", column 'PkId'.\r\nThe statement has been terminated."}

Can anyone help in this?

cracker
  • 4,900
  • 3
  • 23
  • 41
  • If `Insert in Table1` failed, how can you insert a record referring to it? This has nothing to do with the transaction scope, the exception occurs regardless. But anyway, just remove all `transaction.Dispose();` statements. – Gert Arnold Jul 05 '14 at 15:12
  • Yeah, i am not able to insert in the tables referring to it but i want to insert the error which is generated from while inserting the data into one independent table called ErrorHandlerTable but it's still giving me the error which i got while inserting the data. so what i should do in that case – cracker Jul 07 '14 at 04:37
  • If you keep getting a foreign key conflict then make sure you insert something *without* foreign key reference. What makes you think you can fix the exception by playing with transaction scopes? – Gert Arnold Jul 07 '14 at 08:21
  • I am using foreign key for all the tables as i am working with LINQ so is there any option rather then using without foreign key reference!! – cracker Jul 07 '14 at 09:00
  • I have used transaction scope because i need to insert data in multiple tables using each of the primary key (used as foreign key's for other tables) and i want to roll back the transactions in case of any error while inserting the data. As i want to track error which occurred during the execution of the transactions. – cracker Jul 07 '14 at 09:23
  • Is it maybe the dbContext.SaveChanges() that causes saving of the other tables (Table2, Table1..) ? – Kr15 Jul 10 '14 at 08:54

3 Answers3

0

As answered here INSERT statement conflicted with the FOREIGN KEY constraint :-

In your table ysmgr.Table2, it has a foreign key reference to another table. The way a FK works is it cannot have a value in that column that is not also in the primary key column of the referenced table.

If you have SQL Server Management Studio, open it up and sp_help 'ysmgr.Table2'. See which column that FK is on, and which column of which table it references. You're inserting some bad data.

So the steps are :-

1.run sp_helpconstraint

2.pay ATTENTION to the constraint_keys column returned for the foreign key

Community
  • 1
  • 1
Neel
  • 11,625
  • 3
  • 43
  • 61
  • is there PkId in your error table as foreign key? @cracker – Neel Jun 30 '14 at 06:53
  • PkId is Primary key in Table1 and Foreign Key in Table2, so i am sending the Data which are not in Table1. That why it's giving me error. But why i am not able to store the exception in ErroHandlerTable as there it does not contains any relations. – cracker Jun 30 '14 at 06:56
  • does your catch statement catch the exception? @cracker – Neel Jun 30 '14 at 06:57
  • you are disposing first or adding first? coz I cant see any code of inserting in your code example @cracker – Neel Jun 30 '14 at 07:00
  • i am committing the transaction first then disposing the transctions but the error which generated in try block is not allowing me to insert data in cath block also – cracker Jun 30 '14 at 07:03
0

The problem is that, even if your code has disposed the TransactionScope, when it inserts the data in the ErrorHandlerTable tha happens still inside the TransactionScope. So, something is going wrong, and you get a misleading error.

To avoid this, change the code so that the insertion in the ErrorHandlerTable is done outside of the original transaction scope. To do so, you can nest a new using block to provide a new, independent TransactionScope like this:

using(var ts  = new TrasanctionScope(TransactionScopeOption.RequiresNew)

or this

using(var ts  = new TrasanctionScope(TransactionScopeOption.Suppress)

The first option simply creates a new transaction, indepenend of the original one. But, if your insert is an atomic operation, as it seems, you can also use the second option, which creates a new independent transactionless scope.

In this way you can be sure that your insertion in the ErrorHandlerTable happens without any interference with the original transaction scope.

Please, see this docs:

JotaBe
  • 38,030
  • 8
  • 98
  • 117
  • Thanks JotaBe, Let me try this. – cracker Jul 05 '14 at 12:08
  • This is not correct. If the TS is disposed it's not effective anymore. The code after the dispose runs as if there never was a TS. – Gert Arnold Jul 05 '14 at 15:15
  • @GertArnold Is there any place where I can see how that works? Or do I have to look directly at the source code? The TransactionScope reference, and the related object is still be available at that point (even if `Dispose` has ben invoked). So I thought that the "ambient transaction" in which the last operation happens is still the same, an thus that operation happens inside the same scope. What happens when the TransactionScope is disposed that allows the following operations to happen outside of the transaction? Thanks for your comment!! – JotaBe Jul 06 '14 at 20:47
  • Oh, it is not specifically related to TS. It's just how `using` works. As you undoubtedly know a `using` disposes an object at the end of the `using` scope (defined by the braces). But if for some reason the object is disposed earlier it's roughly the same as moving the closing brace to that point. It depends on the `Dispose` implementation of an object what happens when it's disposed. In case of a TS it's a rollback (if `Complete()` has not been called). – Gert Arnold Jul 06 '14 at 20:58
  • If this was entity you would not need transaction scope for related tables if referenced tables are in same dbset – Mike Beeler Jul 10 '14 at 03:13
0

I think this will help you to revert the operations in the tables, please try using below stuff

   using (var transaction = new TransactionScope())
   {
     try
     {
         //Insert in Table1
         //Insert in Table2
         //Insert in Table3

         transaction.Complete();    
         transaction.Dispose();    
     }
     catch(Exception ex)
     {
        transaction.Dispose();  

          //what i have changed
          context.Table1 Table1Object = new YoSafari.Migration.EntityFramework.Table1(); //Create New Object of the table in which u want to insert i.e. Table1 or Table2 etc..
           using (var context = new ContextClass())
           {
              context.Entry(Table1Object).State = EntityState.Unchanged;                  
              //Insert in ErrorHandlerTable (Independent Table i.e. Table1 or Table2 etc..)
              context.SaveChanges();
           }
     }
  }

It will create new object of the tables that will Unchanged the operations and allow you to insert the record in to your ErrorHandlerTable

Please let me know if you are still facing any issue with this.

Deep Shah
  • 293
  • 5
  • 21