Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 456
  • Last Modified:

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

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
reddal
Asked:
reddal
2 Solutions
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
Auric1983Commented:
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
 
elimesikaCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
reddalAuthor Commented:
> 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
 
Éric MoreauSenior .Net ConsultantCommented:
>>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
 
reddalAuthor Commented:
Thanks - you saved me a lot of wasted time trying to find a solution that doesn't exist.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now