Detects INSERT, UPDATE and DELETE records but not updating inserted records

[johnmikel]

Hi, let me just start with that this library is just amazing in what it can do, definitely complements well with SignalR in delivering a full-blast realtime experience.

Just a quick question though, I am trying to an update a record in my table and the OnChange is not detecting it. I know I am missing something as it seems to me that it works perfectly well. Can you give me an idea how to begin troubleshooting this issue?

Thanks a lot!

[christiandelbianco]

Yes, but i need to see your CODE otherwise i cannot help you

[johnmikel]

Which part would you like to check? As there are many components in my code but to give you a summary, I followed the pattern of the MVC + jQuery example (StockTicker) - it is as almost as similar to that implementation.

I kind of figured out what's happening. It works for some time (after deploying to the production server) and then it just stops working for some reason while time goes on. I'm running it on ASP.net v 4.7.2 on an IIS Server in Windows Server 2016.

Below is a sample of the code, from the realtime notifier that links itself to the realtime notifications hub.

        public NotificationsRealtimeNotifier()
        {

            var mapper = new ModelToTableMapper<Notification>();
            mapper.AddMapping(n => n.createdByUserId, "user_id");
            mapper.AddMapping(n => n.toNotifyUserId, "user_notify_id");
            mapper.AddMapping(n => n.description, "text");
            mapper.AddMapping(n => n.isSeen, "is_seen");


            _tableDependency = new SqlTableDependency<Notification>(_connectionString,
                "GLS_notification", "dbo",
                mapper);

            _tableDependency.OnChanged += SqlTableDependency_Changed;
            _tableDependency.OnStatusChanged += SqlTableDependency_OnStatusChanged;
            _tableDependency.OnError += SqlTableDependency_OnError;
            _tableDependency.Start();
        }

        public static IEnumerable<Notification> GetNewNotification(string userIdConnected, int userIdWithNewNotification)
        {
            // Determine if the current user is the one being notified
            if (userIdConnected == userIdWithNewNotification.ToString())
            {
                var notifications = new List<Notification>();

                using (var sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings
                        ["goodLawConnectionString1"].ConnectionString))
                {
                    sqlConnection.Open();
                    using (var sqlCommand = sqlConnection.CreateCommand())
                    {
                        sqlCommand.CommandText = $"SELECT TOP(7) * FROM GLS_notification WHERE user_notify_id='{userIdConnected}' AND insert_date >= {DateTime.Today.ToString("yyyy-MM-dd")} ORDER BY insert_date DESC";

                        using (var sqlDataReader = sqlCommand.ExecuteReader())
                        {
                            while (sqlDataReader.Read())
                            {
                                var id = sqlDataReader.GetInt32(sqlDataReader.GetOrdinal("id"));
                                var title = sqlDataReader.GetString(sqlDataReader.GetOrdinal("text"));
                                var URL = sqlDataReader.GetString(sqlDataReader.GetOrdinal("notification_url"));

                                //var type = sqlDataReader.GetInt32(sqlDataReader.GetOrdinal("notification_type")))

                                var typeId = sqlDataReader.GetInt32(sqlDataReader.GetOrdinal("notification_type"));
                                var description = _dbNotificationTypes.FetchNotificationTypeName(typeId);
                                var createdByUserId = sqlDataReader.GetInt32(sqlDataReader.GetOrdinal("user_id"));
                                var createdByUserInitials = _userHelper.GetUserEmployeeInitials(createdByUserId);
                                var isSeen = sqlDataReader.GetBoolean(sqlDataReader.GetOrdinal("is_seen"));
                                var insertDate = sqlDataReader.GetDateTime(sqlDataReader.GetOrdinal("insert_date"));

                                var notification = new Notification()
                                {
                                    id = id,
                                    title = title,
                                    typeId = typeId,
                                    description = $"{insertDate.ToString(GAUKConstants.GAUKDateFormat)} - {description}",
                                    URL = URL,
                                    createdByUserInitials = createdByUserInitials,
                                    isSeen = isSeen,
                                };

                                if (typeId == 1)
                                {
                                    notification.isFinished = sqlDataReader.GetBoolean(sqlDataReader.GetOrdinal("is_finished"));
                                }

                                notifications.Add(notification);
                            }
                        }
                    }
                }

                return notifications;
            }

            else
            {
                return null;
            }
        }

        void SqlTableDependency_OnStatusChanged(object sender, StatusChangedEventArgs e)
        {
            Console.WriteLine(e);
        }

        void SqlTableDependency_OnError(object sender, ErrorEventArgs e)
        {
            throw e.Error;
        }

        void SqlTableDependency_Changed(object sender, RecordChangedEventArgs<Notification> e)
        {
            if (e.ChangeType != ChangeType.None)
            {
                RealtimeNotificationsHub.GetLatestNotifications(e.Entity.toNotifyUserId);
            }
        }

[christiandelbianco]

Ok. If it stop working after a while, it is because the connection is disposed, because of IIS connections pool. Can you try to set Pooling=false in your connection string?

[johnmikel]

Sorry for the late reply, celebrated Christmas hahahah. I implemented this fix. Let me see if it will run for the whole day. Just to let you know, here are some of the other things I have done as well:

1.) Increased WatchDog timeout (to 12 hours)
2.) Changed the ThreadSafetyMode to Publication Only (instead of Execution and Publication)

I'mma test it for a couple of days, will let you know the feedback! Thank you very much!

[johnmikel]

I'm closing the issue! I found the mistake and it wasn't with your creation. It was how I implemented my SignalR connections! I'm so sorry for the trouble!

[christiandelbianco]

@johnmikel : if the error is something that can happens also to someone else, can you write how to fix it. In this way, if some other developer has same problem, he can find how to resolve it. Thanks

[johnmikel]

Let me see... but I just followed to the instruction everything that had been stated in the docs and in the examples provided, the only difference that I did was I added Pooling=false to the connection string and even myself, I really don't know what it does. I need to read more about what it actually does...