Is it possible to make count Query for SQl Dependency?

Question

Concerning SignalR and SQL Dependency, is it true that the Query Notification can't use aggregate function like count(*)?

Do one have another way or an idea for create query that contain aggregate function ?

Answer 1

Yes, this is the solution if you want to use an aggregate function like COUNT(*) with SqlDependency.

First, in your repository, you will still use a SELECT query. But in the reader, you just count the rows of data when the reader executes the command. Something like this:

int count = 0;

command = new SqlCommand(
    @"select Edolpuz_DB.dbo.TABEL_KONFIRMASI_PEMBAYARAN.ID_BUKTI_PEMBAYARAN " +
    @"from Edolpuz_DB.dbo.TABEL_KONFIRMASI_PEMBAYARAN " +
    @"where Edolpuz_DB.dbo.TABEL_KONFIRMASI_PEMBAYARAN.IS_NEW = @room",
    connect);
command.Parameters.AddWithValue("room", true);

try
{
    command.Notification = null;
    
    SqlDependency dependency = new SqlDependency(command);
    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
    
    if (connect.State == ConnectionState.Open)
        connect.Close();
        
    connect.Open();
    
    reader = command.ExecuteReader();
    while (reader.Read())
    {
        count++;
    }
    
    return count;
}
catch { return 0;}
finally { connect.Close(); }

Answer 2

Be careful using SqlDependency class - it has the problems with memory leaks. Hovewer, you can use an open source realization of the SqlDependency class - SqlDependencyEx. It uses a database trigger and 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 just INSERT or DELETE, avoiding UPDATE, which gives you the desirable result: COUNT-like behavior. Hope this helps.