sql server clr 集成系列之二 简单的sql 函数

erlchina

第一，   新建类库，引用命名空间，using Microsoft.SqlServer.Server;
第二，   编写一个公共类，含有一个公共的静态函数，并且具有特性：[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.None)] ，其中的DataAccess=DataAccessKind.None ，意味该函数不访问用户数据，DataAccessKind.Read 枚举值表示需要访问。
第三，   该方法具有返回值， string 类型。
代码如下：

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.SqlServer.Server;

using System.Data;

using System.Data.SqlTypes;

namespace SQLFunctionTEST

{

    public class SQLFunction

    {

        [Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.None)]

        public static String StringToArray()

        {

            return "hello,world";

        }

    }

}


第四， 编译此类库。
第五，   登陆sql,编写以下代码：

CREATE ASSEMBLY SQLFunctionTEST

FROM 'd:\SQLfunctionTEST.dll'

WITH permission_set = Safe;

第六， 这段代码的意思就是从本地一个dll中创建一个程序集，并且设置权限，默认有三种选择：SAFE | EXTERNAL_ACCESS | UNSAFE
Safe模式就是最具限制性的权限集。由具有 SAFE 权限的程序集所执行的代码将无法访问外部系统资源，例如文件、网络、环境变量或注册表。
EXTERNAL_ACCESS 使程序集可以访问某些外部系统资源，例如文件、网络、环境变量以及注册表。
UNSAFE 可使程序集不受限制地访问资源，无论是 SQL Server 实例内部还是外部的资源都可以访问。从 UNSAFE 程序集内运行的代码可调用未托管代码。
好了，这样就创建了一个叫SQLFunctionTEST 的程序集，那么我开始来从这个程序集创建sql function .
第七：
在第一次使用 sql server clr集成功能的时候，需要开启一个clr选项，执行以下语句：
EXEC sp_configure 'clr enabled', '1';
GO
RECONFIGURE;
GO

然后，来创建函数：
CREATE FUNCTION TESTFunction() RETURNS nvarchar(200)
AS EXTERNAL NAME SQLFunctionTEST.[SQLFunctionTEST.SQLFunction]. StringToArray;
这段话是创建一个 Scarlar Valued Function，（译做标量函数？）返回一个nvarchar。但是大家看 SQLFunctionTEST.[SQLFunctionTEST.SQLFunction]. StringToArray;
可能有点犯晕，这是哪跟哪啊？没关系，我来讲讲，第一个SQLFunctionTEST是指的你从哪个程序集创建的，就是你create到数据库的那个程序集的名字，中括号里面的SQLFunctionTEST 就是程序集里面的命名空间，然后接着是类名，中括号右边就是那个静态方法了。然后，我们就可以用这个sql Function了。

declare @x nvarchar (100)
select @x = dbo.Testfunction()
select @x as functionReturnValue





再附上一个使用sql内部连接的函数示例


using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlMoney DeterminarValor(string descripcion, int cantidad)
    {
        SqlMoney resultado = 0;

        using (SqlConnection cn = new SqlConnection("context connection=true"))
        {
            cn.Open();
            SqlCommand cmd = new SqlCommand("dbo.TraerItemsPorDescripcion", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@descripcion", " "));
            SqlDataReader rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                 resultado = rdr.GetSqlMoney(rdr.GetOrdinal("Precio")) * cantidad;
            }
            rdr.Close();
        }
        return resultado;
    }
};
对比一下，如果是创建标量函数，那么代码中的静态函数的返回值就是 sql 函数中的返回值，
还要提到的一点就是在clr 创建程序集的sql连接，需要使用 “context connection=true”的连接字符串。这个连接字符串我马上就要介绍这个。