這幾天來，努力學習了CLR的存儲過程，創建與部署。從普通的存儲過程，帶參數，以及Output返回值等。Insus.NET今天學習一個例子，怎樣實現CLR Table-Valued函數。在數據庫中，我們可以看到很多種函數類型，Table-falued function,Scalar-valued function 等等。

這篇練習的CLR中編寫的函數就是table-valued function。

在VS開發SQL的 CLR程序，有簡單有復雜，看開發時的衡量了。有些在SQL中無法實現的，可以寫成CLR，然后再部署至SQL中。此篇并沒有看出兩者之間的優勢，僅是一個例子作為參考。比如我們想創建一個多表查詢LEFT JOIN。把SQL語句寫成一個table-valued函數。這個多表查詢，所返回的字段，定義成一個類別：上面代碼示例，可復制代碼:

using System;using System.Collections.Generic;using System.Data;using System.Data.SqlTypes;using System.Text;namespace Insus.NET{    class Fruit    {        public byte Fruit_nbr { get; set; }        public byte  FruitCategory_nbr { get; set; }        public string CategoryName { get; set; }        public byte FruitKind_nbr { get; set; }        public string  KindName { get; set; }        public string  FruitName { get; set; }    }}

創建一個新Item: 按下面的步驟，在標記5中選擇SQL CLR C# User Defined Function。

標記6,給一個名字，標記7"Add"之后：

刪除#14至#19行代碼，添加下面代碼：

上面代碼示例，可復雜代碼：

using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Collections;using Insus.NET;using System.Collections.Generic;public partial class UserDefinedFunctions{    [SqlFunction(Dataaccess = DataAccessKind.Read,                 FillRowMethodName = "Item_FillRow",                 TableDefinition = "Fruit_nbr TINYINT,FruitCategory_nbr TINYINT, " +                                  "CategoryName NVARCHAR(30),FruitKind_nbr TINYINT," +                                  "KindName NVARCHAR(30),FruitName NVARCHAR(30)"                 )    ]    public static IEnumerable Tvf_Fruit()    {        List<Fruit> fruitConnections = new List<Fruit>();        using (SqlConnection connection = new SqlConnection("context connection=true"))        {            connection.Open();            string sql = "SELECT [Fruit_nbr],[FruitCategory_nbr],[CategoryName]," +                "u_fk.[FruitKind_nbr],[KindName],[FruitName] FROM [dbo].[Fruit] AS f " +                "LEFT JOIN [dbo].[udf_FruitKind]() AS u_fk ON (f.[FruitKind_nbr] = u_fk.[FruitKind_nbr])";            using (SqlCommand command = new SqlCommand(sql, connection))            {                using (SqlDataReader objDr = command.ExecuteReader())                {                    while (objDr.Read())                    {                        Fruit oFruit = new Insus.NET.Fruit();                        oFruit.Fruit_nbr = (byte)objDr["Fruit_nbr"];                        oFruit.FruitCategory_nbr = (byte)objDr["FruitCategory_nbr"];                        oFruit.CategoryName = objDr["CategoryName"].ToString();                        oFruit.FruitKind_nbr = (byte)objDr["FruitKind_nbr"];                        oFruit.KindName = objDr["KindName"].ToString();                        oFruit.FruitName = objDr["FruitName"].ToString();                        fruitConnections.Add(oFruit);                    }                }            }        }        return fruitConnections;    }    PRivate static void Item_FillRow(object source, out SqlByte fruit_nbr,        out SqlByte fruitCategory_nbr, out SqlChars categoryName,        out SqlByte fruitKind_nbr, out SqlChars kindName, out SqlChars fruitName)    {        Fruit fruit = (Fruit)source;        fruit_nbr = new SqlByte(fruit.Fruit_nbr);        fruitCategory_nbr = new SqlByte(fruit.FruitCategory_nbr);        categoryName = new SqlChars(fruit.CategoryName);        fruitKind_nbr = new SqlByte(fruit.FruitKind_nbr);        kindName = new SqlChars(fruit.KindName);        fruitName = new SqlChars(fruit.FruitName);    }}

接下來，Build，然后可以部署至SQL中去。上面可復制代碼：

IF EXISTS (SELECT * FROM sys.objects WHERE name = 'Tvf_Fruit')    DROP FUNCTION Tvf_Fruit;GOIF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'FruitClr')    DROP ASSEMBLY FruitClr;GOCREATE ASSEMBLY FruitClrFROM 'E:/FruitClr.dll' WITH PERMISSION_SET = SAFE;GOCREATE FUNCTION Tvf_Fruit()RETURNS TABLE (    Fruit_nbr TINYINT,    FruitCategory_nbr TINYINT,    CategoryName NVARCHAR(30),    FruitKind_nbr TINYINT,    KindName NVARCHAR(30),    FruitName NVARCHAR(30))ASEXTERNAL NAME [FruitClr].UserDefinedFunctions.Tvf_Fruit;GO

執行成功之后，你肯定會發現SQL發生變化的兩個位置：

此時table-valued function創建成功了，在查詢分析器執行一下Tvf_Fruit()函數。

下面內容于2015-03-31 09:30分添加：看看執行的效率如何？ 標記1是原始寫法，標記2是寫成Clr函數。它們得到結果是一樣的。