none
Can you do a transaction with these two commands? RRS feed

  • Question

  •  

    Can you use a TransactionScope somehow when your context uses the ExecuteStoreCommand?

    The code below I want to use only 1 transaction for everything in that using.  Is that possible?

     

    using(FSTEntities context = new FSTEntities())
    {
    context.ExecuteStoreCommand("DELETE FROM tblFSTPlannedOrders");
    
    foreach(var fpo in fstPlannedOrders)
    {
       context.tblFSTPlannedOrders.AddObject(new tblFSTPlannedOrder(fpo));
    }
    
    context.SaveChanges();
    }
    

    Thanks in advance!

     

     

    Friday, October 21, 2011 9:17 PM

Answers

  • I found a solution to my problem:

     

    public void FSTPlannedOrder_DeleteAllAndAddAll(List<FSTPlannedOrder> jdaPlannedOrders)
    		{
    			using(TransactionScope tran = new TransactionScope())
    			{
    				using(FSTEntities context = new FSTEntities())
    				{
    					try
    					{
    						context.SaveChanges(false);
    						context.ExecuteStoreCommand(SQL.SQL.FSTPlannedOrders_DeleteAllRows);
    						context.SaveChanges();
    
    						foreach(var fpo in jdaPlannedOrders)
    						{
    							context.tblFSTPlannedOrders.AddObject(new tblFSTPlannedOrder(fpo));
    						}
    
    						context.SaveChanges();
    						tran.Complete();
    						context.AcceptAllChanges();
    					}
    					catch
    					{
    						throw;
    					}
    				}
    			}
    

     

     

     

    • Marked as answer by Lavagin Tuesday, October 25, 2011 2:02 PM
    Tuesday, October 25, 2011 2:02 PM

All replies

  • On 10/21/2011 5:17 PM, Lavagin wrote:
    > Can you use a TransactionScope somehow when your context uses the
    > ExecuteStoreCommand?
    >
    > The code below I want to use only 1 transaction for everything in that
    > using. Is that possible?
    >
    > using(FSTEntities context = new FSTEntities())
    > {
    > context.ExecuteStoreCommand("DELETE FROM tblFSTPlannedOrders");
    >
    > foreach(var fpo in fstPlannedOrders)
    > {
    >     context.tblFSTPlannedOrders.AddObject(new tblFSTPlannedOrder(fpo));
    > }
    >
    > context.SaveChanges();
    > }
    >
     
    Why not everything you are doing is dealing with a database.
     using(var trans = new Transaction())
    {
     do your thing
    Save
    trans.Complete();
     
    }
     
    Friday, October 21, 2011 9:46 PM
  • Hi Lavagin,

    I think you can use SaveChange(false) to do the two commands, you can refer this blog:

    http://blogs.msdn.com/b/alexj/archive/2009/01/11/savechanges-false.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, October 24, 2011 2:47 AM
    Moderator
  • I am using this method.
     
    		public void FSTPlannedOrder_DeleteAllAndAddAll(List<FSTPlannedOrder> jdaPlannedOrders)
    		{
    			using(FSTEntities context = new FSTEntities())
    			{
    				
    				using(TransactionScope tran = new TransactionScope())
    				{
    					context.SaveChanges(false);
      					foreach(var fpo in jdaPlannedOrders)
    					{
    						context.tblFSTPlannedOrders.AddObject(new tblFSTPlannedOrder(fpo));
    					}
    					tran.Complete();
    					
    
    				}
    				context.AcceptAllChanges();
    			}
    

    But it never actually saves the changes to the database.
     
     
    Monday, October 24, 2011 12:21 PM
  • I found a solution to my problem:

     

    public void FSTPlannedOrder_DeleteAllAndAddAll(List<FSTPlannedOrder> jdaPlannedOrders)
    		{
    			using(TransactionScope tran = new TransactionScope())
    			{
    				using(FSTEntities context = new FSTEntities())
    				{
    					try
    					{
    						context.SaveChanges(false);
    						context.ExecuteStoreCommand(SQL.SQL.FSTPlannedOrders_DeleteAllRows);
    						context.SaveChanges();
    
    						foreach(var fpo in jdaPlannedOrders)
    						{
    							context.tblFSTPlannedOrders.AddObject(new tblFSTPlannedOrder(fpo));
    						}
    
    						context.SaveChanges();
    						tran.Complete();
    						context.AcceptAllChanges();
    					}
    					catch
    					{
    						throw;
    					}
    				}
    			}
    

     

     

     

    • Marked as answer by Lavagin Tuesday, October 25, 2011 2:02 PM
    Tuesday, October 25, 2011 2:02 PM