程式師世界 >> 編程語言 >> .NET網頁編程 >> 關於.NET >> 通過CLR同步SQL Server和Sharepoint List數據（三）

通過CLR同步SQL Server和Sharepoint List數據（三）

編輯：關於.NET

寫在前面

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

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

CLR集成編程的調試

前面已經提到了在Visual Studio中進行SQL CLR集成編程開發，同時Visual Studio也都提供了對CLR集成編程的調試，使開發更加簡便，下面介紹兩種具體的調試方法。

在Server Explorer窗口中使用單步調試

1. 在Visual Studio中打開SQL CLR工程，打開cs文件並在你想調試的代碼上設置斷點，此時Visual Studio會提示你是否允許SQL CLR進行調試，點擊“是” 。同時，在Server Explorer窗口中，右鍵單擊已連接的數據庫節點，會看到 Application Debugging和Allow SQL/CLR Debugging已經被選中。

2. 在Server Explorer窗口中，打開已連接的數據庫節點，找到已部署成功的數據庫類型（存儲過程、函數、觸發器等），單擊右鍵選擇Step Into Function 。此時Visual Studio會自動開始執行該方法並命中斷點開始調試。注意，采用該調試方法前必須先編譯並部署程序集到指定的SQL Server上，否則在Server Explorer窗口中將找不到剛創建的類型。

使用測試腳本調試

除了上面介紹的方法外，還可以在Visual Studio中使用測試腳本進行代碼調試。

在Solution Explorer窗口中，打開Test Scripts目錄下的Test.sql文件，該文件是Visual Studio在工程中自動創建的專門用於測試SQL腳本的。將執行SQL的語句寫在裡面，代碼中設置好斷點，直接點擊F5，Visual Studio就會根據你寫的 SQL腳本執行，並自動命中斷點進行調試。當然，也可以在Test.sql文件上單擊右鍵，選擇Debug Script手動啟動調試。

另外，還可以自己編寫應用程序對SQL CLR集成編程進行調試，不過對於簡單和少量的代碼就沒有必要了。還有一點需要注意，調試前必須設置斷點，SQL CLR 集成編程中代碼一旦出現錯誤會自動跳出，而不指向出錯的代碼行，但在SQL Server端執行的時候CLR還是會給出錯誤內容的。

CLR集成編程中遇到的問題

1. 當CLR程序集需要訪問外部資源時必須將程序集的屬性Permission Level的值設置為External，有的時候可能會遇到下面這樣的錯誤提示。並且已經將 Permission Level的值設置成External，而且數據庫也將Trustworthy打開了。

Msg 10314, Level 16, State 11, Line 2

An error occurred in the Microsoft .NET Framework while trying to load 

assembly id 65536. The server may be running out of resources, or the 

assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or 

UNSAFE. Run the query again, or check documentation to see how to solve 

the assembly trust issues. For more information about this error:

System.IO.FileLoadException: Could not load file or assembly 

'AssemblyName, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' 

or one of its dependencies. An error relating to security occurred. 

(Exception from HRESULT: 0x8013150A) System.IO.FileLoadException:

at System.Reflection.Assembly.nLoad(AssemblyName fileName, String 

codeBase, Evidence assemblySecurity, Assembly locationHint, 

StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean 

forIntrospection)

at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, 

Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean 

forIntrospection)

at System.Reflection.Assembly.InternalLoad(String assemblyString, 

Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean 

forIntrospection)

at System.Reflection.Assembly.Load(String assemblyString)

或者

Server: Msg 10327, Level 14, State 1, Line 1

CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 

'AssemblyName' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. 

The assembly is authorized when either of the following is true: the 

database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the 

database has the TRUSTWORTHY database property on; or the assembly is 

signed with a certificate or an asymmetric key that has a corresponding 

login with EXTERNAL ACCESS ASSEMBLY permission.

發生該問題的原因可能是你登錄當前SQL Server的賬戶沒有權限訪問相應的資源，切換到SQL Server管理員賬戶即可。

USE　<DatabaseName>

GO

EXEC sp_changedbowner ‘sa’

2. CLR中數據訪問被拒絕，盡管使用了SqlContext上下文進行數據庫連接，或者指定了正確的數據庫連接字符串。

A .NET Framework error occurred during execution of user defined 

routine or aggregate 'p_TESTNAME': System.Data.SqlClient.SqlException: 

Login failed for user ‘NT AUTHORITY\NETWORK SERVICE'.

大部分時候你可能會使用NT賬戶去連接SQL Server而不是SQl Server服務賬戶，這時就需要在代碼中使用SqlContext.WindowsIndentity屬性，下面的代碼片段說明了如何使用這個屬性。

1　public　static　void p_TESTNAME()

2　　　　 {

3　　　　　　　　 WindowsIdentity newId =　new 

SqlContect.WindowsIdentity;

4　　　　　　　　 WindowsImpersinationContext impersonatedUser = 

newId.Impersonate();

5

6　　　　　　　　 try

7　　　　 {

8　　　　　　　　 using (SqlConnection conn =　new SqlConnection

(“Server=RemoteServer;Integrated Security=true”))

9　　　　　　　　 {

10　　　　　　　　 conn.Open();

11　　　　　　　　 //Do something…

12　　　　　　　　 conn.Close();

13　　　　　　　　 }

14　　　　　　　　 }

15　　　　 finally

16　　　　 {

17　　　　　　　　 impersonatedUser.Undo();

18　　　　 }

19　　　　 }

20

impersonatedUser.Undo()方法必須執行（無論什麼情況下）。當使用 SqlContext上下文的方式進行連接時必須在該方法完成之後，否則會出現連接失敗的錯誤，這不僅僅是在采用SqlContext進行數據庫連接時，在使用 SqlContext.Pipe方法時也是如此。所以，務必確認在Undo方法完成之後，再調用 SqlContext對象。

3. 有的時候在CLR的宿主服務器A上需要訪問另外一個數據庫服務器B，而B采用的Windows身份驗證，這時可以在A中創建一個Linked Servers，用來指向B，使用的時候在數據庫對象前加上Linked Servers名稱和數據庫名稱，即：

<LinkedServers>.<DBName>.<[dbo].TableName>

因為不能直接使用SqlContext在A中訪問B，通過給定數據庫連接字符串也不能成功在A中訪問B，采用Linked Servers方法可以很好地解決這個問題。有關如何創建Linked Servers，讀者可以參考SQL Server的幫助文檔。

http://msdn.microsoft.com/zh-cn/library/ms162234(SQL.90).aspx

4. 在進行CLR集成開發時有時會生成附屬程序集，如*.XmlSerializers.dll，部署的時候需要將附屬程序集與主程序集一同部署，先部署主程序集，後部署附屬程序集。刪除的時候先刪除附屬程序集，後刪除主程序集。

使用SQL CLR的過程中可能還會遇到很多奇怪的問題，我在項目中使用CLR時就遇到了很多莫名其妙的問題，通過查幫助文檔和Google，後來問題基本都解決了。以後實際應用中再遇到問題再補上，目前只想到這麼多了 :) 下面順便給出一個手動部署dll到SQL Server服務器的腳本，方便大家將調試好的dll部署到生產環境中。

CLR集成編程的部署

下面是手動將程序集部署到SQL Server的一個示例，通過該示例，可以幫助讀者理解如何將程序集部署到SQL Server服務器上。

1　--exec sp_configure 'clr enabled', 1

2　--reconfigure with override

3　--ALTER DATABASE TEST SET trustworthy ON

4　--use TEST

5　--go

6　--Exec sp_changedbowner 'sa'

7　

8　--Dropping Everything

9　if　OBJECT_ID('dbo.SyncHierarchy') is　not　null

10　begin

11　　　 print　'dropping procedure dbo.SyncHierarchy'

12　　　 drop　procedure dbo.SyncHierarchy

13　end

14　IF　EXISTS (SELECT　[name]　FROM sys.assemblies WHERE　[name]　= 

N'SqlClrSharePointSynchronizerXML')

15　begin

16　　　 print　'dropping assembly SqlClrSharePointSynchronizerXML'

17　　　 DROP ASSEMBLY SqlClrSharePointSynchronizerXML with NO 

DEPENDENTS;

18　end

19　IF　EXISTS (SELECT　[name]　FROM sys.assemblies WHERE　[name]　= 

N'SqlClrSharePointSynchronizer')

20　begin

21　　　 print　'dropping assembly SqlClrSharePointSynchronizer'

22　　　 DROP ASSEMBLY SqlClrSharePointSynchronizer with NO 

DEPENDENTS;

23　end

24　go

25

26　--creating everything

27　declare　@path　nvarchar(1000)

28　set　@path　=

　'E:\Import\SqlClrSharePointSynchronizer\SqlClrSharePointSynchronizer\

bin\Debug\'

29　print　'creating assembly SqlClrSharePointSynchronizer'

30　exec ('CREATE ASSEMBLY [SqlClrSharePointSynchronizer] FROM '''　+　

@path　+　'SqlClrSharePointSynchronizer.dll''

31　WITH PERMISSION_SET = EXTERNAL_ACCESS')

32　

33　print　'creating assembly SqlClrSharePointSynchronizerXML'

34　exec ('CREATE ASSEMBLY [SqlClrSharePointSynchronizerXML] FROM '''　

+　@path　+　'SqlClrSharePointSynchronizer.XmlSerializers.dll''

35　WITH PERMISSION_SET = EXTERNAL_ACCESS')

36　go

37　

38

39　CREATE　PROCEDURE　[dbo].[SyncHierarchy]

40　　　　 @siteUrl　[nvarchar](4000),

41　　　　 @linkServer　[nvarchar](4000)

42　WITH　EXECUTE　AS CALLER

43　AS

44　EXTERNAL NAME [SqlClrSharePointSynchronizer].[Synchronizer].

[SyncHierarchy]

45　GO