Getting data from SqlDependency

Question

I've got a table and a SqlDependency that is waiting for new inserts.

OnChange fires as I need, but I don't understand if it's possible to get the row which cause the databse change.

SqlDependency sql command:

SqlCommand cmd = new SqlCommand("SELECT id FROM dbo.DataRequests", m_sqlConn);

OnChange code:

private void OnChange(object sender, SqlNotificationEventArgs e)
{
    SqlDependency dependency = sender as SqlDependency;

    dependency.OnChange -= OnChange;

    Console.WriteLine("Info:   " + e.Info.ToString());
    Console.WriteLine("Source: " + e.Source.ToString());
    Console.WriteLine("Type:   " + e.Type.ToString());


    Console.WriteLine(DateTime.Now);

    GetMessages();

}

Answer 1

No information is available about the rows that caused the dependency to be fired.

I guess as a workaround you could always put a timestamp on your records and track when the event was last fired.

Answer 2

Take a look at this component: SqlTableDependency

For every change done on a SQL Server database table, the C# code receive an event containing a list of RECORDs changed.

Answer 3

Find a very ingenious solution here