Checking for changes to database

Has anyone got code for checking if the database row being viewed has
changed since it was delivered to the browser? ie before the UPDATE is
run? I'd imagine it would work something like this:

First page request (doing the select):
======================================
select row from table
store row in session
bind controls to row values (dataset or whatever)
end

Wait for user to do edit, and press submit.

Page postback:
==============
begin transaction
select same row from database again
compare with row held in session
if they match, apply changes from user postback
if they don't, inform user underlying data has changed, and request
confirmation (showing 'befores' and 'afters')
commit
end

Miha
Sun Nov 23 07:00:55 CST 2003

Hi John,

You might want to read the article
Introduction to Data Concurrency in ADO.NET
in .net help files.

It is like you've described - the main difference would be that comparing
values is done within sql statament itself (WHERE clause).
Plus, I wouldn't put confirmation dialog within transaction itself
(transaction should be as quick as possible) rather I would do rollback and
repeat transaction if user wants to.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

"John Sparrow" <jsparrow@ecclescollege.ac.uk> wrote in message
news:1357b958.0311230444.612acdbb@posting.google.com...
> Has anyone got code for checking if the database row being viewed has
> changed since it was delivered to the browser? ie before the UPDATE is
> run? I'd imagine it would work something like this:
>
> First page request (doing the select):
> ======================================
> select row from table
> store row in session
> bind controls to row values (dataset or whatever)
> end
>
> Wait for user to do edit, and press submit.
>
> Page postback:
> ==============
> begin transaction
> select same row from database again
> compare with row held in session
> if they match, apply changes from user postback
> if they don't, inform user underlying data has changed, and request
> confirmation (showing 'befores' and 'afters')
> commit
> end

jsparrow
Sun Nov 23 16:00:31 CST 2003

Thanks for the reply.

I fully agree about the dialog thing inside the transaction. But I
only use ado.net in the context of asp.net, so that wouldn't really be
an issue (just redirect to 'reconcile' page or whatever).

So with this WHERE clause thing, how does it remember what the row
looked like from the initial select query??

I should RTFM I know (and I will!), but help would be appreciated.

Thanks,

John

"Miha Markic" <miha at rthand com> wrote in message news:<uWizZNcsDHA.640@tk2msftngp13.phx.gbl>...
> Hi John,
>
> You might want to read the article
> Introduction to Data Concurrency in ADO.NET
> in .net help files.
>
> It is like you've described - the main difference would be that comparing
> values is done within sql statament itself (WHERE clause).
> Plus, I wouldn't put confirmation dialog within transaction itself
> (transaction should be as quick as possible) rather I would do rollback and
> repeat transaction if user wants to.
>
> --
> Miha Markic - RightHand .NET consulting & development
> miha at rthand com
>
> "John Sparrow" <jsparrow@ecclescollege.ac.uk> wrote in message
> news:1357b958.0311230444.612acdbb@posting.google.com...
> > Has anyone got code for checking if the database row being viewed has
> > changed since it was delivered to the browser? ie before the UPDATE is
> > run? I'd imagine it would work something like this:
> >
> > First page request (doing the select):
> > ======================================
> > select row from table
> > store row in session
> > bind controls to row values (dataset or whatever)
> > end
> >
> > Wait for user to do edit, and press submit.
> >
> > Page postback:
> > ==============
> > begin transaction
> > select same row from database again
> > compare with row held in session
> > if they match, apply changes from user postback
> > if they don't, inform user underlying data has changed, and request
> > confirmation (showing 'befores' and 'afters')
> > commit
> > end

jsparrow
Sun Nov 23 16:09:25 CST 2003

Just read it!

So presumably if the underlying data had changed, the UPDATE would
fail to find a match, and you'd know about it because @@ROW_COUNT
would be zero?

I still dont understand where it gets the old values from at the
application layer...

It's a simple enough strategy I suppose, just doesnt provide any
diagnostic info about what went wrong. I was thinking more about
comparing the two DataRow objects and then using CustomValidators to
provide user feedback.

It would also be *really* cool to dynamically generate the UPDATE sql
to only alter the affected field(s) (again you'd have to buffer the
original DataRow in the session i guess). Then you could have
simultanious editing of the same row, provided it was different
fields!!

I should get out more...

John

"Miha Markic" <miha at rthand com> wrote in message news:<uWizZNcsDHA.640@tk2msftngp13.phx.gbl>...
> Hi John,
>
> You might want to read the article
> Introduction to Data Concurrency in ADO.NET
> in .net help files.
>
> It is like you've described - the main difference would be that comparing
> values is done within sql statament itself (WHERE clause).
> Plus, I wouldn't put confirmation dialog within transaction itself
> (transaction should be as quick as possible) rather I would do rollback and
> repeat transaction if user wants to.
>
> --
> Miha Markic - RightHand .NET consulting & development
> miha at rthand com
>
> "John Sparrow" <jsparrow@ecclescollege.ac.uk> wrote in message
> news:1357b958.0311230444.612acdbb@posting.google.com...
> > Has anyone got code for checking if the database row being viewed has
> > changed since it was delivered to the browser? ie before the UPDATE is
> > run? I'd imagine it would work something like this:
> >
> > First page request (doing the select):
> > ======================================
> > select row from table
> > store row in session
> > bind controls to row values (dataset or whatever)
> > end
> >
> > Wait for user to do edit, and press submit.
> >
> > Page postback:
> > ==============
> > begin transaction
> > select same row from database again
> > compare with row held in session
> > if they match, apply changes from user postback
> > if they don't, inform user underlying data has changed, and request
> > confirmation (showing 'befores' and 'afters')
> > commit
> > end