SQL Server CLR集成 #

CLR集成优势：
├── 使用.NET Framework功能
├── 更好的性能（复杂计算）
├── 类型安全
├── 面向对象编程
└── 代码复用

CLR应用类型：
├── 存储过程
├── 用户定义函数
├── 触发器
├── 用户定义聚合
├── 用户定义类型
└── 表值函数

-- 启用CLR
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO

-- 验证
SELECT value_in_use FROM sys.configurations WHERE name = 'clr enabled';

-- 设置权限集
-- SAFE：只能访问本地数据
-- EXTERNAL_ACCESS：可访问外部资源
-- UNSAFE：无限制

-- 创建非对称密钥
USE master;
GO
CREATE ASYMMETRIC KEY MyKey FROM EXECUTABLE FILE = 'D:\CLR\MyAssembly.dll';

-- 创建登录名
CREATE LOGIN MyLogin FROM ASYMMETRIC KEY MyKey;

-- 授予权限
GRANT EXTERNAL ACCESS ASSEMBLY TO MyLogin;
-- 或
GRANT UNSAFE ASSEMBLY TO MyLogin;

// 简单CLR存储过程
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public class StoredProcedures
{
    [SqlProcedure]
    public static void GetUsers()
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM users", conn);
            SqlContext.Pipe.ExecuteAndSend(cmd);
        }
    }
    
    [SqlProcedure]
    public static void GetUserById(int userId)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(
                "SELECT * FROM users WHERE id = @id", conn);
            cmd.Parameters.AddWithValue("@id", userId);
            SqlContext.Pipe.ExecuteAndSend(cmd);
        }
    }
}

// CLR标量函数
using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class UserDefinedFunctions
{
    [SqlFunction]
    public static SqlString FormatName(SqlString firstName, SqlString lastName)
    {
        if (firstName.IsNull || lastName.IsNull)
            return SqlString.Null;
        
        return new SqlString(firstName.Value + " " + lastName.Value);
    }
    
    [SqlFunction]
    public static SqlDouble CalculateDistance(
        SqlDouble lat1, SqlDouble lon1, 
        SqlDouble lat2, SqlDouble lon2)
    {
        if (lat1.IsNull || lon1.IsNull || lat2.IsNull || lon2.IsNull)
            return SqlDouble.Null;
        
        // Haversine公式计算距离
        double R = 6371; // 地球半径（公里）
        double dLat = ToRadians(lat2.Value - lat1.Value);
        double dLon = ToRadians(lon2.Value - lon1.Value);
        double a = Math.Sin(dLat / 2) * Math.Sin(dLat / 2) +
                   Math.Cos(ToRadians(lat1.Value)) * 
                   Math.Cos(ToRadians(lat2.Value)) *
                   Math.Sin(dLon / 2) * Math.Sin(dLon / 2);
        double c = 2 * Math.Atan2(Math.Sqrt(a), Math.Sqrt(1 - a));
        
        return new SqlDouble(R * c);
    }
    
    private static double ToRadians(double degrees)
    {
        return degrees * Math.PI / 180;
    }
}

// CLR表值函数
using System;
using System.Collections;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class TableValuedFunctions
{
    [SqlFunction(
        FillRowMethodName = "FillRow",
        TableDefinition = "number INT, square INT")]
    public static IEnumerable GenerateNumbers(int max)
    {
        for (int i = 1; i <= max; i++)
        {
            yield return new NumberInfo(i, i * i);
        }
    }
    
    public static void FillRow(object obj, out SqlInt32 number, out SqlInt32 square)
    {
        NumberInfo info = (NumberInfo)obj;
        number = info.Number;
        square = info.Square;
    }
    
    private class NumberInfo
    {
        public int Number { get; set; }
        public int Square { get; set; }
        
        public NumberInfo(int number, int square)
        {
            Number = number;
            Square = square;
        }
    }
}

// CLR聚合函数
using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(
    Format.Native,
    IsNullIfEmpty = true)]
public struct Concatenate
{
    private SqlString result;
    
    public void Init()
    {
        result = SqlString.Null;
    }
    
    public void Accumulate(SqlString value)
    {
        if (!value.IsNull)
        {
            if (result.IsNull)
                result = value;
            else
                result = result + ", " + value;
        }
    }
    
    public void Merge(Concatenate other)
    {
        Accumulate(other.result);
    }
    
    public SqlString Terminate()
    {
        return result;
    }
}

# 使用csc编译
csc /target:library /out:MyAssembly.dll MyCode.cs

# 或使用Visual Studio编译

-- 注册程序集（SAFE权限）
CREATE ASSEMBLY MyAssembly
FROM 'D:\CLR\MyAssembly.dll'
WITH PERMISSION_SET = SAFE;

-- 注册程序集（EXTERNAL_ACCESS权限）
CREATE ASSEMBLY MyAssembly
FROM 'D:\CLR\MyAssembly.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;

-- 注册程序集（UNSAFE权限）
CREATE ASSEMBLY MyAssembly
FROM 'D:\CLR\MyAssembly.dll'
WITH PERMISSION_SET = UNSAFE;

-- 创建CLR存储过程
CREATE PROCEDURE usp_clr_get_users
AS EXTERNAL NAME MyAssembly.StoredProcedures.GetUsers;
GO

-- 创建CLR函数
CREATE FUNCTION dbo.fn_format_name(
    @firstName NVARCHAR(50),
    @lastName NVARCHAR(50)
)
RETURNS NVARCHAR(101)
AS EXTERNAL NAME MyAssembly.UserDefinedFunctions.FormatName;
GO

-- 创建CLR表值函数
CREATE FUNCTION dbo.fn_generate_numbers(@max INT)
RETURNS TABLE (number INT, square INT)
AS EXTERNAL NAME MyAssembly.TableValuedFunctions.GenerateNumbers;
GO

-- 创建CLR聚合
CREATE AGGREGATE dbo.Concatenate(@value NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
EXTERNAL NAME MyAssembly.Concatenate;
GO

-- 执行CLR存储过程
EXEC usp_clr_get_users;

-- 带参数
EXEC usp_clr_get_user_by_id @userId = 1;

-- 使用CLR标量函数
SELECT 
    name,
    dbo.fn_format_name(first_name, last_name) AS full_name
FROM users;

-- 使用CLR表值函数
SELECT * FROM dbo.fn_generate_numbers(10);

-- 使用CLR聚合
SELECT 
    department_id,
    dbo.Concatenate(name) AS all_names
FROM users
GROUP BY department_id;

-- 查看所有程序集
SELECT 
    name,
    permission_set_desc,
    create_date,
    modify_date
FROM sys.assemblies;

-- 查看程序集文件
SELECT 
    a.name AS assembly_name,
    f.name AS file_name
FROM sys.assemblies a
INNER JOIN sys.assembly_files f ON a.assembly_id = f.assembly_id;

-- 更新程序集
ALTER ASSEMBLY MyAssembly
FROM 'D:\CLR\MyAssembly_v2.dll';

-- 添加依赖程序集
ALTER ASSEMBLY MyAssembly
ADD FILE FROM 'D:\CLR\Helper.dll';

-- 删除CLR函数
DROP FUNCTION dbo.fn_format_name;

-- 删除CLR存储过程
DROP PROCEDURE usp_clr_get_users;

-- 删除CLR聚合
DROP AGGREGATE dbo.Concatenate;

-- 删除程序集
DROP ASSEMBLY MyAssembly;

适合使用CLR的场景：
├── 复杂计算
├── 字符串处理
├── 正则表达式
├── 外部资源访问
├── 自定义聚合
└── 性能敏感操作

不适合使用CLR的场景：
├── 简单数据操作
├── 标准SQL可完成
├── 数据量大
└── 频繁调用

-- CLR vs T-SQL性能对比
-- CLR适合：CPU密集型操作
-- T-SQL适合：数据访问操作

-- 使用CLR进行复杂计算
CREATE FUNCTION dbo.fn_complex_calculation(@param FLOAT)
RETURNS FLOAT
AS EXTERNAL NAME MyAssembly.Calculations.ComplexCalculation;

-- 使用T-SQL进行数据访问
SELECT * FROM users WHERE id = 1;

CLR安全建议：
├── 优先使用SAFE权限集
├── 避免使用UNSAFE
├── 验证输入参数
├── 处理异常
├── 限制程序集权限
└── 定期审计CLR代码

// 使用SqlContext.Pipe发送调试信息
[SqlProcedure]
public static void DebugExample()
{
    SqlContext.Pipe.Send("Debug message");
    SqlContext.Pipe.Send("Variable value: " + someValue);
}

// CLR错误处理
[SqlFunction]
public static SqlString SafeOperation(SqlString input)
{
    try
    {
        // 操作
        return ProcessInput(input);
    }
    catch (Exception ex)
    {
        // 记录错误
        SqlContext.Pipe.Send("Error: " + ex.Message);
        return SqlString.Null;
    }
}