Click here to Skip to main content
16,020,714 members
Articles / Web Development / HTML
Tip/Trick

ASP.NET MVC 5 SignalR, SqlDependency and EntityFramework 6

Rate me:
Please Sign up or sign in to vote.
4.97/5 (15 votes)
1 Feb 2016CPOL3 min read 122.5K   3.9K   32   42
ASP.NET MVC 5 SignalR, SqlDependency and EntityFramework 6

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:

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:

SQL
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.

C#
[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></product>

Add your connection string to the web.config file:

XML
<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 because Entity 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.

C#
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);
    }
}
C#
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
C#
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:

C#
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:

C#
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.cs from the template SignalR Hub Class (v2) and change:

C#
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:

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

change to:

C#
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:

C#
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.

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

Add @section script on Index view.

HTML
@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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Self employed
Portugal Portugal
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionUpon database table changes, no notification is sent Pin
Jaime Stuardo - Chile26-Oct-19 17:29
Jaime Stuardo - Chile26-Oct-19 17:29 
BugStart or Stop Pin
acar@o2.pl27-Jun-19 23:19
acar@o2.pl27-Jun-19 23:19 
Questionperfect Pin
Serdar Şengül10-Mar-19 10:16
Serdar Şengül10-Mar-19 10:16 
QuestionSignaRTestContext Pin
S.I. Herrera Payano28-Sep-18 4:07
S.I. Herrera Payano28-Sep-18 4:07 
QuestionGetting the error "The SqlParameter is already contained by another SqlParameterCollection" Pin
Member 1378950022-Apr-18 23:33
Member 1378950022-Apr-18 23:33 
QuestionGetting an infinite loop on the OnSqlDependencyChange Pin
AllPhee5-Apr-18 23:27
AllPhee5-Apr-18 23:27 
Hi Luis,

Great article, and I must applaud you for itThumbs Up | :thumbsup: .

My case is that I implemented your solution with EF database first and I have 2 separate connection strings since EF DB first and SqlDependency expect varied formats of the connection strings, I am also using a sql view. So now my problem is that the application fires the
OnSqlDependencyChange
infinitely and in turn reloads the
IndexPartial
infinitely even if the database table contents have not changed and no action is taken. the subsequent
SqlNotification
Type is Subscribe. I am not sure what I am missing. Please help

Thanks a lot in advance.
Alpheus
allphpro


modified 6-Apr-18 6:20am.

AnswerRe: Getting an infinite loop on the OnSqlDependencyChange Pin
AllPhee6-Apr-18 0:18
AllPhee6-Apr-18 0:18 
PraiseLuis Manuel Prospero Mano Batista - You Best!! Thanks for sharing Pin
Shaybakov23-Mar-18 18:59
Shaybakov23-Mar-18 18:59 
QuestionPointing to an external dB Pin
Member 1368778923-Mar-18 4:59
Member 1368778923-Mar-18 4:59 
QuestionNotifierEntity In Hidden Pin
Member 982700920-Jan-18 10:59
Member 982700920-Jan-18 10:59 
QuestionI get a error GetNotifierEntity no acceting argument Pin
Member 1260857519-Jan-18 2:55
Member 1260857519-Jan-18 2:55 
GeneralMy vote of 5 Pin
kfedorov15-Jan-18 4:08
kfedorov15-Jan-18 4:08 
QuestionAn issue in my trying Pin
Member 1327856625-Jun-17 22:33
Member 1327856625-Jun-17 22:33 
PraiseExcellent Job Pin
prams_hi9-Mar-17 4:31
prams_hi9-Mar-17 4:31 
QuestionWhere Clause Pin
Analyst920-Dec-16 9:30
Analyst920-Dec-16 9:30 
QuestionCollection with criteria Pin
Member 127665111-Nov-16 1:34
Member 127665111-Nov-16 1:34 
AnswerRe: Collection with criteria Pin
Luis Manuel Prospero Mano Batista2-Nov-16 11:57
professionalLuis Manuel Prospero Mano Batista2-Nov-16 11:57 
GeneralRe: Collection with criteria Pin
Member 125911665-Nov-16 11:28
Member 125911665-Nov-16 11:28 
GeneralRe: Collection with criteria Pin
Member 1276651117-Nov-16 0:12
Member 1276651117-Nov-16 0:12 
GeneralRe: Collection with criteria Pin
Luis Manuel Prospero Mano Batista17-Nov-16 0:27
professionalLuis Manuel Prospero Mano Batista17-Nov-16 0:27 
GeneralRe: Collection with criteria Pin
Member 1276651118-Nov-16 1:23
Member 1276651118-Nov-16 1:23 
GeneralRe: Collection with criteria Pin
Luis Manuel Prospero Mano Batista18-Nov-16 6:41
professionalLuis Manuel Prospero Mano Batista18-Nov-16 6:41 
GeneralRe: Collection with criteria Pin
koguto25-Nov-16 4:12
koguto25-Nov-16 4:12 
GeneralRe: Collection with criteria Pin
Luis Manuel Prospero Mano Batista25-Nov-16 6:17
professionalLuis Manuel Prospero Mano Batista25-Nov-16 6:17 
GeneralRe: Collection with criteria Pin
koguto26-Nov-16 11:15
koguto26-Nov-16 11:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.