In this post, you will learn how to display real time updates from the SQL Server by using SignalR and SQL Dependency in ASP.NET MVC.
The following are the steps that we need to enable in the SQL Server first.
Step 1- Enable Service Broker on the database
The following is the query that need to enable the service broker
ALTER DATABASE BlogDemos SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE ;
Step 2 - Add Connection string to the Web.Config file
<add name=”DefaultConnection” connectionString=”Server=servername;Database=databasename;User Id=userid;Password=password;” providerName=”System.Data.SqlClient” />
Step 3 - Enable SQL Dependency
In Global.asax start the SQL Dependency in App_Start() event and Stop SQL dependency in the Application_End() event
public class MvcApplication : System.Web.HttpApplication
{
string connString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
protected void Application_Start()
{
AreaRegistration.RegisterAllAreas();
FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
RouteConfig.RegisterRoutes(RouteTable.Routes);
BundleConfig.RegisterBundles(BundleTable.Bundles);
GlobalConfiguration.Configure(WebApiConfig.Register);
//Start SqlDependency with application initialization
SqlDependency.Start(connString);
}
protected void Application_End()
{
//Stop SQL dependency
SqlDependency.Stop(connString);
}
}
Step 4 - Install SignalR from the nuget
Run the following command in the Package Manager Console:
Install-Package Microsoft.AspNet.SignalR
Step 5 - Create SignalR Hub Class
Create MessagesHub class in the Hubs folder:
public class MessagesHub : Hub
{
private static string conString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
public void Hello()
{
Clients.All.hello();
}
[HubMethodName("sendMessages")]
public static void SendMessages()
{
IHubContext context = GlobalHost.ConnectionManager.GetHubContext<MessagesHub>();
context.Clients.All.updateMessages();
}
}
Step 6 - Get the Data from the Repository
Create MessagesRepository to get the messages from the database when data is updated.
public class MessagesRepository
{
readonly string _connString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
public IEnumerable<Messages> GetAllMessages()
{
var messages = new List<Messages>();
using (var connection = new SqlConnection(_connString))
{
connection.Open();
using (var command = new SqlCommand(@"SELECT [MessageID], [Message], [EmptyMessage], [Date] FROM [dbo].[Messages]", 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())
{
messages.Add(item: new Messages { MessageID = (int)reader["MessageID"], Message = (string)reader["Message"], EmptyMessage = reader["EmptyMessage"] != DBNull.Value ? (string) reader["EmptyMessage"] : "", MessageDate = Convert.ToDateTime(reader["Date"]) });
}
}
}
return messages;
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
if (e.Type == SqlNotificationType.Change)
{
MessagesHub.SendMessages();
}
}
}
Step 7 - Register SignalR at startup class
Add the following code:
app.MapSignalR();
Step 8 - View Page
Create div messagesTable that will append the table data from the database:
<div class="row">
<div class="col-md-12">
<div id="messagesTable"></div>
</div>
</div>
Now Add the SignalR related scripts in the page:
<script src="/Scripts/jquery.signalR-2.1.1.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 notifications = $.connection.messagesHub;
//debugger;
// Create a function that the hub can call to broadcast messages.
notifications.client.updateMessages = function () {
getAllMessages()
};
// Start the connection.
$.connection.hub.start().done(function () {
alert("connection started")
getAllMessages();
}).fail(function (e) {
alert(e);
});
});
function getAllMessages()
{
var tbl = $('#messagesTable');
$.ajax({
url: '/home/GetMessages',
contentType: 'application/html ; charset:utf-8',
type: 'GET',
dataType: 'html'
}).success(function (result) {
tbl.empty().append(result);
}).error(function () {
});
}
</script>
Step 9 - Create Partial View Page
Create a partial view _MessagesList.cshtml that returns all the messages.
@model IEnumerable<SignalRDbUpdates.Models.Messages>
<p>
@Html.ActionLink("Create New", "Create")
</p>
<table class="table">
<tr>
<th>@Html.DisplayNameFor(model => model.MessageID)</th>
<th>
@Html.DisplayNameFor(model => model.Message)
</th>
<th>
@Html.DisplayNameFor(model => model.EmptyMessage)
</th>
<th>
@Html.DisplayNameFor(model => model.MessageDate)
</th>
</tr>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.MessageID)
</td>
<td>
@Html.DisplayFor(modelItem => item.Message)
</td>
<th>
@Html.DisplayFor(modelItem => item.EmptyMessage)
</th>
<td>
@Html.DisplayFor(modelItem => item.MessageDate)
</td>
</tr>
}
</table>
Step 10 - Set Up the Database
Create the database called blogdemos and run the following script:
USE [BlogDemos]
GO
/****** Object: Table [dbo].[Messages] Script Date: 10/16/2014 12:43:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Messages](
[MessageID] [int] IDENTITY(1,1) NOT NULL,
[Message] [nvarchar](50) NULL,
[EmptyMessage] [nvarchar](50) NULL,
[Date] [datetime] NULL,
CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED
(
[MessageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Messages] ADD CONSTRAINT [DF_Messages_Date] DEFAULT (getdate()) FOR [Date]
GO
Step 11 - Run the project
Whenever data is inserted into the table the dependency_OnChange method will fire.
HostForLIFE.eu ASP.NET MVC 6 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.
