SQL CLR 整合實戰（四）：同步 SQL Server 與 SharePoint List 完整方案與部署

本系列最終篇，呈現完整專案：利用 SQL CLR 預存程序，定期將 SQL Server 來源資料同步至 SharePoint 站點的四層級 List (Area → Region → Country → CompanyCode)，解決人工同步低效與錯誤問題。

專案背景與資料結構

來源：單一資料表 SECCompanyHierarchy，欄位包含 Area/Region/SubRegion/Company 的 Code 與 Name。

目標：SharePoint 四個 List，具備查找關係：

技術架構

• SQL CLR 預存程序：部署於 SQL Server，排程作業觸發
• SharePoint Lists.asmx Web Service：GetListItems / UpdateListItems
• 權限提升：SqlContext.WindowsIdentity.Impersonate() 模擬呼叫者身份存取 SharePoint
• Linked Server：跨伺服器存取來源資料庫

核心程式碼實作

1. Lists Web Service Proxy 擴展 (權限提升)

namespace SqlClrSharePointSynchronizer.Lists {
    public partial class Lists {
        public WindowsImpersonationContext ImpersonationContext;

        public Lists(string siteUrl, bool impersonateCaller, IWebProxy proxy) : this() {
            if (siteUrl.EndsWith("/")) siteUrl += "_vti_bin/Lists.asmx";
            else siteUrl += "/_vti_bin/Lists.asmx";

            if (impersonateCaller)
                ImpersonationContext = SqlContext.WindowsIdentity.Impersonate();

            this.Url = siteUrl;
            this.Credentials = CredentialCache.DefaultCredentials;
            if (proxy != null) this.Proxy = proxy;
        }

        protected override void Dispose(bool disposing) {
            if (disposing && ImpersonationContext != null)
                ImpersonationContext.Undo(); // 必須呼叫！
            base.Dispose(disposing);
        }
    }
}

2. SPListUtil 核心同步邏輯

• 四條 SQL 從來源表查詢四層資料，填充 DataSet
• 依層級順序同步：Area → Region → Country → CompanyCode
• Lookup 欄位值格式：1;#Title1;#2;#Title2 (ID;#Value 格式)
• 大量資料分批呼叫 UpdateListItems 避免 CAML 截斷 (參考前文解決方案)
• 格式化回傳 XML 供診斷

3. 查詢 SQL (支援 Linked Server)

string SELECT_AREA = "SELECT DISTINCT AREA_NAME, AREA_CODE FROM {0}dbo.SECCompanyHierarchy WHERE AREA_NAME <> ''";
string SELECT_REGION = "SELECT DISTINCT REGION_NAME, AREA_CODE, REGION_CODE FROM {0}dbo.SECCompanyHierarchy WHERE REGION_NAME <> ''";
string SELECT_COUNTRY = "SELECT DISTINCT SUBREGION_NAME, REGION_CODE, SUBREGION_CODE FROM {0}dbo.SECCompanyHierarchy WHERE SUBREGION_NAME <> ''";
string SELECT_COMPANYCODE = "SELECT COMPANY_CODE, COMPANY_NAME, SUBREGION_CODE FROM {0}dbo.SECCompanyHierarchy WHERE COMPANY_CODE <> '' AND COMPANY_NAME <> ''";

{0} 為 Linked Server 前置詞 (本地則為空)。

4. 進入點預存程序

public class Synchronizer {
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SyncHierarchy(string siteUrl, string linkServer) {
        // 1. 初始化 DBUtil (連接來源庫)
        // 2. 執行四條查詢填充 DataSet
        // 3. 建立 Lists Web Service Proxy (權限提升)
        // 4. 依序同步四個 List
        // 5. 輸出格式化結果訊息
    }
}

部署腳本 (手動部署至生產環境)

-- 1. 啟用 CLR (若未啟用)
EXEC sp_configure 'clr enabled', 1; RECONFIGURE WITH OVERRIDE;
ALTER DATABASE TargetDB SET TRUSTWORTHY ON;
EXEC sp_changedbowner 'sa';

-- 2. 清除舊版本
IF OBJECT_ID('dbo.SyncHierarchy') IS NOT NULL DROP PROCEDURE dbo.SyncHierarchy;
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'SqlClrSharePointSynchronizerXML')
    DROP ASSEMBLY SqlClrSharePointSynchronizerXML WITH NO DEPENDENTS;
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'SqlClrSharePointSynchronizer')
    DROP ASSEMBLY SqlClrSharePointSynchronizer WITH NO DEPENDENTS;

-- 3. 部署組件 (EXTERNAL_ACCESS 權限)
DECLARE @path NVARCHAR(1000) = 'E:\Deploy\SqlClrSharePointSynchronizer\bin\Debug\';
EXEC ('CREATE ASSEMBLY [SqlClrSharePointSynchronizer] FROM ''' + @path + 'SqlClrSharePointSynchronizer.dll'' WITH PERMISSION_SET = EXTERNAL_ACCESS');
EXEC ('CREATE ASSEMBLY [SqlClrSharePointSynchronizerXML] FROM ''' @path + 'SqlClrSharePointSynchronizer.XmlSerializers.dll'' WITH PERMISSION_SET = EXTERNAL_ACCESS');

-- 4. 建立預存程序
CREATE PROCEDURE [dbo].[SyncHierarchy]
    @siteUrl NVARCHAR(4000), @linkServer NVARCHAR(4000)
WITH EXECUTE AS CALLER
AS EXTERNAL NAME [SqlClrSharePointSynchronizer].[Synchronizer].[SyncHierarchy];

執行驗證

• 參數 1：SharePoint 站點 URL
• 參數 2：Linked Server 名稱 (本地留空)
• Messages 視窗顯示格式化同步結果：每層新增/更新/錯誤筆數

關鍵技術難點總結

系列總結

四篇文章完整涵蓋：CLR 基礎 → VS 開發部署 → 除錯與常見問題 → 實戰專案。SQL CLR 讓資料庫層直接具備 .NET 豐富庫與外部整合能力，是解決跨系統資料同步的利器。