From: Somebody on
* I store application data in a SQL database
* I access it in my app via ADO.Net data table / table adapter
* I do not access any tables directly, its all stored procedures
* Since I am using WPF, I wrap most of the data in ObservableCollection<T>.

Basically what happens is that I grab data from the data base, store it in
local memory in ObservableCollection<T> collection objects and all is good.

The getters just return the data and the setters update the local copy of
the data and call stored procedures to update the database.

This all seems like it should work in a single user environment.

But what happens in a multi-user environment?

Lets say UserA updates something in the database, there doesn't seem to be
any way for UserB to see the changes without implementing some kind of
cheezy refresh button.

Also, what happens if UserA and UserB make conflicting changes?

I guess I need to implement some kind of locking mechanism? UserA checks out
ItemA and UserB can only get a read-only copy?
UserB will still eventually have to hit a refresh button?

Any way to get notifications from the database? Polling seems cheezy (and
wrong).


From: Mr. Arnold on
Somebody wrote:
> * I store application data in a SQL database
> * I access it in my app via ADO.Net data table / table adapter
> * I do not access any tables directly, its all stored procedures
> * Since I am using WPF, I wrap most of the data in ObservableCollection<T>.
>
> Basically what happens is that I grab data from the data base, store it in
> local memory in ObservableCollection<T> collection objects and all is good.
>
> The getters just return the data and the setters update the local copy of
> the data and call stored procedures to update the database.
>
> This all seems like it should work in a single user environment.
>
> But what happens in a multi-user environment?
>
> Lets say UserA updates something in the database, there doesn't seem to be
> any way for UserB to see the changes without implementing some kind of
> cheezy refresh button.

No there is not unless you want to go out of your way with some kind of
real time solution. Is it worth it and is it practical?
>
> Also, what happens if UserA and UserB make conflicting changes?

The best you could do was to read the data for change for what UserB was
about to update. If the data has changed from the initial read of data
by UserB, then data has changed, notification message sent, and UserB
must get a the data again.

Otherwise, the other approach is last one wins.

>
> I guess I need to implement some kind of locking mechanism? UserA checks out
> ItemA and UserB can only get a read-only copy?
> UserB will still eventually have to hit a refresh button?

It sounds kind of cheezy. What happens if application goes down and your
locking mechanism has a record locked out. How are you going to unlock
it without your intervention?

>
> Any way to get notifications from the database? Polling seems cheezy (and
> wrong).
>
>

No, you can't get any notification from the database. And polling in a
multi user application is a waste of time and CPU cycles, slowing the
application down

If you're going to use T-SQL in a sproc, then make sure you use 'Nolock'
so that you're doing table locks, locking out other users from accessing
data in the tables.
From: Mr. Arnold on
Somebody wrote:

<snipped>

<correction>

If you're going to use T-SQL in a sproc, then make sure you use 'Nolock'
so that you're not doing table locks, locking out other users from
accessing data in the tables.