1

I've spent 2 whole weeks in googling some working sample on this. I was almost kind of knocking my head against concrete wall....deeply suffering until now.

Ok, I have a simple web page to display two columns, say, Msg & MsgID, from a table named MsgLog in MS SQL Server. I have stored procedure that will insert new Msg entries into the table. (in future, possibly could update existing entries)

My desire is, whenever there is a new msg entry inserted, or an existing msg entry got updated, the web page must automatically reflect this change without user clicking any page button, or without any coded timer function, otherwise the web page just leave as it is. The page refresh can only be triggered by database table change.

I read lots of internet posts regarding using SqlCacheDependency to achieve this, but none provides complete steps and codes. I tried to tweak their sample codes in my project, but none of work successfully, as I predicted. Or is it impossible to implement this?

My development environment is: Visual Studio 2010 professional, using ASP.NET FRAMEWORK 4.0, MVC 3 application using C# with embedded sql statements with SqlCommand object, database is MS SQL Server 2008 enterprise editon, IIS express.

Can someone genius to show me complete workable detailed steps from sql server setting, to coding. Functionality is just as simple as display rows with 2 columns from one table in a web page, and reflect changes realtime if and only if the table got updated or inserted. Not using polling or timer techniques; Not using LINQ to SQL, but using SqlCommand.

thanks millions of thousannds.

Jianguang Fang
  • 21
  • 5

1 Answers1

1

This in itself doesn't have to do directly with SqlCacheDependency.
You are wanting to do something that until only recently was really done with timers/polling unless you were one of the big players (ie google chat) Once your server notification event is raised then use SignalR to notify the clients of the change, so it requires an 'open' connection. Otherwise polling/refreshing is your only option. I haven't used SignalR, but I do know its really your only option now to do what you want. There have been some other projects in this field, but this is your best bet by far.

https://github.com/SignalR/SignalR

Adam Tuliper
  • 29,982
  • 4
  • 53
  • 71
  • Many thanks, Adam. That great helps even it tells me that it is not possible to do in traditional way. I've been thinking this fucntion of data or message pushing to web client should be a general function in nowadays IT. I'm not going to try SingalR due to project schedule. – Jianguang Fang Feb 03 '12 at 16:40
  • However I still have thoughts of using SqlCacheDependency. My understanding is, using SqlCacheDependency is able to notify web server of database change. Normally, web server invokes function to display client page in response to client request; in contrast, when web server is notified by SqlCacheDependency, it could invoke function to read database, followed by invoking same function to display client page. That way, it is auto refreshing by database change, right? Sounds logical, but don't know why this can't be done... any explanation? – Jianguang Fang Feb 03 '12 at 16:55
  • it may be able to be done but only using a client framework. you may need to signal your signlar code from SqlCacheDependency (not sure of the exact implementation of houw signalR notifies clients). Im sure theres a likely way to get it to work though for what you want, it will just take some fiddling with signalR and your event for SqlCacheDependency – Adam Tuliper Feb 04 '12 at 03:57
  • I think I'll just not to implement this, until the signalR technology is popular and become standard package in visual studio. thanks Adam. – Jianguang Fang Feb 07 '12 at 06:28