这几天来,努力学习了CLR的存储过程,创建与部署。从普通的存储过程,带参数,以及Output返回值等。
Insus.NET今天学习一个例子,怎样实现CLR Table-Valued函数。在数据库中,我们可以看到很多种函数类型,Table-valued function,Scalar-valued function 等等。
这篇练习的CLR中编写的函数就是table-valued function。
在VS开发SQL的 CLR程序,有简单有复杂,看开发时的衡量了。有些在SQL中无法实现的,可以写成CLR,然后再部署至SQL中。此篇并没有看出两者之间的优势,仅是一个例子作为参考。
比如我们想创建一个多表查询LEFT JOIN。把SQL语句写成一个table-valued函数。这个多表查询,所返回的字段,定义成一个类别:
上面代码示例,可复制代码:
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
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。
上面代码示例,可复杂代码:
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
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中去。
上面可复制代码:
![](https://i-blog.csdnimg.cn/blog_migrate/81178cc93a2a3bb5048d90d76e7ec935.gif)
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
此时table-valued function创建成功了,在查询分析器执行一下Tvf_Fruit()函数。
下面内容于2015-03-31 09:30分添加:
看看执行的效率如何? 标记1是原始写法,标记2是写成Clr函数。它们得到结果是一样的。
http://www.cnblogs.com/insus/p/4378354.html