你的位置:首页 > 数据库

[数据库]CLR Table


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

View Code


创建一个新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);  }}

View Code


接下来,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

View Code


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


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