Home > Asp.net > How to get notification from database when any transaction on records?

How to get notification from database when any transaction on records?

Shirish Batule Asp.net, Asp.net MVC, C#, MS SQL Server 0

This post was most recently updated on August 5th, 2024

Technologies Used:

  1. Visual Studio 2013 Express
  2. Sql Server 2008 R2 Express
  3. MVC 5 Nuget
  4. SqlDependency MSDN
  5. SignalR 2.0.1 Nuget
  6. Jquery 2.0.3 Nuget
  7. Toast Notifications

Script to Create Broker & Service:

create a user called “GeneralUser” with a password of “dbpassword” in your SQl Server logins. Run the following script in a Sql query pane, to create a message broker\service for your database.

1
2
3
4
5
6
7
8
9
10
USE <DatabaseName>;
GO
CREATE QUEUE TrackerQueue;
CREATE SERVICE TrackerService ON QUEUE TrackerQueue (
  [http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO GeneralUser;
ALTER DATABASE <DatabaseName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE <DatabaseName> SET ENABLE_BROKER
ALTER DATABASE <DatabaseName> SET MULTI_USER
GO

 Explanation of Code:

SqlDependency

The C# database listener code (below) when initialized, will create a unique database broker\service instance within the database. The Getlist();  method will retrieve the Data from the database and create a listener each time the respective database table is updated, new record inserted or deleted. I am caching the data with within an application variable, so that when a new client connects to the site, they do not have to query the database for the latest defects – this cache will be kept when a change is detected in the database as the method dependency_OnDataChangedDelegate() will be run – thus invalidating the cache

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
public class SqlListener
{
 
    private NotificationViewModel notificationViewModel;
    private Object threadSafeCode = new Object();
 
    public SqlListener()
    {
        notificationViewModel = new NotificationViewModel();
    }
 
 
    public string GetList()
    {
 
 
        this.notificationViewModel.notifications = new List<Notifications>();
 
 
 
        //the connection string to your database          
        string connString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        string proc = ConfigurationManager.AppSettings["StoredProcedureName"];
        // SqlDependency.Stop(connString);
        SqlDependency.Start(connString);
 
 
 
        if (!CheckUserPermissions()) return null;  //first we need to check that the current user has the proper permissions, otherwise display the error          
 
        using (SqlConnection sqlConn = new SqlConnection(connString))
        {
            using (SqlCommand sqlCmd = new SqlCommand())
            {
                sqlCmd.Connection = sqlConn;
                sqlCmd.Connection.Open();
 
              
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.CommandText = proc;
                sqlCmd.Notification = null;
 
 
 
                SqlDependency dependency = new SqlDependency(sqlCmd);
                dependency.OnChange += new OnChangeEventHandler(dependency_OnDataChangedDelegate);
 
                if (sqlConn.State != ConnectionState.Open) sqlConn.Open();
 
                using (SqlDataReader reader = sqlCmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Notifications details = new Notifications();
                        if (reader["NotificationId"].ToString() != "")
                            details.NotificationId = Int32.Parse(reader["NotificationId"].ToString());
                        if (reader["NotificationTopic"].ToString() != "")
                            details.NotificationTopic = reader["NotificationTopic"].ToString();
                      
                        this.notificationViewModel.notifications.Add(details);
                    }
                }
 
 
            }
 
            lock (threadSafeCode)
            {
                HttpRuntime.Cache["notifications"] = SerializeObjectToJson(this.notificationViewModel);
            }
 
            return (HttpRuntime.Cache["notifications"] as string);
        }
    }
 
    /// <summary>
    /// Checks the user permissions.
    /// </summary>
    /// <returns></returns>
    public bool CheckUserPermissions()
    {
        try
        {
            SqlClientPermission permissions = new SqlClientPermission(PermissionState.Unrestricted);
            permissions.Demand(); //if we cannot Demand() it will throw an exception if the current user doesnt have the proper permissions
            return true;
        }
        catch { return false; }
    }
 
    /// <summary>
    /// Handles the OnDataChangedDelegate event of the dependency control.
    /// </summary>
    /// <param name="sender">The source of the event.</param>
    /// <param name="e">The <see cref="System.Data.SqlClient.SqlNotificationEventArgs"/> instance containing the event data.</param>
    private void dependency_OnDataChangedDelegate(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type != SqlNotificationType.Change) return;
 
        var context = GlobalHost.ConnectionManager.GetHubContext<NotificationsHub>();
        string actionName = ((System.Data.SqlClient.SqlNotificationInfo)e.Info).ToString();
        context.Clients.All.addMessage(this.GetList(), actionName);
 
        //sql notification will have been used up at this stage - will be rebined later in code
        SqlDependency dependency = sender as SqlDependency;
        dependency.OnChange -= new OnChangeEventHandler(dependency_OnDataChangedDelegate);
    }
 
    /// <summary>
    /// Serializes the object.
    /// </summary>
    /// <param name="pObject">The p object.</param>
    /// <returns></returns>
    public String SerializeObjectToJson(Object objNotifications)
    {
        try
        {
            return new System.Web.Script.Serialization.JavaScriptSerializer().Serialize(objNotifications);
        }
        catch (Exception) { return null; }
    }
 
}

 When the SqlDependency is created with the SqlDependency.Start(); command, a new (unique) queue and service objects are created  within the database. If I had of provided a name in the start command as a parameter, the queue and service would can have been called this parameter name, instead of a GUID style naming convention.

SignalR Hub

There are two simple methods within the NotificationsHub class, one to push the newly edited database table data to the clients (all the connected clients in this case), and a method to retrieve cached data when a device connects for the first time (performance gain). You will notice that I have put a lock on the code that updates the cache, so that there are no conflicts between threads when performing the update.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
public  class NotificationsHub
    {
        private Object threadSafeCode = new Object();
 
        public void Send(string jsonBugs, string action)
        {
            Clients.All.addMessage(jsonBugs, action);
        }
 
        public void Start()
        {
            // check if application cache has previously been populated
            if (String.IsNullOrEmpty((HttpRuntime.Cache["notifications"] as string))) // first time in
            {
                lock (threadSafeCode)
                {
                    SqlListener listener = new SqlListener();
                    string jsonBugs = listener.GetList();
                    HttpRuntime.Cache["notifications"] = jsonBugs;
                    Clients.Caller.addMessage(jsonBugs, "Select");
                    listener = null;
                }
            }
            else
            {
                Clients.Caller.addMessage((HttpRuntime.Cache["notifications"] as string), "Select");
            }
        }  
    }

 JavaScript

This custom script will perform the connection to the SignalR class on the server and create the respective bindings for the controls and notifications. Put this code on master page.

<script src=”/Scripts/jquery-1.8.2.min.js” type=”text/javascript”></script>
<link href=”/Scripts/Toast/css/jquery.toastmessage.css” rel=”stylesheet” type=”text/css” />
<script src=”/Scripts/Toast/jquery.toastmessage.js” type=”text/javascript”></script>
<script src=”/Scripts/jquery.signalR-2.0.1.min.js” type=”text/javascript”></script>

<script type=”text/javascript”>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
$(document).ready(function () {
            // notification initialise
            //$.mobile.loading('show', {
            //    text: 'Connecting to server...',
            //    textVisible: true,
            //    theme: 'b',
            //    html: ""
            //});
 
            // SignalR initialise    
            var Notifications = $.connection.NotificationsHub;
 
            // server entry point to client
            Notifications.client.addMessage = function (objNotifications, action) {
                var header = '';
 
                //  alert(obj);
                var NotificationsCount = [];
                var data = [];
                var obj = $.parseJSON(objNotifications);
                
                data = obj.Notifications;
                        
                var content = '';
                var $tbl = $('#NotificationsGrid');
                $tbl.empty();
                var NotificationsId;
                var NotificationsTopic;
              
                var rows = [];
                for (var i = 0; i < data.length; i++) {
                    if (data[i].NotificationsId > 0) {
                        NotificationsId = data[i].NotificationsId;
                        NotificationsTopic = data[i].NotificationsTopic;
                        
                    }
                }
              
 
                switch (action) {
                  
                    case "Update":
                        header = "<a href='/Notification/NotificationsDetails/" + NotificationsId + "'>  " + NotificationsTopic + "</a>";
                      
                        break;
                      
                    case "Insert":
                        header = "<a href='/Notification/NotificationsDetails/" + NotificationsId + "'>  " + NotificationsTopic + "</a>";
                        break;
                    default:
                        header = "Notifications status...";
                }
 
                var NotificationsStatus = header,
                                        toastMessageSettings = {
                                            text: NotificationsStatus,
                                            sticky: false,
                                            position: 'top-right',
                                            type: 'success',
                                            closeText: ''
                                        };
              
            
                    var myToast = $().toastmessage('showToast', toastMessageSettings); // display notification
              
            };
 
            // start SignalR    
            $.connection.hub.start().done(function () {
                Notifications.server.start();
 
                
 
            });
            // SignalR End
        });
 
        function ConvertJsonDateString(jsonDate) {
            var shortDate = null;
 
            if (jsonDate) {
                var regex = /-?\d+/;
                var matches = regex.exec(jsonDate);
                var dt = new Date(parseInt(matches[0]));
                var month = dt.getMonth() + 1;
                var monthString = month > 9 ? month : '0' + month;
                var day = dt.getDate();
                var dayString = day > 9 ? day : '0' + day;
                var year = dt.getFullYear();
                var time = dt.toLocaleTimeString();
                shortDate = dayString + '/' + monthString + '/' + year + ' : ' + time;
            }
            return shortDate;
        };

</script>

This Article is TAGGED in , , , , , , . BOOKMARK THE permalink.

Get in Touch


Get access to some of the top IT talent, leverage our expertise to jumpstart your project.

Your Interest *
Attachment (If Any)
No file chosen
0 / 180
Shirish Batule

Related articles

XMLHttpRequest for Async Ajax Call

Problem Statement: Async Ajax Call using XMLHttpRequest Solution :          

MS SQL connection using JDBC in Selenium

To connect MS SQL database using jdbc driver in java, we need

T-SQL Query Optimization in MS SQL Server, The all important WHERE clause

As an T-SQL Developer, we always tend to wonder why my query

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">