Sql Dependency skips table row data

Question

I have an application that gets the user's actions, I subscribed the DB table user_actions (table columns: id [PK], action [int], date_created [datetime]) to SQL dependency. my problem is when I inserted 2 rows at the same time example: action=2 and action=3 then insert another 15 rows of action=2. The row for action=3 skipped sometimes action=2 as well.

From my app it shows like:

id: 1, action: 2
id: 2, action: 3 --- missing
id: 3, action: 2
id: 4, action: 2
id: 5, action: 2
id: 6, action: 2
id: 7, action: 2
id: 8, action: 2 --- missing
id: 9, action: 2
id: 10, action: 2
id: 11, action: 2
id: 12, action: 2 --- missing
id: 13, action: 2

Does anyone experience the same thing as mine? what is the possible problem?

Kind regards.

Here is my code:

void Initialization()
{
    // Create a dependency connection.
    SqlDependency.Stop(connectionString);
    SqlDependency.Start(connectionString);
    SqlDependencyInit();
}

void SqlDependencyInit()
{
    using (var conn = new SqlConnection(connectionString))
    {
        conn.Open();
    
        // Create a new SqlCommand object.
        using (SqlCommand command = conn.CreateCommand())
        {
            command.CommandType = "Text";
            command.CommandText = "SELECT id FROM dbo.user_actions";
        
            command.Notification = null;
            
            // Create a dependency and associate it with the SqlCommand.
            SqlDependency dependency =  SqlDependency(command);

            // Subscribe to the SqlDependency event.
            dependency.OnChange+=new
           OnChangeEventHandler(OnDependencyChange);

            // Execute the command.
            command.ExecuteReader();
        }       
    }
}

void OnDependencyChange(object sender,SqlNotificationEventArgs e)
{
    if (e.Info == SqlNotificationInfo.Insert) 
    {
        var id = Helper.ExecuteScalar("select top 1 id from user_actions order by id desc");
        processUserAction(id);
        SqlDependencyInit();
    }
}

Answer 1

The answer is quite simple: the event gets fired once per modification statement, not once per row. If you want to process only new rows, you need some way of tracking them. The easiest, if rather unreliable, method is to keep track of the last ID seen.

You are also resubscribing the event each time the event is called. You shouldn't need to do this, you only need to catch errors in order to resubscribe.

int _currentId = 0;

void SqlDependencyInit()
{
    const string query = "SELECT id FROM dbo.user_actions";
    using (var conn = new SqlConnection(connectionString))
    using (SqlCommand command = new SqlCommand(query, conn))
    {
        conn.Open();
            
        SqlDependency dependency = new SqlDependency(command);
        dependency.OnChange += OnDependencyChange;

        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
                _currentId = Math.Max(_currentId, reader.GetInt32(0));
        }
    }
}

void OnDependencyChange(object sender, SqlNotificationEventArgs e)
{
    if (e.Info == SqlNotificationInfo.Insert) 
    {
        var list = new List<int>();

        const string query = @"
select id
from user_actions
where id > @id;
";
        using (var conn = new SqlConnection(connectionString))
        using (var command = new SqlCommand(query, conn))
        {
            comm.Parameters.Add("@id", SqlDbType.Int).Value = _currentId;
            conn.Open();
            using (var reader = comm.ExecuteReader())
            {
                list.Add((int)reader["id"]);
            }
        }

        foreach (var id in list)
            processUserAction(id);
    }
    else if (e.Type == SqlNotificationType.Subscribe)
    {
        // handle various errors here

        // To resubscribe, do this:
        SqlDependencyInit();
    }
}

A better solution might be to create a rowversion column on the table, and then keep track of that. This is guaranteed to increment monotonically for every change. You can pass it like this

long _currentRv;

void SqlDependencyInit()
{
    const string query = "SELECT id, CAST(rv AS bigint) FROM dbo.user_actions";

                list.Add((long)reader["rv"]);

        const string query = @"
select id
from user_actions
where rowversion > @rv;
";
        using (var conn = new SqlConnection(connectionString))
        using (var command = new SqlCommand(query, conn))
        {
            comm.Parameters.Add("@rv", SqlDbType.Binary, 8).Value = BitConverter.GetBytes(_currentRv);