Database Change Notifications in ASP.NET using SignalR and SqlDependency

To display real time updates from the SQL Server by using SignalR and SQL Dependency I've done these steps:

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: Enable SQL Dependency

//Start SqlDependency with application initialization
 SqlDependency.Start(connString);

Step 3: Create the hub Class

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 4: 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 5: Register SignalR at startup class

app.MapSignalR();

Step 6: then use the method to show real time at your view

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

Hope this helps :)

You should provide your code, it's easier to figure out what the problem is that way.

From what you have mentioned, I can think of only two things.

1) The tutorial you used, it's using SignalR 1.0. If you are using SignalR 2.0, you should not follow the tutorial exactly. A few things changed in SignalR 2.0, you can read about it using below link:

http://www.asp.net/signalr/overview/signalr-20/getting-started-with-signalr-20/upgrading-signalr-1x-projects-to-20

2) If you are using SignalR 1.0, make sure you include the "jquery" file correctly, and if it's correct, try to change the hub proxy path like below:

  <script src="/signalr/Hubs"></script>

Hope it helps