A. 如何更新sql Server里的CLR程序集
這樣大大增強了Sql Server的功能，為編程開發帶來了方便。怎樣創建CLR存儲過程網上的介紹文章已經很多了，本文不討論了。 作者在使用過程中遇到了這樣的問題，就是程序集assembly的更新問題。在Sql Server中程序集不能修改，只能刪了重建。見下圖： 接下來如果想用drop命令把它刪掉，就會出現這個：
由於對象 'IsLegalDate' 引用了 'ZSqlExtend'，DROP ASSEMBLY 失敗。原來基於這個程序集肯定要建很多存儲過程和函數，這些對象引用了這個程序集，DROP ASSEMBLY 失敗。可以單擊上圖中「查看依賴關系」。怎麼辦呢？那隻能把引用這個程序集的所有對象都刪掉，這樣一來工作量可就大了，而且重建程序集後還要建這些存儲過程和函數。這些存儲過程和函數也有可能被引用，也刪不掉。這樣我們更新程序集豈不是太麻煩了？筆者想出一個變通的辦法，與廣大網友交流。首先ZSqlExtend的代碼如下，裡面有一個IsLegalDate函數。用VS2008編譯後的文件是ZSqlExtend.dll。using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Text.RegularExpressions; namespace ZSqlExtend_NS{public class ZSqlExtend_cls{[SqlFunction(DataAccess = DataAccessKind.Read)]
public static bool IsLegalDate(int iyear, int imonth, int iday){bool ild = false;try{DateTime newdate = new DateTime(iyear, imonth, iday);
DateTime dMin = new DateTime(1900, 1, 1);
DateTime dMax = new DateTime(2079, 6, 7);
ild = newdate >= dMin && newdate < dMax;}catch{ild = false;}return ild;
}} //END CLASS }根據這個dll文件在Sql Server中創建程序集和函數：create assembly ZSqlExtend
from 'E:\hs1\ZSqlExtend\bin\ZSqlExtend.dll' with permission_set = Safe;
go CREATE FUNCTION IsLegalDate
(@y int,@m int,@d int)
RETURNS bit
AS EXTERNAL NAME ZSqlExtend.[ZSqlExtend_NS.ZSqlExtend_cls].IsLegalDate;
go 然後再建一個類庫項目，比如我再做一個ZSqlExtend_mmy的類庫項目，用VS2008編譯後的文件是ZSqlExtend_mmy.dll，這個是ZSqlExtend.dll的代替品。把ZSqlExtend.dll的內容在這個再寫一遍，不過這個是假的，所以可以寫成這樣：using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Text.RegularExpressions; namespace ZSqlExtend_NS{public class ZSqlExtend_cls{[SqlFunction(DataAccess = DataAccessKind.Read)]
public static bool IsLegalDate(int iyear, int imonth, int iday){return true;
} } //END CLASS }把這個「假的」ZSqlExtend_mmy.dll也注冊進Sql Server。create assembly ZSqlExtend_mmy
from 'E:\hs1\ZSqlExtend_mmy\bin\ZSqlExtend_mmy.dll' with permission_set = Safe;go我們的第一步工作就做完了。現在我在項目ZSqlExtend里修改了IsLegalDate，需要重建程序集，就出現了本文一開始的情況。當然你可以先把IsLegalDate刪掉，但是我不能這么做，因為很多表的計算欄位和視圖引用了IsLegalDate。所以我只能讓IsLegalDate和程序集ZSqlExtend脫鉤，然後將程序集ZSqlExtend刪除重建。要想脫鉤就得用那個ZSqlExtend_mmy了，因為它其中的函數簽名和ZSqlExtend完全一樣。函數IsLegalDate是不能刪了，但我們可以修改它，把它改在別的程序集上。Sql Server中執行：alter FUNCTION IsLegalDate
(@y int,@m int,@d int)
RETURNS bit
AS EXTERNAL NAME ZSqlExtend_mmy.[ZSqlExtend_NS.ZSqlExtend_cls].IsLegalDate;goOK，此時程序集ZSqlExtend已經可以刪除重建了，在Sql Server中運行：drop assembly ZSqlExtend;create assembly ZSqlExtend
from 'E:\hs1\ZSqlExtend\bin\ZSqlExtend.dll' with permission_set = Safe;
go當然還要把函數IsLegalDate再改回來alter FUNCTION IsLegalDate
(@y int,@m int,@d int)
RETURNS bit
AS EXTERNAL NAME ZSqlExtend.[ZSqlExtend_NS.ZSqlExtend_cls].IsLegalDate;go這里需要注意2個問題：1、也許你會問，既然要把函數IsLegalDate改掉，讓它和程序集ZSqlExtend無關，何不直接用語句：ALTER FUNCTION [dbo].[IsLegalDate](@y [int], @m [int], @d [int])
RETURNS [bit]beginreturn 1;end哈哈，你會得到如下提示： 無法對 'dbo.IsLegalDate' 進行更改，因為它是不兼容的對象類型。2、為什麼要再編譯個DLL呢？把編譯好的ZSqlExtend.dll改個名那個「假的」不就有了嗎。
B. 如何在SQL Server中運用CLR調用.NET
如何在SQL Server中運用CLR調用.NET
1、在.NET中新建一個類庫項目，並在這個項目中添加一個類文件，並把要被SQL Server調用的方法定義為公有的，靜態的方法。
2、把這個項目編譯為一個DLL。
3、在SQL Server中注冊這個DLL。
4、通過使用SQL Server的函數來訪問指定的.NET方法。
C. 如何：創建和運行 CLR SQL Server 用戶定義的函數
部署成功後，可調用並執行用戶定義的函數。注意 在默認情況下，Microsoft SQL Server 中關閉了公共語言運行庫 (CLR) 集成功能。必須啟用該功能才能使用 SQL Server 項目項。若要啟用 CLR 集成，請使用 sp_configure 存儲過程的「啟用 clr」選項。有關更多信息，請參見啟用 CLR 集成。注意顯示的對話框和菜單命令可能會與幫助中的描述不同，具體取決於您現用的設置或版本。若要更改設置，請在「工具」菜單上選擇「導入和導出設置」。有關更多信息，請參見 Visual Studio 設置。 創建用戶定義的 SQL Server 函數 使用Visual Studio 建立和部署此函數 C++ 示例在編譯時必須使用 /clr:safe 編譯器選項。 對於Visual Basic 和 Visual C#，在「解決方案資源管理器」中，打開「TestScripts」文件夾，然後雙擊「Test.sql」文件，以打開它進行編輯。添加執行您的用戶定義函數的代碼。請參見下面的第二個示例。對於Visual C++，在「解決方案資源管理器」中雙擊「debug.sql」文件，以打開它進行編輯。添加執行您的用戶定義函數的代碼。請參見下面的第二個示例。將用戶定義函數部署到 SQL Server。有關更多信息，請參見如何：將 SQL Server 項目項部署到 SQL Server 中。按F5 通過在 SQL Server 上執行用戶定義的函數來對其進行調試。下面的代碼示例創建用戶定義的將價格作為參數的標量函數 addTax，添加銷售稅並返回價格和銷售稅的加和。創建該函數後，將其部署到 SQL Server。有關更多信息，請參見如何：將 SQL Server 項目項部署到 SQL Server 中Visual Basic 復制代碼 Imports System.Data.SqlTypes Imports Microsoft.SqlServer.ServerPartial Public Class UserDefinedFunctions Public Const SALES_TAX As Double = 0.086 _ Public Shared Function addTax(ByVal originalAmount As SqlDouble) As SqlDouble Dim taxAmount As SqlDouble = originalAmount * SALES_TAX Return originalAmount + taxAmount End FunctionEnd ClassC# 復制代碼 using System.Data.SqlTypes; using Microsoft.SqlServer.Server;public partial class UserDefinedFunctions{public const double SALES_TAX = .086; [SqlFunction()] public static SqlDouble addTax(SqlDouble originalAmount){SqlDouble taxAmount = originalAmount * SALES_TAX; return originalAmount + taxAmount;}}C++ 復制代碼 #include "stdafx.h"#using #using #using using namespace System; using namespace System::Data; using namespace System::Data::Sql; using namespace System::Data::SqlTypes; using namespace Microsoft::SqlServer::Server;// In order to debug your Aggregate, add the following to your debug.sql file://// SELECT dbo.addTax(10) //public ref class UserDefinedFunctions{public:static initonly double SALES_TAX = 0.086; [SqlFunction()] static SqlDouble AddTax(SqlDouble originalAmount){SqlDouble taxAmount = originalAmount * SALES_TAX; return originalAmount + taxAmount;}};向您的項目中 TestScripts 文件夾中的 Test.sql（在 Visual C++ 中為 debug.sql）文件，添加用於測試用戶定義函數的代碼。例如，若要測試此函數，請使用查詢，如「SELECT dbo.addTax(10)」。您應見到返回的值「10.86」。 復制代碼
D. SQL clr對資料庫有影響嗎
沒有影響，同時解決了好多問題，只有好處。
1、 SQLCLR提供編程結構使數據操作和計算更加容易。
T-SQL 專門為資料庫中的直接數據訪問和操作而設計。盡管 T-SQL 在數據訪問和管理方面領先，但是它沒有提供編程結構來使數據操作和計算更加容易。例如，T-SQL 不支持數組、集合、for-each 循環、位轉移或類。盡管在 T-SQL 中可以模擬其中某些構造，但是託管代碼對這些構造提供集成支持。根據方案的不同，這些功能可以為使用託管代碼實現某些資料庫功能提供令人心動的理由。
2、 對於計算和復雜的執行邏輯，託管代碼比 T-SQL 更適合，它全面支持許多復雜的任務，包括字元串處理和正則表達式。
通過 .NET Framework 庫中提供的功能，可以訪問數千個預生成的類和常式。可以很容易從任何存儲過程、觸發器或用戶定義函數進行訪問。基類庫 (BCL) 包括的類提供用於字元串操作、高級數學運算、文件訪問、加密等的功能。
3、 一般來說，函數和聚合是SQL CLR的出色應用。
SQL CLR 代碼的開發人員可以利用 .NET Framework API中存在的大量有用函數和類。這個類/函數庫比 T-SQL 中支持的內置函數要豐富得多。此外，CLR 編程語言提供了 T-SQL 中所沒有的豐富構造（例如數組和列表等）。與 T-SQL（它是一種解釋語言）相比，CLR 編程語言之所以具有更好的性能，是因為託管代碼是已編譯的。對於涉及算術計算、字元串處理、條件邏輯等的操作，託管代碼的性能可能要優於 T-SQL 一個數量級。
4、 託管代碼的一個優點是類型安全性，即確保代碼只通過正確定義並且許可權許可的方式訪問類型。
在執行託管代碼之前，CLR 將驗證代碼是否安全。例如，通過檢查代碼來確保不讀取以前未曾寫入的內存。CLR 還可以幫助確保代碼不操作非託管內存。
5、 開發人員應該將SQLCLR作為一種無法使用T-SQL顯式表達邏輯的備選解決方案。
SQLCLR給開發人員提供了另一種編寫存儲過程的方法，但是利用T-SQL的聲明性結構來處理基於集合的數據選擇與修改要遠遠優於在.net中的過程化結構和ADO.NET對象模型中進行處理，因此SQLCLR不能作為實現業務層邏輯的替代品。那麼根據這個規則，開發人員應該首先使用T-SQL解決問題。
6、 SQLCLR的局限
盡管其中許多類可以從 SQL Server 的 CLR 代碼中使用，但是不適合伺服器端使用的類（例如窗口類）將無法使用。
E. 怎樣從SQL Server 2005 CLR存儲過程返回結果集
部署CLR存儲過程 上面的示例代碼需要進行編譯才能通過存儲過程來調用。在命令行中執行下面的命令來編譯這些代碼並創建類庫DLL MSSQLTipsCLRLib.dll： CSC/target:libraryStoredProceres.cs/out:MSSQLTipsCLRLib.dll 假設你使用的是Microsoft .NET框架的第二版本，那麼你可以在C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727目錄下看到CSC.EXE。 執行下面的T-SQL腳本來創建這個存儲過程： ALTERDATABASEmssqltips FROM'C:\mssqltips\MSSQLTipsCLRLib.dll' WITHPERMISSION_SET=EXTERNAL_ACCESSGOCREATEPROCEDUREdbo.GetListOfFiles @pathNVARCHAR(256) ,@patternNVARCHAR(64) ,@.StoredProceres.GetListOfFiles 在編譯了這個代碼到類庫(.dll)之後，CREATE ASSEMBLY命令就被執行了，它將這個DLL與ASSEMBLY資料庫對象關聯起來。FROM條件從句必須指向DLL的實際路徑。PERMISSION_SET必須設置為EXTERNAL_ACCESS，因為.NET代碼將訪問SQL Server 之外的文件系統。TRUSTWORTHY選項被設置為on，以允許外部訪問。最後，CREATE PROCEDURE命令的EXTERNAME NAME將這個集合、類和函數與這個存儲過程名稱關聯起來。 要執行這個存儲過程，執行下面的腳本： EXECdbo.GetListOfFiles'C:\mssqltips','*.*',0 你將看到類似於下面的輸出，這取決於你選擇的文件夾的內容;例如，一個只有一列、每一行是一個文件的結果集：圖2接下來的步驟 在這里下載示例腳本並執行從一個CLR存儲過程返回結果集。 記住，當在Microsoft .NET框架中有滿足你需求的函數時，使用SQL Server的CLR可能是個很好的解決方法。
F. SQL Server資料庫如何手動部署CLR程序集
（中國軟體網訊）
1.如果以前部署過當前程序集，則必須先刪除引用當前程序集的所有模塊(各類函數和存儲過程等)，然後刪除當前程序集。
2.重新部署當前程序集，然後再創建相關模塊(各類函數和存儲過程等)。
以下是一個sql模板，有需要的可以套一下：
-- ================================= 刪除先 ================================= --
-- MyCLRSqlserver 為程序集部署名稱
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'MyCLRSqlserver' and is_user_defined = 1)BEGIN
-- 刪除集合函數。
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StrJoin]') AND type = N'AF')
DROP AGGREGATE dbo.StrJoin;
-- 刪除標量函數。
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[___fun_CLR_ChangeSalarieScope]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION dbo.___fun_CLR_ChangeSalarieScope;
-- 卸載程序集。
DROP ASSEMBLY [MyCLRSqlserver];END;
-- ================================= 重新創建 ================================= --
-- 注冊程序集(如果是在伺服器上部署，需要將此dll復制到伺服器，然後修改文件地址)。
CREATE ASSEMBLY [MyCLRSqlserver] FROM 'G:AAXX.dll';
---------------------- [dbo].[StrJoin] ----------------------
CREATE AGGREGATE [dbo].[StrJoin]
(@Value [nvarchar](4000))
RETURNS[nvarchar](4000)
EXTERNAL NAME [MyCLRSqlserver].[MyCLRSqlserver.StrJoin]GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'AGGREGATE',@level1name=N'StrJoin'GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'StrJoin.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'AGGREGATE',@level1name=N'StrJoin'GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=14 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'AGGREGATE',@level1name=N'StrJoin'GO
-- ================================= END ================================= --
以上是手工創建過程比較麻煩，當然如果模塊不多的話這樣也沒問題，如果很多的話可以查當前資料庫的系統視圖assembly_moles獲取所有CLR模塊，然後寫代碼自動生成模塊DROP和CREATE過程。
G. SQL中CLR用戶自定義數據是什麼意思
SQL Server CLR 集成簡介
公共語言運行庫 (CLR) 是 Microsoft .NET Framework 的核心，為所有 .NET Framework 代碼提供執行環境。在 CLR 中運行的代碼稱為託管代碼。CLR 提供執行程序所需的各種函數和服務，包括實時 (JIT) 編譯、分配和管理內存、強制類型安全性、異常處理、線程管理和安全性。
通過在 Microsoft SQL Server 中託管 CLR（稱為 CLR 集成），可以在託管代碼中編寫存儲過程、觸發器、用戶定義函數、用戶定義類型和用戶定義聚合函數。因為託管代碼在執行之前會編譯為本機代碼，所以，在有些方案中可以大大提高性能。
託管代碼使用代碼訪問安全性 (CAS)、代碼鏈接和應用程序域來阻止程序集執行某些操作。SQL Server 2005 使用 CAS 幫助保證託管代碼的安全，並避免操作系統或資料庫伺服器受到威脅。
CLR 集成的優點
Transact-SQL 專門為資料庫中的直接數據訪問和操作而設計。盡管 Transact-SQL 在數據訪問和管理方面領先，但是它沒有提供編程構造來使數據操作和計算更加容易。例如，Transact-SQL 不支持數組、集合、for-each 循環、位轉移或類。盡管在 Transact-SQL 中可以模擬其中某些構造，但是託管代碼對這些構造提供集成支持。根據方案的不同，這些功能可以為使用託管代碼實現某些資料庫功能提供令人心動的理由。
Microsoft Visual Basic .NET 和 Microsoft Visual C# 提供面向對象的功能，例如封裝、繼承和多態性。現在，相關代碼可以很容易在類和命名空間中進行組織。在使用大量伺服器代碼時，這樣可以更容易地組織和維護您的代碼。
對於計算和復雜的執行邏輯，託管代碼比 Transact-SQL 更適合，它全面支持許多復雜的任務，包括字元串處理和正則表達式。通過 .NET Framework 庫中提供的功能，可以訪問數千個預生成的類和常式。可以很容易從任何存儲過程、觸發器或用戶定義函數進行訪問。基類庫 (BCL) 包括的類提供用於字元串操作、高級數學運算、文件訪問、加密等的功能。
注意
盡管其中許多類可以從 SQL Server 的 CLR 代碼中使用，但是不適合伺服器端使用的類（例如窗口類）將無法使用。
託管代碼的一個優點是類型安全性，即確保代碼只通過正確定義並且許可權許可的方式訪問類型。在執行託管代碼之前，CLR 將驗證代碼是否安全。例如，通過檢查代碼來確保不讀取以前未曾寫入的內存。CLR 還可以幫助確保代碼不操作非託管內存。
選擇 Transact-SQL 和託管代碼
在編寫存儲過程、觸發器和用戶定義函數時，必須做的一個決定是使用傳統的 Transact-SQL 還是使用 Visual Basic .NET 或 Visual C# 等 .NET Framework 語言。對於幾乎或根本不需要過程邏輯的數據訪問，請使用 Transact-SQL。對於具有復雜邏輯的 CPU 密集型函數和過程，或要使用 .NET Framework 的 BCL 時，請使用託管代碼。
選擇在伺服器中執行和在客戶端中執行
決定使用 Transact-SQL 還是託管代碼的另一個因素是您希望代碼駐留的位置，駐留在伺服器計算機上還是客戶端計算機上。Transact-SQL 和託管代碼均可以在伺服器上運行。這樣使代碼和數據距離很近，可以利用伺服器的處理能力。另一方面，您可能希望避免將處理器密集型任務放在資料庫伺服器上。現在，大多數客戶端計算機非常強大，您可能希望將盡可能多的代碼放在客戶端上，以利用客戶端的處理能力。託管代碼可以在客戶端計算機上運行，而 Transact-SQL 不能。
選擇擴展存儲過程和託管代碼
生成的擴展存儲過程可以執行 Transact-SQL 存儲過程無法執行的功能。但是，擴展存儲過程會影響 SQL Server 進程的完整性，而通過類型安全性驗證的託管代碼不會。另外，內存管理、線程和構造的調度以及同步服務在 CLR 的託管代碼與 SQL Server 之間更深入地集成。通過 CLR 集成，可以通過比擴展存儲過程更加安全、可伸縮性更強的方式來編寫所需的存儲過程，以執行 Transact-SQL 中無法執行的任務。
H. 在sql clr 項目中，如何實現多行字元串
利用函數：group_concat(),實現一個ID對應多個名稱時，原本為多行數據，把名稱合並成一行。
其完整語法：
GROUP_CONCAT(expr)
該函數返回帶有來自一個組的連接的非NULL值的字元串結果。其完整的語法如下所示：
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
mysql> SELECT student_name,
-> GROUP_CONCAT(test_score)
-> FROM student
-> GROUP BY student_name;
Or:
mysql> SELECT student_name,
-> GROUP_CONCAT(DISTINCT test_score
-> ORDER BY test_score DESC SEPARATOR ' ')
-> FROM student
-> GROUP BY student_name;
在MySQL中,你可以獲取表達式組合的連接值。你可以使用DISTINCT刪去重復值。假若你希望多結果值進行排序，則應該使用 ORDER BY子句。若要按相反順序排列，將 DESC (遞減) 關鍵詞添加到你要用ORDER BY 子句進行排序的列名稱中。默認順序為升序；可使用ASC將其明確指定。 SEPARATOR 後面跟隨應該被插入結果的值中間的字元串值。默認為逗號 (『,')。通過指定SEPARATOR '' ，你可以刪除所有分隔符。
使用group_concat_max_len系統變數，你可以設置允許的最大長度。 程序中進行這項操作的語法如下，其中 val 是一個無符號整數：
SET [SESSION | GLOBAL] group_concat_max_len = val;
I. Sql Server CLR創建程序集時報錯，有大神知道該怎麼搞么
/*
1.例,創建CLR標量函數(根據表名,統計該表記錄數)
用記事本編輯,保存FirstUdf.cs
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
public class T
{
[SqlFunction(DataAccess = DataAccessKind.Read)]
public static int ReturnOrderCount(string tName)
{
using (SqlConnection conn
= new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand(
string.Format(@"SELECT COUNT(*) AS Cnt FROM {0}",tName), conn);
return (int)cmd.ExecuteScalar();
}
}
}
2.編譯DLL
2.1打CMD,進入.Net目錄(C:\Windows\Microsoft.NET\Framework\v3.5) 輸入命令
或者
2.2該目錄,創建BAT,執行即
編譯命令
csc.exe /t:library /out:FirstUdf.dll FirstUdf.cs
3.載入DLL,創建函數,查看結
*/
CREATE ASSEMBLY FirstUdf FROM 'C:\Windows\Microsoft.NET\Framework\v3.5\FirstUdf.dll';
GO
CREATE FUNCTION Fn_GetCount(@A nVarchar(100)) RETURNS INT
AS EXTERNAL NAME FirstUdf.T.ReturnOrderCount;
GO
SELECT dbo.Fn_GetCount('apo_city');
GO