CODEWITHSUNDEEP
c#winformsvisual-studio-2010sql-server-2008-r2
fdkgfosfskjdlsjdlkfsf
fdkgfosfskjdlsjdlkfsf

Reputation: 3303

SqlDependency.OnChange not firing in WinForm?

I used Detecting Changes with SqlDependency as example for the code that I'm writing. I've also looked at other links with similar code, but none of them work.

Essentially, I simply want to change label1.Text when a change has been made to table [ErrorLog]. For some reason, OnDependencyChange is not firing.

I've enabled Service Broker in the database: 

ALTER DATABASE TestDB 
SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

Now, here's my complete code. It's very short:

public partial class Form1 : Form
{
    private string GetConnectionString()
    {
        return @"Data Source=USER-PC\SQLEXPRESS;Initial Catalog=TestDB;Persist Security Info=True;User ID=TestUser;Password=12345;";
    }

    SqlConnection connection;

    public Form1()
    {
        InitializeComponent();

        connection = new SqlConnection(GetConnectionString());
        connection.Open();

        SqlDependency.Start(GetConnectionString());
        i = 0;
    }

    int i;

    void OnDependencyChange(object sender, SqlNotificationEventArgs e)
    {
        i++;
        label1.Text = "Changed: " + i.ToString();
        // Handle the event (for example, invalidate this cache entry).
    }

    void SomeMethod()
    {
        // Assume connection is an open SqlConnection.
        // Create a new SqlCommand object.
        using (SqlCommand command = 
            new SqlCommand("SELECT [ErrorLog].[ID],[ErrorLog].[Project],[ErrorLog].[Form],[ErrorLog].[Message],[ErrorLog].[Exception],[ErrorLog].[InsertDate] " + 
            "FROM [dbo].[ErrorLog]", connection))
        {
            // Create a dependency and associate it with the SqlCommand.
            SqlDependency dependency = new SqlDependency(command);

            // Maintain the reference in a class member.
            // Subscribe to the SqlDependency event.
            dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);

            // Execute the command.
            using (SqlDataReader reader = command.ExecuteReader())
            {
                // Process the DataReader.
            }
        }
    }
}

I checked if service broker is enabled and it is; the following returns 1: 

SELECT is_broker_enabled 
FROM sys.databases 
WHERE name = 'TestDB';

Any help is appreciated.

Thanks.

Upvotes: 1

Views: 1579

Answers (1)

cvraman
cvraman

Reputation: 1697

You are doing everything correctly except one thing. Call the method SomeMethod() once in your Form1 constructor.

All subsequent changes to your table data will trigger the dependency change.

Upvotes: 2

Related Questions