?
Solved

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

Posted on 2009-04-02
6
Medium Priority
?
452 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 1000 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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 1000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

752 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