Problem updating data from real time SQL with SignalR

Question

I am using SignalR to update records in real time and show them in a table for the client side with connection to SQL, I establish the connection, when I run the application they load all the data but when I update the DB the update is not shown in the client side table, but when I give F5 (refresh) in the browser, the current data is just displayed. I can't find the error, I also don't see any console errors. I attached the code for suggestions :) thanks

Client:

<body>
<h1>Hola</h1>

<div class="table table-responsive">
    <table id="tbl-productos" class="table table-bordered table-striped"></table>
</div>

<script src="~/jquery/jquery.min.js"></script>
<script src="~/aspnet/signalr/dist/browser/signalr.min.js"></script>

<script>
    $(function () {
        fnCrearTabla();
    });

    function fnCrearTabla() {
        var tabla = $("#tbl-productos");
        $.ajax({
            url: "/Home/listadoProductos",
            type: "GET",
            contentType: "application/json; charset-utf-8",
            dataType: "json",
            success: function (data) {
                if (data.length > 0) {
                    //tabla.empty();
                    var thead = "<tr>";
                    thead += "<th>id</th>"
                    thead += "<th>nombre</th>"
                    thead += "<th>precio</th>"
                    thead += "<th>Stock</th>"
                    thead += "</tr>"

                    tabla.append(thead);

                    var array = [];
                    var tbody = "";

                    for (var i = 0; i < data.length; i++) {
                        tbody += "<tr>";
                        tbody += "<td>" + data[i].id + "</td>"
                        tbody += "<td>" + data[i].nombre + "</td>"
                        tbody += "<td>" + data[i].precio + "</td>"
                        tbody += "<td>" + data[i].stock + "</td>"
                        tbody += "</tr>"
                    }

                    array.push(tbody);
                    tabla.append(array.join(""));
                }
            },
            error: function (e) {
                console.error(e);
            }
        });

    }

    var connection = new signalR.HubConnectionBuilder().withUrl("http://localhost:63257/tablaHub").build();
    //connection.serverTimeoutInMilliseconds = 100000; // 100 second}

    connection.on("ActualizarGrilla", function () {
        fnCrearTabla();
    });

    connection.start().catch(function () {
         console.error(e);
    });
</script>

Controller:

public class HomeController : Controller
{
    private readonly IProductoServicio _productoServicio;

    public HomeController(IProductoServicio productoServicio)
    {
        this._productoServicio = productoServicio;
    }

    public IActionResult Index()
    {
        return View();
    }

    public JsonResult listadoProductos()
    {
        var data = _productoServicio.Listado().ToList();
        return Json(data);
    }
}

Startup.cs

public class Startup
{
    private readonly IWebHostEnvironment _webHostEnvironment;
    private readonly IConfiguration _configuration;

    public Startup(IWebHostEnvironment webHostEnvironment)
    {
        this._webHostEnvironment = webHostEnvironment;

        var builder = new ConfigurationBuilder()
            .SetBasePath(_webHostEnvironment.ContentRootPath)
            .AddJsonFile("appsettings.json")
            .AddEnvironmentVariables();
        _configuration = builder.Build();
    }

    public void ConfigureServices(IServiceCollection services)
    {
        services.AddSingleton(_configuration);

        #region IOC

        services.AddScoped<IProductoRepositorio, ProductoRepositorio>();
        services.AddScoped<IProductoServicio, ProductoServicio>();

        #endregion

        services.AddSignalR();
        services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_3_0);
    }

    public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
    {
        app.UseDeveloperExceptionPage();
        app.UseStaticFiles();

        app.UseEndpoints(endpoints =>
        {
            endpoints.MapHub<TablaHub>("/tablaHub");
            endpoints.MapControllerRoute("default", "{controller=Home}/{action=Index}/{id?}");
        });
    }
}

And this is where everything happens, implemented class --->

ProdcutoRepositorio.cs:

public class ProductoRepositorio : IProductoRepositorio
{
    private readonly IConfiguration _configuration;
    private readonly IHubContext<TablaHub> _tablaHub;

    public ProductoRepositorio(IConfiguration configuration,
                               IHubContext<TablaHub> tablaHub)
    {
        this._configuration = configuration;
        this._tablaHub = tablaHub;
    }

    public IEnumerable<Producto> Get()
    {
        List<Producto> listProducto = null;
        Producto oProducto = null;
        SqlDependency dependency = null;

        var connectionString = _configuration.GetConnectionString("dbconnection");

        using (SqlConnection cn = new SqlConnection(connectionString))
        {
            try
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand("upsGetProductos", cn);
                cmd.CommandType = CommandType.StoredProcedure;

                #region SqlDependecy

                cmd.Notification = null;
                dependency = new SqlDependency(cmd);
                dependency.OnChange += DetectarCambios;
                SqlDependency.Start(connectionString);

                #endregion

                SqlDataReader dr = cmd.ExecuteReader();

                if (dr.HasRows)
                {
                    listProducto = new List<Producto>();

                    while (dr.Read())
                    {
                        oProducto = new Producto()
                        {
                            Id = dr.IsDBNull(dr.GetOrdinal("Id")) ? -1 : dr.GetInt32(dr.GetOrdinal("Id")),
                            Nombre = dr.IsDBNull(dr.GetOrdinal("Nombre")) ? "nodata" : dr.GetString(dr.GetOrdinal("Nombre")),
                            Precio = dr.IsDBNull(dr.GetOrdinal("Precio")) ? Convert.ToDecimal(0) : dr.GetDecimal(dr.GetOrdinal("Precio")),
                            Stock = dr.IsDBNull(dr.GetOrdinal("Stock")) ? -1 : dr.GetInt32(dr.GetOrdinal("Stock"))
                        };
                        listProducto.Add(oProducto);
                    }
                }
                return listProducto;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
    }

    public void DetectarCambios(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type == SqlNotificationType.Change)
        {
            _tablaHub.Clients.All.SendAsync("ActualizarGrilla");
        }

        Get();
    }

    public void Dispose()
    {
        GC.SuppressFinalize(this);
    }
}

And my hub class...TablaHub:

public class TablaHub : Hub
{
}

allan makura · Accepted Answer · 2020-01-08 22:23:19Z

1

I think you might be facing caching problem since you are making an ajax call. You can start by having a look at the network traffic in your browser to see if it's actually making calls to the backend for getting the data when you make the changes. It would be useful for debugging if you add some console logging maybe inside fnCrearTabla to check you are receiving the messages from signalR. The following code would disable caching

$.ajax({  cache: false,  ...});

answered Jan 8, 2020 at 22:23

allan makura

9791 gold badge10 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Problem updating data from real time SQL with SignalR

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related