none
Sprocs and Optimistic Concurrency RRS feed

  • Question

  • Let's say I have a three column table where updates are done through a sproc:


    CREATE TABLE dbo.Person
    (
      Id int PRIMARY KEY NOT NULL,
      FullName nvarchar(64),
      Version timestamp
    )


    CREATE PROCEDURE dbo.Person_Update
      @id int,
      @fullName nvarchar(64),
      @version timestamp OUTPUT
    AS

      UPDATE dbo.Person SET [FullName] = @fullName
      WHERE [Id] = @id AND [Version] = @version

      SET NOCOUNT ON;

      SELECT @version = [Version]
      FROM dbo.Person (NOLOCK)

    Because of the way System.Data.Linq.ChangeDirector+StandardChangeDirector is written, an update against a stale “Version” with a sproc will not cause a conflict to be created or an exception to be thrown.  After executing dbo.Person_Update the rows affected would be zero, but this is not an accessible property on IExecuteResult.  I could change the sproc to store and then return @@rowcount and then throw a ChangeConflictException, but I would need to write custom code in the auto-generated DataContext.Person_Update sproc wrapper.  Additionally I can't add an ObjectChangeConflict because it's internal, which then requires me to handle sproc conflicts different from dynamic SQL conflicts.

                                                                                                                                                        

    How does Microsoft recommend handling optimistic concurrency with sprocs? 

    Monday, August 4, 2008 7:04 PM

Answers

  • You can get this information out of the stored proc.  When a sproc does not return a result set it still returns a value. The IExecuteResult.Value property returns an instance of IFunctionResult which also has a .Value property.  This IFunctionResult.Value property will hold the return value of the stored proc (an int for SQL Server.)  This value will normally be the @@rowcount value unless you turn NOCOUNT ON like you did. However, you can explicitly return a value via the RETURN statement.

     

    The bigger problem is that the code generated for the UpdatePerson method (not the stored proc wrapper) calls the stored proc method but does nothing with the return value.

     

    What you really want there is something like this:

     

    public partial MyDataContext {


       private void UpdatePerson(Person p) {

            long version = p.Version;

            IFunctionResult result = this.PersonUpdate(p.Id, p.FullName, ref version);

            if ((int)result.Value != 1) throw new ChangeConflictException();

            p.Version = version;

       }

     

       private IFunctionResult PersonUpdate(int id, string fullname, ref long version) { ... }
    }

     

     

    CREATE PROCEDURE dbo.Person_Update
      @id int,
      @fullName nvarchar(64),
      @version timestamp OUTPUT
    AS

      DECLARE @rc int


      UPDATE dbo.Person SET [FullName] = @fullName
      WHERE [Id] = @id AND [Version] = @version

      SET @rc = @@rowcount


      SET NOCOUNT ON

      SELECT @version = [Version]
      FROM dbo.Person (NOLOCK)
      WHERE [id] = @id

     

      RETURN @rc

    Tuesday, August 5, 2008 1:51 AM
    Moderator