.NET Core中SignalR与Oracle数据库连接（含示例表连接）

环境准备 #

• .NET Core 6+（或稳定版）
• Oracle 数据库（需提前创建错误日志表）
• NuGet 依赖包：
  • Microsoft.AspNetCore.SignalR
  • Oracle.EntityFrameworkCore（或Oracle.ManagedDataAccess.Core）
  • Microsoft.EntityFrameworkCore

1. 数据库端配置 #

错误日志表创建 #

CREATE TABLE ERROR_LOGS (
    ID NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ERROR_MESSAGE VARCHAR2(1000) NOT NULL,
    OCCURRED_AT TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,
    IS_PUSHED CHAR(1) DEFAULT 'N' CHECK (IS_PUSHED IN ('Y', 'N'))
);

可选：Oracle AQ 实时触发配置（替代轮询） #

如果需要真正的实时推送，用Oracle高级队列（AQ）触发消息：

-- 创建队列表
BEGIN
    DBMS_AQADM.CREATE_QUEUE_TABLE(
        QUEUE_TABLE => 'ERROR_LOG_QUEUE_TABLE',
        QUEUE_PAYLOAD_TYPE => 'SYS.AQ$_JMS_TEXT_MESSAGE',
        COMPATIBLE => '10.0'
    );
END;
/

-- 创建队列
BEGIN
    DBMS_AQADM.CREATE_QUEUE(
        QUEUE_NAME => 'ERROR_LOG_QUEUE',
        QUEUE_TABLE => 'ERROR_LOG_QUEUE_TABLE'
    );
END;
/

-- 启动队列
BEGIN
    DBMS_AQADM.START_QUEUE(QUEUE_NAME => 'ERROR_LOG_QUEUE');
END;
/

-- 插入日志时自动发送消息到队列的触发器
CREATE OR REPLACE TRIGGER TRG_ERROR_LOG_AQ
AFTER INSERT ON ERROR_LOGS
FOR EACH ROW
DECLARE
    ENQ_MSG SYS.AQ$_JMS_TEXT_MESSAGE;
    ENQ_OPTIONS DBMS_AQ.ENQUEUE_OPTIONS_T;
    MSG_PROPERTIES DBMS_AQ.MESSAGE_PROPERTIES_T;
    MSG_ID RAW(16);
BEGIN
    ENQ_MSG := SYS.AQ$_JMS_TEXT_MESSAGE.CONSTRUCT;
    ENQ_MSG.SET_TEXT('{"Id":' || :NEW.ID || ',"Message":"' || REPLACE(:NEW.ERROR_MESSAGE, '"', '\"') || '","OccurredAt":"' || TO_CHAR(:NEW.OCCURRED_AT, 'YYYY-MM-DD HH24:MI:SS.FF') || '"}');
    DBMS_AQ.ENQUEUE(
        QUEUE_NAME => 'ERROR_LOG_QUEUE',
        ENQUEUE_OPTIONS => ENQ_OPTIONS,
        MESSAGE_PROPERTIES => MSG_PROPERTIES,
        PAYLOAD => ENQ_MSG,
        MSGID => MSG_ID
    );
END;
/

-- 给数据库用户授权AQ操作权限
GRANT EXECUTE ON DBMS_AQADM TO your_db_user;
GRANT ALL ON ERROR_LOG_QUEUE_TABLE TO your_db_user;
GRANT ALL ON ERROR_LOG_QUEUE TO your_db_user;

2. .NET Core 后端实现 #

Program.cs 配置 #

var builder = WebApplication.CreateBuilder(args);

// 注册SignalR服务
builder.Services.AddSignalR();

// 注册Oracle数据库上下文
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseOracle(builder.Configuration.GetConnectionString("OracleConnection")));

// 注册后台监听服务（二选一：AQ监听或轮询）
builder.Services.AddHostedService<ErrorLogAqListenerService>();
// builder.Services.AddHostedService<ErrorLogPollingService>();

var app = builder.Build();

// 配置SignalR端点
app.MapHub<ErrorLogHub>("/errorLogHub");

app.Run();

数据库上下文与实体类 #

using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
    public DbSet<ErrorLog> ErrorLogs { get; set; }
}

public class ErrorLog
{
    public int Id { get; set; }
    public string ErrorMessage { get; set; }
    public DateTime OccurredAt { get; set; }
    public string IsPushed { get; set; }
}

SignalR Hub 类 #

using Microsoft.AspNetCore.SignalR;

public class ErrorLogHub : Hub
{
    // 客户端连接时获取历史日志
    public async Task<List<ErrorLog>> GetHistoricalLogs()
    {
        using var scope = Context.RequestServices.CreateScope();
        var dbContext = scope.ServiceProvider.GetRequiredService<AppDbContext>();
        
        return await dbContext.ErrorLogs
            .OrderByDescending(l => l.OccurredAt)
            .Take(20)
            .ToListAsync();
    }
}

后台服务：Oracle AQ 监听版 #

using Microsoft.AspNetCore.SignalR;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
using System.Text.Json;

public class ErrorLogAqListenerService : BackgroundService
{
    private readonly IHubContext<ErrorLogHub> _hubContext;
    private readonly IConfiguration _config;

    public ErrorLogAqListenerService(IHubContext<ErrorLogHub> hubContext, IConfiguration config)
    {
        _hubContext = hubContext;
        _config = config;
    }

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            using var conn = new OracleConnection(_config.GetConnectionString("OracleConnection"));
            await conn.OpenAsync(stoppingToken);

            var queue = new OracleAQQueue("ERROR_LOG_QUEUE", conn);
            queue.MessageType = OracleAQMessageType.JmsTextMessage;

            try
            {
                var dequeueOpts = new OracleAQDequeueOptions
                {
                    Wait = 10, // 无消息时等待10秒
                    Visibility = OracleAQVisibilityMode.OnCommit
                };

                var message = queue.Dequeue(dequeueOpts);
                if (message != null)
                {
                    var jmsMsg = (OracleAQJmsTextMessage)message.Payload;
                    var errorLog = JsonSerializer.Deserialize<ErrorLog>(jmsMsg.Text);
                    if (errorLog != null)
                    {
                        // 推送给所有在线客户端
                        await _hubContext.Clients.All.SendAsync("ReceiveNewErrorLog", errorLog, stoppingToken);

                        // 更新数据库标记为已推送
                        using var scope = CreateScope();
                        var dbContext = scope.ServiceProvider.GetRequiredService<AppDbContext>();
                        var log = await dbContext.ErrorLogs.FindAsync(errorLog.Id);
                        if (log != null)
                        {
                            log.IsPushed = "Y";
                            await dbContext.SaveChangesAsync(stoppingToken);
                        }
                    }
                }
            }
            catch (OracleException ex) when (ex.Number == 25228)
            {
                // 队列超时，无消息，继续循环
            }
            catch (Exception ex)
            {
                Console.WriteLine($"AQ监听异常: {ex.Message}");
                await Task.Delay(5000, stoppingToken); // 异常后延迟5秒重试
            }
            finally
            {
                await conn.CloseAsync();
            }
        }
    }

    private IServiceScope CreateScope()
    {
        var serviceProvider = new ServiceCollection()
            .AddDbContext<AppDbContext>(options => options.UseOracle(_config.GetConnectionString("OracleConnection")))
            .BuildServiceProvider();
        
        return serviceProvider.CreateScope();
    }
}

后台服务：定时轮询版（简单替代方案） #

using Microsoft.AspNetCore.SignalR;
using Microsoft.EntityFrameworkCore;

public class ErrorLogPollingService : BackgroundService
{
    private readonly IHubContext<ErrorLogHub> _hubContext;
    private readonly IServiceProvider _serviceProvider;
    private readonly int _pollInterval = 5000; // 5秒轮询一次

    public ErrorLogPollingService(IHubContext<ErrorLogHub> hubContext, IServiceProvider serviceProvider)
    {
        _hubContext = hubContext;
        _serviceProvider = serviceProvider;
    }

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            using var scope = _serviceProvider.CreateScope();
            var dbContext = scope.ServiceProvider.GetRequiredService<AppDbContext>();

            // 获取未推送的新日志
            var newLogs = await dbContext.ErrorLogs
                .Where(l => l.IsPushed == "N")
                .OrderBy(l => l.OccurredAt)
                .ToListAsync(stoppingToken);

            if (newLogs.Any())
            {
                foreach (var log in newLogs)
                {
                    await _hubContext.Clients.All.SendAsync("ReceiveNewErrorLog", log, stoppingToken);
                    log.IsPushed = "Y";
                }
                await dbContext.SaveChangesAsync(stoppingToken);
            }

            await Task.Delay(_pollInterval, stoppingToken);
        }
    }
}

配置文件（appsettings.json） #

{
  "ConnectionStrings": {
    "OracleConnection": "User Id=your_db_user;Password=your_db_pwd;Data Source=your_oracle_tns;"
  }
}

3. 前端实现 #

<!DOCTYPE html>
<html>
<head>
    <title>实时错误日志</title>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/microsoft-signalr/7.0.0/signalr.min.js"></script>
    <style>
        .log-item {
            padding: 10px;
            margin: 5px 0;
            border: 1px solid #ddd;
            border-radius: 4px;
        }
        .error-message {
            color: #dc3545;
            font-weight: bold;
        }
        .occurred-at {
            font-size: 0.8em;
            color: #6c757d;
        }
    </style>
</head>
<body>
    <h1>实时错误日志展示</h1>
    <div id="logsContainer"></div>

    <script>
        const connection = new signalR.HubConnectionBuilder()
            .withUrl("/errorLogHub")
            .build();

        // 接收新推送的日志
        connection.on("ReceiveNewErrorLog", (errorLog) => {
            addLogToPage(errorLog);
        });

        // 加载历史日志
        async function loadHistory() {
            try {
                const logs = await connection.invoke("GetHistoricalLogs");
                logs.forEach(addLogToPage);
            } catch (err) {
                console.error(err);
            }
        }

        // 渲染日志到页面
        function addLogToPage(log) {
            const logDiv = document.createElement("div");
            logDiv.className = "log-item";
            logDiv.innerHTML = `
                <div class="error-message">${log.errorMessage}</div>
                <div class="occurred-at">发生时间: ${new Date(log.occurredAt).toLocaleString()}</div>
            `;
            document.getElementById("logsContainer").prepend(logDiv);
        }

        // 启动SignalR连接
        async function startConnection() {
            try {
                await connection.start();
                console.log("SignalR连接成功");
                await loadHistory();
            } catch (err) {
                console.error(err);
                setTimeout(startConnection, 5000); // 连接失败后重试
            }
        }

        startConnection();

        // 页面关闭时断开连接
        window.addEventListener("beforeunload", () => {
            connection.stop();
        });
    </script>
</body>
</html>

注意事项 #

• 生产环境需添加完善的异常捕获与日志记录
• AQ方案需确保Oracle用户权限充足，轮询方案需根据业务调整轮询间隔
• 前端SignalR库版本需与后端.NET版本兼容

内容的提问来源于stack exchange，提问作者Lakshay Malhotra