SQL SERVER 2005 CLR集成之简单SQL函数讲解

转自:http://developer.51cto.com/art/200910/158170.htm

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