none
SqlDependency.OnChange is not fired RRS feed

  • Question

  • Hi, 

    I am trying to be notified when some data changes happened in table. So, I created simple test-application, but it does not work. I have studied other samples, MSDN documentation... nothing helped.

    When I call SqlDependency.Start(_ConString) I see that Service and Queue are created under Service Broker in MSSQL database (and they are deleted when I call SqlDependency.Stop(_ConString)).

    Users table is very simple - only ID primary key and FirstName, LastName columns. Broker is enabled in database. Database is local MSSQL Express, DB user is sa, connection is OK. Everything looks fine to me but no event is fired.

    Visual Studio 2017 Pro, MSSQL 2017 Express, Win10 Pro

    Here is my code:

            string _ConString;
    
            private void Form1_Load(object sender, EventArgs e)
            {
                _ConString = "Data Source=.;Initial Catalog=TEST;Persist Security Info=True;User ID=sa;Password=XXXX";
    
                SqlDependency.Stop(_ConString);
                SqlDependency.Start(_ConString);
    
                SqlConnection con = new SqlConnection(_ConString);
                SqlCommand cmd = new SqlCommand("SELECT FirstName, LastName FROM dbo.[Users]", con);
    
                SqlDependency dep = new SqlDependency(cmd);
                dep.OnChange += Dep_OnChange;
            }
    
            private void Dep_OnChange(object sender, SqlNotificationEventArgs e)
            {
                // never fired
            }
    
            private void Form1_FormClosed(object sender, FormClosedEventArgs e)
            {
                SqlDependency.Stop(_ConString);
            }

    Has anyone any idea why OnChange is not fired?

    Thank You

    Mirek Vanický


    Friday, January 12, 2018 2:33 PM

Answers

All replies

  • What i understand here is you need to register the events at start and then do other operation which will actually trigger the event, so if event is subscribed not before when it is actually expected to fire it will not work, you can change your code to be :

    SqlDependency dep = new SqlDependency(cmd); dep.OnChange += Dep_OnChange; // register events first

    // now call methods SqlDependency.Stop(_ConString); SqlDependency.Start(_ConString); SqlConnection con = new SqlConnection(_ConString); SqlCommand cmd = new SqlCommand("SELECT FirstName, LastName FROM dbo.[Users]", con);

    This should now work if your SqlDependency is handling the event firing correctly.



    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


    Blog | LinkedIn | Stack Overflow | Facebook
    profile for Ehsan Sajjad on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, January 12, 2018 4:46 PM
  • The samples also include a call of ExecuteReader: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/detecting-changes-with-sqldependency, probably without any reading. See if it helps.

    Also seems that OnChange happens a single time, but you can register another one inside OnChange.

    • Edited by Viorel_MVP Friday, January 12, 2018 7:28 PM
    • Marked as answer by Mirek Vanický Monday, January 15, 2018 9:34 AM
    Friday, January 12, 2018 7:14 PM
  • I have solved this issue with this steps:

    • recreate my database
    • enable broker on database (ALTER DATABASE [TEST] SET ENABLE_BROKER)
    • add to code: 
    con.Open();
    cmd.ExecuteReader().Dispose();
    con.Close();

    Call of ExecuteReader must be done! (Thank you  Viorel_)

    (see also https://stackoverflow.com/questions/43255472/why-sqldependency-onchange-is-not-being-fired/43255912#43255912)

    Monday, January 15, 2018 9:40 AM