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.

"*what would be an alternative? using a webservice that polls the database every x seconds?*" - yep, pretty much. — James, May 07 '15 at 15:13
Also, what do you mean by having no influence on the table? Does that mean you don't control how the data is inserted? Wonders can be done with custom helper columns and triggers if the only thing you care about is that the 3rd party's `insert`s and `select`s still have to work. Unless they're using `select *`, of course :D — Luaan, May 07 '15 at 15:22

Luaan · Accepted Answer · 2015-05-07T15:20:21.927

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".

Wow, so simple but somehow i never thought about this. I just update my code with a simpel query and even with plenty records my OnChange event fires every time. Chat messages arent editable so this should be fine. The only thing i forgot and took me a while to figure out is that i didnt fire the ExecuteReader() for my "detect changes query". Awesome, thanks! — blaataap, May 08 '15 at 07:48

score 3 · Answer 2 · edited May 23 '17 at 12:09

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.

Alternative for sqldependency

2 Answers2