Update C# client whenever database is updated

views:

114

answers:

+1 Q:

Update C# client whenever database is updated

I am using MySQL with C# / Visual Studio 2008. I need to know any method so that if one user updates the database, all the logged in users over the network, are notified that a change has been occurred. If i logged in the database through my application, at that time the serial no of the latest record in the database was 10, and then i someone with the privileges updates the record in the database and at the same time i am being notified that the database has been updated with a new record. Common example can be of MSN messenger's alert for a friend when he gets online... Though i really don't want that popup, but alert functionality is something like that...

One more thing to ask: I want a functionality like if a record's status is not updated (for say about more than 3 hours), then i want all users to get alert about that issue also. This functionality is needed for all of the records, not specifically for new records.

+2 A:

Quite a long answer, but your best bet would be something like SqlCacheDependency.

Also you could use something called the "Reverse AJAX" technique, which is used by Google Mail notifications, and talked about here: http://stackoverflow.com/questions/988082/how-does-incoming-mail-notification-on-gmail-works

Besides those two, your own other options AFAIK is simple server polling.

Also, you haven't mentioned what your client is.

Is it a public web site on the internet? Is it an intranet app, it is a WPF app?

If it's a web site, you're best best is client-side callbacks using the Reverse AJAX technique i mentioned.

RPM1984 2010-09-22 11:09:59

SqlCacheDependency with probably won't work with MySql

Andrzej Nosal 2010-09-22 11:28:35

Its a desktop windows application which is connected to a local database server. Also i am using ODBC connector for MySQL, and for that MysqlDependency does not exist. Sad for me as it was a nice and neat solution. The clients are the same s/w running on different machines with different accounts. Also mention what is AFAIK

booota 2010-09-22 11:34:55

@ A. Nosal SqlCacheDependency's alternative for Mysql is MySqlDependency. You can read furthermore over here http://www.devart.com/dotconnect/mysql/docs/Devart.Data.MySql~Devart.Data.MySql.MySqlDependency.html

booota 2010-09-22 11:37:35

AFAIK = as far as i know. Okay its a windows application and mysql - hmm unfortunately not two of my greatest strengths (i prefer mssql and web). maybe someone else here can help, also update your question/tags to specify the client is a winforms app.

RPM1984 2010-09-22 11:43:40

@RPM1984.... Can you suggest any cheat/tweak for this purpose?

booota 2010-09-22 11:47:53

what about having a seperate win forms app (which connects to the same DB) run as a windows tray application? http://stackoverflow.com/questions/158895/how-to-make-a-windows-forms-net-application-display-as-tray-icon

RPM1984 2010-09-22 11:51:27

@RPM1984... Will that not resource effective... also how can that application be handled from the current one... i mean if i exit my main application that should also be terminated...

booota 2010-09-22 12:19:27

@RPM1984: Ok bro i have seen the Reverse Ajax, but still cant think of any appropriate solution... I am so stuck in this problem... really need something to do regarding this issue...

booota 2010-09-23 05:17:05

@booota - Reverse AJAX won't work in your scenario (because it's a winforms application). As i said, i dont have much skills in winforms so cant really be much help. Good luck

RPM1984 2010-09-23 05:26:52

@RPM1984: Thanks bro anyways...

booota 2010-09-23 10:32:06

+1 A:

You probably need to design some kind of poll functionality in your client. For example, you can send a request each minute asking the database "Have you updated anything since [date the client latest got updates]?" If the database answers true, you let the client request the full set of updates. If not, you let it sleep another minute.

Tomas Lycken 2010-09-22 11:10:18

in addition, you could piggyback this question on to any other queries to the database.

Daren Thomas 2010-09-22 11:49:39

Instead of a "Have you updated anything" followed by "Get new data", it's better to squash into a "Get updates since last" to cut out an unnecessary database round-trip.

snemarch 2010-09-22 12:51:43

why not to get the max(serial), and each time check if its greater than the current value?

booota 2010-09-22 18:04:35

@snemarch: The bottleneck is just as likely to be network traffic - it seems the OP only wants to update the actual data *when he really needs to*. The actual query getting the updated data might be quite heavy, for example.

Tomas Lycken 2010-09-22 22:37:46

@booota: What happens if someone updates a record, but doesn't add a new one? The serial doesn't change.

Tomas Lycken 2010-09-22 22:38:17

@Tomas Lycken: Nice point to be considered. So i should take it as there is no solution for me because i am using ODBC connector for MySQL. Wait i can use a counter may be... though its the worst case of cheat... :(

booota 2010-09-23 04:17:42

@Tomas Lycken: wrt. network traffic, saving an unnecessary round-trip is nice for latency. If a "is-there-new-data" will always be followed by "get-new-data", you might as well combine them and do zero processing on empty resultselt. If you already have a low-cost "is-there-new-data" service call, combining it with "get-new-data" shouldn't be any more expensive.

snemarch 2010-09-23 07:18:20

@booota: I'd use a trigger of some sort in the database, that updates a date field somewhere. When you want to check something has changed, you input the date when you last got your updates, and return true/false depending on which date is the later.

Tomas Lycken 2010-09-23 07:23:49

@snemarch: Good point! That can certainly be done. However, in that case it is really important that the empty result set returned when no update is needed is distinctive from just an empty result set created by someone deleting the last record. In other words, the client must *in all possible cases* be able to know whether the return value(s) indicate that there was no new data, or if it is the new data itself. This can be a design problem early in the development, since you might not know what kinds of data you will want to return, and therefore not what kinds are OK as "no-data"-notifiers.

Tomas Lycken 2010-09-23 07:27:19

@Tomas: very good point - in a small project I worked on, we solved this by returning both added, modified and deleted rows. For this to work, instead of deleting rows from the database we added an IsDeleted flag and postponed actual deletion to scheduled cleanups.

snemarch 2010-09-23 08:55:49

@snemarch: Nice idea... Really helpful. In my project i dont think the client needs any kind of deletion from the database... so i just need an "Updated" and "Inserted" flag. What do you say?

booota 2010-09-23 10:33:34

@booota: would probably work fine :) - our solution used a DATETIME for each row to detect both updated and added records; this gave issues if client and server clocks weren't in sync (a ROWVERSION would have been better). Seems MySQL doesn't have ROWVERSION but does have auto-updating TIMESTAMP, so you can use that - just be sure to return newest TIMESTAMP from the server and use that for subsequent refreshes rather than client's currenttime :)

snemarch 2010-09-23 10:59:08

Using the SqlDependency Class is a better way to make all your data driven more faster and efficient. It just remove the need of constantly re-query your database checking every time when a changes is made in the data.

Anuja Pawar 2010-09-22 11:53:45

ansaurus

tags:

views:

answers:

Update C# client whenever database is updated

related questions