Update databound datasource in c# when data in database changes

Question

Hi, I posted a similar post to this before but since then i have done some research and thought i'd put this one out again to see if anyone maybe has any thoughts on my problem.

I am running a WPF application with C# as the code beind. The datbase i am using is SQL Server 2005.

Now i am currently binding to the database using ADO.Net and retrieving the data from the stored procs in the db. This is then stored in datasets and further down the line bound to controls in my WPF application.

Now the data that i am binding to in the db is constantly changing, let say every few seconds. What i want is a mechanism to automatically tell me in C# when the data that i have bound to in the db, so the data returned from my stored procs has changed.

I looked on the web and found notification services and SQLDependency class but this is being deprecated. I also saw CLINQ but this doesn't seem to handle the database notification side, it only works for collections in c# from what i understand.

I mean the plan B method is to just have a thread in my C# code to poll the same stored proc every few seconds based on a timestamp that is stored on every row in the returned dataset. If my current timestamp is greater then what is being returned then retreive that data. This would then run in this new thread looping over and over. An if any data was returned from the connection in the thread that would mean that data has changed and so store that data into my collections in c# so they can be bound to my WPF app.

Obviously this is something i don't really want to do as i thought there maybe a smarter way to do this but i just can't seem to located anythings.

Does anyone have any ideas on this one?

Much appreciated Iffy.

Answer 1

how about SQL CLR triggers? SQL triggers can be run every time an update/insert/delete occurs in targeted tables in your database and it seems CLR SQL triggers extend this functionality to managed code:

http://msdn.microsoft.com/en-us/library/938d9dz2%28VS.80%29.aspx

not an expert on this but hopefully it will give you something useful to look at.

Answer 2

Plan B sounds like a good choice to me - there are no notification methods built-in to SQL Server to allow you to do this, so your only options would be:

1. Triggers combined with CLR user defined functions. The trouble is that depending on how much work you do in your user defined function this could add quite a lot of overhead for anyone writing to the database - this is not the most efficient layer to implement a notification mechanism in.
2. Implement some sort of notification mechanism and make sure that everyone who writes to the database uses this notification mechanism to notify others that the data has changed.

Considering how much effort #2 is, I'd say that plan B is a pretty good choice.

Depending on how frequently you poll and how efficient your polling is, you will probably find that the end effect for the user is just as good as option #2 anyway, and the overhead of doing that polling is probably less than you think. (if you have many users then you can use techniques like caching / a common polling thread to help out)