Howto catch changes in the database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all,

I need something in VB.NET that monitor changes in a database (any DB or
even an XML file or something). I can make something that checks every 5
seconds, but maybe there is a better solution like an event is fired from
ado.net. I do not have to make changes or something, but need to detect and
catch the changed records/values and fire myself in the server application,
so other objects can take actions, if needed.

And if there is no solution this way, what is the best way to accomplish this?

Thanks in advance!

Derck Weelink
 
What you are describing is Notification Services. This has been integrated
into Yukon and I'll be demonstrating this in Montreal on the 18th (this
Saturday) at the DevTeach conference. Notification Services is available
separately as well, but IIRC it's bundled with SQL Server 2000. ADO.NET 2.0
will support this by permitting you to build a Command object and link it
with a SqlDependency object. When properly registered (I'll show you how on
Saturday), the server will fire an event in your code to signal that the
selected data has changed.

hth

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

Thanks for your reply!

So, what is want is only for sql server 2005? There is now way to do this
with ado.net 2.0 and/or any datasource??

If no, do you have some suggestions what the best way is in ado.net or any
other object?

Thanks,

Derck
 
Derckie,

As Bill said, the most straightforward way to do this is SqlDependency -
which unfortunately is not available in .NET 1.1 + Sql2k.

However, all hope isn't lost.

What you *can* do in Sql2k is touch a file from a trigger on the table you
wish to detect changes in. In your application then, you can put a
FileDependency on that file, and immediately go look in the database for the
changes. .. now what I am not too clear about is .. can you do fully managed
filedependency in .NET 1.1? My first hunch is YES - but even if you can't,
doing it through Win32 is trivial (real easy to do).

If googling on that approach doesn't help you, please let me know and I'll
try and find specific information - but I am quite certain the above
approach is do-able.

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
 
BTW, per my last reply, here is FileDependency code in .NET 1.1 -

public class FileDependency
{
private System.IO.FileSystemWatcher m_fileSystemWatcher;

public delegate void FileChange(object sender,
System.IO.FileSystemEventArgs e);
//The OnFileChange event is fired when the file changes.
public event FileChange OnFileChange;

public FileDependency(string fullFileName)
{
//Validate file.
System.IO.FileInfo fileInfo = new
System.IO.FileInfo(fullFileName);
if (!fileInfo.Exists)
throw new System.IO.FileNotFoundException();

//Get path from full file name.
string path = System.IO.Path.GetDirectoryName(fullFileName);
//Get file name from full file name.
string fileName = System.IO.Path.GetFileName(fullFileName);
//Initialize new FileSystemWatcher.
m_fileSystemWatcher = new System.IO.FileSystemWatcher();

m_fileSystemWatcher.Path = path;
m_fileSystemWatcher.Filter = fileName;
m_fileSystemWatcher.EnableRaisingEvents = true;
this.m_fileSystemWatcher.Changed += new
System.IO.FileSystemEventHandler(this.fileSystemWatcher_Changed);
}

private void fileSystemWatcher_Changed(object sender,
System.IO.FileSystemEventArgs e)
{
OnFileChange(sender, e);
}


Happy? :-)


- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
 
And here is code for touching a file based on a trigger.

CREATE TRIGGER WriteCacheDepFile ON [dbo].[Employees]
FOR INSERT, UPDATE, DELETE
AS
EXEC sp_makewebtask '\\peter\C$\Cache\mycache.txt', 'SELECT top 1 FirstName
FROM employees'

U LIKE? :-)

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
 
Just to add a note: be aware that such notifications takes resources from
sql server...
 
takes = consumes

Miha Markic said:
Just to add a note: be aware that such notifications takes resources from
sql server...

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
SLODUG - Slovene Developer Users Group www.codezone-si.info

Derckie said:
Hello all,

I need something in VB.NET that monitor changes in a database (any DB or
even an XML file or something). I can make something that checks every 5
seconds, but maybe there is a better solution like an event is fired from
ado.net. I do not have to make changes or something, but need to detect
and
catch the changed records/values and fire myself in the server
application,
so other objects can take actions, if needed.

And if there is no solution this way, what is the best way to accomplish
this?

Thanks in advance!

Derck Weelink
Click to expand...
Click to expand...
 
Back
Top