第一,   新建类库,引用命名空间,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”的连接字符串。这个连接字符串我马上就要介绍这个。