Please help with SqlDependency and ASP.NET

TRAIAN MACAVEIU 26 Reputation points
2022-06-04T12:08:56.663+00:00

I try to obtain in an ASP.NET application using SqlDependency, through a stored procedure, information that contains the last record from a table corroborated with information from other tables. I was stuck in determining the last record ... It seems that the syntax "select TOP 1 * from Table order by id desc" or "select * from Table where id = (select max (id) from Table))" does not work with SqlDependency . What can I do to get the last registration?

No comments
{count} votes

2 answers

Sort by: Most helpful
  1. TRAIAN MACAVEIU 26 Reputation points
    2022-06-05T20:36:08.237+00:00

    I solved the problem: I created a new table on the server with the same structure as the table I want to monitor. To the main table I added a trigger after inserted which empties my secondary table and inserts the new row added. SqlDependency now acts on the secondary table which always has a single record. I couldn't find another more academic method!

    No comments

  2. Erland Sommarskog 67,821 Reputation points Microsoft MVP
    2022-06-05T14:38:13.447+00:00

    Long time since I worked with Query Notification, and I have never used it through SqlDependency. But as I recall, you have a plain query without TOP 1, and you will be waiting until the result set of that query changes. When this happens you can retrieve the rows. I don't think you can call a stored procedure.

    No comments