Multi User Application

[Guest]

Hi,
I have been written an application in visual studio 2003 (c# winforms)
with microsoft SQL Server. Cuncurrency is what I have somewhat problems with.
I have though about the following to make my application the closest to real
time possible that way everybody can have the most current data. I would like
your opinion, because I implement this.
First of all, because a bring data in chunks into the dataset, at
times, a user "A" may update the data and user "B" will not now that this
happen. There is of course a way of knowing after user "B" updates, because
if the update is protected on the where statement (update..... where
fielda=@fieldA and fieldb=@fieldb and so on), I could give a warning message
and refresh data. I find this method not to be useful for all applications.
So, what I have in mind is the following.
1) Try to have data bring back to the client every time they activate a screen
2) Create a class where using sockets or alike type of communication to
inform all the users when specific tables and or screens have been update and
take action to this. each user will have 2 extra threads , one for listening
and one for sending the message, apart from the main thread where the
application is running.
3) Always check the records before updating
4) In a PO Screen for example, where the user may be editing one of them,
try to lock if possible the record, from the other users.
5) There is one more problem, which are some processes that need cursors.
where I need to loop and update accordanly. I would like to provide some type
of lock for the record that is reading on the loop, to prevent problems.

I would like to know your input, specially if you think I'm crazy.
Thanks

 

[William \(Bill\) Vaughn]

I have written about this for a decade and all of my books discuss this
issue in detail--and so do many others.
When you fetch more rows than the user needs at a single time, you can
expect to have collisions. I recommend that when you develop a database
system that you "design in" concurrency. There are a number of ways to do
this (as described in the book). One approach is to segment the data so that
each client has their own set of data--a set that other clients are not
allowed to access. For example, a soccer coach enters player stats for their
own players and no others. When this application executes a query it fetches
a minimum number of rows--a single player (not the whole team). While some
might suggest that you should fetch the whole table and somehow expect
(optimistically) that others won't change the rows you're holding, I don't
concur.
There are more sophisticated solutions for special cases. ADO.NET 2.0
implements one of these called QueryNotifications. In this case you can
setup a "back channel" that notifies your client when selected rows have
changed. I doubt, however if this is what you need.
I suggest you do some additional research... there are many good sources.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Guest]

Thank you Bill for your input. I like the query notifications ("ADO.NET 2.0
implements one of these called QueryNotifications...") you mention. I'm
using ADO.NET 1.1 so, Unfortunely, I'm not sure when I will be able to move
to ado.net 2.0 in the current project. In my application,it will be
unrealistic to fetch data that only one user needs, because everybody needs
and are able to modify. For example, the purchasing team, needs to see all
PO. I was planning to do my own query notification, but the fact that ado.net
2.0 has this feature, it may be worth exploring.
Thank you
Frank