Concurrency with Linq To Sql Stored Procedures

Question

I come from the asp.net world where we'd use an objectdatasource, hooked up to data access layer, and set it's ConflictDetection property to "CompareAllValues". There is an OldValuesParameterFormatString on the ObjectDataSource that you use to identify old value parameters.

The sql procedure that does an update would then require both new params and old params and that was it... Super simple to implement; the ODS handled the old values for you.

I've moved over to Linq to SQL and WinForms. I've created a WCF service that is our business layer and I have a stored procedure that will update some table. In the data context designer I see that there is an Update Check property on my class columns. I'm not directly updating the table from the class, rather I'm calling a stored procedure to do the update. Is there some way to retain the original values, perhaps from the data context, similar to they way an objectdatasource would?

Answer 1

Not the answer you may be looking for, but since you mentioned using WCF as a business layer along with LINQ2SQL, I felt it is my obligation to point out this article for your reference:

http://www.sidarok.com/web/blog/content/2008/05/26/linq-to-sql-with-wcf-in-a-multi-tiered-action-part-1.html

While the article implements ASP.NET as the main presentation layer, but considering your background, it might actually make the article easier to understand.

I personally handled the same sort of development as you are doing right now (winforms for client, WCF for business logic layer, LINQ2SQL for data access), but being complete beginner to WCF & LINQ2SQL at the time, I basically forced to drop the original values retention. This article comes closest to your needs, though to be honest I've not seen anything that works with using stored procedures.

Answer 2

Are you using stored procedures directly (through a SqlCommand) or through LINQ to SQL? LINQ to SQL supports using stored procs for all its database access. You might want to look at Updating our Database using Stored Procedures, part 7 of Scott Guthrie's blog post series about LINQ to SQL. You can setup the use of sprocs through the DBML designer or in code using a DataContext partial class. The idea is that you send both the new and original values (e.g. Name and OriginalName) to the sproc so it can to its concurrency checking.

If you are using the sproc directly and not through LINQ to SQL, and all you want is to get the object's original values, you can obtain them by using Table<T>.GetOriginalEntityState() like this:

Order modifiedOrder = db.Orders.First();  // using first Order as example
modifiedOrder.Name = "new name";          // modifying the Order
Order originalOrder = db.Orders.GetOriginalEntityState(modifiedOrder);

Answer 3

• Ditch the sprocs and create a new DBML file for your tables.
• Drag your tables in and bam! LinqToSql will create entity classes for you with methods for updating (creating.. etc).

LinqToSql has a number of approaches for concurrency. One of the overloaded Attach() methods (used for updates) requires 2 params: the original entity and the new entity. LinqToSql will do what the ObjectDataSource used to do and compare the old values with the new and throw concurrency exceptions (it even makes handling concurrency exceptions much easier. +10 ..but that's not your question).

Use this reference, particularly the section towards the bottom called With Complete Entities. It makes a lot of sense and shows how the different concurrency approaches are used and how to handle the exceptions.

hav fun.