Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsFree MagazinesWhite PapersSubmit Content
Discussion GroupsASP.NETWindows FormsLanguages.NET FrameworkVisual Studio.NET
Articles.NET FrameworkASP.NETToolsWindows Forms
.NET DirectoryOpen Source ProjectsUser GroupsWeb Resources
Related Topics
Visual Basic 6SQL ServerMS AccessOther DB ProductsMS Server ProductsMore Topics ...

.NET Forum / ASP.NET / General / March 2008

Tip: Looking for answers? Try searching our database.

Concurrency / LINQDataSource / FormView / Stored Procedures

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jay Pondy - 26 Mar 2008 18:52 GMT
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

Rate this thread:







Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.