By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,077 Members | 1,790 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,077 IT Pros & Developers. It's quick & easy.

notification about DB changes

P: n/a
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
Jan 4 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Marty" <xm******@hotmail.com> wrote in message
news:dKPuf.42318$OU5.12642@clgrps13...
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

Jan 4 '06 #2

P: n/a
What is your database engine?

If you are using SQL 2000, have a look at the Notification Services:
http://www.microsoft.com/sql/technol...n/default.mspx

SQL 2005 and Visual Studio 2005 eased that part.

--
HTH

Éric Moreau, MCSD, Visual Developer - Visual Basic MVP
Conseiller Principal / Senior Consultant
Concept S2i inc. (www.s2i.com)
http://emoreau.s2i.com/

"Marty" <xm******@hotmail.com> wrote in message
news:dKPuf.42318$OU5.12642@clgrps13...
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

Jan 4 '06 #3

P: n/a
Hello Eric,

my DB engine is MySQL. Do you have a link for that,

thank you :)
marty

Eric Moreau wrote:
What is your database engine?

If you are using SQL 2000, have a look at the Notification Services:
http://www.microsoft.com/sql/technol...n/default.mspx

SQL 2005 and Visual Studio 2005 eased that part.

Jan 4 '06 #4

P: n/a
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

Nicholas Paldino [.NET/C# MVP] wrote:
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.

Jan 4 '06 #5

P: n/a
Marty,

Yes, that is the general technique. Basically, refresh when needed. It
doesn't scale too well when you have a constant connection open...
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Marty" <xm******@hotmail.com> wrote in message
news:E8Uuf.39260$m05.2327@clgrps12...
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

Nicholas Paldino [.NET/C# MVP] wrote:
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.


Jan 4 '06 #6

P: n/a
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.
--
Paul
"Marty" wrote:
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

Nicholas Paldino [.NET/C# MVP] wrote:
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.

Jan 4 '06 #7

P: n/a
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.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Marty" <xm******@hotmail.com> wrote in message
news:b6Uuf.39259$m05.38986@clgrps12...
Hello Eric,

my DB engine is MySQL. Do you have a link for that,

thank you :)
marty

Eric Moreau wrote:
What is your database engine?

If you are using SQL 2000, have a look at the Notification Services:
http://www.microsoft.com/sql/technol...n/default.mspx

SQL 2005 and Visual Studio 2005 eased that part.

Jan 4 '06 #8

P: n/a
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.


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
Jan 4 '06 #9

P: n/a

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.

--
Paul
"Marty" wrote:
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.


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

Jan 4 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.