ASP.NET Core Web Api with Dapper (SignalR-SqlTableDependency): unable to connect to web server https

Question

I use Dapper and N-tier architecture in my ASP.NET Core 8.0 project. My project works with SignalR, but when I add SqlDependency, I get an error

Unable to connect to web server https

Hub side:

public class AppHub : Hub
{
    private readonly IExpenseService _expenseService;

    public AppHub(IExpenseService expenseService)
    {
        _expenseService = expenseService;
    }

    public async Task SendExpense()
    {
        var value = _expenseService.itemListExpense();
        await Clients.All.SendAsync("ReceiveExpense", value);
    }
}

Table dependency:

private readonly SqlTableDependency<ResultExpenseDto> _tableDependency;
private readonly AppHub _appHub;

public ExpenseDependency(AppHub appHub)
{
    string connectionString = "Data Source=ADAPC\\SQLEXPRESS;Initial Catalog=DB_WPMS;Integrated Security=True;TrustServerCertificate=True;";
    _appHub = appHub;
    _tableDependency = new SqlTableDependency<ResultExpenseDto>(connectionString);
    _tableDependency.OnChanged += _tableDependency_OnChanged;
    _tableDependency.OnError += _tableDependency_OnError;
}

public void Start()
{
    _tableDependency.Start();
}

private void _tableDependency_OnError(object sender, TableDependency.SqlClient.Base.EventArgs.ErrorEventArgs e)
{
    throw new NotImplementedException();
}

private void _tableDependency_OnChanged(object sender, TableDependency.SqlClient.Base.EventArgs.RecordChangedEventArgs<ResultExpenseDto> e)
{
    if (e.ChangeType != TableDependency.SqlClient.Base.Enums.ChangeType.None)
    {
        _appHub.SendExpense();
    }
}

ApplicationBuilder extensions:

public static class ApplicationBuilderExtensions
{
    public static void UseTableDependency(this IApplicationBuilder applicationBuilder)
    {
        var serviceProvider = applicationBuilder.ApplicationServices;
        var service = serviceProvider.GetService<ExpenseDependency>();
        service.Start();
    }
}

Program.cs file:

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddCors(options =>
{
    options.AddPolicy("CorsPolicy", builder =>
    {
        builder.AllowAnyHeader()
        .AllowAnyMethod()
        .SetIsOriginAllowed((host) => true)
        .AllowCredentials();
    });
});

builder.Services.AddHttpClient();
builder.Services.AddControllersWithViews();

builder.Services.AddSignalR();
builder.Services.AddSingleton<AppHub>();
builder.Services.AddSingleton<ExpenseDependency>();

builder.Services.AddDbContext<Context>();
builder.Services.AddIdentity<AppUser, AppRole>(options =>
{
    options.User.RequireUniqueEmail = true;
}).AddEntityFrameworkStores<Context>().AddErrorDescriber<CustomIdentityValidator>().AddDefaultTokenProviders();

builder.Services.ConfigureApplicationCookie(options =>
{
    options.LoginPath = "/Login/Index";
});

builder.Services.AddSession();
builder.Services.AddDistributedMemoryCache();

var app = builder.Build();

if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Home/Error");
    app.UseHsts();
}

app.UseSession();

app.UseCors("CorsPolicy");

app.UseHttpsRedirection();
app.UseStaticFiles();

app.UseRouting();

app.UseAuthentication();
app.UseAuthorization();

app.MapHub<AppHub>("/appHub");

app.MapControllerRoute(
    name: "default",
    pattern: "{controller=Home}/{action=Index}/{id?}");

app.UseTableDependency();

app.Run();

When I comment out the line app.UseTableDependency();, the project runs, but naturally table dependency is not used.

What is the error? I ask for your support.

Answer 1

Due to the lack of your customized SqlTableDependency code, it is difficult for us to reproduce.

But there is an obvious mistake in your ExpenseDependency class. Private readonly AppHub _appHub; should not be used, but private readonly IHubContext<AppHub> _appHub; should be used.

And you are using UseTableDependency to start the SqlDependency, it's a wrong way.

Here is the best practice for you. Implementing SignalR in ASP.NET Core Web App (MVC) for real-time database updates

And I have test it and it works well, and I move start method like below.

And we can use it like below. We don't need to use your custom middleware UseTableDependency.