CLR Table-Valued函数

这几天来，努力学习了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;
GO

IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'FruitClr')
    DROP ASSEMBLY FruitClr;
GO

CREATE ASSEMBLY FruitClr
FROM 'E:\FruitClr.dll' 
WITH PERMISSION_SET = SAFE;
GO

CREATE FUNCTION Tvf_Fruit()
RETURNS TABLE (
    Fruit_nbr TINYINT,
    FruitCategory_nbr TINYINT,
    CategoryName NVARCHAR(30),
    FruitKind_nbr TINYINT,
    KindName NVARCHAR(30),
    FruitName NVARCHAR(30)
)
AS
EXTERNAL NAME [FruitClr].UserDefinedFunctions.Tvf_Fruit;
GO

执行成功之后，你肯定会发现SQL发生变化的两个位置：

此时table-valued function创建成功了，在查询分析器执行一下Tvf_Fruit()函数。

 

下面内容于2015-03-31 09:30分添加：
看看执行的效率如何？ 标记1是原始写法，标记2是写成Clr函数。它们得到结果是一样的。