Home > Archive > ASP .NET > March 2008 > Concurrency / LINQDataSource / FormView / Stored Procedures
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Concurrency / LINQDataSource / FormView / Stored Procedures
|
|
| Jay Pondy 2008-03-26, 7:56 pm |
| 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
--
----------------------
Thanks - Jay Pondy
| |
| Patrice 2008-03-26, 7:56 pm |
| 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
"Jay Pondy" <jpondy@AugustaNewsprint.com> a écrit dans le message de news:
452954B7-19B3-455B-8937-1228F76FD95A@microsoft.com...
> 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
>
> --
> ----------------------
> Thanks - Jay Pondy
>
| |
| Jay Pondy 2008-03-26, 7:56 pm |
| 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.
"Patrice" wrote:
> 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
>
> "Jay Pondy" <jpondy@AugustaNewsprint.com> a écrit dans le message de news:
> 452954B7-19B3-455B-8937-1228F76FD95A@microsoft.com...
>
>
>
| |
| Patrice 2008-03-26, 7:56 pm |
| 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/Sh...ID=123&SiteID=1
Good luck.
--
Patrice
| |
| Jay Pondy 2008-03-27, 8:19 am |
| 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.
"Patrice" wrote:
> 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/Sh...ID=123&SiteID=1
>
> Good luck.
>
> --
> Patrice
>
>
>
>
>
| |
| Patrice 2008-03-27, 8:19 am |
| And you only have an update now in your proc ? According to
http://weblogs.asp.net/scottgu/arch...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
"Jay Pondy" <jpondy@AugustaNewsprint.com> a écrit dans le message de news:
47D2CF08-7E91-412F-B30C-77D249923373@microsoft.com...[color=darkred]
> 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.
>
>
> "Patrice" wrote:
>
| |
| Jay Pondy 2008-03-27, 8:19 am |
| 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.
--
Jay Pondy
--------------------------------
We see the world, not as it is, but as we are.
"Patrice" wrote:
> And you only have an update now in your proc ? According to
> http://weblogs.asp.net/scottgu/arch...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
>
>
>
> "Jay Pondy" <jpondy@AugustaNewsprint.com> a écrit dans le message de news:
> 47D2CF08-7E91-412F-B30C-77D249923373@microsoft.com...
>
>
>
| |
| Patrice 2008-03-27, 8:19 am |
| 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/jwool...y-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 2008-03-27, 7:35 pm |
| 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...
"Patrice" wrote:
> 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/jwool...y-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 2008-03-28, 7:28 pm |
| 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!!!
--
----------------------
Thanks - Jay Pondy
"Jay Pondy" wrote:
[color=darkred]
> 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...
>
>
>
> "Patrice" wrote:
>
|
|
|
|
|