Sql table dependency task cancellation

[ajex85]

Hello!

I' m trying to use Table sql dependency as hosted service inside and asp.net core web.api project. The issue is that after the initialization phase I receive a cancellation token and the notification doesn't work this is the log: 2020-12-08 16:56:52.412 []Trigger dbo_RES_INFO_LINEA_2e6eecda-cdd0-4486-a262-01fc26c4cb3b created. 2020-12-08 16:56:52.489 []Watch dog started. 2020-12-08 16:56:52.573 []All OK! Database objects created with naming dbo_RES_INFO_LINEA_2e6eecda-cdd0-4486-a262-01fc26c4cb3b. 2020-12-08 16:56:52.574 []Waiting for receiving RES_INFO_LINEA's records change notifications. 2020-12-08 16:56:52.590 []Get in WaitForNotifications. 2020-12-08 16:57:07.588 []Operation canceled. 2020-12-08 16:57:07.929 []DropDatabaseObjects method executed. 2020-12-08 16:57:07.929 []Stopped waiting for notification.

I faced the same behaviour using a worker service (so windows service project) in .net core. The only way I was able to get it working is using a console application but it' s not my scope. Is there a way to make it working or could you point me to where the issue could be using these hosted service?

[Phiph]

HI Alex, I'm trying to get it working as a background task too, I'm getting closer - but I think we need to crack open the API a bit to support it.

[Revan1985]

Hello @ajex85 , I found the same problem at work. We are using the Dependency with a .net core worker service. The only way I found to be able to work with it is to restart the dependency when the service receive an error.

I have setup the event OnStatusChanged, and if the service get a TableDependencyStatus.WaitingForNotification, we try to restart the notification. can you let me know if this resolve your problem?

Here the code (missing not usefull part), we are using version 8.5.8.0

private readonly SqlServerConnection _connection;
private int _restartDependencyRetryInterval = default;

public async Task StartTableDependencyAsync()
{
    SqlTableDependency... _tableDependency ...;
    ...
    _tableDependency.OnStatusChanged += new StatusEventHandler(StatusChanged);
    _tableDependency.Start();
}

private void StatusChanged(object sender, StatusChangedEventArgs e)
{
    switch (e.Status)
    {
        case TableDependencyStatus.WaitingForNotification:
            ...
            break;
        case TableDependencyStatus.StopDueToError:
            TryRestartDependency();
            break;
        default:
            // Do nothing
            break;
    }
}

private void TryRestartDependency()
{
    if (_connection.Check())
    {
        _logger.LogInformation("Restart table dependency");
        DisposeTableDependency();
        StartTableDependencyAsync().Wait();
        _restartDependencyAttempt = 0;
    }
    else if (_restartDependencyAttempt < 3)
    {
        _logger.LogInformation("Unavailable DB connection");
        _restartDependencyAttempt++;
        Thread.Sleep(500);
        TryRestartDependency();
    }
    else
    {
        _logger.LogInformation("Waiting for restart table dependency");
        _restartDependencyAttempt = 0;
        Thread.Sleep(_restartDependencyRetryInterval);
        TryRestartDependency();
    }
}

public void DisposeTableDependency()
{
    try
    {
        _tableDependency.Dispose();
    }
    catch (Exception e)
    {
        _logger.LogError(e.ToString());
        throw;
    }
}

Have a nice day