C#编写CLR函数

本案例在VS2017环境中开发；

　　1、新建项目，“数据库项目”，添加 UserDefinedFunctions.cs类文件，代码如下：

using System;

using System.Data;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Collections;

using System.Collections.Generic;

public class UserDefinedFunctions

{
　　 //标量值函数

    [Microsoft.SqlServer.Server.SqlFunction(Name = "Clr_HelloSqlClr")]

    public static SqlString HelloSqlClr(SqlString input)

    {

        return input;

    }

　　 //表值函数

    [Microsoft.SqlServer.Server.SqlFunction(Name = "Clr_CreditList",

        FillRowMethodName = "SplitFillRow",

        TableDefinition = "typename nvarchar(30),loanbal nvarchar(10),nowovercout nvarchar(3)")]

    public static IEnumerable CreditList()

    {

        List<ReturnData> returnDataList = new List<ReturnData>();

        returnDataList.Add(new ReturnData("a", "a","a"));

        returnDataList.Add(new ReturnData("b", "b", "b"));

        returnDataList.Add(new ReturnData("c", "c", "c"));

        return returnDataList;

    }

    public class ReturnData

    {

        public SqlString TypeName { get; set; }

        public SqlString LoanBal { get; set; }

        public SqlString NowOverCount { get; set; }

        public ReturnData(string name, string password,string overcount)

        {

            this.TypeName = name;

            this.LoanBal = password;

            this.NowOverCount = overcount;

        }

    }

    public static void SplitFillRow(object returnDataObj,

        out SqlString typename, out SqlString loanbal, out SqlString overcount)

    {

        ReturnData item = returnDataObj as ReturnData;

        typename = "";

        loanbal = "";

        overcount = "";

        if (item != null)

        {

            typename = item.TypeName;

            loanbal = item.LoanBal;

            overcount = item.NowOverCount;

        }

    }

}

　　2、搭建 csc.exe 环境，在计算机-属性-高级系统设置-环境变量中，系统变量中找到 Path，双击打开后，在输入框尾端加上 “;C:\Windows\Microsoft.NET\Framework64\v3.5”，

由于SQL2008R2 只支持.NET 3.5 所以引用 3.5版本的路径。在.NET 3.5版本路径下，双击 csc.exe，打开系统cmd 输入 “csc /?” 返回正常，则说明csc环境搭配完成；

　　3、打开cmd 输入 “E:\> csc /target:library /out:SqlServer.SqlClr.Functions.dll E:\UserDefinedFunctions.cs” ，E:\> 代表所在位置，目前在E盘根目录，E:\UserDefinedFunctions.cs 表示cs文件所在路径，回车运行，将在 E盘根目录下生成 SqlServer.SqlClr.Functions.dll 文件；

　　4、在SQL2008 R2 中执行

CREATE ASSEMBLY [SqlServer.Test.Functions]

FROM 'E:\SqlServer.SqlClr.Functions.dll'

WITH PERMISSION_SET = SAFE

CREATE FUNCTION [dbo] . [Clr_CreditList] ()

RETURNS table

(

    typename nvarchar(30),

    loanbal nvarchar(10),

    nowovercout nvarchar(3)

) WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [SqlServer.Test.Functions] . [UserDefinedFunctions] . [CreditList]

　　调用：

select *

  from dbo.Clr_CreditList()

CSC

C#编写CLR函数