i want to build a little app that pops up a tray notification (or a toast popup or something) whenever an update or insert going into a certain table in a SQL server database.
What is the simplest way of doing this as i want to avoid polling if possible?
You could trigger extended stored procedure, which would notify your tray.
Maybe this link is better.
Extended stored procedures are what I thought of first, too, and are probably the solution I'd use if I wanted to run the monitoring app on the SQL Server itself. But I'm guessing that's probably not the case.
I'd suggest using MSMQ as an intermediate layer, myself, since it comes with just about every version of Windows these days and is more or less tailor-made for this sort of thing. So, going through the layers, here, you have:
There's sample code for accessing MSMQ from SQL Server here: http://www.codeproject.com/KB/database/SqlMSMQ.aspx
Query Notifications. This is the SQL Server feature that allows an application to subscribe to notifications pushed from the server when data is changed. It is usually leveraged through the SqlDependency class.
I have recently posted the LinqToCache project that allows you to add SqlDependency based notifications and cache invalidation to LINQ queries:
var query = (from r in ctx.table select r).AsCached(
"table", new CachedQueryOptions() {
OnInvalidated = (sender, args) {
// the query was invalidated, data has changed
// refresh display or notify user
}
});