自定义函数和存储过程在.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;
![None.gif](/Images/OutliningIndicators/None.gif)
![None.gif](/Images/OutliningIndicators/None.gif)
public
partial
class
StoredProcedures
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
//这里是告诉sqlserver,这个方法要注册成存储过程
//我感觉[Attribute]这个东西翻译成标签更形像:)
[Microsoft.SqlServer.Server.SqlProcedure]
public static void TestStoredProcedure(string name, ref string outstr)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
// 在此处放置代码
outstr = "hello," + name;
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
using (SqlConnection cn = new SqlConnection())
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//使用上下文链接也就是当前数据库链接
cn.ConnectionString = "context connection=true";
using (SqlCommand cmd = cn.CreateCommand())
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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;
![None.gif](/Images/OutliningIndicators/None.gif)
public
partial
class
UserDefinedFunctions
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
//需要返回一个表时用TVF(streaming table-valued function)
//可以用select from 语句查询这个方法的返回
//TVF需要返回Ienumerable接口,例如:Array,这里返回一个数组
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
//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)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
FileInfo[] files;
//模拟当前SQL安全上下文
WindowsImpersonationContext OriginalContext= SqlContext.WindowsIdentity.Impersonate();
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
DirectoryInfo di = new DirectoryInfo(directoryName);
files = di.GetFiles(pattern);
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (OriginalContext != null)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
OriginalContext.Undo();
}
}
return files;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
public static void BuildRow(object Obj,
ref SqlString fileName,
ref SqlInt64 fileLength,
ref SqlDateTime fileModified)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (Obj != null)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
FileInfo file = (FileInfo)Obj;
fileName = file.Name;
fileLength = file.Length;
fileModified = file.LastWriteTime;
}
else
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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;
![None.gif](/Images/OutliningIndicators/None.gif)
public
partial
class
UserDefinedFunctions
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString ScalarFunction()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
// 在此处放置代码
return new SqlString("Hello");
}
}
;
sqlserver查询查询分析器中运行如下语句
SELECT
[
TestProject
]
.
[
dbo
]
.
[
ScalarFunction
]
()
结果如下