473,326 Members | 2,061 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

DB Concurrency

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
Aug 30 '05 #1
5 1462
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

Aug 30 '05 #2
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


Aug 30 '05 #3
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


Aug 30 '05 #4
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

Aug 30 '05 #5
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#)
------------------------------------------------------------------------
Aug 30 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
by: aurora | last post by:
Hello! Just gone though an article via Slashdot titled "The Free Lunch Is Over: A Fundamental Turn Toward Concurrency in Software" http://www.gotw.ca/publications/concurrency-ddj.htm]. It argues...
4
by: Charlie Williams | last post by:
I am having difficulty performing updates and deletions on an Access database using the Update() method of the OleDBDataAdapter. I can insert rows without a problem, but I get a concurrency...
3
by: Suzanne | last post by:
Hi All I'm having problems getting my data adapter to throw a concurrency exception with an INSERT command. I want to throw a concurrency exception if an attempt is made to enter a row into...
2
by: xAvailx | last post by:
I have a requirement that requires detection of rows deleted/updated by other processes. My business objects call stored procedures to create, read, update, delete data in a SQL Server 2000 data...
4
by: Robert Schuldenfrei | last post by:
Dear NG, I was about to "improve" concurrency checking with a Timestamp when I discovered that my current code is not working. After about a day of beating my head against the wall, I am...
8
by: Mike Kelly | last post by:
I've chosen to implement the "optimistic concurrency" model in my application. To assist in that, I've added a ROWVERSION (TIMESTAMP) column to my main tables. I read the value of the column in my...
4
by: Bob | last post by:
While testing my my program I came up with a consistency exception. My program consists of three datagridviews, One called dgvPostes which is the parent grid and its two children,one called...
7
by: William E Voorhees | last post by:
I'm updating an Access database in a windows multi-user environment. I'm using disconnected data I read data from an Access Data table to a data object I update the data object from a...
3
by: John | last post by:
Hi I have a vs 2003 winform data app. All the data access code has been generated using the data adapter wizard and then pasted into the app. The problem I have is that I am getting a data...
5
by: John | last post by:
Hi I have developed the following logic to handle db concurrency violations. I just wonder if someone can tell me if it is correct or if I need a different approach.Would love to know how pros...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.