FracturedPixel
FracturedPixel

Reputation: 47

C# dependency_OnChange Need to identify where it is coming from?

I am using a SQL dependency_OnChange event to know when a table has been updated. From multiple databases (same schema different clients)

However, I have inspected both the SqlNotificationEventArgs and the sender object and neither have any identifying information about which DB instance this change event came from?

Here is the snippet where I am creating the dependency:

using (var conn = new SqlConnection(connectionString))
                    {
                            conn.Open();
                        var cmd = new SqlCommand("SELECT... WHERE....");
                        cmd.Connection = conn;
                        var dependency = new SqlDependency(cmd);
                        dependency.OnChange += dependency_OnChange;
                        SqlDependency.Start(connectionString);
                        cmd.ExecuteNonQuery();
                    }

And the signiture of the OnChange Method:

private static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
       //Foo
    }

How can I determine which DB instance the change occurred in?

Update: Following on from K.Gabor's suggestion of using a Lambda expression I managed to add the connection string to the event handler like so:

dependency.OnChange += (sender, e) => dependency_OnChange(sender, e, connectionString);

Upvotes: 0

Views: 282

Answers (1)

K. Gabor
K. Gabor

Reputation: 36

If I understand you correctly, you want to retrieve information about your SqlConnection object (because you have more than one) in your event handler function.

You can easily do this, with a wrapper class, but it is even better to do it with a short lambda.

Try this: dependency.OnChange += (sender, e) => dependency_OnChange(sender, e, conn.DataSource); and change the signature of your event handler function to private static void dependency_OnChange(object sender, SqlNotificationEventArgs e, string dataSource).

You get something like:

static void Main(string[] args)
{
    SqlDependency.Start(connectionString);

    using (var conn = new SqlConnection(connectionString))
    {
        conn.Open();
        var cmd = new SqlCommand("SELECT * FROM test_table", 
            conn);
        var dependency = new SqlDependency(cmd);
        dependency.OnChange += (sender, e) => dependency_OnChange(sender, e, conn.DataSource);
        cmd.ExecuteNonQuery();

        Console.WriteLine("Waiting for something to happen press any key to exit!");
        Console.ReadKey();
    }

    SqlDependency.Stop(connectionString);
}

private static void dependency_OnChange(object sender, SqlNotificationEventArgs e, string dataSource)
{
    Console.WriteLine("Change registered!");
}

You could pass the entire SqlConnection object or any property of it (instead of the datasource string), but it might not be the best way, since the SqlConnection can be disposed at any time, and in theory the event might get fired afterwards, so the compiler will complain.

Upvotes: 1

Related Questions