locked
Database Notifications using SignalR RRS feed

  • Question

  • User-2027411970 posted

    10 Points

    58 Posts

    Database Notifications using SignalR

    16 Jul 2016 04:25 PM|LINK

    Hello,

    I have gone through internet and find a solution to implement Database Notifications using SignalR.

    The project was designed by using an example and it runs perfectly without any error:

    But I found a sticky matter in this code: Below is my code to get records from DB.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;

    namespace PISignalR
    {
        public class WorkRegisterRepository
        {
            readonly string _connString = ConfigurationManager.ConnectionStrings["PIDB"].ConnectionString;
            public IEnumerable<WorkRegister> GetWorkRegister()
            {
                var receipts = new List<WorkRegister>();
                using (var connection = new SqlConnection(_connString))
                {
                    connection.Open();
                    using (var command = new SqlCommand(
                                                        @"

    SELECT a.[column1], b.[column2]

    FROM [dbo].[TABLE1]

    INNER JOIN [dbo].[TABLE2]

    ", connection
                                                        )
                           )
                    {
                        command.Notification = null;
                        //var dependency = new SqlDependency(command, "Service=PINotificationService;local database=PI_DATA", int.MaxValue);
                        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())
                        {
                            receipts.Add(item: new WorkRegister
                            {
                                Department = Convert.ToString(reader["Department"]),
                                Name = Convert.ToString(reader["Name"]),
                                Operation = Convert.ToString(reader["Operation"]),
                                Date = Convert.ToString(reader["Date"]),
                                ConsOpt = Convert.ToDouble(reader["ConsOpt"]),
                                StdOpt = Convert.ToDouble(reader["StdOpt"]),
                                WrkDonePD=Convert.ToDouble(reader["WrkDonePD"])
                             });
                        }
                    }
                }
                return (receipts);
            }
            private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
            {
                SqlNotificationInfo _NotificationInfo = new SqlNotificationInfo();
                if (e.Type == SqlNotificationType.Change)
                {
                    _NotificationInfo = e.Info;
                    WorkRegisterHub.SendWorkRegister();
                }
               //WorkRegisterHub.SendWorkRegister();
            }
        }
    }

    The error/issue comes into the picture when I debug my code and see that NotificationInfo Returns Subscribe Status and technically DAtabase Notification didnt work at all.

    I have tried by changing my Connection string with all possible changes. like using Sa and other authenticated users to connect to the database.

    Please help me to sort out this issue.

    Br,

    Bhavin.

    Monday, July 18, 2016 3:24 AM

All replies

  • User61956409 posted

    Hi Bhavin,

    SqlDependency allows us to receive notifications when the original data in the database changes. Do you check the database to make sure if any data are changed or updated? And please debug the code to check if dependency_OnChange could fire.

    Besides, the following tutorial with complete sample and steps to use SignalR and SqlDependency to broadcast updates notification, you could follow the tutorial to create new application to test if the sample could work fine on your side.

    http://www.c-sharpcorner.com/UploadFile/b1df45/real-time-data-update-using-signalr/

    http://verrigo.blogspot.sg/2015/01/automatic-updates-from-sql-server-using.html

    Best Reagrds,

    Fei Han

    Tuesday, July 19, 2016 5:59 AM
  • User-2027411970 posted

    Fie Han,

    My Table work_register is continuously getting records from user end. We want to monitor routine entries by a web page using Signal R.

    The examples runs without error.

    When I debug the code, SqlNotificationInfo gets Subscribe status and Status is Invalid.

    what should I do?

    Br,

    Bhavin.

    Tuesday, July 19, 2016 6:56 AM
  • User61956409 posted

    Hi Bhavin,

    As I mentioned in my previous reply, do you check if record changes in your database when you update/insert records into database via your application?  And please compare steps of implement data updates notification of the tutorials with yours to find if any difference with your doing.

    Best Regards,

    Fei Han

    Friday, July 22, 2016 8:13 AM
  • User-2027411970 posted

    Fei,

    The issue was resolved. I have used an inbuilt function in my query, I removed it and it runs.

    Now my next question is regarding ajax call.

    Here is my home view js code.

    <script type="text/javascript">
        $(function () {
            // Declare a proxy to reference the hub.
            //var notifications = $.connection.usersHub;
            var notifications = $.connection.wrtestHub;

            //debugger;
            // Create a function that the hub can call to broadcast messages.
            notifications.client.updateWR = 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/GetWR',
                contentType: 'application/html ; charset:utf-8',
                type: 'GET',
                dataType: 'html'
            }).done(function (result) {
                tbl.empty().append(result);

            }).fail(function () {

            });
        }
    </script>

    The above code simply empties the table and refill it when a notification arises.

    I think its not a good approach.

    Whenever a database notification generated, only updated or newly inserted values should be inserted in existing table.

    Anyone can guide how to do it with current scenario?

    Br,

    Bhavin.

    Friday, July 22, 2016 8:43 AM
  • User61956409 posted

    Hi Bhavin,

    As you did, we could call web method to fetch records and update client UI when client-side function updateWR fire. If you check the returned value “result”, you will find the older records displayed in “messagesTable” is contained in result, so you need to empty messagesTable before you append new records to it. if you do not want this, you could try to modify the function updateWR to make it accepts an argument, and you could pass updated records information as parameter to function updateWR on hub side.

    context.Clients.All.updateWR("your message"); 

    Best Regards,

    Fei Han

    Monday, July 25, 2016 7:21 AM
  • User-2027411970 posted

    Fei,

    I am thinking on the same way logic. But I dont know exactly how to achieve,

    Basically, I need to implement for Updated, Deleted or Inserted Records. In such cases I need to check the result which loaded on the first time, then when sqldepenency fires an event, when a database us updated either by inserting, deleting or updating record(s). with the updated result.

    is there any way with jQuery or something like libraries to achieve this?

    Br,

    Bhavn.

    Wednesday, July 27, 2016 10:48 AM