CODEWITHSUNDEEP
c#sqlsql-server
antoinestv
antoinestv

Reputation: 3306

SqlDependency fires immediately

I want to use SqlDependency to get notifications when some datas are changed by others applications using the database.

public class DatabaseChangesNotification : IDisposable
{
    private static string chaineDeConnexion = ConfigurationManager.ConnectionStrings["TransfertContext"].ConnectionString;

    private static readonly Lazy<DatabaseChangesNotification> _instance = new Lazy<DatabaseChangesNotification>(() => new DatabaseChangesNotification());

    private DatabaseChangesNotification()
    {
        System.Diagnostics.Trace.WriteLine("--- SqlDependency START ---");
        SqlDependency.Start(chaineDeConnexion);
    }

    public void Dispose()
    {
        System.Diagnostics.Trace.WriteLine("--- SqlDependency STOP ---");
        SqlDependency.Stop(chaineDeConnexion);
    }

    public static DatabaseChangesNotification Instance
    {
        get 
        {
            return _instance.Value; 
        }
    }

    public void AbonnerNotification(string requete, OnChangeEventHandler eventhandler)
    {
        using (SqlConnection connection = new SqlConnection(chaineDeConnexion))
        {
            using (SqlCommand command = new SqlCommand(requete, connection) { Notification = null }) // clear existing notifications
            {
                connection.Open();

                var sqlDependency = new SqlDependency(command);

                OnChangeEventHandler delegateAutoRemove = null;
                delegateAutoRemove = (sender, e) => {
                    var dependency = sender as SqlDependency;
                    dependency.OnChange -= delegateAutoRemove;
                    eventhandler(sender, e); 
                };

                sqlDependency.OnChange += delegateAutoRemove;

                command.ExecuteNonQuery();
            }
        }
    }
}

So, with a single line i can register an event handler :

DatabaseChangesNotification.Instance.AbonnerNotification(@"SELECT IdUtilisateur, Code, Nom, Prenom, NomComplet, Login, Synchroniser FROM dbo.Utilisateur", OnChanges);  

    public void OnChanges(object sender, SqlNotificationEventArgs e){
        System.Diagnostics.Trace.WriteLine("------------------------------ UPDATTEEEE -------------------------");
        System.Diagnostics.Trace.WriteLine("Info:   " + e.Info.ToString());
        System.Diagnostics.Trace.WriteLine("Source: " + e.Source.ToString());
        System.Diagnostics.Trace.WriteLine("Type:   " + e.Type.ToString());

        GlobalHost.ConnectionManager.GetHubContext<TransfertClientHub>().Clients.All.hello("users modified !");
        //AbonnementChanges();
    }

But my problem is that the notification is immediatly fired :

--- ABONNEMENT ---
------------------------------ UPDATTEEEE -------------------------
Info:   Query
Source: Statement
Type:   Subscribe

That's why I commented AbonnementChanges in my event handler OnChanges (or it will loop infinitely).

I don't know where the problem comes from because I reset the notifications ({ Notification = null }) and my request respect the requirements (https://msdn.microsoft.com/en-us/library/ms181122.aspx).

Edit : I want to add that select * from sys.dm_qn_subscriptions returns nothing.

Edit : It looks like it comes from database configuration, and not from my implemention, as i tried another implemention which result in the same behaviour : http://www.codeproject.com/Articles/144344/Query-Notification-using-SqlDependency-and-SqlCach

Edit : I don't see where it comes from since i use SA which is sysadmin and have all rights, isn't it ?

Edit : I tried to define another connection to the database following this tutorial : http://www.codeproject.com/Articles/12862/Minimum-Database-Permissions-Required-for-SqlDepen

So i created 2 roles :

EXEC sp_addrole 'sql_dependency_subscriber' 
EXEC sp_addrole 'sql_dependency_starter' 

-- Permissions needed for [sql_dependency_starter]
GRANT CREATE PROCEDURE to [sql_dependency_starter] 
GRANT CREATE QUEUE to [sql_dependency_starter]
GRANT CREATE SERVICE to [sql_dependency_starter]
GRANT REFERENCES on 
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
  to [sql_dependency_starter] 
GRANT VIEW DEFINITION TO [sql_dependency_starter] 

-- Permissions needed for [sql_dependency_subscriber] 
GRANT SELECT to [sql_dependency_subscriber] 
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber] 
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_subscriber] 
GRANT REFERENCES on 
CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
  to [sql_dependency_subscriber]

and then i added the user (production) to this roles :

-- Making sure that my users are member of the correct role.

EXEC sp_addrolemember 'sql_dependency_starter', 'production'
EXEC sp_addrolemember 'sql_dependency_subscriber', 'production'

But with this connection i have the same behaviour than before. Notification are fired imediatly :

------------------------------ UPDATTEEEE -------------------------
Info:   Query
Source: Statement
Type:   Subscribe

Edit : I tried with simpler requests like : SELECT Nom, Prenom FROM dbo.Utilisateur. Here are the details of the table which should be inspected :

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Utilisateur](
    [IdUtilisateur] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_Utilisateur_IdUtilisateur]  DEFAULT (newid()),
    [Code] [varchar](10) NOT NULL,
    [Nom] [varchar](100) NOT NULL,
    [Prenom] [varchar](100) NULL,
    [NomComplet]  AS (([Prenom]+' ')+[Nom]),
    [Login] [varchar](50) NULL,
    [Synchroniser] [bit] NOT NULL CONSTRAINT [DF_Utilisateur_Synchroniser]  DEFAULT ((1)),
    [DATE_CREATION] [datetime] NOT NULL CONSTRAINT [DF__Utilisate__DATE___2AA1E7C7]  DEFAULT (getdate()),
    [DATE_DERNIERE_MODIF] [datetime] NOT NULL CONSTRAINT [DF__Utilisate__DATE___2B960C00]  DEFAULT (getdate()),
    [Desactive] [bit] NOT NULL CONSTRAINT [DF_Utilisateur_Desactive]  DEFAULT ((0)),
 CONSTRAINT [PK_Utilisateur] PRIMARY KEY CLUSTERED 
(
    [IdUtilisateur] 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

SET ANSI_PADDING OFF
GO

As we can see there are some columns which can't be requested. That's why i don't use it.

Now let's check with SELECT Nom, Prenom FROM dbo.Utilisateur :

But that still doesn't works ... =(

Final edit - Solution : As Jon Tirjan said, it was caused by my computed column NomComplet which is not valid with the Service Broker (even when I don't ask to be notified on changes on this column, which is strange to me).

Upvotes: 9

Views: 1941

Answers (2)

dyatchenko
dyatchenko

Reputation: 2343

Thanks George Stocker for deleting my previous answer, but I had a serious issue with SqlDependency and I insist:

Be careful using SqlDependency class - it has the problems with memory leaks.

For my project I've used open source realization - SqlDependencyEx. It uses a database trigger and native Service Broker notification to receive events about the table changes. This is an usage example:

int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
          TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME)) 
{
    sqlDependency.TableChanged += (o, e) => changesReceived++;
    sqlDependency.Start();

    // Make table changes.
    MakeTableInsertDeleteChanges(changesCount);

    // Wait a little bit to receive all changes.
    Thread.Sleep(1000);
}

Assert.AreEqual(changesCount, changesReceived);

With SqlDependecyEx you are able to monitor INSERT, DELETE, UPDATE separately and receive actual changed data (xml) in the event args object. Hope this help.

Upvotes: 0

Jon Tirjan
Jon Tirjan

Reputation: 3694

Service Broker doesn't work on tables with computed columns. You need to remove NomComplet from your table, or change it to an actual column which is populated another way (trigger, stored procedure, etc.)

The notification is being fired immediately because an error occurs while setting up the queue.

Upvotes: 5

Related Questions