Click here to Skip to main content
13,664,842 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

84.8K views
29 bookmarked
Posted 9 Feb 2015
Licenced CPOL

SignalR Database Update Notifications in ASP.NET MVC using SQL Dependency

, 9 Feb 2015
Rate this:
Please Sign up or sign in to vote.
SignalR Database update notifications in ASP.NET MVC using SQL Dependency

In this post, we 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 needs 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.

getAllMessages is a function that return the partialview data and bind it into the messagesTable div.
<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

When eve data is inserted into the table, the dependency_OnChange method will fire.

You can download the source from Github.

The post SignalR Database update notifications in ASP.NET MVC using SQL Dependency appeared first on Venkat Baggu Blog.

License

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

Share

About the Author

madan535
Software Developer (Senior) eBiz Solutions http://venkatbaggu.com/
India India
No Biography provided

You may also be interested in...

Pro
Pro

Comments and Discussions

 
Questioninserted one record in db then its not displaying updated records Pin
Member 1193366017-Jun-18 1:36
memberMember 1193366017-Jun-18 1:36 
Questionnot working in real time Pin
Member 1258366224-Feb-17 0:18
memberMember 1258366224-Feb-17 0:18 
AnswerRe: not working in real time Pin
Brian Briggs15-Feb-18 3:11
memberBrian Briggs15-Feb-18 3:11 
QuestionThanks Madan this document was really helpful. Pin
NirajRamShrestha4-Mar-16 7:22
memberNirajRamShrestha4-Mar-16 7:22 
QuestionCommand with where clause Pin
bui nam1-Mar-16 21:24
memberbui nam1-Mar-16 21:24 
Questioni'm update a row in database but still not any update in browser.... Pin
Nayeem Mansoori17-Jan-16 22:48
memberNayeem Mansoori17-Jan-16 22:48 
AnswerRe: i'm update a row in database but still not any update in browser.... Pin
Tejas Patel2-Feb-16 9:05
memberTejas Patel2-Feb-16 9:05 
QuestionI can't add another Field in the list To display. Pin
Member 121652606-Dec-15 22:01
memberMember 121652606-Dec-15 22:01 
QuestionWebform implementation Pin
Member 1199013928-Oct-15 15:39
memberMember 1199013928-Oct-15 15:39 
BugSignalR client send multiple requests!! Pin
gayancc10-Jun-15 1:16
membergayancc10-Jun-15 1:16 
GeneralRe: SignalR client send multiple requests!! Pin
Rodrigo Fonseca de Campos27-Nov-15 0:31
memberRodrigo Fonseca de Campos27-Nov-15 0:31 
GeneralRe: SignalR client send multiple requests!! Pin
Rodrigo Fonseca de Campos27-Nov-15 7:06
memberRodrigo Fonseca de Campos27-Nov-15 7:06 
GeneralRe: SignalR client send multiple requests!! Pin
roxcon29-May-16 20:32
memberroxcon29-May-16 20:32 
GeneralRe: SignalR client send multiple requests!! Pin
Member 1193366017-Jun-18 2:06
memberMember 1193366017-Jun-18 2:06 
Questionnotification does not show Pin
aakash.jalodkar4-May-15 21:55
professionalaakash.jalodkar4-May-15 21:55 
AnswerRe: notification does not show Pin
madan5354-May-15 22:59
membermadan5354-May-15 22:59 
GeneralRe: notification does not show Pin
aakash.jalodkar11-May-15 18:55
professionalaakash.jalodkar11-May-15 18:55 
GeneralRe: notification does not show Pin
BO55 VXR25-Nov-15 22:04
memberBO55 VXR25-Nov-15 22:04 
QuestionNotification does not happen Pin
sahookris14-Apr-15 3:25
membersahookris14-Apr-15 3:25 
AnswerRe: Notification does not happen Pin
Tejas Patel2-Feb-16 9:02
memberTejas Patel2-Feb-16 9:02 
GeneralRe: Notification does not happen Pin
Member 1193366017-Jun-18 1:35
memberMember 1193366017-Jun-18 1:35 
QuestionQuestion.. Pin
deo cabral13-Apr-15 17:23
professionaldeo cabral13-Apr-15 17:23 
QuestionQuery On Table Name Pin
Member 1156569130-Mar-15 0:00
memberMember 1156569130-Mar-15 0:00 

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.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01-2016 | 2.8.180810.1 | Last Updated 9 Feb 2015
Article Copyright 2015 by madan535
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid