【图】SQLCLR（二）存储过程和自定义函数

　　自定义函数和存储过程在.net里其实都是方法。只是方法上方标注[Microsoft.SqlServer.Server.SqlProcedure]

　　和[Microsoft.SqlServer.Server.SqlFunction]不同而已。自定义函数又分TVF函数和Scalar两种，最大区别在于TVF返回表后者返回Scalar（标量），这一篇我们做一下比较。

　　先看两段代码

　　存储过程:

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

public partial class StoredProcedures
{
　　　 //这里是告诉sqlserver，这个方法要注册成存储过程
　　　 //我感觉[Attribute]这个东西翻译成标签更形像:)
　　　 [Microsoft.SqlServer.Server.SqlProcedure]
　　　 public static void TestStoredProcedure(string name, ref string outstr)
　　　 {
　　　　　　　 // 在此处放置代码
　　　　　　　 outstr = "hello," + name;

　　　　　　　 using (SqlConnection cn = new SqlConnection())
　　　　　　　 {
　　　　　　　　　　　 //使用上下文链接也就是当前数据库链接
　　　　　　　　　　　 cn.ConnectionString = "context connection=true";
　　　　　　　　　　　 using (SqlCommand cmd = cn.CreateCommand())
　　　　　　　　　　　 {
　　　　　　　　　　　　　　　 cmd.CommandText = "Select * from userinfo";
　　　　　　　　　　　　　　　 cn.Open();
　　　　　　　　　　　　　　　 //SqlContext.Pipe.Send这个方法输出结果集
　　　　　　　　　　　　　　　 //接受SqlDataReader,SqlDataRecord和string
　　　　　　　　　　　　　　　 SqlContext.Pipe.Send(cmd.ExecuteReader());
　　　　　　　　　　　　　　　 //你也可以用下边这样
　　　　　　　　　　　　　　　 //SqlContext.Pipe.ExecuteAndSend(cmd);
　　　　　　　　　　　 }
　　　　　　　 }
　　　 }
};

　　执行存储过程

DECLARE @name nvarchar(4000)
DECLARE @outstr nvarchar(4000)
set @name='david fan'
-- TODO: 在此处设置参数值。
EXECUTE [TestProject].[dbo].[TestStoredProcedure]
　　 @name
　 ,@outstr OUTPUT
print @outstr

　　结果如下

　　输出参数返回值

　　自定义函数

　　一，TVF函数

　　示例函数的作用是搜索目录下的某一类型的文件

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.IO;
using System.Security.Principal;

public partial class UserDefinedFunctions
{
　　　 //需要返回一个表时用TVF(streaming table-valued function)
　　　 //可以用select from 语句查询这个方法的返回
　　　 //TVF需要返回Ienumerable接口,例如:Array，这里返回一个数组

　　　 //FillRowMethodName为填充表行的方法
　　　 //TableDefinition为表结构，对应FillRowMethodName方法的参数
　　　 [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "BuildRow",
　　　　 TableDefinition = "Name nvarchar(32), Length bigint, Modified DateTime")]
　　　 public static IEnumerable FileListCs(string directoryName, string pattern)
　　　 {
　　　　　　　 FileInfo[] files;
　　　　　　 //模拟当前SQL安全上下文
　　　　　　　 WindowsImpersonationContext OriginalContext= SqlContext.WindowsIdentity.Impersonate();
　　　　　　　 try
　　　　　　　 {
　　　　　　　　　　　 DirectoryInfo di = new DirectoryInfo(directoryName);
　　　　　　　　　　　 files = di.GetFiles(pattern);
　　　　　　　 }
　　　　　　　 finally
　　　　　　　 {
　　　　　　　　　　　 if (OriginalContext != null)
　　　　　　　　　　　 {
　　　　　　　　　　　　　　　 OriginalContext.Undo();
　　　　　　　　　　　 }
　　　　　　　 }
　　　　　　　 return files;
　　　 }

　　　 public static void BuildRow(object Obj,
　　　　　　　　　 ref SqlString fileName,
　　　　　　　　　 ref SqlInt64 fileLength,
　　　　　　　　　 ref SqlDateTime fileModified)
　　　 {
　　　　　　　 if (Obj != null)
　　　　　　　 {
　　　　　　　　　　　 FileInfo file = (FileInfo)Obj;
　　　　　　　　　　　 fileName = file.Name;
　　　　　　　　　　　 fileLength = file.Length;
　　　　　　　　　　　 fileModified = file.LastWriteTime;
　　　　　　　 }
　　　　　　　 else
　　　　　　　 {
　　　　　　　　　　　 fileName = SqlString.Null;
　　　　　　　　　　　 fileLength = SqlInt64.Null;
　　　　　　　　　　　 fileModified = SqlDateTime.Null;
　　　　　　　 }
　　　 }
}

　　因为这个函数对于sqlserver来讲要访问外部资源，所以需要配置一下项目和sqlserver2005

　　项目右键属性，数据库,权限级别选外部

　　打开sqlserver2005查询分析器执行下边语句 TestProject 为我的数据库名，你的如果不是，当然需要修改了。

ALTER DATABASE TestProject SET TRUSTWORTHY ON;

　　成功后，项目右键部署。

　　查询分析器中执行

SELECT * FROM [TestProject].[dbo].[FileListCs] (
　　 'c:'
　 ,'*.txt')

　　结果如下

　　二，Scalar 函数

　　这类函数返回类型如图，像SqlString这类sqlserver的scalar类型

　　下面就是这类函数的一个小例子。

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]
　　　 public static SqlString ScalarFunction()
　　　 {
　　　　　　　 // 在此处放置代码
　　　　　　　 return new SqlString("Hello");
　　　 }
};

　　sqlserver查询查询分析器中运行如下语句

SELECT [TestProject].[dbo].[ScalarFunction] ()

　　结果如下