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