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] ()結果如下

第二篇完成，謝謝大家指教！