Database Change Notifications in ASP.NET using WebSocket

Display database change on website in real-time

Database table changes are usually not displayed right away in an application, escecially if it is a web application.

With HTML5 and Web API that is about to change. New MVC introduces async controllers which can be used to do live data update to HTTP client. This especially involves using of WebSockets and SignalR.

In this example I'm about to show how you can display in real-time data changes committed to database tables. I found a few articles on Internet which are describing how to do this, so I combined code from both and made a test app which works and you can download it from this article.

First thing we need to to is to ensure that out database has "Broker Enabled" to be able to send notifications to a client. That can be done from options dialog on a database itself

Sql Server Broker

or by running the following command in query window in SQL Server Management Studio

ALTER DATABASE [Temp] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

The second thing to be done is to initialize Notification in Global.asax.cs file with two lines for Application_Start and Application_End

public class Global : HttpApplication
    {
        void Application_Start(object sender, EventArgs e)
        {
            // Code that runs on application startup
            AreaRegistration.RegisterAllAreas();
            GlobalConfiguration.Configure(WebApiConfig.Register);
            RouteConfig.RegisterRoutes(RouteTable.Routes);

            SqlDependency.Start(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString); 
        }

        void Application_End()
        {
            SqlDependency.Stop(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
        }
    }

Now we enabled web application to handle notifications from SQL Server, all we have to do now is to specify what are we monitoring and handle that in our controller. Since controller needs to send messages to a client, we will have to use WebSocket or SignalR.

In our case (since I did not work with SignalR yet) we are going to use WebSocket. This will require to fetch Microsoft.WebSockets.dll from NuGet.

So here goes the controller code

    public class DatabaseNotificationController : ApiController
    {
        public HttpResponseMessage Get()
        {
            HttpContext.Current.AcceptWebSocketRequest(new ChatWebSocketHandler());
            return Request.CreateResponse(HttpStatusCode.SwitchingProtocols);
        }

        class ChatWebSocketHandler : Microsoft.Web.WebSockets.WebSocketHandler
        {

            public ChatWebSocketHandler()
            {
                SetupNotifier();
            }

            protected void SetupNotifier()
            {
                using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(@"SELECT [FullName],[experiance_nYears] FROM [dbo].[t_Doctor]", connection))
                    {
                        command.Notification = null;
                        SqlDependency dependency = new SqlDependency(command);
                        dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                        if (connection.State == ConnectionState.Closed)
                        {
                            connection.Open();
                        }
                        var reader = command.ExecuteReader();
                        reader.Close();
                    }
                }
            }

            private static WebSocketCollection _chatClients = new WebSocketCollection();

            public override void OnOpen()
            {
                _chatClients.Add(this);
            }

            public override void OnMessage(string msg)
            {
                
            }

            private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
            {
                _chatClients.Broadcast(string.Format("Data changed on {0}", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
                SetupNotifier();
            }
        }

    }

This controller is pretty much all you need to grab notifications of table updates. Method dependency_OnChange will handle update and broadcast message to all the connected clients. All we are left to to is to connect client to this controller.

Since it is HTML5 feature, we will initialize WebSocket client with simple JavaScript on the page.

    $(document).ready(function () {
        initSocket();
    });

    function initSocket(recordType) {
        var uri = "ws://localhost:8080/api/DatabaseNotification";
        websocket = new WebSocket(uri);

        websocket.onopen = function () {
            $('#messages').prepend('<div>Connected to server.</div>');
            websocket.send(recordType);
        };

        websocket.onerror = function (event) {
            $('#messages').prepend('<div>ERROR</div>');
        };

        websocket.onmessage = function (event) {
            $('#messages').prepend('<div>' + event.data + '</div>');
        };
    }

This solution will work in all modern browsers which support HTML5 but it will fail for other browsers. To handle most of the browser vendors and version you might consider involving SignalR.

The following is a list of browser supporting using of WebSocket directly on a page without SignalR. The whole support list of web browsers supporting WebSockets can be found at http://caniuse.com/websockets

Websocketsupport

References

Disclaimer

Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.

About the author

DEJAN STOJANOVIC

Dejan is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform including ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects

CONNECT WITH DEJAN