Sunday, November 30, 2008

LINQ to SQL SubmitChanges()

The DataContext.SubmitChanges() method will update the database in the following order:

- Perform all inserts
- Perform all updates
- Perform all deletes

Running SQL Profiler while running the following code will first perform all inserts on product, then the updates and the deletes. Also note that SubmitChanges will perform a batch update (which is good). The SubmitChanges is a single statement, but it does not implement a transaction, so if you want a rollback to occur if an exception occurs (for instance a foreign key violation) then you'll need to use a TransactionScope.

public void UpdateOrders()

    using ( var db = new VideoGameStoreDBDataContext() )

        var query =  from p in db.Products

                    select p;

        foreach(var product in query)

            product.ListPrice = product.ListPrice * 1.1;

            if ( product.ListPrice > 400 )

                db.Products.DeleteOnSubmit( product );

        ProductType game = (from pt in db.ProductTypes

                         where pt.ProductTypeName == "Game"

                         select pt).Single();

        Product newProduct = new Product()

            ProductName = "Travian",

            ListPrice = 20,

            ProductType = game,

            ProductDescription = "Online Game",

            ProductTypeID = 1,

            ListPriceCurrency = "$"

};

        db.Products.InsertOnSubmit( newProduct );

        db.SubmitChanges();

LINQ

Comments [1] |

Tuesday, December 16, 2008 8:42:02 AM (Eastern Standard Time, UTC-05:00)

As far as i know the SubmitChanges does use a transaction. It Starts a new transactions if it isn't already in a transaction.
See bullet 3 Submit Changes to the Database (LINQ to SQL)

RednasNavMad

Comments are closed.