Menu
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?
831
The notifications are provided by Service Broker in your database, so will need to enable this service in your databse. The OnChange event will raise when specified table changes (update, delete, insert..) void Initialization () { // Create a dependency connection.
To get a notify when some record is updated, avoid the application to query the table you cab use TableDependency component (in your specific case SqlTableDependency ). Here is an example: The event handler is triggered for every INSERT UPDATE or DELETE operation done on the table, reporting you the modified value.
For PostgreSQL I know a way to get notification from the database when a row changes. use trigger when insert / update / delete occurs. when event occurs send a notify to a client socket. be sure you app have a client to the server. then your app will receive a notification.
There isn't any means of the database pushing notifications to the application. The application needs to poll the database to check for updates, and then deal with the updates appropriately. Show activity on this post. If by "updates to the database" you mean any update by any application, you're out of luck: it's not doable.
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
}
});
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
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
