Solved

How to receive a notification of a data change in c# from SQL Server 2005?

Posted on 2009-04-02
6
449 Views
Last Modified: 2012-05-06
Hi,

I have a .NET (v1.1) c# console application that accesses data in an SQL Server 2005 database, via SQL.

I want to have a mechanism to receive a notification (event? trigger?) when something changes in a given table so I can reload it. Whats the easiest way to code that? Obviously I could just poll or something like that - but I'm sure there will be a nice solution supported by my combination of .NET 1.1 SQLConnection classes and SQL Server 2005 backend.

Whats the best/easiest way to achieve that?

thanks - reddal

0
Comment
Question by:reddal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 24050250
using ADO.Net 2.0 and SQL 2005, you can use the notification: http://msdn.microsoft.com/en-us/library/ms379594.aspx

can you migrate to .Net 2.0?
0
 
LVL 16

Expert Comment

by:Auric1983
ID: 24050281
Create a second table with the following columns

Tablename (varchar) , lastupdated (datetime), complete (bit)

You could create a SQL update trigger
on the table you want to monitor to insert
a record into the "monitor table" with the name of the table, the date/time of the update and set the complete field to 0

You'd then be able to query that monitoring table for "action required"
0
 
LVL 19

Accepted Solution

by:
elimesika earned 250 total points
ID: 24050367
HI

You will have to upgrade at least to .NET 2.0 to use CLR SP
http://msdn.microsoft.com/en-us/library/5czye81z(VS.80).aspx

or to use SqlCacheDependency (also .NET 2.0)
http://davidhayden.com/blog/dave/archive/2006/04/30/2930.aspx

on 1.1 , as far as I know , polling is the only answer...
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:reddal
ID: 24050371
> emoreau: can you migrate to .Net 2.0?

Yes that is planned (to 3.5 in fact) - but not immediately. Do you know if there is a .NET 1.1 solution?

> Auric1983:

I would still have to poll the monitoring table at regular intervals using your solution I think? I'm hoping to find a way to avoid that.

- reddal
0
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 250 total points
ID: 24050399
>>Do you know if there is a .NET 1.1 solution?

I don't know any other easy way (other then polling the database every x seconds).
0
 

Author Closing Comment

by:reddal
ID: 31565793
Thanks - you saved me a lot of wasted time trying to find a solution that doesn't exist.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question