ASP.NET MVC 5 SignalR, SqlDependency and EntityFramework 6

• Download source - 592.4 KB

Introduction

I needed to implement SignalR with SqlDependency together with EntityFramework 6 for a project that I'm working on.

After much searching, I gave priority to these two articles, without, however, none of them meets my requirements:

• How to use SqlDependency to get the notification in Entity Framework
• Database Change Notifications in ASP.NET using SignalR and SqlDependency

Basically, my requirements are:
Any MVC Razor Index view, self refresh when any change occurs at the SQLSERVER database level, on the tables included in the model/query generated by the Controller.

Background

I will try to reduce the step by step, without images, because it's a very big explanation and I will try to be as objective as possible.

Environment:

• Visual Studio Professional 2013
• Microsoft SqlServer 2008R2 or 2012

Visual Studio Solution:

• ASP.NET MV5 Template without Authentication

NuGet packages:

• EntityFramework 6.1.3 (latest)
• Microsoft ASP.NET SignalR 2.2.0(latest)
• (don´t forget to update all NuGet packages to the latest version)

Development Description

SQL SERVER

Open Microsoft SqlServer Management Studio and create a new database and a table, in my case:

USE [master]
GO

CREATE DATABASE [SignalRDemo]
Go

ALTER DATABASE [SignalRDemo] SET COMPATIBILITY_LEVEL = 100
GO

ALTER DATABASE [SignalRDemo] SET  ENABLE_BROKER 
GO

USE [SignalRDemo]
GO

CREATE TABLE [dbo].[Products](
	[ProductID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](100) NOT NULL DEFAULT(''),
	[UnitPrice] [decimal](18, 2) NOT NULL DEFAULT(0),
	[Quantity] [decimal](18, 2) NOT NULL DEFAULT(0),
 CONSTRAINT [PK_dbo.Products] PRIMARY KEY CLUSTERED 
(
	[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
	ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] 

Visual Studio

Create a solution/project based on ASP.NET MV5 Template without Authentication, install the above NuGet packages and also update all NuGet packages to the latest stable version.

Entity Framework ViewModel

On the folder Model, add three classes EntityBase.cs, Product.cs and SignaRTestContext.cs.

[Serializable]
[DataContract(IsReference = true)]
public abstract class EntityBase
{
}

[Serializable]
[DataContract(IsReference = true)]
public class Product : EntityBase
{
    [DataMember]
    [Display(Name = "ID")]
    public Int32 ProductID { get; set; }

    [DataMember]
    [Display(Name = "Name")]
    public String Name { get; set; }

    [DataMember]
    [Display(Name = "Unit Price")]
    public Decimal UnitPrice { get; set; }

    [DataMember]
    [Display(Name = "Quantity")]
    public Decimal Quantity { get; set; }
}

public class SignaRTestContext : DbContext
{
    const String DefaultConnectionName = "SignaRTestConnection";

    #region "ctor"

    public SignaRTestContext() : this(DefaultConnectionName)
    {
    }

    public SignaRTestContext(String sqlConnectionName) :
    base(String.Format("Name={0}", sqlConnectionName))
    {
    }

    #endregion

    #region Collections Definitions

    public DbSet<product> Products { get; set; }

    #endregion

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<product>()
                    .ToTable("Products", "dbo")
                    .HasKey(t => t.ProductID);
    }
}</product>

Add your connection string to the web.config file:

<configuration>
...
  <connectionStrings>
    <add name="SignaRTestConnection" connectionString="Data Source=localhost;
	Initial Catalog=SignalRDemo;Persist Security Info=True;
	Integrated Security=SSPI;MultipleActiveResultSets=true;Pooling=False;" 
	providerName="System.Data.SqlClient" />
  </connectionStrings>
...
</configuration>

Product Controller

Create, under the folder Controllers, a new Controller named ProductsController (select the template MVC5 Controller with views using Entity Framework) and maybe you will need to rebuild the project first. You can use async actions.

Later, you can remove all views and actions except the Index. Remove on Index.chtml all Html.ActionLink also, we don't need that for this purpose.

OWIN

Before you run the Web Application, you need to add on the project's root a Owin Startup.

Test the App

Open the View Index.chtml under folder Views/Products and debug the application. If everything is done well, you will see the Index page with empty table. Try to add some records to the database table and refresh the Index page.

SqlDependency

Before we implement SignalR, we need to implement SqlDependency to work with Entity Framework, and here we have a lot of work becauseEntity Framework doesn't work directly with SqlDependency (you can read about here).

We will need to create two extensions, one for DbContext, the other for ObjectQuery, which can be done under the folder Model.

public static class DbContextExtensions
{
    public static ObjectContext UnderlyingContext(this DbContext context)
    {
        return ((IObjectContextAdapter)context).ObjectContext;
    }
 
    public static NotifierEntity GetNotifierEntity<TEntity>
    (this DbContext dbContext, IQueryable iQueryable) where TEntity : EntityBase
    {
        var objectQuery = dbContext.GetObjectQuery<TEntity>(iQueryable);
        return new NotifierEntity()
        {
            SqlQuery = objectQuery.ToTraceString(),
            SqlConnectionString = objectQuery.SqlConnectionString(),
            SqlParameters = objectQuery.SqlParameters()
        };
    }
    
    public static ObjectQuery GetObjectQuery<TEntity>
    (this DbContext dbContext, IQueryable query) where TEntity : EntityBase
    {
        if (query is ObjectQuery)
            return query as ObjectQuery;
 
        if (dbContext == null)
            throw new ArgumentException("dbContext cannot be null");
            
        var objectSet = dbContext.UnderlyingContext().CreateObjectSet<TEntity>();
        var iQueryProvider = ((IQueryable)objectSet).Provider;
 
        // Use the provider and expression to create the ObjectQuery.
        return (ObjectQuery)iQueryProvider.CreateQuery(query.Expression);
    }
}

public static class ObjectQueryExtensions
{
    public static String SqlString(this ObjectQuery objectQuery)
    {
        if (objectQuery == null)
            throw new ArgumentException("objectQuery cannot be null");
        
        return objectQuery.ToTraceString();
    }
 
    public static String SqlConnectionString(this ObjectQuery objectQuery)
    {
        if (objectQuery == null)
            throw new ArgumentException("objectQuery cannot be null");
        
        var dbConnection = objectQuery.Context.Connection;
        return ((EntityConnection)dbConnection).StoreConnection.ConnectionString;
    }
 
    public static ICollection<SqlParameter> SqlParameters(this ObjectQuery objectQuery)
    {
        if (objectQuery == null)
            throw new ArgumentException("objectQuery cannot be null");
 
        var collection = new List<SqlParameter>();
        foreach (ObjectParameter parameter in objectQuery.Parameters)
            collection.Add(new SqlParameter(parameter.Name, parameter.Value));
        return collection;
    }
 
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", 
    "CA2100:Review SQL queries for security vulnerabilities")]
    public static SqlCommand SqlCommand(this ObjectQuery objectQuery)
    {
        if (objectQuery == null)
            throw new ArgumentException("objectQuery cannot be null");
 
        var sqlCommand = new SqlCommand(objectQuery.SqlConnectionString(), 
        new SqlConnection(objectQuery.SqlConnectionString()));
        foreach (ObjectParameter parameter in objectQuery.Parameters)
            sqlCommand.Parameters.AddWithValue(parameter.Name, parameter.Value);
        
        return sqlCommand;
    }
}

Don't worry about NotifierEntity. We will take care of that later.

Now create a folder SqlServerNotifier.

In this folder, we will add the following classes:

• NotifierEntity.cs (class to send to the Index View with the EF: SqlQuery, SqlConnectionString and the SqlCommands for SignalR initialize the SqlDependency);
• NotifierEntityExtentions.cs (Json serialization for NotifierEntity
• PushSqlDependency.cs ( to push the SqlDependency OnChange )
• SqlDependencyRegister.cs (to register the SqlDependency with the proper EF SqlQuery)
• and a EvenHandler SqlNotificationEventHandler

public class NotifierEntity
{
    ICollection<SqlParameter> sqlParameters = new List<SqlParameter>();
 
    public String SqlQuery { get; set; }
            
    public String SqlConnectionString { get; set; }
            
    public ICollection<SqlParameter> SqlParameters
    {
        get
        {
            return sqlParameters;
        }
        set
        {
            sqlParameters = value;
        }
    }
 
    public static NotifierEntity FromJson(String value)
    {
        if (String.IsNullOrEmpty(value))
            throw new ArgumentNullException("NotifierEntity Value can not be null!");
        return new JavaScriptSerializer().Deserialize<NotifierEntity>(value);
    }
}
 
public static class NotifierEntityExtentions
{
    public static String ToJson(this NotifierEntity entity)
    {
        if (entity == null)
            throw new ArgumentNullException("NotifierEntity can not be null!");
        return new JavaScriptSerializer().Serialize(entity);
    }
}
 
public class PushSqlDependency
{
    static PushSqlDependency instance = null;
    readonly SqlDependencyRegister sqlDependencyNotifier;
    readonly Action<String> dispatcher;
 
    public static PushSqlDependency Instance(NotifierEntity notifierEntity, Action<String> dispatcher)
    {
        if (instance == null)
            instance = new PushSqlDependency(notifierEntity, dispatcher);
        return instance;
    }
 
    private PushSqlDependency(NotifierEntity notifierEntity, Action<String> dispatcher)
    {
        this.dispatcher = dispatcher;
        sqlDependencyNotifier = new SqlDependencyRegister(notifierEntity);
        sqlDependencyNotifier.SqlNotification += OnSqlNotification;
    }
 
    internal void OnSqlNotification(object sender, SqlNotificationEventArgs e)
    {
        dispatcher("Refresh");
    }
}
 
public class SqlDependencyRegister
{
    public event SqlNotificationEventHandler SqlNotification;
        
    readonly NotifierEntity notificationEntity;       
        
    internal SqlDependencyRegister(NotifierEntity notificationEntity)
    {
        this.notificationEntity = notificationEntity;
        RegisterForNotifications();
    }
 
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", 
    "CA2100:Review SQL queries for security vulnerabilities")]
    void RegisterForNotifications()
    {
        using (var sqlConnection = new SqlConnection(notificationEntity.SqlConnectionString))
            using (var sqlCommand = new SqlCommand(notificationEntity.SqlQuery, sqlConnection))
            {
                foreach (var sqlParameter in notificationEntity.SqlParameters)
                    sqlCommand.Parameters.Add(sqlParameter);
                    
                sqlCommand.Notification = null;
                var sqlDependency = new SqlDependency(sqlCommand);
                sqlDependency.OnChange += OnSqlDependencyChange;
                if (sqlConnection.State == ConnectionState.Closed)
                    sqlConnection.Open();
                sqlCommand.ExecuteNonQuery();
            }
    }
        
    void OnSqlDependencyChange(object sender, SqlNotificationEventArgs e)
    {
        if (SqlNotification != null)
            SqlNotification(sender, e);
        RegisterForNotifications();
    }
}
 
public delegate void SqlNotificationEventHandler(object sender, SqlNotificationEventArgs e);

And to finish the SqlDependency, you only need to change the Global.asax.cs:

public class MvcApplication : System.Web.HttpApplication
{
    protected String SqlConnectionString { get; set; }
 
    protected void Application_Start()
    {
        using (var context = new SignaRTestContext())
            SqlConnectionString = context.Database.Connection.ConnectionString;
        
       ...
 
        if (!String.IsNullOrEmpty(SqlConnectionString))
            SqlDependency.Start(SqlConnectionString);
    }
 
    protected void Application_End()
    {
        if (!String.IsNullOrEmpty(SqlConnectionString))
            SqlDependency.Start(SqlConnectionString);
    }
}

Rebuild the solution, don't need to debug because we can't see anything yet.

SignalR

Now it's time to implement SignalR (you can read about it in here).

Open Startup.cs file and change:

using Owin;
using Microsoft.Owin;
 
[assembly OwinStartup(typeof(SignalRWebApp.Startup))]
namespace SignalRWebApp
{
    public class Startup
    {
        public void Configuration( IAppBuilder app)
        {
            app.MapSignalR();
        }
    }
}

Create a folder Hubs and inside a class ProductHub.csfrom the template SignalR Hub Class (v2) and change:

public class ProductHub : Hub
{
    internal NotifierEntity NotifierEntity{ get; private set; }
 
    public void DispatchToClient()
    {
        Clients.All.broadcastMessage("Refresh");
    }
 
    public void Initialize(String value)
    {
        NotifierEntity = NotifierEntity.FromJson(value);
        if (NotifierEntity == null)
            return;
        Action<String> dispatcher = (t) => { DispatchToClient(); };
        PushSqlDependency.Instance(NotifierEntity, dispatcher);
    }
}

Open ProductsController.cs:

public async Task<ActionResult> Index()
{
    return View(await db.Products.ToListAsync());
}

change to:

public async Task<ActionResult> Index()
{
    var collection = db.Products;
    ViewBag.NotifierEntity = db.GetNotifierEntityk<Product>(collection).ToJson();
    return View(await collection.ToListAsync());
}

add another Action to only render the table:

public async Task<ActionResult> IndexPartial()
{
	var collection = db.Products;
        ViewBag.NotifierEntity = db.GetNotifierEntity<Product>(collection).ToJson();
        return PartialView(await collection.ToListAsync());
}

From this last Action, create a PartialView and edit this last one and the Index view:

Copy @model IEnumerable<SignalRWebApp.Models.Product> and paste on IndexPartial view
Add @Html.Hidden("NotifierEntity", (object)ViewBag.NotifierEntity) on IndexPartial view.
Cut the table and paste on IndexPartial view.
Add DIV on Index view.

<div id="tbProducts">
    @Html.Partial("IndexPartial", Model)
</div>

Add @section script on Index view.

@section scripts {

    <script src="~/Scripts/jquery.signalR-2.2.0.min.js"></script>
    <script src="~/signalr/hubs"></script>

    <script type="text/javascript">
        var signalRHubInitialized = false;

        $(function () {
            InitializeSignalRHubStore();
        });

        function InitializeSignalRHubStore() {

            if (signalRHubInitialized)
                return;

            try {
                var clientHub = $.connection.productHub;

                clientHub.client.broadcastMessage = function (message) {
                    if (message === "Refresh")
                        ReloadIndexPartial();
                };

                $.connection.hub.start().done(function () {
                    clientHub.server.initialize($("#NotifierEntity").val());
                    signalRHubInitialized = true;
                });

            } catch (err) {
                signalRHubInitialized = false;
            }
        };

        function ReloadIndexPartial() {

            $.post('@(Url.Action("IndexPartial", 
            "Products", null, Request.Url.Scheme))')
                .done(function (response) {
                    $("#tbProducts").html(response)
                    if (!signalRHubInitialized)
                        InitializeSignalRHubStore();
                });
        };
    </script>
}

Rebuild the solution and debug.

Make changes on SQL Table directly and the changes will be reflected to the Index view.

I hope this tip can help you and of course, you can improve this.

One more thing: because we are sending sensitive information to Hidden field, it´s better to encrypt/decrypt Json NotifierEntity.

That's all, and thanks for your patience.