SQL Server 实时推送通知到 Node.JS

使用SQL Server实时推送通知到Node.js

javascript
const sql = require('mssql');
const WebSocket = require('ws');
// 创建WebSocket服务器
const wss = new WebSocket.Server({ port: 8080 });
// 监听WebSocket连接
wss.on('connection', ws => {
  console.log('Client connected');
  // 连接到SQL Server数据库
  sql.connect(config, err => {
    if (err) {
      console.error(err.message);
      return;
    }
    // 查询CDC变更表的最新数据
    const request = new sql.Request();
    request.query('SELECT * FROM cdc.dbo_MyTable_CT', (err, result) => {
      if (err) {
        console.error(err.message);
        return;
      }
      // 将数据发送给客户端
      ws.send(JSON.stringify(result.recordset));
      // 监听CDC变更表的更改
      const changeQuery = 'SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_MyTable(@from_lsn, @to_lsn, N\'all\')';
      const changeRequest = new sql.Request();
      changeRequest.stream = true;
      changeRequest.input('from_lsn', sql.Binary, result.output.cdc_start_lsn);
      changeRequest.input('to_lsn', sql.Binary, result.output.cdc_current_lsn);
      const changeStream = changeRequest.query(changeQuery);
      changeStream.on('data', data => {
        // 将变更数据发送给客户端
        ws.send(JSON.stringify(data));
      });
    });
  });
  // 监听WebSocket关闭
  ws.on('close', () => {
    console.log('Client disconnected');
  });
});