1

Very quick question - if I deploy a WPF application to a few users, can I use an SQL Dependency? In other words, I was thinking about using an SQL Dependency (although I can't figure it out, but that's another topic...) in a WPF program, but I've read tidbits that this doesn't work if multiple users subscribe to it or something. Is this true? And if so, whats the best way to do it? I'm simply trying to figure out the best way for the WPF program to recognize when a new row is added to a database (and then to pull the information in that row)

Thanks!

EDIT: Would it be easier / more effective to just use a background worker, and an endless loop that queried the database every few seconds? And in the event that the number of rows increases, then do something?

P.S. Using C# and SQL Server 2008 R2 Express

Community
  • 1
  • 1
keynesiancross
  • 3,441
  • 15
  • 47
  • 87

1 Answers1

3

Yes, you can use SqlDependency and SqlCacheDependency in a WPF application.

You will have to make sure that you call SqlDependency.Start at the correct moment and SqlDependency.Stop when you're done or when the program exits. Then you can point the dependency to a SQLCommand object and make sure that you subscribe to the OnChange event.

Here is a nice example with both a WPF and ASP.NET implementation.

Wouter de Kort
  • 39,090
  • 12
  • 84
  • 103
  • Thanks! Can multiple instances of the same program (ie different users) still do this though? – keynesiancross Jan 30 '12 at 20:34
  • @keynesiancross Yes that's possible. SqlDependencies are meant to be used in a multi-user environment. SqlServer will make sure that each query that is executed is checked against any dependencies so that it can send notifcations to clients. – Wouter de Kort Jan 30 '12 at 20:36
  • May have found my problem.... My SQL query used COUNT(*), as I wanted it to keep an eye on the number of rows. Is there any other query I could use to do the same thing? – keynesiancross Jan 30 '12 at 20:42
  • 1
    @keynesiancross would it by to expensive to do a SELECT Id FROM Table and cache those results? – Wouter de Kort Jan 30 '12 at 20:47
  • I'm not sure... But I'm wondering now if it might be easier using a background worker, an endless loop on a timer, and a "SELECT COUNT(*)" query..... – keynesiancross Jan 30 '12 at 20:49
  • If the changes are relatively rare, constantly pulling for data changes will definitely be more expensive. I would give it a try by selecting all row ids and caching those. If you get a cache change on those, you can always manually invalidate other queries you have cached. – Wouter de Kort Jan 30 '12 at 20:52
  • The changes generally happen every minute or so... Also, for your idea, how would you cache the results? I'm a self taught kind of programmer, and don't have a lot of experience in this area... Thx for the help – keynesiancross Jan 30 '12 at 20:56
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/7171/discussion-between-wouter-de-kort-and-keynesiancross) – Wouter de Kort Jan 30 '12 at 20:57