I am migrating to SqlTableDependency to get notifications of changes to the database on my asp.net mvc web system. Previously, I used SqlDependency to get the notifications, but I found it more useful to do that migration.
The problem is a little random because the system works except when a customer deletes data and triggers the dependency and a second later another client selects that same table.
I think it's because the requests are made fast enough but this did not happen to me when I used sqldependency. Here the Error:
System.Data.SqlClient.SqlException: 'The referenced entity 'INSERTED' was modified during DDL execution. Please retry the operation.'
Here my code:
public class SqlMesas
{
private CreatechContext db = new CreatechContext();
public void Dependancy()
{
var cString = ConfigurationManager.ConnectionStrings["CreatechContext"].ConnectionString;
SqlTableDependency<Mesa> dependency = new SqlTableDependency<Mesa>(cString, "Mesas");
dependency.OnChanged += DataOnChange;
dependency.Start();
}
private async void DataOnChange(object sender, RecordChangedEventArgs<Mesa> e)
{
try
{
if (e.ChangeType != ChangeType.None)
{
MesasController controller = new MesasController();
var data = await controller.GetMesasOrden();
var hub = GlobalHost.ConnectionManager.GetHubContext<OrdenMeseroHub>();
await hub.Clients.All.updateMesa(data);
await hub.Clients.All.changeMesa("OK");
Dependancy();
}
}
catch (Exception)
{
throw;
}
}
Is it possible to solve this? or continued using sqldependency since this one did it.
note: I still do not do anything with the data that returns sqltabledependency because I'm still trying it ..