CODEWITHSUNDEEP
c#.netsql-serverdatabase
Marcin Bator
Marcin Bator

Reputation: 381

How to receive notifications in .NET application when database table changes?

I've always wondered how huge applications (such as Facebook) handle notifying users about events (ex. someone messaged you) in practically real time without pooling the database every second/minute etc (I assume that Facebook doesn't check if there are any new messages for logged in user every second). Is there any way in Sql Server to receive notifications in C# application when database changes (row is inserted or updated)?

To (hopefully) make things a little bit more practical:

How would you handle a desktop chat application that is connected to a database server and allows users to message each other providing instant notifications for the person receiving message?

How would you handle notifying user about messages that they received while they were "offline"?

Let's assume that application would handle several hundreds/thousands users and notifications should be (relatively) instant (without pooling database every 10 seconds to see if there are any new messages).

Upvotes: 0

Views: 2017

Answers (3)

Dmitry
Dmitry

Reputation: 344

As it was mentioned above, it supposed to be a server(application) responsibility, not db

I would do it in the following way:

The application(let it be a central server) has to make sure if a message is delivered or not. If it's delivered, save it to the history(db). If it's not, save it to postponed messages(db).

If user is offline, there will be a moment when he/she logs in to the server. Once log in is completed, server sends him/her postponed messages.

In case of serverless/distributed system the logic will be the same, except postponed messages may be saved either on only client side, or on some nodes that do messages routing

Upvotes: 1

PhillipH
PhillipH

Reputation: 6212

It depends on how accurate you need to be. Facebook doens't need to be accurate so can afford to do this anyway it wants. For an accurate implementation, for instance where there is a Quality of Service issue so that message must get to their destination in a certain time, then you need to have a bidirectional link between the client and server. Websockets does this on HTTP although it really justs masks a lightweight HTTP polling mechanism.

In terms of making sure a specific session (user) gets messages, I'd be tempted to use an in-memory database (like Redis) as the writeable-cache for all the messages that get generated. How you get the session store in Redis updated for your users session depends on how the message is originated; it could be

1) Because of a database update 2) Becuase of an external system input 3) Because some time has elapsed and some schedule gatekeeper reached.

In all these cases you might use a different technology to notify the distributed message cache, so in SQL Server you might generate a Trigger to write to a outgoing message table, perhaps using SQL Server Object Broker or MSMQ as the message dump. A seperate process would parse the message dump and upload the Redis cache.

Upvotes: 2

Daryl Beaux Knowles
Daryl Beaux Knowles

Reputation: 66

Using the database as the center of a communications hub is going to bottleneck and will likely not scale well. Since you mentioned .NET, you should check out SignalR. https://www.asp.net/signalr

In a simple use case, you could write to the database and simultaneously send notify events to tell the clients to check for the new stuff you just wrote to the database.

Upvotes: 5

Related Questions