3

IDE used:

  • Microsoft Visual Studio Community 2019

Language used:

  • Asp.net with c# (Razor Pages)

Libraries used:

  • SignalR
  • SqlTableDependency

Issue explanation:

The initial calling for the database is running totally fine without any errors and the retrieved data is displayed correctly in the razor page using Ajax for calling the method which located inside page model.
BUT
Whenever I make changes inside the database it is catching an error saying:

System.Data.SqlClient.SqlException: 'The referenced entity 'INSERTED' was modified during DDL execution. Please retry the operation.'

I am not sure what is causing this issue for me, as well as that even when it is catching the above exceptions for me; it is contiening reading the changes and apply them into the asp page.
May anyone please direct me to how avoiding this exception?

Related methods

  • Repository

public class SkidRepository : ISkidRepository
{
    private SqlTableDependency<Stream> _sqlTableDependencyForSkid;
    private readonly IHubContext<StreamsHub> _hubContext;
    private string _connectionString = "";
    private readonly string startDateTimeFormat = "yyyy-MM-dd 00:00:00";
    private readonly string endDateTimeFormat = "yyyy-MM-dd 23:59:59";
    private DateTime dateTime;
    public SkidRepository(IConfiguration configuration, IHubContext<StreamsHub> hubContext)
    {
        _connectionString = configuration.GetConnectionString("DBConnection");
        _hubContext = hubContext;
        InitSqlTableDependancy();
    }

    public List<Skid> GetAllSkids()
    {
        var skids = new List<Skid>();
        try
        {
            using (var sqlConnection = new SqlConnection(_connectionString))
            {
                sqlConnection.Open();
                using (var sqlCommand = sqlConnection.CreateCommand())
                {
                    sqlCommand.CommandText = "SELECT Distinct [sID] FROM dbo.TableName";
                    using (var sqlDataReader = sqlCommand.ExecuteReader())
                    {
                        while (sqlDataReader.Read())
                        {
                            var id = sqlDataReader["sID"].ToString();
                            skids.Add(new Skid { ID = id });
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }
        return skids;
    }

    public void InitSqlTableDependancy()
    {
        var mapper = new ModelToTableMapper<Stream>();
        mapper.AddMapping(c => c.ID, "sID");
        _sqlTableDependencyForSkid = new SqlTableDependency<Stream>(_connectionString, "DatabaseName", mapper: mapper);
        _sqlTableDependencyForSkid.OnChanged += SqlTableDependancyOnChanged;
        _sqlTableDependencyForSkid.OnError += SqlTableDependancyOnError;
        _sqlTableDependencyForSkid.Start();
    }

    private void SqlTableDependancyOnError(object sender, ErrorEventArgs e)
    {
        Console.WriteLine($"SqlTableDependency error: {e.Error.Message}");
    }

    private void SqlTableDependancyOnChanged(object sender, RecordChangedEventArgs<Stream> e)
    {
        if (e.ChangeType != ChangeType.None)
        {
            _hubContext.Clients.All.SendAsync("updateSList");
        }
    }
}

Screenshot

enter image description here

Mr. Solver
  • 117
  • 1
  • 2

0 Answers0