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

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