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