How to make database changes visible to your application?

  • Thread starter Thread starter Dino Buljubasic
  • Start date Start date
D

Dino Buljubasic

Hi,

I am using VS2005 (C#) and SQL Server 2000.

How can I make sure that:

1. Client A queries info from database and displays it in a list view
or other controls on a form. If Client B changes some of this info
that is displayed by client A, clint A gets its info updated

2. If clint B tries to delete data currently displayed by client A,
the attempt should fail.

How can I make sure that database data displayed on the client's forms
is always newest data?

Any help will be appreciated.

_dino_
 
This has been discussed a thousand times on this list and elsewhere. I
suggest researching "concurrency", "locking", "pessimistic" and other
related terms in the list archives. If you still can't find an answer or
don't understand what you find, come back and we'll offer a few suggestions.

--
____________________________________
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.
__________________________________
 
Dino,

What you are trying to do is very nearly impossible using VS.Net and ADO.Net.

Most .Net applications handle concurrency issues when a client actually
attempts to update the database. Why won't that approach work for you?

Kerry Moorman
 
Ah, I would not go that far. Yes, this is possible with ADO.NET but it does
not help much. Even with ADO classic, a server-side cursor still has to be
polled to return the current state of the database rows you're monitoring.
Typically, the server does not have a "back channel" to the clients so it
does not have a way to notify them that changes have been made.
SqlNotification can do this, but it can be very expensive in lost server
performance unless used wisely. As far as locking is concerned, that is also
possible. You can create a scoped transaction that simulates a pessimistic
cursor on the server that prevents others from changing selected rows--but
again, this is problematic (to say the least) if not handled wisely.

Most developers faced with this situation poll the server for changes in
focused data regions, use focused server-side cursors (which are still
possible using ANSI CREATE CURSOR commands) or resort to systems where there
aren't collisions in the first place. The key here is to focus the queries
on easily retrieved rowsets--not entire tables or the product of a complex
JOIN.

hth

--
____________________________________
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.
__________________________________
 
Bill,

Yes, that is why I said it is "very nearly impossible" "using ADO.Net".

With a connected technology like ADO it can be done brute force with
pessimistic locking (un-scalable and close to un-workable if you have a bunch
of users).

With ADO.Net it can be simulated with transactions (often talked about but I
suspect rarely implemented).

But these are kludges that I wouldn't recommend to anyone that did not have
a good bit of experience already. That experienced person probably would not
need to ask how to do it (specific questions maybe but not general ones).

So in that context I will stick by my response that it is very nearly
impossible using ADO.Net.

Of course, the suggestions you made, especially focusing on retrieving small
amounts of data and not entire tables, etc., will help mitigate the problem
and are good ideas in general.

Kerry Moorman


William (Bill) Vaughn said:
Ah, I would not go that far.
[snip]
--
____________________________________
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.
__________________________________

Kerry Moorman said:
Dino,

What you are trying to do is very nearly impossible using VS.Net and
ADO.Net.

Most .Net applications handle concurrency issues when a client actually
attempts to update the database. Why won't that approach work for you?

Kerry Moorman
Click to expand...
Click to expand...
 
Back
Top