通過CLR同步SQL Server和Sharepoint List資料（四）

寫在前面

    本系列文章一共分為四部分：

        1. CLR概述。

        2. 在Visual Studio中進行CLR整合編程並部署到SQL Server，包括預存程序、觸發器、自訂函數、自訂類型和彙總。

        3. CLR整合編程的調試和所遇到的問題。

        4. 利用CLR同步SQL Server表和Sharepoint List（來源於實際項目應用）。

 

    本系列文章建立在以下軟體環境的基礎上：

• Windows Server 2003 Enterprise Edition Service Pack 2
• Microsoft Visual Studio Team System 2008
• Microsoft SQL Server 2008
• Microsoft Office Sharepoint Server 2007

 

背景

    先說說為什麼要進行SQL Server和Sharepoint List的資料同步，這是一個實際項目的應用。

    客戶的Sharepoint網站上擁有四個List，相互之間組成了一個具有階層的Organization資料，該資料最終來源於由另外一個系統所使用的資料庫中的一個表。在項目的前期，開發人員手動從資料庫中將資料匯出來，然後匯入到Sharepoint網站已建立好的List中；項目的後期，來源資料庫中的資料發生了變化，於是開發人員需要手動檢查這些資料的變化，並同步到Sharepoint List中。因為資料量比較大，這項工作很耗費人力和精力，於是，我們打算在SQL Server中編寫一個預存程序，並添加到SQL Server的計劃任務中，讓SQL Server在一個周期中自動去同步這些資料。因為涉及到Sharepoint網站和List的訪問以及許可權的控制，我們想到了在SQL Server中使用CLR方法來編寫預存程序並進行部署。以下是這四個List的結構和資料來源的。

Area List

    兩個text類型的欄位，Title和Area_Code，Title是Area的名稱，Area_Code是Area的地區編碼。

Region List

    Title：text類型，Region的名稱。

    Area：Loopup類型，指向Area的ID。

    Region_Code：text類型，Region的編碼。

 

 Country List

    Title：text類型，Country的名稱。

    Region：Lookup類型，指向Region的ID。

    Country_Code：text類型，Country的編碼。

 

CompanyCode List

    CompanyName：text類型，Company的名稱。

    CompanyCode：Company的編碼。

    Country：Lookup類型，指向Country的ID。

 

資料庫中的源表

    將層次關係組合在一個表中，Area_Code和Area_Name對應Area List，Region_Code和RegionName對應Region List，SubRegion_Code和SubRegion_Name對應Country List，Company_Code和Company_Name對應CompanyCode List。

 

 

準備工作

    我們事先需要在一個測試環境中進行編碼測試，然後再部署到生產環境上。先在測試伺服器上建立一個空白Sharepoint網站，然後按照上面的描述建立四個List，再從客戶的伺服器上將資料匯入到測試用的資料庫中。建立List的時候注意欄位的名稱，重新修改名稱會導致List欄位的內部名稱和外部名稱不一致，這在後面編寫同步程式的時候需要注意一下。

    開啟Visual Studio，建立一個SQL Server Project工程，取名為SqlClrSharePointSynchronizer，接下來就開始我們的同步程式編碼啦。

 

開始

    程式主要完成兩部分的工作，先串連到資料來源所在的資料庫，取到資料，然後串連到要進行資料同步的Sharepoint網站，最後調用WSS提供的方法將資料寫到對應的List中。考慮到所編寫的程式是需要部署到宿主要資料庫中才能執行的，首先要考慮的問題是採用什麼方式執行程式。因為程式所完成的功能相對比較獨立，不依賴於資料庫中的其它對象（如表、視圖等），比較理想的方式就是預存程序和自訂函數，而自訂函數又分為Scale函數和TVF函數兩種（在前面的文章中已經介紹過），Scale函數適用於對資料的處理，類似於一個資料處理工具函數，用在這裡顯然不合適，而TVF函數需要指定一個合適的輸出表結構，況且我們的程式只是完成一個資料同步過程，輸出的資訊可能簡單到只有成功或失敗這樣簡單的文字，所以也不需要TVF函數。另外，自訂函數是必須要有傳回值的，這也可能不是我們所需要的。那麼預存程序是最合適的選擇了，於是我們先建好一個名為Synchronizer的類和一個public static方法SyncHierarchy，來表示一個資料庫的預存程序。

    接下來的工作是要把基礎工作做好，一個DBUtil類，用來處理資料庫連接和擷取資料，這個在大多數三層結構的ASP.NET程式開發中都有，沒什麼好說的，只是我在這裡不需要它完成更多的功能，只需要執行SQL語句並返回一個填充好資料的DataSet即可，其餘的方法都被刪掉了。讀者在附件中可以看到這個簡單的類，這裡我就不再重複貼出代碼了。

    重要的是需要在工程中使用WSS提供的方法。我們在工程中引用Sharepoint網站提供的Web服務，它的地址是http://SiteName/_vti_bin/lists.asmx，其中SiteName就是Sharepoint網站的地址。將這個Web引用取名為Lists，修改代理類如下：

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Security.Principal;
using System.Net;

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

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

            if (impersonateCaller)
            {
                this.impersonationContext = SqlContext.WindowsIdentity.Impersonate();
            }

            this.Url = siteUrl;
            this.Credentials = CredentialCache.DefaultCredentials;
            //this.Credentials = new NetworkCredential("uid", "pwd", "domain");

            if (proxy != null)
            {
                this.Proxy = proxy;
            }

        }

        protected override void Dispose(bool disposing)
        {
            if (disposing && impersonationContext != null)
            {
                impersonationContext.Undo();
            }
            base.Dispose(disposing);
        }
    }
}

    主要功能是提升程式訪問Sharepoint List時的許可權，該許可權利用SqlContext對象通過SQL Server宿主程式的當前登入帳戶去類比，記住許可權提升之後必須執行Undo操作，這個是對應的！

    核心部分的代碼在SPListUtil類中完成，主要實現調用Web Service和執行更新SPList的方法。主要使用到了兩個Web Service方法，Lists.GetListItems()和Lists.UpdateListItems()。有一個需要注意的地方，SPList中有Lookup類型的欄位，這就需要我們在更新資料的時候將這種欄位的值寫成1;#Title1;#2;#Title2的形式，這個需要自己在程式中去處理。還有就是當一次性更新的資料量比較大時，執行Lists.UpdateListItems()方法時所使用的CAML會被自動截斷，這個我已經在《使用WSS的Lists.UpdateListItems()方法之被截斷的CAML 》一文中給出瞭解決辦法。

    具體的代碼讀者可以看我在本文後面給出的附件。我通過四條SQL語句從資料來源表中讀取資料並填充到DataSet中，然後順序將這四部分資料進行轉換並更新到對應的SPList中，同時輸出Lists.UpdateListItems()方法的返回資訊，該資訊是一段XML文本，包含了執行CAML更新資料時的詳細情況，在FormatResult()方法中進行格式化，將返回資訊格式化為我們希望的樣子。下面是我在程式中使用的用於取資料的SQL語句。

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 <> ''";

     因為考慮到宿主要資料庫中可能會使用LinkedServers方式串連客戶資料庫伺服器，所以使用了這種方式的SQL語句。其它的地方應該都很好理解，讀者看原始碼就OK了。

    最後說一下部署。按照我在前面幾篇文章中介紹的部署方式就可以完成dll在宿主要資料庫伺服器上的部署，Visual Studio中直接在專案檔上點擊右鍵，點Deploy就可以自動部署，一般情況下自動部署不會有什麼問題，但是我們總會遇到將編譯好的dll部署到正式的伺服器上的問題，讀者可以看看我在第三篇文章中給出的那個手動部署的SQL指令碼的例子。部署成功後，在SQL Server查詢分析器中執行，下面是執行過程的。 

  

 

    預存程序的第一個參數給定了資料同步的目的地址，第二個參數是LinkedServers的名稱，如果資料庫就在本地的宿主伺服器上，則不需要指定第二個參數。同時Messages視窗中也給出了經過格式化後的執行結果。

程式碼範例下載

 

結語

    通過四篇文章介紹了SQL CLR編程的背景、樣本、調試和部署，以及實際應用的例子，希望對正在使用或將來會使用SQL CLR的朋友有所協助，同時也為自己今後使用SQL CLR編程開發做一個記錄。

1

2

3

4