SQL Server CLR 使用 C# 自定義函數

来源:http://www.cnblogs.com/Brambling/archive/2017/12/07/8000911.html
-Advertisement-
Play Games

一、簡介 Microsoft SQL Server 2005之後，實現了對 Microsoft .NET Framework 的公共語言運行時(CLR)的集成。CLR 集成使得現在可以使用 .NET Framework 語言編寫代碼，從而能夠在 SQL Server 上運行，現在就可以通過 C# 來 ...


一、簡介

Microsoft SQL Server 2005之後，實現了對 Microsoft .NET Framework 的公共語言運行時(CLR)的集成。
CLR 集成使得現在可以使用 .NET Framework 語言編寫代碼，從而能夠在 SQL Server 上運行，現在就可以通過 C# 來編寫 SQL Server 自定義函數、存儲過程、觸發器等。
我最初的目的是因為在 SQL Server 資料庫中遇到數字的十進位與十六進位的互相轉換問題，也看過一些方法吧，但是最後我卻選擇了用 CLR 來做，畢竟在 C# 中兩三行代碼就能搞定的問題。。。

 

二、配置 SQL Server CLR

開啟 CLR：

--開啟所有伺服器配置
sp_configure 'show advanced options', 1; 
RECONFIGURE WITH override 
GO 
--開啟 CLR
sp_configure 'clr enabled', 1; 
RECONFIGURE WITH override 
GO

關閉 CLR：

--關閉所有伺服器配置
sp_configure 'show advanced options', 0; 
RECONFIGURE WITH override 
GO 
--關閉 CLR
sp_configure 'clr enabled', 0; 
RECONFIGURE WITH override 
GO

在後面註冊 CLR 程式集時，發生因操作許可權問題而導致的失敗時，可以嘗試執行下麵的 SQL 語句，這裡我把 SQL 一併貼出來。

--許可權不夠時，設置目標資料庫為可信賴的，例如：Test
ALTER DATABASE [Test] SET TRUSTWORTHY ON 

--修改資料庫所有者為當前登錄的用戶，也可以為其他用戶，例如：sa
EXEC sp_changedbowner 'sa'

 

三、CLR Function

打開 Visual Studio 新建一個 SQL Server 資料庫項目，這裡需要註意 .NET Framework 的版本。
因為我的目標資料庫為 SQL Server 2008，所以這裡我選擇的是 .NET Framework 3.5 的版本。
然後添加新建項，選擇 SQL CLR C# 用戶自定義函數，先從標量函數開始。

 

1、標量函數

public partial class UserDefinedFunctions
{
    /// <summary>
    /// 10進位轉16進位
    /// </summary>
    /// <param name="strNumber"></param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true, Name = "ConvertToHexadecimal")]
    public static SqlString ConvertToHexadecimal(SqlString strNumber)
    {
        SqlString result = string.Empty;
        string str = strNumber.ToString();
        int number = 0;
        if (int.TryParse(str, out number))
        {
            result = number.ToString("X");
        }
        return result;
    }

    /// <summary>
    /// 16進位轉10進位
    /// </summary>
    /// <param name="strNumber"></param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true, Name = "ConvertToDecimal")]
    public static SqlString ConvertToDecimal(SqlString strNumber)
    {
        SqlString result = string.Empty;
        string str = strNumber.ToString();
        int number = 0;
        try
        {
            number = int.Parse(str, System.Globalization.NumberStyles.HexNumber);
            result = Convert.ToString(number, 10);
        }
        catch
        {
        }
        return result;
    }
}

 

2、表值函數

public partial class UserDefinedFunctions
{
    /// <summary>
    /// SQL Server 字元串分割方法
    /// </summary>
    /// <param name="separator"></param>
    /// <param name="pendingString"></param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction(
        DataAccess = DataAccessKind.Read,
        IsDeterministic = true,
        Name = "SqlSplit",
        FillRowMethodName = "SqlSplit_FillRow",
        TableDefinition = "SerialNumber int,StringValue nvarchar(1024)")]
    public static IEnumerable SqlSplit(SqlString separator, SqlString pendingString)
    {
        string _separator = string.Empty;
        string _pendingString = string.Empty;
        if (separator.IsNull)
        {
            _separator = ",";
        }
        else
        {
            _separator = separator.ToString();
            if (string.IsNullOrEmpty(_separator))
            {
                _separator = ",";
            }
        }

        if (pendingString.IsNull)
        {
            return null;
        }
        else
        {
            _pendingString = pendingString.ToString();
            if (string.IsNullOrEmpty(_pendingString))
            {
                return null;
            }
        }

        string[] strs = _pendingString.Split(new string[] { _separator }, StringSplitOptions.RemoveEmptyEntries);
        if (strs.Length <= 0)
        {
            return null;
        }

        List<ResultData> resultDataList = new List<ResultData>();
        for (int i = 0; i < strs.Length; i++)
        {
            resultDataList.Add(new ResultData(i + 1, strs[i]));
        }
        return resultDataList;
    }

    /// <summary>
    /// 填充數據方法
    /// </summary>
    /// <param name="obj"></param>
    /// <param name="serialNumber"></param>
    /// <param name="stringValue"></param>
    public static void SqlSplit_FillRow(Object obj, out SqlInt32 SerialNumber, out SqlString StringValue)
    {
        ResultData resultData = (ResultData)obj;
        SerialNumber = resultData.SerialNumber;
        StringValue = resultData.StringValue;
    }

    /// <summary>
    /// 定義返回類型
    /// </summary>
    public class ResultData
    {
        /// <summary>
        /// 序號，即行號
        /// </summary>
        public SqlInt32 SerialNumber { get; set; }

        /// <summary>
        /// 分割後的每個子字元串
        /// </summary>
        public SqlString StringValue { get; set; }

        public ResultData(SqlInt32 serialNumber, SqlString stringValue)
        {
            SerialNumber = serialNumber;
            StringValue = stringValue;
        }
    }
}

SqlFunctionAttribute 的屬性及介紹：

--屬性                    --說明
--DataAccess            --指示該函數是否涉及訪問存儲在SQL Server的數據
--FillRowMethodName        --在同一個類的方法的名稱作為表值函數(TVF)，這個參數在表值函數中才會用到，用於指定表值函數的數據填充方法
--IsDeterministic        --指示用戶定義的函數是否是確定性的
--IsPrecise                --指示函數是否涉及不精確計算，如浮點運算
--Name                    --函數在SQL Server中註冊時使用的函數的名稱
--SystemDataAccess        --指示該函數是否需要訪問存儲在系統目錄或SQL Server虛擬系統表中的數據
--TableDefinition        --如果方法作為表值函數(TVF)，則為一個字元串，該字元串表示表結構的定義

標量函數與表值函數可以寫在同一個類文件裡面，並且可以包含多個，但是聚合函數就不行了，現在需要添加一個新項，選擇 SQL CLR C# 聚合。

 

3、聚合函數

我這裡寫的這個聚合函數的作用是把多個字元串拼為一個字元串，我之前還真有遇到這種情況需要的。

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
    Format.UserDefined, 
    IsInvariantToDuplicates = false, 
    IsInvariantToNulls = true, 
    IsInvariantToOrder = false, 
    MaxByteSize = 8000, 
    Name = "SumString")]
public struct UserDefinedSqlAggregate : IBinarySerialize
{
    private StringBuilder stringBuilder;

    /// <summary>
    /// 查詢處理器使用此方法初始化聚合的計算
    /// </summary>
    public void Init()
    {
        stringBuilder = new StringBuilder();
    }

    /// <summary>
    /// 查詢處理器使用此方法累計聚合值
    /// </summary>
    /// <param name="Value"></param>
    public void Accumulate(SqlString Value)
    {
        stringBuilder.Append(string.Format("{0},", Value));
    }

    /// <summary>
    /// 查詢處理器使用此方法合併聚合的多個部分計算的值
    /// </summary>
    /// <param name="Group"></param>
    public void Merge(UserDefinedSqlAggregate Group)
    {
        stringBuilder.Append(Group.stringBuilder);
    }

    /// <summary>
    /// 此方法用於返回完成聚合計算的結果
    /// </summary>
    /// <returns></returns>
    public SqlString Terminate()
    {
        return new SqlString(stringBuilder.ToString());
    }

    #region Implement interface IBinarySerialize
    /// <summary>
    /// 讀
    /// </summary>
    /// <param name="r"></param>
    public void Read(System.IO.BinaryReader r)
    {
        stringBuilder = new StringBuilder(r.ReadString());
    }

    /// <summary>
    /// 寫
    /// </summary>
    /// <param name="w"></param>
    public void Write(System.IO.BinaryWriter w)
    {
        w.Write(stringBuilder.ToString());
    }
    #endregion
}

SqlUserDefinedAggregateAttribute 的屬性及介紹：

--屬性                        --說明
--Format                    --選擇序列化的 Format 格式，預設選擇 Native，表示使用本地序列化格式。如果選擇 UserDefined，則聚合類需要實現 IBinarySerialize 介面
--IsInvariantToDuplicates    --指示聚合是否與重覆的值相計算保持不變
--IsInvariantToNulls        --指示聚合是否與空值相計算保持不變
--IsInvariantToOrder        --指示聚合最後計算的結果是否與順序無關
--IsNullIfEmpty                --指示在沒有對任何值進行累計時，聚合返回值是否為 null 
--MaxByteSize                 --聚合實例的最大大小(以位元組為單位)
--Name                        --聚合函數的名稱

然後生成項目，接下來註冊程式集和註冊函數就可以使用了。

 

4、註冊 CLR 程式集

註冊程式集的方式有以下兩種：

第一種，這種方式註冊程式集比較簡單，但是缺點就是程式集不能移動或刪除。

--註冊CLR程式集方式一，指定程式集DLL的路徑
USE Test 
GO 
CREATE ASSEMBLY UserDefinedClrAssembly 
--AUTHORIZATION sa        --指定資料庫所有者，預設為當前用戶
FROM 'C:\Users\Administrator\Desktop\CLR Assembly\UserDefinedSqlClr.dll'        --指定文件路徑
WITH PERMISSION_SET = UNSAFE;        --指定程式集的許可權
                                --SAFE：無法訪問外部系統資源；
                                --EXTERNAL_ACCESS：可以訪問某些外部系統資源；
                                --UNSAFE：可以不受限制的訪問外部系統資源
GO

這裡如果發生因為程式集拒絕訪問的錯誤，那就把電腦用戶 Everyone 的許可權改為完全控制就可以了。

第二種，這種方式註冊程式集稍微複雜一些，但是好處就是註冊成功之後，可以移動甚至刪除DLL文件，只要不是變更遷移資料庫，都不用重新註冊。

--註冊CLR程式集方式二，指定程式集DLL的16進位文件流
USE Test 
GO 
CREATE ASSEMBLY UserDefinedClrAssembly 
--AUTHORIZATION sa        --指定資料庫所有者，預設為當前用戶
FROM 0x4D5A90000300000004000000FFFF0000B8000000000000004000000000    --指定DLL的16進位文件流(當然沒這麼少，我刪掉了)
WITH PERMISSION_SET = UNSAFE;        --指定程式集的許可權
                                --SAFE：無法訪問外部系統資源；
                                --EXTERNAL_ACCESS：可以訪問某些外部系統資源；
                                --UNSAFE：可以不受限制的訪問外部系統資源
GO

獲取DLL的16進位文件流，可以使用 UltraEdit 這個軟體，具體操作方法這裡就不多說了。

註冊成功之後，可以使用下麵的 SQL 語句查看程式集的信息，還包括查詢自定義的函數、存儲過程等的SQL語句，這個下麵註冊函數之後可以用到。

--查看程式集信息
SELECT * FROM sys.assemblies 

--查看模塊信息，即自定義函數、視圖、存儲過程、觸發器等等
SELECT * FROM sys.sql_modules
GO

 

5、註冊函數

下麵是三種函數的註冊方式的 SQL 語句。

USE Test 
GO 

--註冊標量函數 ConvertToHexadecimal 
CREATE FUNCTION [dbo].[ConvertToHexadecimal](@strNumber NVARCHAR(128))
RETURNS NVARCHAR(128) 
WITH EXECUTE AS CALLER        --用於在用戶在執行函數的時候對引用的對象進行許可權檢查
AS 
EXTERNAL NAME [UserDefinedClrAssembly].[UserDefinedFunctions].[ConvertToHexadecimal]    --EXTERNAL NAME 程式集名.類名.方法名
GO 

--註冊標量函數 ConvertToDecimal 
CREATE FUNCTION [dbo].[ConvertToDecimal](@strNumber NVARCHAR(128))
RETURNS NVARCHAR(128) 
WITH EXECUTE AS CALLER        --用於在用戶在執行函數的時候對引用的對象進行許可權檢查
AS 
EXTERNAL NAME [UserDefinedClrAssembly].[UserDefinedFunctions].[ConvertToDecimal]    --EXTERNAL NAME 程式集名.類名.方法名
GO 

--註冊表值函數 SqlSplit 
CREATE FUNCTION [dbo].[SqlSplit](@separator NVARCHAR(32),@string NVARCHAR(MAX))
RETURNS TABLE 
(
    SerialNumber INT,
    StringValue NVARCHAR(1024)
)
WITH EXECUTE AS CALLER        --用於在用戶在執行函數的時候對引用的對象進行許可權檢查
AS 
EXTERNAL NAME [UserDefinedClrAssembly].[UserDefinedFunctions].[SqlSplit]    --EXTERNAL NAME 程式集名.類名.方法名
GO 

--註冊聚合函數 SumString 
CREATE AGGREGATE [dbo].[SumString](@params NVARCHAR(128))
RETURNS NVARCHAR(MAX) 
EXTERNAL NAME [UserDefinedClrAssembly].[UserDefinedSqlAggregate]    --EXTERNAL NAME 程式集名.類名
GO

註冊函數成功之後，接下來測試一下。

DECLARE @TempTable TABLE
(
    Id INT NOT NULL,
    Name NVARCHAR(32) NOT NULL 
)
INSERT INTO @TempTable (
    Id,
    [Name]
)
SELECT '1','小張' UNION ALL 
SELECT '2','小明' UNION ALL 
SELECT '2','小麗' UNION ALL 
SELECT '2','小李' UNION ALL 
SELECT '3','小王' UNION ALL 
SELECT '3','小舞' 

SELECT dbo.ConvertToHexadecimal('15')

SELECT dbo.ConvertToDecimal('FC')

SELECT * FROM SqlSplit(',',',123,456,789,')

SELECT Id,dbo.SumString([Name]) Names 
FROM @TempTable 
GROUP BY Id

結果如圖。

 

下麵是刪除函數和刪除程式集的 SQL 語句，雖然可能用不到，但是還是貼出來吧。

這裡需要註意的是，刪除程式集時要保證不存在函數、存儲過程、觸發器等對程式集的引用。

--刪除標量函數 ConvertToHexadecimal 
DROP FUNCTION dbo.ConvertToHexadecimal

--刪除標量函數 ConvertToDecimal 
DROP FUNCTION dbo.ConvertToDecimal

--刪除表值函數 SqlSplit 
DROP FUNCTION dbo.SqlSplit

--刪除聚合函數 SumString 
DROP FUNCTION dbo.SumString

--刪除程式集 UserDefinedClrAssembly 
DROP ASSEMBLY UserDefinedClrAssembly

 

本想一篇寫完的，還是算了，存儲過程和觸發器留待下一篇。

其實存儲過程和觸發器也沒什麼了，只是 C# 代碼不一樣而已，其他註冊之類的大同小異。

 

這裡推薦一篇博客，大家也可以去看這篇，寫得還是挺完整的，有些地方都是借鑒於此。

http://blog.csdn.net/tjvictor/article/details/4726933

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • windows系統dos視窗全屏
    第一次進入博客園 2017年12月7日 之前使用dos視窗時都輸入的是簡短的指令,今天突然感覺小框看著不舒服,就找了一下度娘,在這裡感謝萬能的百度,一鞠躬. 1.win+r打開dos命令視窗 2.cmd+ENTER 3.quit退出wmic模式 退出dos命令視窗時需要重新進行上述操作! ...
  • Nginx安裝echo模塊
    1、下載需要的echo模塊https://github.com/openresty/echo-nginx-module/tags# wget https://github.com/openresty/echo-nginx-module/archive/v0.60.tar.gz# tar zxvf v ...
  • 34.Linux-printk分析、使用printk調試驅動
    本節學習目的 1)分析printk()函數 2)使用printk()調試驅動 1.在驅動調試中,使用printk(),是最簡單,最方便的辦法 當uboot的命令行里的“console=tty1”時,表示printk()輸出在開發板的LCD屏上 當uboot的命令行里的“console=ttySA0, ...
  • 徹底理解Oracle中的集合操作與複合查詢
    --Oracle中的複合查詢 複合查詢:包含集合運算(操作)的查詢 常見的集合操作有: union： 兩個查詢的並集(無重覆行、按第一個查詢的第一列升序排序) union all：兩個查詢的並集(有重覆行) intersect：兩個查詢的交集(無重覆行、按第一個查詢的第一列升序排序) minus： ... ...
  • 深入理解null的原理
    --null的原理 --oracle一直將null和空字元串’’同等對待 --1.null的運算 --算術表達式和null 運算總為null,實際上所有的操作符除了||連接操作符外，只要有一個操作符為null,則結果為null。 --------------------------------nul... ...
  • Oracle初級——續續篇
    逝者如斯夫，不捨晝夜 所有的SQL都經過測試，可粘貼，可複製，有問題請各位大神指出...... 後續，會有視圖和索引，以及存儲過程的文章，客官們不要著急，耐心等待......... ...
  • Oracle 基礎——同義詞
    一、什麼是同義詞 同義詞是先有對象的別名，主要作用是簡化SQL語句、隱藏對象的名稱和所有者、提供對對象的公共訪問。同義詞共有兩種類型，分別是公有同義詞和私有同義詞；公有同義詞可以被所有的資料庫用戶訪問，私有同義詞只能在當前用戶模式下訪問，且不能與當前用戶內的對象或者同義詞同名。Oracle用戶想要創 ...
  • 使用DataFlow表達ControlFlow的一些思考
    一、控制流 從接觸 面向過程語言 開始，使用控制流編程的概念已是司空見慣。 分支 和 迴圈 是最常見的控制流形式。由於控制條件的存在，總有一部分代碼片段會執行，另一部分不會執行。 在控制流中，想要進行數據傳遞，最關鍵的是藉助於 變數 保存中間狀態。因此，控制流編程看起來是 將數據嵌套在控制流內 的編 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類