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:
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></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 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.
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;
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.cs from 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.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.