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
]
?()
结果如下
第二篇完成，谢谢大家指教！?
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
