自定义函数和存储过程在.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] ()
结果如下
第二篇完成，谢谢大家指教！