CODEWITHSUNDEEP
sqlwpfvb.netwinformssql-server-2008-r2
user1590636
user1590636

Reputation: 1194

How to refresh the data on other user's interface when some user edits the data

In a VB.net application I have a SQL interface, I have a users table in the SQL database for users of this application, all of them see the same data now when a user clicks a button changes are saved in the sql and data is refreshed for that user, however I want the data to be refreshed for all users as soon as some user clicks that button.

Ideas I have

  1. refreshing data every second, which is not good
  2. SQL trigger to vb.net which I need more clarification about!

What would be the most effective way?

i would like a work around without timers!

Thanks

Upvotes: 3

Views: 2162

Answers (3)

Robert Koritnik
Robert Koritnik

Reputation: 105029

SQL Server Notification Services

SQL provides subscription for notifications that provide mechanisms related to your requirements.

http://msdn.microsoft.com/en-us/library/ms172483(v=sql.90).aspx

Note: removed since you later provided info you're using SQL Server 2008 R2

Old trick with System.IO.FileSystemWatcher

The other way is to create a SQL trigger on your table that does a simple file timestamp change. File itself is 0 bytes in size so we're not writing anything to it, making it a fast operation.

All clients then watch this file for modifications and when it happens watcher triggers a change event in which your clients would simply reload their data.

CREATE TRIGGER DataChanged
ON YourTable
FOR INSERT, UPDATE, DELETE
AS
    EXEC master..xsp_UpdateSignalFile 'YourTable.DataChanged'

The idea of this CLR stored procedure is to be as fast as possible to keep it scalable. IN case of heavy load data server this could likely be even optimised by using external processes to update file...

You can find the code for the CLR stored procedure in this MSDN article that is actually related to web forms, but you don't need other things than this CLR procedure. And maybe the explanation of how the whole system works.

Your clients would then be using System.IO.FileSystemWatcher to watch for file changes on some network location (where the procedure would be updating files) and update their data when change happens.

Simple and elegant trick that doesn't use polling or unnecessary requests to the data server. You can of course have triggers on several tables each changing a different file and depending on the form you're displaying on the client you'd then watch that particular file for the lifetime of the form. When user changes context to a different data your client app releases file watcher (and creates a new one of needed for a different file).

Upvotes: 2

Danny
Danny

Reputation: 132

Simple,

You can't do that. Your browser only pulls data once and except for a few things like coockies your browser will not remember anything.

You only option is to refresh the page every 5 or 10 secons or so. This can only be done using javascript. Fortunatly Visual studio privides you with some sort of javascript. This will prevent the browser from refresshing. So you whon't hear that klik every 5 sec.

Add an updatepanel around the controls you want to refresh. Then add a postbacktrigger and set it to 5000 miliseconds.

Add to "page load" --> resresh your control.

A better way would be to add a webmethod that will only send you the data but above works fine and is easy to implement.

Good luck.

Upvotes: 0

Brandon
Brandon

Reputation: 945

I'm guessing this is a Windows Form App, rather than an aspx web application? I would suggest reloading the form on some sort of user raised event.

Upvotes: 0

Related Questions