DB Concurrency

[Steve]

Visual Studio 2003 .Net / C# / SQL 2000

I am trying to work out the best way to ensure data concurrency in my
application. All updates and inserts etc are done via stored procedures.
When a user enters a screen i call a stored procedure which returns me a
record or many records and display them in my screen. When they insert a
record I just call a stored procedure passing over all the screen values, and
the same with updating records. At the moment I have not done anything to do
with concurrancy in the application so I am open to all ideas. One idea I
have had is that each table has a "Version" field, and that everytime I
display the data you retrieve the version number, then when you update a
record you check this version number is still the same in the db, if it is
then you commit, if its not then you error as someone else must have updated
that record. But where would this code go? do I put this in each stored
proc or do I have a generic stored proc for it or to I code it into the
screen?

Thanks

Steve

[Scott M.]

Your idea of a "version" is a good one, but you don't need to create this,
you should already have a primary key field in your database and that field
is what you should be checking your new data against. It's not so much a
version of the existing data as it is making sure that you are working on
the correct record. The primary key ensures this.

In your sp's, you should be adding a WHERE condition that checks the primary
key of the record being edited against a primary key in the database to
ensure that the correct record gets modified.

"Steve" <St***@discussions.microsoft.com> wrote in message
news:8A**********************************@microsof t.com...

Visual Studio 2003 .Net / C# / SQL 2000

I am trying to work out the best way to ensure data concurrency in my
application. All updates and inserts etc are done via stored procedures.
When a user enters a screen i call a stored procedure which returns me a
record or many records and display them in my screen. When they insert a
record I just call a stored procedure passing over all the screen values,
and
the same with updating records. At the moment I have not done anything to
do
with concurrancy in the application so I am open to all ideas. One idea I
have had is that each table has a "Version" field, and that everytime I
display the data you retrieve the version number, then when you update a
record you check this version number is still the same in the db, if it is
then you commit, if its not then you error as someone else must have
updated
that record. But where would this code go? do I put this in each stored
proc or do I have a generic stored proc for it or to I code it into the
screen?

Thanks

Steve

[Steve]

I do already have a where in there to ensure i am updating the correct
record, the main issue I am trying to address though here is different. If a
user for example, brings a record up to view, then go and make a drink or
something so is away from their desk for a couple of minutes. Then another
user logs on, brings up that same record and changes it before user 1 comes
back. Then, user1 comes back and changes the record, theres a problem,
because the version of the data that he/she is looking at is now incorrect as
someone else changed that same record.

"Scott M." wrote:

Your idea of a "version" is a good one, but you don't need to create this,
you should already have a primary key field in your database and that field
is what you should be checking your new data against. It's not so much a
version of the existing data as it is making sure that you are working on
the correct record. The primary key ensures this.

In your sp's, you should be adding a WHERE condition that checks the primary
key of the record being edited against a primary key in the database to
ensure that the correct record gets modified.

"Steve" <St***@discussions.microsoft.com> wrote in message
news:8A**********************************@microsof t.com...

Visual Studio 2003 .Net / C# / SQL 2000

I am trying to work out the best way to ensure data concurrency in my
application. All updates and inserts etc are done via stored procedures.
When a user enters a screen i call a stored procedure which returns me a
record or many records and display them in my screen. When they insert a
record I just call a stored procedure passing over all the screen values,
and
the same with updating records. At the moment I have not done anything to
do
with concurrancy in the application so I am open to all ideas. One idea I
have had is that each table has a "Version" field, and that everytime I
display the data you retrieve the version number, then when you update a
record you check this version number is still the same in the db, if it is
then you commit, if its not then you error as someone else must have
updated
that record. But where would this code go? do I put this in each stored
proc or do I have a generic stored proc for it or to I code it into the
screen?

Thanks

Steve

[Scott M.]

No, the issue isn't different. Most "good" primary keys are made up of
values that are a combination of some unique data-specific value and a
timestamp. When used this way, the timestamp piece can be checked to see if
you are working with the same record "version" as is in the database.

"Steve" <St***@discussions.microsoft.com> wrote in message
news:62**********************************@microsof t.com...

I do already have a where in there to ensure i am updating the correct
record, the main issue I am trying to address though here is different.
If a
user for example, brings a record up to view, then go and make a drink or
something so is away from their desk for a couple of minutes. Then
another
user logs on, brings up that same record and changes it before user 1
comes
back. Then, user1 comes back and changes the record, theres a problem,
because the version of the data that he/she is looking at is now incorrect
as
someone else changed that same record.

"Scott M." wrote:

Your idea of a "version" is a good one, but you don't need to create
this,
you should already have a primary key field in your database and that
field
is what you should be checking your new data against. It's not so much a
version of the existing data as it is making sure that you are working on
the correct record. The primary key ensures this.

In your sp's, you should be adding a WHERE condition that checks the
primary
key of the record being edited against a primary key in the database to
ensure that the correct record gets modified.

"Steve" <St***@discussions.microsoft.com> wrote in message
news:8A**********************************@microsof t.com...

> Visual Studio 2003 .Net / C# / SQL 2000
>
> I am trying to work out the best way to ensure data concurrency in my
> application. All updates and inserts etc are done via stored
> procedures.
> When a user enters a screen i call a stored procedure which returns me
> a
> record or many records and display them in my screen. When they insert
> a
> record I just call a stored procedure passing over all the screen
> values,
> and
> the same with updating records. At the moment I have not done anything
> to
> do
> with concurrancy in the application so I am open to all ideas. One
> idea I
> have had is that each table has a "Version" field, and that everytime I
> display the data you retrieve the version number, then when you update
> a
> record you check this version number is still the same in the db, if it
> is
> then you commit, if its not then you error as someone else must have
> updated
> that record. But where would this code go? do I put this in each
> stored
> proc or do I have a generic stored proc for it or to I code it into the
> screen?
>
> Thanks
>
> Steve

[Cor Ligthert [MVP]]

Steve,

Are you inventing the wheel again?

Your wheel is in my opinion still from stone and not real round.

Have just a look on MSDN for concurrency.

http://msdn.microsoft.com/library/de...oncurrency.asp

I hope this helps,

Cor

[Frans Bouma [C# MVP]]

Steve wrote:

Visual Studio 2003 .Net / C# / SQL 2000

I am trying to work out the best way to ensure data concurrency in my
application. All updates and inserts etc are done via stored
procedures. When a user enters a screen i call a stored procedure
which returns me a record or many records and display them in my
screen. When they insert a record I just call a stored procedure
passing over all the screen values, and the same with updating
records. At the moment I have not done anything to do with
concurrancy in the application so I am open to all ideas. One idea I
have had is that each table has a "Version" field, and that everytime
I display the data you retrieve the version number, then when you
update a record you check this version number is still the same in
the db, if it is then you commit, if its not then you error as
someone else must have updated that record. But where would this
code go? do I put this in each stored proc or do I have a generic
stored proc for it or to I code it into the screen?

Please read:
http://weblogs.asp.net/fbouma/archiv...5/24/7499.aspx

Frans

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------