notification about DB changes

  • Thread starter Thread starter Marty
  • Start date Start date
M

Marty

Hi,

I have an application bound to a database through ADO.NET, how can it be
notified when the database content is updated by a third party ?

If you have any idea or link that I could read I would very appreciate.
If ADO.NET is not the solution, with which engine can this be done?

Thank you,
Marty
 
Marty,

You will want to look into the SqlDependency class in .NET 2.0. There
really is no clean way in .NET 1.1 and before.

However, you have to ask yourself what you are using the SqlDependency
for. It is very resource intensive, and you shouldn't use it on tables that
change frequently (since it all requires an open connection).

Hope this helps.
 
Hello Nicolas,

I understand that this can be ressource intensive. What is the
alternative to keep a grid updated by databse chages? I mean, would it
be by constantly pooling the DB for evantual changes?

Regards,
marty
 
Marty,

Yes, that is the general technique. Basically, refresh when needed. It
doesn't scale too well when you have a constant connection open...
 
The other option that can be used in a real-time environment is to 'stage'
the data. So instead of 'you' going to the database another server is
responsible for accessing and updating the data from the database. When you
require the Table/Row/Item you send a request and optionally register your
interest in the data. A copy of data is returned to you and if it is changed
by someone else you are notified and will continue to be so until you
unregister.

You may also implement a 'read-only' lock at register time. Locked data
cannot be changed by anyone else apart from timeouts on the part of the
locker. Updates can only be done by the locker.

Updates to the data are done by notifying the server of the change and
releasing of the lock. The Server writes the data to the database whenever it
sees fit.

This process is more efficient than database notification but of course
requires a considerable amount of effort to write. (I know - been there, done
that). It also has the advantage that it can be used for non-database
information, especially if the only reason the data was placed in the
database was to share it rather than take advantage of relationships.
 
Marty,

SQL notification services is a SQL Server specific technology. Other
databases might have it, but the classes in the framework are for SQL Server
only.

If you want to use something with MySQL, you have to look for a solution
specific to that server.
 
Hi Paul, thank you for your ideas,
The other option that can be used in a real-time environment is to 'stage'
the data. So instead of 'you' going to the database another server is
responsible for accessing and updating the data from the database. When you
require the Table/Row/Item you send a request and optionally register your
interest in the data. A copy of data is returned to you and if it is changed
by someone else you are notified and will continue to be so until you
unregister.
Click to expand...

This first case is the one we have (without the "registering" part of it).
How this "registering" is implemented?
Is it on database side?
Would it work with mySQL ?

Regards,
marty
 
The registration is totally external to the database and is done by the
"data server". The dataserver is a program sitting between the application
and the database.

This method normally works by sending a TCP request from the application to
the "data server" which may or may not reside on the same machine as the
database. Performance is better if it does.

Your application call is of the form:

ReplyClass rc = SendRequest(RequestType, RequestParameters. Registration)

Request Type: Delete, Update, move, lock, unlock - You decide
Request Parameters: Totally application dependant

The registration parameter tells the dataserver what to do...

1) Just give me the data and forget I ever asked for it.
2) Give me the data and tell me when someone (including me) changes it. I
will supply a key to you (the dataserver) and when it is updated send me back
the key and the updated field / row whatever.
3) Give me the data and lock it against changes by anybody else.
4) Update and unlock (or just unlock)
If you are going to read and update later you would put a lock on it. I use
the word registration here as it was commonly used in Service-based OOP
programming.

The Data Server has the problem of tracking all the requests and memorizing
them. Each 'user+application' requires a unique ID so the dataserver can
track who has what.

There are a number of areas to be careful about:

1) Applications must tell the dataserver when they are closing so the
dataserver can release all locks. Users switching off / crashing without
closing down can cause problems. Lock timeouts are essential.

2) If there is a considerable delay between the read and update (or rather
the dataserver request to read and the dataserver request to update) then it
will be necessary to ...
read without lock
let the user do his thing
read with lock to make sure nothing has changed
update and release lock

3) Each user application must have a Listener running so it can receive
updates.

4) Deadly embraces can happen but a short timeout can fix that.

5) The application / client side has to cope with notification that a lock
has timed out or been supplanted by a request with a higher permission.

Like I say - it's lot of work and you have to get your logic right. But once
re-usable objects are written it is easy. And only use this architecture when
really necessary. 90% of your stuff will continue to use good old selects.
 
Back
Top