Notify users using bell shaped icon

I am building an application where I have to notify users when new record is inserted in the database. My research has indicated that I can use signalr and sql dependency for that. However, I have some few concerns I need to address.

1. The tables are many and the notification need to be done for each table, so using sql dependency may mean I may have to create dependency for each table.
2. Will sqldependency have performance issues?
3. There is also the need to keep count of unread notifications for each client.

I have googled so many times but i seem not to grasp how the concept should be like. I will like to have a simple logic and understandable codes. any help will be appreciated. I am using ASP.Net core MVC

> I seem not to grasp how the concept should be like. I will like to
> have a simple logic and understandable codes. any help will be
> appreciated. I am using ASP.Net core MVC

Well, based on your scenario and description it seems that you are looking for designing a system where you want to keep informed your user about any changes take place within your database.

Actually, this can be done either using Asp.net core SignalR or Sql Dependency.
I hope you already, have the basic idea how these two technology works.

Apart from this, I would rather trying to answer your question:

> The tables are many and the notification need to be done for each
> table, so using sql dependency may mean I may have to create
> dependency for each table.

As you may know, SqlDependency nothing but creates a trigger on particular table and the trigger will be fired once any chnages would be tracked. So when you will configure the dependency it will create the trigger.

As you have many tables, you have to create the dependency for every table as well and at this moments SqlDependency doesn't have the mechanism to manage them at once. You can get more details here in official document.

> Will sqldependency have performance issues?

It depends, usually, for SQL Dependency there is no known performence issue. But, as you may know if your table contains many triggers it might effect upon performence issue. Because, while your triggers executing and if at the same time any read, write operation also being called then it would critically impact on the application performence.

> There is also the need to keep count of unread notifications for each
> client.

I think, this wouldn't be a big-deal to track. You can check it by keeping a flag for the particular client. For instance, you can set a property and on click event you can change the status, because, when the message would broadcust towards the user, there would be chnage event on client side.

In addition to this, as you are using Asp.net core project, you could consider asp.net core signalR.

So that, you can query all of your entity and then you can broadcust the update using Clients.All.SendAsync method. You may create and API endpoint which will do the task to notify all of your users using the SignalR Hub Class

Demo:

        [Route("cookie/BroadCastToUsers")]
        [HttpGet]
        public IHttpActionResult BroadCastToUsers()
        {
            //Get total sell from sells table
            var totalSell = 100;
            //Get Total product from product table:
            var totalProduct = 150;
            //Get top selling product from product table 
            var topSellingProduct = "Microsoft Surface";



            var message = $"Hello from signalR Total sells:{totalSell}, Total product:{totalProduct}, Top selling product:{topSellingProduct}";

            var context = GlobalHost.ConnectionManager.GetHubContext<HubClassUpdateUsers>();
            context.Clients.All.BroadcastMessage("sysytem",message);

            return Ok();
        }

Hub Class:

public class HubClassUpdateUsers : Hub
    {
        public override Task OnConnected()
        {
            return base.OnConnected();
        }

        public void BroadcastMessage(string name, string message)
        {
            Clients.All.broadcastMessage(name, message);
        }
        
    }

Output:

Note: Please refer to following document for details implementation and guidelines:

1. Asp.net core SignalR
2. SQL Dependency
3. SQL Dependency Implementation