Alternative for sqldependency

Question

I have a mssql table that holds chat information. It has computed columns, has millions of rows and is being used/populated by an third party application. Now I want to use this table to show chat history on my website and possible in future have the ability to send messages using signalr. I did a couple tests using sqldependency and it works but only for specific queries that will not help in my case(I have to exclude the computed columns, cannot order by last msg, cannot set top 100 messages etc)

So my questions is what would be an alternative? using a webservice that polls the database every x seconds?

Side note, i have no influence on the table at all.

Answer 1

I think you can still use SqlDependency if you think a bit more about your actual requirements.

The query you're using to detect changes doesn't have to be the same you use to fetch the changes. So you could have

select id from chat where chatRoomId = 123

as the "detect changes query". This will simply tell you "There's a new message. Or more.", nothing else. Then, to get the actual new rows, you could use your complicated query:

select 
 top 100 someComplexColumn, everythingElse 
from chat 
where chatRoomId = 123 
order by messageTime desc

As long as the "detect changes query" is good enough at separating the bulk of changes you don't care about from the changes you do care about, you're fine. Remember, SqlDependency doesn't actually tell you what changed, or anything else about the data. It doesn't care about how much data fits the query now - it only tracks changes that fit the filter you specify. And since chat messages are usually immutable, you can get away with checking just the id column (e.g. "new item"). If your chat messages are editable, you'll have to also add some changedOn column to your "detect changes query".

Answer 2

A good alternative for SqlDependency is SqlDependencyEx. It doesn't have its memory leak problems and uses a database trigger plus a native Service Broker notification to receive events about the table changes. This is an usage example:

int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
          TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME)) 
{
    sqlDependency.TableChanged += (o, e) => changesReceived++;
    sqlDependency.Start();

    // Make table changes.
    MakeTableInsertDeleteChanges(changesCount);

    // Wait a little bit to receive all changes.
    Thread.Sleep(1000);
}

Assert.AreEqual(changesCount, changesReceived);

With SqlDependecyEx you are able to monitor INSERT, DELETE, UPDATE separately and receive actual changed data (xml) in the event args object. Filtering incoming messages helps you implement desirable behavior. Hope this help.