CODEWITHSUNDEEP
c#asp.netsql-serversignalr
jef
jef

Reputation: 103

SignalR and sql dependency only on insert

I use signalR and SQL Dependency for real time notification in asp.net mvc 5.2 I implement SignalR from this link 

http://venkatbaggu.com/signalr-database-update-notifications-asp-net-mvc-usiing-sql-dependency/

no I have one problem , in this scenario on any change on table , SQL dependency run and return record,I want just on insert in table SQL dependency fired and return record,

because in this table I update a bit filed for read notification on user read notification in panel, when I update the filed SQL dependency fired,

how I can defined SQL dependency just for insert in table?

thank you for your help

i use this code

 public IEnumerable<Messages> GetAllMessages()
        {
            var messages = new List<Messages>();
            using (var connection = new SqlConnection(_connString))
            {
                connection.Open();
                using (var command = new SqlCommand(@"SELECT [Id],[Player_Id], [Message] FROM [dbo].[Notification] WHERE [Player_Id] = " + PlayerLogin.userId + " AND PlayerIsRed = 0", 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 { Id = (int)reader["Id"], Message = (string)reader["Message"], Player_Id = (int)reader["Player_Id"] });
                    }
                }

            }
            return messages;
        }

Upvotes: 6

Views: 4039

Answers (2)

jaycer
jaycer

Reputation: 3111

You cannot directly limit the type of data changes that SqlDependency notifies about. The filter suggested by Sunil will work, but there is another work around to only getting notified of inserts: triggers.

You could put an insert trigger on the table you want notified about, and have the trigger insert a record on another table, such as PlayerNotification, and then point your SqlDependency query to PlayerNotification.

CREATE TRIGGER [dbo].[NotificationTrigger] ON [dbo].[Notification]
FOR INSERT
AS
BEGIN
    INSERT INTO 
        [dbo].[PlayerNotification] ( 
              [Id], [Player_Id], [Message] )
    SELECT DISTINCT 
          [Id], [Player_Id], [Message]
    FROM 
        Inserted i
END

Upvotes: 0

Sunil Chaudhary
Sunil Chaudhary

Reputation: 407

You can use your SqlDependency function something like that:-

     void sqlDep_OnChange(object sender, SqlNotificationEventArgs e)
            {
                if (e.Type == SqlNotificationType.Change && e.Info==SqlNotificationInfo.Insert)
                {
                    // your code
                }
            }

Upvotes: 3

Related Questions