Broadcast real time when data change in database

Hướng dẫn dưới đây về việc broadcase real time khi có thay đổi dữ liệu trong cơ sở dữ liệu (broadcase realtime when data change in database).

Kỹ thuật liên quan để áp dụng bao gồm:

  • SignalR
  • SqlDependency class
  • Sql Service Broker

Các bước cài đặt

  • Thiết lập Sql Service Broker: ON


  • Cài đặt và viết mã SignalR

Mở NuGet Package Manager Console  và gõ lệnh sau

install-package Microsoft.AspNet.SignalR

  • Thiết lập chuỗi kết nối


DefaultConnection” connectionString=”data source=SERVER-NAME;database=DATABASENAME;user id =USERID;password=PASSOWRD” providerName=”System.Data.SqlClient” />


  • Tạo SignalR Hub class và viết mã nguồn như sau (sử dụng SqlDependency)

public class NotificationHub : Hub

Int16 totalNewMessages = 0;
Int16 totalNewCircles = 0;
Int16 totalNewJobs = 0;
Int16 totalNewNotification = 0;

public void SendNotifications()
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings[“DefaultConnection”].ConnectionString))
string query = “SELECT NewMessageCount, NewCircleRequestCount, NewNotificationsCount, NewJobNotificationsCount FROM [dbo].[Modeling_NewMessageNotificationCount] WHERE UserProfileId= 1”;
//(select max(UserProfileId) from [dbo].[Modeling_NewMessageNotificationCount])
using (SqlCommand command = new SqlCommand(query, connection))
try {
command.Notification = null;
DataTable dt = new DataTable();
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
if (connection.State == ConnectionState.Closed)
var reader = command.ExecuteReader();
if (dt.Rows.Count > 0)
totalNewMessages = Int16.Parse(dt.Rows[0][“NewMessageCount”].ToString());
totalNewCircles = Int16.Parse(dt.Rows[0][“NewCircleRequestCount”].ToString());
totalNewJobs = Int16.Parse(dt.Rows[0][“NewJobNotificationsCount”].ToString());
totalNewNotification = Int16.Parse(dt.Rows[0][“NewNotificationsCount”].ToString());
catch(Exception ex)

//Call function on Client
IHubContext context = GlobalHost.ConnectionManager.GetHubContext();
context.Clients.All.RecieveNotification(totalNewMessages, totalNewCircles, totalNewJobs, totalNewNotification);
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
if (e.Type == SqlNotificationType.Change)
NotificationHub nHub = new NotificationHub();

  • Mapping SignalR trong Startup.cs class
[assembly: OwinStartup("Testing", typeof(MyStartup))]
 public class MyStartup
 public void Configuration(IAppBuilder app)
  • Start and Stop SqlDependency
void Application_Start(object sender, EventArgs e)  
        // Code that runs on application startup 
    void Application_End(object sender, EventArgs e)  
        //  Code that runs on application shutdown 
  • Client code
    New Notifications 
        $(function () {             
            // Declare a proxy to reference the hub. 
            var notifications = $.connection.notificationHub; 
            // Create a function that the hub can call to broadcast messages. 
            notifications.client.recieveNotification = function (totalNewMessages, totalNewCircles, totalNewJobs, totalNewNotifications) { 
                // Add the message to the page.                 
            // Start the connection. 
            $.connection.hub.start().done(function () { 
            }).fail(function (e) { 

Broadcast Realtime SQL data using SignalR

You have “spanNewMessages”>0 New Message Notification.
You have “spanNewCircles”>0 New Circles Notification.
You have “spanNewJobNotifications”>0 New Job Notification.
You have “spanNewNotifications”>0 New Notification.


Source: Microsoft


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s