Automatic Updates From SQL Server Using Signalr and Grid.MVC

 

Intro

I was wondering one day if Signalr could be used to automagically update  a web page when a new record is inserted in an SQL Server table. After a bit of digging around, i found that, you guessed it, the answer was YES! So here’s how.
In this post i’ll asume that you know how to create a new MVC 5 project and install a nuget package.

Prerequisites

First, you will need to download and install Visual Studio 2013 Community Edition. 
Create a new MVC 5 project and then install the following nuget packages: Microsoft.AspNet.SignalR, Grid.MVC and Dapper.NET. We’ll be using Dapper to connect to our database.
Run the following commands in Package Manager Console:

• Install-Package Microsoft.AspNet.Signalr

• Install-Package Grid.Mvc

• Install-Package Dapper

Setting Up the Database

Create a new database named TestDatabase. In this database create a new table named SalesNotifications using the following script:

CREATE TABLE [dbo].[SalesNotifications]

(

    [Id] INT NOT NULL PRIMARY KEY Identity(1,1), 

    [ProductName] NVARCHAR(50) NULL, 

    [Quantity] INT NULL, 

    [SalesNotificationRead] BIT NULL

)

Enable service broker on this database by running the following script:

ALTER DATABASE TestDataBase SET ENABLE_BROKER with rollback immediate

 

Startup.cs and Global.asax

We will use SqlDependency class in order to receive notifications from the server (for this reason the service broker was enabled on the database).

Modify the class Startup.cs in order to enable Signlr. Add the following code in the method Configuration:

app.MapSignalR();

We’ll need to start the listner for receiving change notifications from the server. In order to do that, add the following line in Global.asax in the Application_Start method:

SqlDependency.Start(connString);

When the application ends, we’ll need to stop the listener. Call SqlDependency.Stop in Application_End()

protected void Application_End()
        {
            //Free the dependency
            SqlDependency.Stop(connString);
        }

Hubs

Create a new folder in your solution named Hubs. This will be the place where we’ll put the Signalr hub class. After that, create a new Signalr Hub class in this folder, name it SalesHub. In order to do that, add new item, go to the Web tab and search for Signalr Hub Class (v2): 

    [HubName("salesHub")]
    public class SalesHub : Hub
    {
        public void Hello()
        {
            Clients.All.hello();
        }

        public static void UpdateSalesList()
        {
            IHubContext context = GlobalHost.ConnectionManager.GetHubContext<SalesHub>();
            context.Clients.All.updateSalesList();
        }
    }

The method UpdateSalesList() will be called when the OnChangeEventHandler event will be fired and this method will be used to broadcast messages.

Models and Database Access

First, create new model in the Models folder. Name it SalesNotifications.

public class SalesNotifications
    {
        [HiddenInput] 
        public int Id { get; set; }
        public string ProductName { get; set; }
        [Range(0, 1000000000,
            ErrorMessage = "Quantity must be at least zero")]
        public int Quantity { get; set; }
        public bool SalesNotificationRead { get; set; }
    }

For the database access, we’ll use Dapper.NET to access the database. Dapper.NET is a micro-orm, simple and fun to use. It’s made by Marc Gravell and Sam Saffron and it’s a brilliant piece of software.
Create a new folder in your solution named DataAccess. In this folder, add a new class named SalesNotificationsRepository. This class will have 3 methods: GetAllUnreadSalesNotifications() that will pull from the database all unread notifications,
MarkSalesNotificationAsRead(int requestId) will mark a notifications as Read based on Id and dependency_OnChange that will execute every time the SQL Server will send a notification to the web app.

public class SalesNotificationsRepository
    {
        string connectionString = ConfigurationManager.ConnectionStrings["dbConnectionString"].ToString();
        SqlConnection dbcon = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnectionString"].ToString());
        public List<SalesNotifications> GetAllUnreadSalesNotifications()
        {
           var result = new List<SalesNotifications>();
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(@"select [Id]
                                  ,[ProductName]
                                  ,[Quantity]
                                  ,[SalesNotificationRead]
                                  FROM [dbo].[SalesNotifications] where SalesNotificationRead = 0", connection))
                {
                    command.Notification = null;

                    var dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                    if (connection.State == ConnectionState.Closed)
                        connection.Open();

                    var reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        result.Add(item: new SalesNotifications
                        {
                            Id = (int)reader["Id"],
                            ProductName = (string)reader["ProductName"],
                            Quantity = Convert.ToInt32(reader["Quantity"]),
                            SalesNotificationRead = Convert.ToBoolean(reader["SalesNotificationRead"])
                        });
                    }
                }

            }
            return result; 
        }
        public int MarkSalesNotificationAsRead(int requestId)
        {
            var sqlQuery =
                "UPDATE SalesNotifications " +
                "SET SalesNotificationRead = 1 " +
                "WHERE Id = @requestId";
            var result = this.dbcon.Execute(sqlQuery, new { requestId = requestId });
            return result;
        }

        private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            if (e.Type == SqlNotificationType.Change)
            {
                SalesHub.UpdateSalesList();
            }
        }
    }

The last two methods are pretty simple, so let’s look at the first one. In order to get the data from the SalesNotifications table and to receive the notifications from the SQL Server, we’ll need to associate an instance of SqlDependency class to an SqlCommand

 using (var command = new SqlCommand(@"select [Id]
                                  ,[ProductName]
                                  ,[Quantity]
                                  ,[SalesNotificationRead]
                                  FROM [dbo].[SalesNotifications] where SalesNotificationRead = 0", connection))
                {
                    command.Notification = null;

                    var dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

The select query will pull all the unread sales notifications from the database and dependency_OnChange will be called every time there is a change in the SalesNotifications table data. Note that this select has some limitations.

Controllers and Views

The SalesNotifications controller is very simple. It has only two actions: One will be used to get unread sales notifications and the other to mark a sales notification as read.

 public class SalesNotificationsController : Controller
    {
        SalesNotificationsRepository salesNotificationRepository = new SalesNotificationsRepository();
        // GET: SalesNotifications
        public ActionResult Index()
        {
            return View();
        }

        public ActionResult GetAllUnreadSalesNotifications()
        {
            return PartialView("_SalesNotificationsList", salesNotificationRepository.GetAllUnreadSalesNotifications());
        }

        public ActionResult MarkSalesNotificationAsRead(int Id)
        {
            salesNotificationRepository.MarkSalesNotificationAsRead(Id);
            return View("Index");
        }
    }

In the Views folder, create a new folder named SalesNotifications and an empty view named Index. This view will be used to display the list of unread sales notifications.
Create a new empty partial view in the Shared folder and name it _SalesNotificationsList. Sales notifications will be displayed using Grid.MVC in this partial view.
The Index view will look like this:

@{
    ViewBag.Title = "Sales Notifications";
}

<div class="row">
    <div class="col-md-12">
        <div id="salesNotifications"></div>
    </div>
</div>
@section Scripts{
    <script src="~/Scripts/jquery.signalR-2.1.2.js"></script>
    <!--Reference the autogenerated SignalR hub script. -->
    <script src="/signalr/hubs"></script>
    <script type="text/javascript">
        $(function () {
            // Declare a proxy to reference the hub.
            var salesNotificationsHub = $.connection.salesHub;

            //debugger;
            // Create a function that the hub can call to broadcast messages.
            salesNotificationsHub.client.UpdateSalesList = function () {
                getAllSalesNotifications()

            };
            // Start the connection.
            $.connection.hub.start().done(function () {
                getAllSalesNotifications();
            }).fail(function (e) {
                alert(e);
            });
        });


        function getAllSalesNotifications() {
            var tbl = $('#salesNotifications');
            $.ajax({
                url: '/SalesNotifications/GetAllUnreadSalesNotifications',
                contentType: 'application/html ; charset:utf-8',
                type: 'GET',
                dataType: 'html'
            }).success(function (result) {
                tbl.empty().append(result);
            }).error(function () {

            });
        }
    </script>
}

and the _SalesNotificationsList view will look like this:

@model IEnumerable<MVCAutoUpdateFromSQLServerWithSignalr.Models.SalesNotifications>
@using GridMvc.Html;
@{
    ViewBag.Title = "_SalesNotificationsList";
}

<h2>Sales Notifications</h2>

@Html.Grid(Model).Columns(columns =>
           {

               columns.Add(d => d.Id)
                .Encoded(false)
                .Sanitized(false)
                .SetWidth(130)
                .RenderValueAs(d =>
                    @<b>
                        @Html.ActionLink("Mark As Read", "MarkSalesNotificationAsRead", new { Id = d.Id })
                    </b>);
               columns.Add(foo => foo.ProductName).Sortable(true).Filterable(true).Titled("Product Name");
               columns.Add(foo => foo.Quantity).Sortable(true);
           })

Wraping it up

In this post, Signalr, SqlDependency, ASP.NET MVC 5 where used to  update automatically a web site when new records are inserted in an SQL Server database.
You can download (or fork) a complete solution over at github.