VS2008 / SQL Server 2005 / Windows XP
I have been unable to generate a concurrency exception using a single table
in a DataContext.dbml with Insert / Update / Delete SPs via a LINQDataSource
and a FormView.
I am able to successfully add, update and delete rows but not generate a
concurrency exception.
After two days I'm feeling pretty darned stumped!! I can bundle the whole
thing up if anybody is up for taking a look.
Here is the SQL I am using:
CREATE TABLE [dbo].[Departments](
[PKID] [int] IDENTITY(1,1) NOT NULL,
[Code] [varchar](10) NOT NULL,
[Description] [varchar](50) NOT NULL,
[TS] [timestamp] NOT NULL,
CONSTRAINT [PK_Departments] PRIMARY KEY CLUSTERED
(
[PKID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE PROCEDURE [dbo].[zDepartmentInsert]
@PKID INT OUTPUT,
@Code VARCHAR(10),
@Description VARCHAR(50),
@TS TIMESTAMP OUTPUT
AS
INSERT INTO [dbo].[Departments] (
[Code],
[Description]
) VALUES (
@Code,
@Description)
SELECT
@PKID = SCOPE_IDENTITY(),
@TS = TS
FROM [dbo].[Departments]
WHERE
[PKID] = SCOPE_IDENTITY()
GO
CREATE PROCEDURE [dbo].[zDepartmentUpdate]
@PKID int,
@Code varchar(10),
@Description varchar(50),
@TS timestamp OUTPUT
AS
UPDATE [dbo].[Departments] SET
[Code] = @Code,
[Description] = @Description
WHERE
[PKID] = @PKID AND
[TS] = @TS
SELECT
@TS = [TS]
FROM [dbo].[Departments]
WHERE
[PKID] = @PKID
GO
CREATE PROCEDURE [dbo].[zDepartmentDelete]
@PKID int,
@TS timestamp
AS
DELETE FROM
[dbo].[Departments]
WHERE
[PKID] = @PKID AND
[TS] = @TS
GO

Signature
----------------------
Thanks - Jay Pondy
Patrice - 26 Mar 2008 19:18 GMT
What if you are using the same criteria in your SELECT than in your UPDATE.
IMO the problem is that the select always return a single row so it hides
the fact that the update statement didn't processed any row.
--
Patrice
> VS2008 / SQL Server 2005 / Windows XP
>
[quoted text clipped - 85 lines]
> [TS] = @TS
> GO
Jay Pondy - 26 Mar 2008 19:52 GMT
If I understand you correctly you are talking about the Update SP.
If I modify it so that it checks the @@RowCount to make sure the row was
updated before I grab the new TimeStamp it still does NOT detect a
concurrency problem.
IF @@RowCount = 1
SELECT
@TS = [TS]
FROM [dbo].[Departments]
WHERE
[PKID] = @PKID
If I add an ELSE statement and RAISERROR an exception does occur but as I
understand it the DataContext should be detecting the concurrency issue
without raising errors from the SPs.
> What if you are using the same criteria in your SELECT than in your UPDATE.
> IMO the problem is that the select always return a single row so it hides
[quoted text clipped - 92 lines]
> > [TS] = @TS
> > GO
Patrice - 26 Mar 2008 20:22 GMT
Yes as an optimistic concurrency issue anyway relates to the update (??).
My approach would be :
- drop whatever doesn't pertain to concurrency including the select
statement
- I would even add a 1=0 criteria clause to create a no brainer concurrency
issue
From here, it should work then :
- add back the criteria (you'll have now to actually create a concurrency
issue)
- add back the select statement (likely *before* doing the update so that
this result doesn't mess the update statement)
For now the goal is to make 100% sure that the update statement is seen as
affecting no records... From there we should be able to see if this is how
it is handled or if some more work is needed...
You have also a linq forum at :
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=123&SiteID=1
Good luck.
--
Patrice
Jay Pondy - 27 Mar 2008 11:14 GMT
Leaving only the update statement with a 1=0 criteria to force a concurrency
issue did not work. Same result - the formview appears as if the update was
made but no concurrency issue was raised.
> Yes as an optimistic concurrency issue anyway relates to the update (??).
>
[quoted text clipped - 21 lines]
> --
> Patrice
Patrice - 27 Mar 2008 11:40 GMT
And you only have an update now in your proc ? According to
http://weblogs.asp.net/scottgu/archive/2007/08/23/linq-to-sql-part-7-updating-ou
r-database-using-stored-procedures.aspx
it looks like a simple update should do it...
I'll try to give this a shot when coming back from lunch (two hours from now
here) (please post here if meanwhile you found the solution)....
--
Patrice
> Leaving only the update statement with a 1=0 criteria to force a
> concurrency
[quoted text clipped - 30 lines]
>> --
>> Patrice
Jay Pondy - 27 Mar 2008 12:59 GMT
Yes - I've read that article about five times and gone over it with a fine
tooth comb and wish Scott had made good on his promise to follow up with an
article dedicated to concurrency issues. It'll be at least 5 or 6 hours
before I can get back on this problem - I appreciate your interest and help.

Signature
Jay Pondy
--------------------------------
We see the world, not as it is, but as we are.
> And you only have an update now in your proc ? According to
> http://weblogs.asp.net/scottgu/archive/2007/08/23/linq-to-sql-part-7-updating-ou
r-database-using-stored-procedures.aspx
[quoted text clipped - 40 lines]
> >> --
> >> Patrice
Patrice - 27 Mar 2008 13:27 GMT
It's look tougher than expected. My understanding for now is that the idea
is that this is no more a framework base feature (previously it was checking
affected rows) but that you have to handle this if you are customizing the
linq behavior (likely so that you have full control if needed about what is
done especially if you add aditional linq providers ?).
http://linqinaction.net/blogs/jwooley/archive/2007/08/27/using-stored-procedures
-with-linq-with-concurrency-checking.aspx
could be a good starting point (it looks like it shows how to plug your own
code so that you get the original/new values). Additionaly you would have
also to add adtional code so that if the sp signals a concurrency error (for
example ain a return value) you explicitely throw conflictexception (and
posisbly you would have also to list concurrency conflic details if you need
to provide this info further down)...
Sorry for the poor help but I thought it was much similar to how it was
previously done. Hoepfully somone who have gone throguh this will finally
popup...
--
Patrice
Jay Pondy - 27 Mar 2008 21:19 GMT
The article you cited (on the beta2 version) mentions S Gutherie pointing
Wooley to a helper method on the table entity type to fetch the original
values. When I look at the code behind in DataContext.designer.cs the call
to the stored procedure method has the designer generated code Gutherie
mentions.
Based on your input I thought maybe a return value signals a concurrency
problem but varying the return made no difference. The designer generated
code actually does fetch what should be the new value for the time stamp and
returns it to the caller. When I do a normal update you can in fact see the
timestamp value change in the formview so it is being round tripped from the
stored procedure.
If I create the same simple project with a FormView and a LINQDataSource
bound to a table with an identity and timestamp and do NOT use stored
procedures and add the following code to the web form code behind:
protected void Page_Load(object sender, EventArgs e)
{
ds.Updated += new
EventHandler<LinqDataSourceStatusEventArgs>(ds_Updated);
}
protected void ds_Updated(object sender, LinqDataSourceStatusEventArgs e)
{
if (e.Exception != null && e.Exception is ChangeConflictException)
{
e.ExceptionHandled = true;
txtMessage.InnerHtml = e.Exception.Message;
}
}
and then open two browsers on the same record in edit mode to create a
concurrency fault the ChangeConflictException is in fact trapped. If I
examine the DataContext code behind there is designer generated code in there
to trap the concurrency or raise the exception which means this being handled
by the LINQ implementation itself.
Sign me
Still Puzzled...
> It's look tougher than expected. My understanding for now is that the idea
> is that this is no more a framework base feature (previously it was checking
[quoted text clipped - 16 lines]
> --
> Patrice
Jay Pondy - 28 Mar 2008 18:04 GMT
From the book "LINQ in Action": "Additionally, we'll be responsible for
handling concurrency conflicts explicitly."
Once you start using stored procedures for updates and deletes all of those
nifty concurrency features found in the DataContext disappear!!!

Signature
----------------------
Thanks - Jay Pondy
> The article you cited (on the beta2 version) mentions S Gutherie pointing
> Wooley to a helper method on the table entity type to fetch the original
[quoted text clipped - 58 lines]
> > --
> > Patrice