程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQLCLR（二）存儲過程和自定義函數

SQLCLR（二）存儲過程和自定義函數

編輯：關於SqlServer

　　自定義函數和存儲過程在.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] ()

　　結果如下