?
Solved

Updating with multiple users

Posted on 2009-02-17
12
Medium Priority
?
183 Views
Last Modified: 2012-05-06
Hi all,

This is a bit of a general question but here we go.

I have a datagridview on a windows form (VS 2005). This DGV gets its data from a dataview which feeds off a dataset which gets its data from a SQLdataadaptor (Looking at a SQL Server 2000 database). The DGV is filled on open and any changes (deleted/inserted or changed records) are saved on exit.

My query is. What happens if three users have the form open. One adds a record, one deletes a record and one makes a change to a record. What can I expect to be the result? Will it crash or will all changes be written successfully? I'm trying to get my head around the fact different users may be looking at different data depending upon when they opened the form. Someone who leaves it open all day would not see any changes users have been making throughout the day.

How would the experts recommend dealing with such situations?

Thanks in advance

What I would like to know
0
Comment
Question by:FMabey
  • 6
  • 6
12 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23658052
Basically this deals with Concurrency, And to tackle this out, you can do the following approach:

1. Have one additional column in your SELECT query which fills the datagridview (ideally it can be a timestamp column)
2. When any of the values changes, you will issue an UPDATE statement based on the primary key in that table right.
3. Add this additional column too in the WHERE clause of UPDATE statement.
4. Hence the first user who saves it will be able to modify the records and update that timestamp column to a new value.
5. If any other user tries to update that particular record with the old timestamp and primary key, it will fail.
6. Catch that as an exception and display a message box in the application that some other user have modified that record.

This is the very basic and successful approach of handling Concurrency in all Applications.
Kindly revert if you have any doubts in my approach.
0
 
LVL 3

Author Comment

by:FMabey
ID: 23658184
But what problems could I run into with deletion and insertion? I can see how that will work for me with changes to existing data but if one user deletes a record and another adds one, will this not cause SQL Server some confusion when updating?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23658217
<< I can see how that will work for me with changes to existing data but if one user deletes a record and another adds one, will this not cause SQL Server some confusion when updating? >>

No confusions at all, Because if an user is deleting a record, then it will be deleted and hence the other users wont be able to see that record at all. If any others are adding any record, then it is a new one and hence users can update that record based upon its timestamp column.

JFF.. Servers dont confuse unless we code it to confuse.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 3

Author Comment

by:FMabey
ID: 23658242
Sorry, what I meant was what happens if a user opens the form at 5pm and leaves it open. Another opens the form at 6pm deletes 2 records and adds 1, then closes (saves the changes). After these changes the 1st user is still looking at the data he saw when he opened at 5pm. If he then makes makes changes to a record that's already been deleted that will error wont it?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23658542
Yes. It will error out and this is the fundamental scenario for handling concurrencies.

If the user tries to change any, just catch out that exception of zero records updated or zero records deleted (You will get zero if you use that timestamp column too to filter out records) and show a message box that records have been modified by other users.

I hope that should suffice and have seen many applications with that logic implemented. Its the user's responsibility to delete / modify as soon as he can so that others can work in parallel. Kindly inform this to the users prior.
0
 
LVL 3

Author Comment

by:FMabey
ID: 23781981
So am I right in thinking that I can't have two users in adding and deleting records in the same SQL Server table at the same time? Surely this can't be?
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23784737
FMabey,
    Your statements diverts me now from your requirement. Can you clearly restate your requirements along with your doubts so that I can help you out.

If you have any doubts over comment no 23658052, kindly point that one too.
0
 
LVL 3

Author Comment

by:FMabey
ID: 23795024
Ok,

Thanks for you patience on this one!

On a windows form we have the following:
DA1 - A SqlDataAdaptor
DS1 - A Dataset
DV1 - A Dataview
DG1 - A DataGridV

DV1 is what displays the data in the DG1 and is used to filter the data accordingly.
We also have User1, User2 and User3. These users should be able to access the form at the same time. My issue is that because the data is loaded on the open of the form, how do I deal with all three users updating, inserting and deleting into the same table? If 2 users are using the form at the same time how would locks and modifications be handled. I cannot have a user locked out of the table because another user is say editting a record.

Any questions please ask.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23795387
Yes.. I need further more inputs:

1. For example, I opened a record in DV1 and I try to update it with (5 mins sleep time say). In that mean time, you are trying to update that record. Kindly tell me who should succeed either you or me ( Because that is the part we need to deal carefully)
2. If any one changes a record and the Other still sees the old record, what steps needs to be done.

Kindly tell me those so that I can change a little bit in my approach given earlier to handle the situations exactly what you need.
0
 
LVL 3

Author Comment

by:FMabey
ID: 23845485
Thanks for your reply,

1. Two users shouldn't be changing the same record. A record should be locked so another user cannot edit it.
2. I would like a flag to be shown against the record to tell them that it has been updated and that they will need to refresh to see the updated data. If a user deletes a record I would ideally want that record to become uneditable and inform the user why.
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 23848906
<< Two users shouldn't be changing the same record. A record should be locked so another user cannot edit it.

In your SELECT query, add the hint WITH ROWLOCK like

SELECT * FROM urtable WITH ROWLOCK

<< I would like a flag to be shown against the record to tell them that it has been updated and that they will need to refresh to see the updated data. If a user deletes a record I would ideally want that record to become uneditable and inform the user why. >>

Follow my Comment 23658052 and when the second user tries to do anything on the Deleted record, it will not update any record and will throw an exception for it. Catch that and show it as a Message for the user.

<< I would like a flag to be shown against the record to tell them that it has been updated and that they will need to refresh to see the updated data.>>

Unless otherwise you automatically issue several SELECT s, you wont be able to face this one out. And hence that is not practical.
0
 
LVL 3

Author Closing Comment

by:FMabey
ID: 31547710
Thanks for your patience... Excellent explainations.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question