您现在的位置：程式碼之家 >> 編程語言 > >> C語言 >> VC >> VC 教學

SQL Server 存儲過程調用外部 DLL：擴充預存過程完整實作指南

編輯：VC 教學

場景與動機

傳統輪詢資料庫觸發業務邏輯資源消耗大。解決方案：觸發器 → master 庫擴充預存過程 → 外部 DLL → 發送訊息/事件通知應用程式。實現即時、低延遲的資料變更推送。

環境需求

VC6 需安裝新版 Platform SDK（含 srv.h、opends60.lib）
VC.NET 可直接編譯
DLL 需放置於 SQL Server 執行目錄或 System32，修改後需重啟 SQL Server

資料庫端：建立擴充預存過程


USE master
GO
CREATE PROCEDURE sp_testdll AS
BEGIN
    -- 註冊外部函數
    EXEC sp_addextendedproc 'SetFileName', 'storeproc.dll'
    EXEC sp_addextendedproc 'addLine', 'storeproc.dll'

    DECLARE @szFileName VARCHAR(200), @szText VARCHAR(200), @rt INT
    SELECT @szFileName = 'c:\\welcome.txt'

    EXEC @rt = SetFileName @szFileName
    IF @rt = 0 BEGIN
        SELECT @szText = 'welcome 01'
        EXEC @rt = addLine @szText
        SELECT @szText = 'welcome 02'
        EXEC @rt = addLine @szText
    END

    -- 釋放
    EXEC sp_dropextendedproc 'SetFileName'
    EXEC sp_dropextendedproc 'addLine'
    DBCC SetFileName (FREE)
    DBCC addLine (FREE)
END
GO

DLL 端實作：Open Data Services API

擴充預存過程 DLL 必須遵循 ODS 規範，導出函數簽名為：


SRVRETCODE WINAPI FunctionName(SRV_PROC* pSrvProc)

完整 DLL 原始碼


#include 
#include   // Platform SDK 提供

#define XP_NOERROR 0
#define XP_ERROR   1

char g_szFileName[MAX_PATH + 1];

void WriteLog(const char* msg) {
#ifdef _DEBUG
    HANDLE h = CreateFile("c:\\storeproc.log", GENERIC_WRITE,
        FILE_SHARE_READ|FILE_SHARE_WRITE, NULL, OPEN_ALWAYS, 0, NULL);
    if (h != INVALID_HANDLE_VALUE) {
        SetFilePointer(h, 0, NULL, FILE_END);
        DWORD dw;
        WriteFile(h, msg, (DWORD)strlen(msg), &dw, NULL);
        WriteFile(h, "\r\n", 2, &dw, NULL);
        CloseHandle(h);
    }
#endif
}

extern "C" SRVRETCODE WINAPI SetFileName(SRV_PROC* pSrvProc) {
    WriteLog("SetFileName start");
    if (srv_rpcparams(pSrvProc) != 1) return XP_ERROR;

    BYTE bType; ULONG cbMax, cbActual; BOOL fNull;
    if (srv_paraminfo(pSrvProc, 1, &bType, &cbMax, &cbActual, NULL, &fNull) == FAIL
        || cbActual == 0) {
        WriteLog("Param failed");
        return XP_ERROR;
    }
    ZeroMemory(g_szFileName, sizeof(g_szFileName));
    memcpy(g_szFileName, srv_paramdata(pSrvProc, 1), cbActual);
    WriteLog("Set filename ok");
    return XP_NOERROR;
}

extern "C" SRVRETCODE WINAPI addLine(SRV_PROC* pSrvProc) {
    WriteLog("addLine start");
    if (srv_rpcparams(pSrvProc) != 1) return XP_ERROR;

    BYTE bType; ULONG cbMax, cbActual; BOOL fNull;
    if (srv_paraminfo(pSrvProc, 1, &bType, &cbMax, &cbActual, NULL, &fNull) == FAIL
        || cbActual == 0) return XP_ERROR;

    char* buf = new char[cbActual + 3];
    if (!buf) return XP_ERROR;
    ZeroMemory(buf, cbActual + 3);
    memcpy(buf, srv_paramdata(pSrvProc, 1), cbActual);
    buf[cbActual] = '\r'; buf[cbActual+1] = '\n'; buf[cbActual+2] = 0;

    HANDLE hf = CreateFile(g_szFileName, GENERIC_WRITE,
        FILE_SHARE_READ|FILE_SHARE_WRITE, NULL, OPEN_ALWAYS, 0, NULL);
    if (hf == INVALID_HANDLE_VALUE) {
        delete[] buf; return XP_ERROR;
    }
    SetFilePointer(hf, 0, NULL, FILE_END);
    DWORD dw; BOOL ok = WriteFile(hf, buf, (DWORD)strlen(buf), &dw, NULL) && dw == strlen(buf);
    delete[] buf; CloseHandle(hf);
    return ok ? XP_NOERROR : XP_ERROR;
}

BOOL WINAPI DllMain(HINSTANCE, DWORD, LPVOID) { return TRUE; }