none
Unable to get SQLDependency to work as expected RRS feed

  • Question

  • Hi Folks,

      I have a simple console application (C#) for which I am trying to set up a notification system using SQLDependency.

      This issue is as soon as I call Start() in my client, a notification is issued with state 'invalid'

      In SQL Server (2016) I have enabled the broker and created a queue called BobsQueue and a service on the queue called BobsNotifications,  and I have granted query notifications to user Bob.

    I have a table called  BobsTable with three colums, a,b & c  (a is a PK, Identity).  And my select statement is  SELECT [a],[b],[c] FROM [dbo].[BobsTable].

    In my console application (C#)

    • I create a sql connection and a sql command.
    • I set SqlCmd.notification = null to clean existing notifications.
    • Create a new SqlDependance( sqlCmd, options: "Service=BobsNotifications", timeout: 120);
    • Add a OnChange handler  (OnBobsDataChanged)
    • Open the connection
    • Execute my query to fill a data table
    • Call SqlDependency.Start(connectionString, "BobsQueue)

    As soon as I call the start, I can see on my Sql Server two converstation endpoings with the status 'Conversing'.

    The 'OnBobsDataChange' immediately fires, but e.Info = Invalid, e.Source = Statement, e.Type = Subscribe.   I understand that error to mean "The command submitted contained a statement that does not support notifications"

    My sqlcmd just contains  SELECT [a],[b],[c] FROM [dbo].[BobsTable].  The only other parameter I have passed into it is the connection string.

    Has anyone any ideas on how to debug this?

    Thursday, March 14, 2019 2:09 AM

Answers

  • Just as an update I did solve this issue.

       The changes I made were.

    In my application startup routine.   

    1. Ensure I have a valid SqlClientPermission
    2. Call SqlDependency.Stop()
    3. Call SqlDependency.Start() with a connection string, but without a queue parameter
    4. Call my register routine.

    In the register routine

    1. Create a SqlConnection
    2. Create  SqlCommand  passing in my select statement and connection, set the commandType to text
    3. Open the connection if it is closed
    4. Create a SqlDependency passing in only the sqlCommand and not an options.
    5. Add the change handler
    6. Create a data set using ds.Load(sqlCmd.ExecuteReader(CommandBehaviour.Close),...

    In my change handler

    1. get the SqlNotificationInfo/SOurce/Type out of teh SqlNotificationEventArgs param
    2. Case Sender to a SqlDependency
    3. Subtract the change handler
    4. Check for hasChanges  (update or insert) - call appropriate handler routine.
    5. Call register routine
    Finally, I needed to change my query to use a two part db name.   Select [a],[b],[c] from [dbo].[BobsTable]
    • Marked as answer by AndyW2007 Thursday, March 14, 2019 3:26 AM
    • Edited by AndyW2007 Thursday, March 14, 2019 3:57 AM
    Thursday, March 14, 2019 3:26 AM