澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站:自定义函数

2019-10-12 作者:数据库网络   |   浏览(61)

一、简介

.NET编程和SQL Server ——Sql Server 与CLR集成 

 

hsrzyn 

原文地址:

Microsoft SQL Server 2005之后,实现了对 Microsoft .NET Framework 的公共语言运行时(CLR)的集成。
CLR 集成使得现在可以使用 .NET Framework 语言编写代码,从而能够在 SQL Server 上运行,现在就可以通过 C# 来编写 SQL Server 自定义函数、存储过程、触发器等。
我最初的目的是因为在 SQL Server 数据库中遇到数字的十进制与十六进制的互相转换问题,也看过一些方法吧,但是最后我却选择了用 CLR 来做,毕竟在 C# 中两三行代码就能搞定的问题。。。

一、SQL Server 为什么要与CLR集成

澳门新萄京官方网站:自定义函数。1、 SQL Server 提供的存储过程、函数等十分有限,经常需要外部的代码来执行一些繁重的移植;

2、与CLR集成可将原本需要独立的程序来实现的功能迁移到SQL Server 内部进行数据操作;

3、T-SQL数据查询语言在返回数据集方面很好,但是除此之外表现不佳。与CLR的集成可解决这一问题;

4、.NET的操作代码和执行的速度比T-SQL快的很多。.NET程序是已经编译好的二进制代码,而不是作为存储过程来构建,不再编译就直接可运行。

SQLCLR

 

[翻译]在SQL Server中使用CLR调用.NET方法

 

二、SQL Server 中的程序集(编译、添加、修改、删除)

只有在添加了程序集后才能在该程序集的基础上建立CLR存储过程、CLR函数等。

1、CLR代码(编译)→DLL文件(注册)→SQL Server (作为数据库对象)→执行数据库操作 过程如下:

(1)将托管程序编写为一组类定义。编写好代码后编译成一个DLL文件;

存储过程、用户自定义函数、触发器的编写为类的静态方法;

用户自定义类型、聚合函数编写为一个结构体。

(2)DLL文件上传SQL Server 磁盘上,并使用create assembly 将DLL程序集存储到系统目录;

(3)创建SQL对象(函数、存储过程、触发器等)并将其绑定到程序集的入口点;

存储过程:create procedure

用户自定义函数:create function

触发器:create trigger

用户自定义类型:create type

聚合函数:create aggregate

(4)像使用T-SQL例程一样使用。

2、SQL Server 中的程序集(创建程序集并上载到SQL Server 实例然后创建数据库对象)

(1)SQL Server 2008默认情况下禁用了CLR集成的功能,必需先启用CLR集成后才能在SQL Server 访问.NET对象。

启用CLR集成

exec sp_configure 'show advanced options','1'; 
go 
reconfigure; 
go 
exec sp_configure 'clr enabled','1';//开启CLR集成 
go 
reconfigure; 
go

解释

(2)将DLL程序集添加到SQL Server 中。在SQL Server 中添加程序集使用create assembly命令。

create assembly assembly_name(程序集名) 
[澳门新萄京官方网站:自定义函数。authorization owner_name] 
from {<client_assembly_specifier>|<assembly_bits>} 
[with permission_set={safe|external_access|unsafe}]

其中,<client_assembly_specifier>:表示程序集所在的本地位置或网络位置以及与程序集对应的清单文件名。

<assembly_bits>:表示组成程序集和依赖程序集的二进制值的列表。

permission_set={safe|external_access|unsafe :表示指定SQL Server 访问程序集时相程序集授予的一组访问权限,默认值为safe。

(3)修改程序集

alter assembly assembly_name

[from <client_assembly_specifier>|<assembly_bits>]澳门新萄京官方网站, 
[with <assembly_option>[,....n]] 
[drop file{file_name[,....n]|all}] 
[add file from client_file_specifier [as file_name]|file_bits as file_name}[,....n]][;]

  其中,<assembly_option>::=permission_set=[{safe|external_access|unsafe} | visibility={on|off} | unchecked data],其中 visibility={on|off}:指示在创建CLR函数、存储过程、触发器、用户定义的类型以及用户自定义聚合函数时,该程序集是否可见。如果设置为OFF则程序集只能由其他程序集调用。unchecked data :默认情况下,如果alter assembly 必须验证各个表行的一致性,则他将失败。该选项使得用户可以通过使用DBCC CHECKTABLE将检查推迟到以后的某个时间进行。

A、为程序集添加文件:

alter assembly assembly_name

add file from client_file_specifier [as file_name]|file_bits as file_name}[,....n]][;]

B、更新程序集:

use database_name

go

alter assembly assembly_name

drop file all

go

alter assembly assembly_name

from <client_assembly_specifier>|<assembly_bits>]

add file from client_file_specifier [as file_name]|file_bits as file_name}[,....n]][;]

(4)删除程序集

删除程序集是,将从数据库中删除程序集和它的所有关联文件,如,源代码和调试文件等。但如果该程序集被其他对象引用则返回错误。

drop assembly assembly_name[,....n] 
[with no dependents]

其中, with no dependents :表示只删除assembly_name而不删除该程序集引用的相关程序集。如果不指定它,则drop assembly 将删除assembly_name和所有相关程序集。

什么是SQLCLR

SQL CLR (SQL Common Language Runtime) 是自 SQL Server 2005 才出现的新功能,它将.NET Framework中的CLR服务注入到 SQL Server 中,使得.NET代码可在SQL Server服务器进程中执行。

通过在 Microsoft SQL Server 中托管 CLR(称为 CLR 集成),开发人员可以在托管代码中编写存储过程、触发器、用户定义函数、用户定义类型和用户定义聚合函数, 改变了以前只能通过T-SQL语言来实现这些功能的局面。因为托管代码在执行之前会编译为本机代码,所以,在有些方案中可以大大提高性能。

 

原文发布日期:2007.05.17
作者:Mark Smith
翻译:webabcd

二、配置 SQL Server CLR

三、创建CLR函数(Function)

  要创建被SQL Server 引用的CLR程序则需要引用Microsoft.SqlServer.Server命名空间,创建CLR函数还需要使用该命名空间下的SqlFunctionAttribute特性类即将[Microsoft.SqlServer.ServerSqlFunction.]放置CLR函数的头部。

1、创建CLR标量值函数

(1)使用C#编写CLR标量值函数在VS2010中创建CLR函数后,编译成DLL文件,并将该文件添加到数据库中。

(2)在SQL Server中使用CLR标量值函数 使用create function创建引用注册程序集的函数。

create function --[schema_name.]function_name //[schema_name.]如:[dbo.] 

{@parameter_name [as] [type_schema_name.]parameter_data_type [=default]}[,....n] 

return {return_date_type} 
[with <clr_function_option> [,...n]] 
[as]external name assembly_name.class_name.method_name

  其中external name assembly_name.class_name.method_name:指定将程序集与函数绑定的方法。<clr_function_option>::={[returns null on null input | called no null input] | [execute_as_clause] } 其中returns null on null input | called no null input] | [execute_as_clause ]:指定标量值函数的onNULLCall属性。如果未指定,则默认值为 called on null input。这意味着即使传递的参数为null,也将执行函数体。如果在CLR函数中指定了returns null on null input ,它指示当SQL Server接收到的任何一个参数为null时,它可以返回null,而无须实际调用函数体。 优先采用create function语句指示的属性。不能为表值函数指定Onnullcall属性。

2、创建CLR表值函数 
(1)使用C#编写CLR表值函数 
CLR表值函数只返回一个表,在.NET中中创建对应的函数,返回的结果是一个IEnumerable接口,用于表示一个集合。集合中是对象的实例并不是SQLServer中所识别的表,因此需要在函数的属性中指定FillRowMethodName,这个参数的值是用于将.NET中的对象转换为表列的函数名。即将特性[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName="FillSplitTable")]放置与于表值函数的头部,以指定该特性下的函数为CLR表值函数。其中,FillSplitTable是将.NET 中的对象转换为表列的函数名。还有用于将.NET中的对象转换为表列的方法必须为静态方法。第一个参数必须为System.Object类型,接下来的参数的个数就是列的个数。同时接下来的参数都必须声明为ref参数。SQLServer中返回的列的数据类型和顺序必须与该函数中ref参数的数据类型和顺序相 同。编写完后编译成DLL文件并添加到数据库中。 
(2)在SQLServer中使用CLR表值函数 
A、更新程序集 
要在SQLServer中使用C#编写的CLR表值函数,必须先更新程序集。 
如: 
alter assembly assembly_name 
from '程序集地址' 
with permission_set=safe 
B、创建CLR表值函数 
create function [schema_name.]function_name 

{@parameter_name [as][type.schema_name.] 
parameter_data_type [=default]}[,...n] 

return table<clr_table_type_definition> 
[with <clr_function_option>[,...n] ] 
[order(<order_clause>)] 
[as]external name assembly_name.class_name.method_name[;] 
其中,<clr_table_type_definition>::=({column_name data_type}[,...n])定义CLR函数的表数据类型。表声明仅包含列名称和数据类型。表始终放在主文件组中。 order(<order_clause>)指定从表值函数中返回结果的顺序。

3、在T-SQL中使用CLR函数 

SQLCLR实例

Visual Studio 2008提供了“SQL Server项目”类型,在这种类型的项目中,为5种基本SQL CLR实体定义了模板,使开发人员可以很容易的创建SQL CLR代码。

介绍
我们一起来做个示例,在.NET中新建一个类,并在这个类里新建一个方法,然后在SQL Server中调用这个方法。按照微软所述,通过宿主 Microsoft .NET Framework 2.0 公共语言运行库 (CLR),SQL Server 2005显著地增强了数据库编程模型。 这使得开发人员可以用任何CLR语言(如C#、VB.NET或C 等)来写存储过程、触发器和用户自定义函数。

开启 CLR:

四、创建CLR存储过程(Procedure)

1、使用C#编写CLR存储过程所需的函数: 
  在C#中编写可用于CLR存储过程引用的函数必须使用SqlProcedure属性标识。存储过程不需要返回值,所以在C#中建立void函数即可。存储过程一般用于查询并生成一个查询的表,在c#中需要使用SqlPipe对象将表格结果与信息传回给客户端。一般,通过SqlContext类的Pipe属性获得SqlPipe对象,后调用Pipe对象的Send()方法将表格结果或信息传送给客户端,或者使用SqlPipe对象的ExecuteAndSend()方法将查询结果传送给客户端。ExecuteAndSend()方法提供了一种高效率的方式将查询结果传送给客户端。使用特性[Microsoft.SqlServer.Server.SqlProcedure]放置在存储过程调用的函数的头部,用以标示该函数是作为CLR存储过程被调用的,CLR存储过程对应的函数。将C#编写的代码编译成DLL文件,并添加到数据库中。

2、在SQL Server中使用CLR存储过程

create {proc|procedure}[schema_name.]procedure_name [;number] 

{ @parameter [type_schema_name.] data_type } 
[varying] [=default] [out|output] [readonly] 
][,...n] 
[with <procedure_option> [,...n]] 
[for replication] 
as external name assembly_name.class_name.method_name [;]

其中,external name assembly_name.class_name.method_name指定.net framework程序集的方法,以便程序集引用。class_name必须存在与该程序集中,而且指定的方法必须为该类的静态方法。

<procedure_option>::=[encryption] [recompile]

3、创建有output参数的CLR存储过程

存储过程中也可以使用output参数,带有output的参数的值在存储过程内部被修改后也会将修改应用到存储过程外部相当于指针和ref参数。output参数对应于C#中的ref参数。

4、在T-SQL中使用CLR存储过程

 

 

存储过程

打开vs2010,新建项目,选择“数据库”项目类型

澳门新萄京官方网站 1

点击确定后会让选择“数据库连接”,连接到目标数据库。

在项目中右键新建,发现可以新建的类型有函数、存储过程、聚合函数、触发器和用户自定义类型:

澳门新萄京官方网站 2

选择“新建存储过程”,生成的方法有“Microsoft.SqlServer.Server.SqlProcedure”标记,稍作修改,如下所示:

[Microsoft.SqlServer.Server.SqlProcedure]
   public static void HelloPro(SqlString name)
   {
       SqlContext.Pipe.Send("Hello, "  name.ToString());
   }

 

一个简单的存储过程就建好了(就是.net.中的一个方法),但是要注意该存储过程是没有返回值的。部署后测试(关于如何部署将在下一节演示)如下图所示:

澳门新萄京官方网站 3

例子中的类引用了System.Data.SqlTypes命名空间,其包含了SQL Server 中本地数据类型对应的类型,比如上面的SqlString类型对应于数据库中的 nvarchar,长度最大为 4000 。这些类提供一种比.NET Framework 公共语言运行库 (CLR) 提供的数据类型更快更安全的替代方案。使用此命名空间中的类有助于防止类型转换错误时出现精度损失的情况。还引用了Microsoft.SqlServer.Server 命名空间,该命名空间包含将 Microsoft .NET Framework 公共语言运行库 (CLR) 集成到 Microsoft SQL Server和SQL Server 数据库引擎进程执行环境时所要用到的类、接口和枚举。

代码使用了SqlContext.Pipe.Send()方法,它的目的是将字符串消息直接发送到客户端或当前输出使用者,如果没有这行代码,那么执行该存储过程后获取不到任何值。

上面那个例子没有返回值,也没有与数据库交互,下面这个例子与数据库交互:

[Microsoft.SqlServer.Server.SqlProcedure]
   public static Int32  GetStudentIdByName(SqlString name)
   {       
       int id = 0;
       //使用上下文连接,也就是当前数据库连接
       using (SqlConnection conn = new SqlConnection("context connection=true"))
       {
           SqlCommand cmd = new SqlCommand();
           cmd.CommandText = "select id_student,name_student from student where name_student =@name";
           SqlParameter paraname = new SqlParameter ("@name",SqlDbType .NVarChar ,4000);
           paraname.Value = name;
           cmd.Parameters.Add(paraname);
           cmd.Connection = conn;
           conn.Open();
           SqlDataReader reader = cmd.ExecuteReader();          
           if (reader.Read())
               id =  reader.GetInt32(0);
           reader.Close();
       }
       return id;
   }

 

这个存储过程实现的功能是根据学生的姓名获取学生的ID,其中name参数代表学生姓名,存储过程的返回值就代表ID。看测试结果

澳门新萄京官方网站 4

 对于output型参数,只需要在参数前添加ref即可,看下面的例子 

[Microsoft.SqlServer.Server.SqlProcedure]
  public static void TestOutPut(ref SqlString output)
  {
      output = "Hello,"  output;
  }

 在此仅仅是做一例子说明问题,查看测试结果

澳门新萄京官方网站 5

存储过程也可以返回结果集,看代码

[Microsoft.SqlServer.Server.SqlProcedure]
   public static void GetStudentByClassID(SqlInt32  id_class)
   {
       using (SqlConnection conn = new SqlConnection("context connection=true"))
       {
           SqlCommand cmd = new SqlCommand();
           cmd.CommandText = "select id_student,name_student from student where id_class =@id";
           SqlParameter paraname = new SqlParameter("@id", SqlDbType.Int );
           paraname.Value = id_class;
           cmd.Parameters.Add(paraname);
           cmd.Connection = conn;
           conn.Open();
           SqlDataReader reader = cmd.ExecuteReader();
           SqlContext.Pipe.Send(reader);
       }      
   }

 上面的代码是根据班级的ID获取学生的信息,测试结果:

澳门新萄京官方网站 6

 在该例子中,也使用到了SqlContext.Pipe.Send(),而且发送的是SqlDataReader类型。该方法目前提供了三个重载方法,除了string、SqlDataReader外,还有SqlDataRecord。SqlDataRecord表示结果中的单个数据行及其元数据。

我们如何实现这些功能呢?
为了使用CLR,我们需要做如下几步:
    1、在.NET中新建一个类,并在这个类里新建一个public方法。
    2、编译这个类为一个DLL。
    3、在SQL Server中注册这个DLL。
    4、新建一个SQL Server函数来访问指定的.NET方法。

--开启所有服务器配置
sp_configure 'show advanced options', 1; 
RECONFIGURE WITH override 
GO 
--开启 CLR
sp_configure 'clr enabled', 1; 
RECONFIGURE WITH override 
GO

五、创建CLR触发器(Trigger)

触发器是数据库服务器中发生时间事自动执行的特殊存储过程。

DML触发器:如果用户通过DML事件数据,则执行DML触发器。DML事件是针对表或视图的insert、update 、或delete语句。

DDL触发器:用于响应各种DDL事件,主要是create、alter、drop语句。

        1、使用C#编写CLR触发器

          为了能够在C#中处理触发器触发时的情况,Microsoft.SqlServer.Server命名空间提供了SqlTriggerContext 类。SqlTriggerContext 类提供所激发的触发器的上下文信息,通过SqlContext.TriggerContext来获得。通过TriggerAction来获得触发的类型,SqlTriggerContext.TriggerAction 属性指示激发触发器的操作。在使用C#编写CLR触发器是有可能用到触发器中的俩张特殊的表:insert和deleted的时候需要使用SqlCommand.如:

SqlConnection connection = new SqlConnection("context connection=true");

connection.Open();//打开链接

SqlCommand sqlcom=new SqlCommand();

sqlcom.CommandText="Select * from " "inserted"; //使用到inserted表

reader=sqlcom.ExecuteReader();//执行SQL语句

reader.Read();//读取数据

for(int columnNumber=0;columnNumber<triggerContext.ColumnCount; columnNumber )

{ //将每一列的列名通过pipe.Send方法发送到客户端

Pipe.Send("Update Column" reader.GetName(columnNumber) "?"

triggerContext.IsUpdateColumn(columnNumber).Tostring());

}

reader.Close();//关闭链接 将C#编写的代码编译成DLL文件后添加到数据库并更新SQL Server中的程序集。

2、在SQL Server中使用CLR触发器

将程序集中的触发器函数添加到SQL Server中,需要用到create trigger命令。

create trigger [schema_name.] trigger_name 
on {table | view} 
[with <dml_trigger_option>[,...n]] 
{for | after | instead of} 
{ [insert] [,] [update] [,] [delete] } 
[with append] 
[not for replication] 
as external name assembly_name.class_name.method_name

其中,external name assembly_name.class_name.method_name用于指定程序集与触发器绑定的方法。该方法不带任何参数而且必需返回空值。

       3、在T-SQL中使用CLR触发器

 

 

 

部署

在SQL Server2005/2008里面,CLR默认是关闭的。可以使用如下SQL语句开启CLR。

查看状态:

sp_configure 'clr enabled'

 开启

exec sp_configure 'clr enabled',1  --1,启用clr,禁用clr
reconfigure

 添加程序集:

create ASSEMBLY [mySQLCLR]
FROM 'd:mySQLCLR.dll'
WITH PERMISSION_SET = SAFE  

 其中,from后面的表示dll文件的路径。利用下面的SQL可以查看该数据库已经注册的程序集。

select * from sys.assemblies 

 注册存储过程:

上述四个存储过程对应的注册SQL语句是:

CREATE PROCEDURE [dbo].[HelloPro]
    @name [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [mySQLCLR].[StoredProcedures].[HelloPro]
GO
 
 
CREATE PROCEDURE [dbo].[GetStudentIdByName]
    @name [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [mySQLCLR].[StoredProcedures].[GetStudentIdByName]
GO
 
 
CREATE PROCEDURE [dbo].[GetStudentByClassID]
    @id_class [int]
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [mySQLCLR].[StoredProcedures].[GetStudentByClassID]
GO
 
 
CREATE PROCEDURE [dbo].[TestOutPut]
    @output [nvarchar](4000) OUTPUT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [mySQLCLR].[StoredProcedures].[TestOutPut]
GO

 也就是

CREATE PROCEDURE 存储过程名 参数
WITH EXECUTE AS CALLER
AS EXTERNAL NAME 数据库中Assembly名称.程序集中Assembly名称.程序方法名

 相关语法在SQL Server的联机丛书中有解释。部署后可以在SQL Server Management Studio中查看

澳门新萄京官方网站 7

 “程序集”里面可以查看到我们部署的项,在“存储过程”里面也包含通过SQLCLR创建的存储过程,与一般存储过程不同的是,这些存储过程的图标上有个“锁”,而且是不可编辑的。

也可以利用SQL Server Management Studio来添加程序集,如下图所示:

澳门新萄京官方网站 8

在弹出的新建程序集窗口中浏览DLL文件就可以添加程序集。

更简单的方法是直接利用Visual Studio来自动部署。在项目上右键,选择“部署”,也可以通过点击调试菜单下的“启动调试”、“开始执行”等来部署,在部署前,需要先连接到数据库(点击项目属性,在数据库选项卡中设置),部署后可以发现,所有的存储过程均部署完毕。

其中,在项目属性的选项卡页面,有“权限级别”的设置,可用于指定向CLR程序集授予何种安全级别。

“安全”:程序集仅能执行本地数据访问和计算任务

“外部”:程序集可以执行本地数据访问和计算任务,还能访问网络、文件系统、注册表和环境变量。

“不安全”:程序集的权限不受限制。

接下来,我们一起来完成一个示例
首先,在Visual Studio中新建一个名为“SQLServerCLRTest”的类库项目。 然后,新建一个名为“CLRFunctions”的类,并在其内添加一个名为“HelloWold”的方法,代码如下:

关闭 CLR:

六、创建用户定义聚合函数(Aggregate)

在SQL Server中,经常需要对数据按组进行自定义的聚合操作,默认的聚合函数只有SUM(),MAX(),MIN(),AVG()等,因此就需要定义用户自定义聚合函数。

1、使用C#编写聚合函数

创建用户自定义聚合函数必须使用特性[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]放置聚合函数的头部,以标识该函数是用户自定义聚合函数。此外创建的聚合函数还必须是可序列化的,使用特性[Serializable]标识。

聚合函数实际上是一个结构类型或者说聚合函数对应的是一个struct类型而不是一个方法,在其中必须实现4个方法:

(1)Init()初始化函数: 为要处理的每组行调用Init()方法。在这个方法中,为要计算的每组行进行初始化;

(2)Accumulate()定义具体聚合操作的函数: 为所有组中的每个值调用这个方法。这个方法的参数必须是正确的累加类型,还可以上用户定义的类型。该函数定义聚合函数的具体聚合操作;

(3)Merge()合并函数: 聚合的结果必须和另一个聚合结果合并起来,调用Merge()方法。

(4)Terminate()结束函数: 在处理每一组的最后一行后,调用该方法。这里,聚合的结果必须用正确的数据类型返回。

编写好聚合函数后重新编译整个项目将DLL文件添加的数据库中。后使用alter assembly命令将聚合到SQL Server的程序集中。

2、在SQL Server中创建用户自定义聚合函数

在SQL Server中创建用户自定义聚合函数以引用CLR中的聚合函数。创建用户自定义聚合函数使用create aggregate命令。如下:

create aggregate [schema_name.] aggregate_name 

@param_name <input_sqltype>[,...n] 

returns <return_type> 
external name assembly_name [.class_name] 
<input_sqltype>::= 
system_scalar_type | {[udt_schema_name.] udt_type_name} 
<return_type>::= 
system_scalar_type | {[udt_schema_name.] udt_type_name}

其中,system_scalar_type:表示要存放输入参数值或返回值的任意一个SQL Server系统标量数据类型。除了text、ntext和image之外的所有标量数据类型,都可以用作自定义聚合函数的参数。不能指定非标量类型(如cursor和table)。 
udt_schema_name:表示CLR用户定义类型所属的架构的名称。如果未指定则数据库按以下顺序引用udt_schema_name:本机SQL类型命名空间、当前数据库中当前用户的默认架构、当前数据库中的dbo架构。

udt_type_name:表示当前数据库中以创建的CLR用户自定义类型的名称。如果未指定udt_schema_name,则SQL Server假定该类型属于当前用户的架构。

assembly_name [.class_name] :表示指定与用户定义的聚合函数绑定在一起的程序集以及(可选)该程序集所属的架构名称和该程序集中实现该用户定义聚合函数的类名称。

3、在T-SQL中使用用户自定义聚合函数

create aggregate CountVowels 

@input nvarchar(4000) 

returns int 
external name TestAssembly.CountVowels 
go 
select City ,COUNT(City) as PersonCount,dbo.CountVowels(City) as CityVowelsCount 
from Person.Address 
group by City 

调试

利用强大的VS,可以很容易的对SQLCLR进行调试,VS2010新建的数据库项目中有个Test.Sql文件,在该文件中可以直接书写测试的T-SQL,也可以添加断点,

澳门新萄京官方网站 9

就像其他项目那样调试,运行到断点后按F11可以进入到相关的.net方法中,即时窗口、变量监视等功能一个都不少。

澳门新萄京官方网站 10

如果在调试的时候报错如下图

澳门新萄京官方网站 11

原因及解决:当 Visual Studio 调试器无法使用要调试的数据库服务器注册用户时,将发生此错误。最可能的原因是缺少对 SQL Server 实例的必要权限。通过拥有 sysadmin 固定服务器角色的登录名将 Transact-SQL 编辑器连接到服务器是不够的;Visual Studio 用户的 Windows 登录名还必须是该 SQL Server 实例上 sysadmin 固定服务器角色的成员。

测试后的结果在“输出”选项卡中显示,细心观察的话,可以发现调试的时候会先将程序集部署。

澳门新萄京官方网站 12public class CLRFunctions 
澳门新萄京官方网站 13澳门新萄京官方网站 14澳门新萄京官方网站 15{
澳门新萄京官方网站 16    public static string HelloWorld(string Name) 
澳门新萄京官方网站 17澳门新萄京官方网站 18    澳门新萄京官方网站 19{
澳门新萄京官方网站 20        return ("Hello "   Name);
澳门新萄京官方网站 21    }
澳门新萄京官方网站 22}

--关闭所有服务器配置
sp_configure 'show advanced options', 0; 
RECONFIGURE WITH override 
GO 
--关闭 CLR
sp_configure 'clr enabled', 0; 
RECONFIGURE WITH override 
GO

七、创建CLR用户定义类型(UDT)

创建CLR用户自定义类型来扩展SQL的类型系统,UDT可用于定义表中的列的类型或T-SQL中的变量或例程(存储过程、触发器等)参数的类型。用户定义类型实例可以是表中的列,比处理、函数或存储过程中的变量,或者函数或者存储过程的参数。

1、使用C#定义类型

用户定义类型必须实现接口INullable,申明IsNull属性表示该类型是否为空值,而且用户定义类型在C#中用一个可序列化的结构体表示,这点和CLR用户自定义聚合函数相同。编写好C#代码后进行编译生成DLL文件并更新到数据库中。

2、在SQL Server中使用CLR用户定义类型

要创建CLR用户定义类型需使用create type命令,不仅可以创建基于SQL数据类型的用户自定义类型,也可以创建基于CLR的用户自定义类型。

create type [schema_name] type_name

external name assembly_name.[class_name]

3、使用CLR用户自定义类型

create type myFirstType

external name myTypeAssembly.myFirstType

go

select table testMyFirstType

(

T myFirstType;

)

go

insert into testMyFirstType

values(‘1,7’);

insert into testMyFirstType

values(‘6,0’);

go

select T

from testMyFirstType

触发器

触发器与存储过程类似,都是.net中的方法,不同的就是方法上的标注信息。

看例子

[Microsoft.SqlServer.Server.SqlTrigger(Name = "Insert_ClassName", Target = "class", Event = "after Insert")]
 public static void Insert_ClassName()
 {
     int id = 0;
     string strSQL = "select id_class, grade_class,class_class from inserted";
     using (SqlConnection conn = new SqlConnection("context connection = true"))
     {
         SqlCommand cmd = new SqlCommand();
         cmd.CommandText = strSQL;
         cmd.Connection = conn;
         conn.Open();
         SqlDataReader reader = cmd.ExecuteReader();
         if( reader .Read ())
         {
             id = int.Parse(reader["id_class"].ToString ());
             strSQL = "update class set name_class ="
                 " '"  reader ["grade_class"] "年级"  reader ["class_class"] "班级' "
                 "where id_class = " id ;
             reader.Close();
             cmd.CommandText = strSQL;
             cmd.ExecuteNonQuery();
         }
     }
 }

 

触发器使用Microsoft.SqlServer.Server.SqlTrigger标记,其中的Name为在数据库中触发器的名字,Target为表名,event是触发器的事件类型。

测试

澳门新萄京官方网站 23

这是一个非常简单的方法(为了让SQL Server可以调用它,它必须要是public和static的),这个方法有一个string类型的参数,返回信息为“Hello”加上你传入的参数。

在后面注册 CLR 程序集时,发生因操作权限问题而导致的失败时,可以尝试执行下面的 SQL 语句,这里我把 SQL 一并贴出来。

函数

在数据库中,函数分为标量值函数、表值函数和聚合函数,标量值函数也就是返回“一个值”,表函数则返回一个结果集。

现在,我们需要编译这个项目为一个DLL,并在SQL Server中注册它。 这也是比较简单的,在VS中右键单击项目,选择“生成”后程序就会生成一个DLL。 如果你的项目是调试模式的话,那么就可以在如下所示那样的路径里找到编译好的DLL。

--权限不够时,设置目标数据库为可信赖的,例如:Test
ALTER DATABASE [Test] SET TRUSTWORTHY ON 

--修改数据库所有者为当前登录的用户,也可以为其他用户,例如:sa
EXEC sp_changedbowner 'sa'

标量值函数(Scalar)

[Microsoft.SqlServer.Server.SqlFunction]
  public static SqlString myFunction(SqlString name)
  {
      return new SqlString("Hello,"  name .ToString ());
  }

 

方法由“SqlFunction”标注,如果该函数需要访问数据库,则需要将标注修改为:[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind .Read)].表示读取数据。SqlFunction还有以下属性:

IsDeterministic:指示用户定义的函数是否是确定性的,是则为 true;否则为 false;

Name :函数在 SQL Server 中注册时所使用的名称。不指定的话就是.net中的方法名;

IsPrecise:指示函数是否涉及不精确的计算,如浮点运算。是则为 true;否则为 false;

FillRowMethodName:方法的名称,该方法与 TVF 协定所使用的表值函数 (TVF) 在同一个类中。(见下一节)

TableDefinition:如果方法用作表值函数 (TVF),则为一个字符串,该字符串表示结果的表定义(见下一节)。

测试如下:

澳门新萄京官方网站 24

澳门新萄京官方网站 25C:Documents and Settingsmark.smithMy DocumentsVisual Studio 2005ProjectsSQLServerCLRTestSQLServerCLRTestbinDebugSQLServerCLRTest.dll

 

表值函数(TVF)

表值函数,返回的是个“表”(结果集),因此,在.net代码中返回的值应该实现Ienumerable的类型,

代码

  class student
   {
       public int ID { get; set; }
       public string Name { get; set; }
   }
[Microsoft.SqlServer.Server.SqlFunction(
       DataAccess = DataAccessKind.Read,
       FillRowMethodName = "F_GetReturnData",
       TableDefinition ="student_id int,student_name nvarchar(50)"
       )]
   public static IEnumerable f_GetStudentByClassId(SqlInt32 classid)
   {
       List<student> students = new List<student>();    
       using (SqlConnection conn = new SqlConnection("context connection= true"))
       {
           SqlCommand cmd = new SqlCommand();
           cmd.CommandText = "select  id_student,name_student from student where id_class =@id";
           SqlParameter paraname = new SqlParameter("@id", SqlDbType.Int);
           paraname.Value = classid;
           cmd.Parameters.Add(paraname);
           cmd.Connection = conn;
           conn.Open();
           SqlDataReader reader = cmd.ExecuteReader ();
           while (reader.Read())
           {
               students .Add (new student (){
                   ID = reader .GetInt32 (0),
                   Name = reader .GetString (1)});
           }             
           reader.Close();
       }
       return students;
   }
   public static void F_GetReturnData(object student, ref SqlInt32 id, ref SqlString name)
   {
       student s = student as student;
       id = s.ID;
       name = s.Name;
 }

 

上面的代码功能是根据班级ID返回学生信息,f_GetStudentByClassId是在SQL Server中可见的函数,方法前加标注SqlFunction,其中FillRowMethodName指定“填充行”(往表中插入行)的方法 ,TableDefinition表示返回的表的结构。如上例,表结构式含有student_id和student_name两个字段,将f_GetStudentByClassId得到的集合在F_GetReturnData中填充给返回的表。需要注意的是,在填充行的方法(本例中的F_GetReturnData)中,第一个字段后面的其他字段要与返回表的字段(TableDefinition中声明的)类型一致,不能调换顺序,否则部署失败。

测试

澳门新萄京官方网站 26

找到这个DLL后,我们就可以把它拷贝到我们的SQL Server机器上了,如果是相同机器的话我们只要记住这个路径即可。

三、CLR Function

聚合函数

目前T-SQL拥有许多的内置聚合函数,如Sum、Count、Max,但内置聚合函数有时候并不能满足需要,利用T-SQL无法新建聚合函数,但是SQL CLR是可以的。

聚合函数也接收参数、返回值。传递给聚合函数的参数通常是一列值,通常与Group By一块使用,将Group By的每个作用域的列传递给该聚合。聚合的任务就是在向其传递每个分力值的时候更新一个变量,将该变量值返回。

先看例子

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
    Format.UserDefined ,
    IsInvariantToNulls =true ,
    IsInvariantToDuplicates =false ,
    IsInvariantToOrder = false ,
    MaxByteSize = 8000)]
public struct myAggregate:IBinarySerialize
{
    public void Init()
    {
        studentname = new StringBuilder();
    }
    public void Accumulate(SqlString Value)
    {
        studentname.Append(Value.Value .ToString ());
        studentname.Append(",");
    }
    public void Merge(myAggregate Group)
    {
        studentname.Append(Group.studentname);
}
    public SqlString Terminate()
    {
        string result = studentname.ToString();
        result = result.Remove (result .LastIndexOf (','));
        return new SqlString(result);
    }
    
    private StringBuilder  studentname;
 
    public void Read(System.IO.BinaryReader r)
    {
        studentname = new StringBuilder(r.ReadString());
    }
 
  public void Write(System.IO.BinaryWriter w)
    {
        w.Write(this.studentname.ToString());
    }
}

 

聚合类必须拥有四个方法:Init,Accumulate、Merge、Terminate。

Init:开始一个新的聚合

Accumulate接受一个SQL类型,将分立值处理为聚合,

Terminate:返回一个SQL类型,在处理所有分力值之后返回最终的聚合值

Merge方法接受一个与该聚合本身类型相同的对象,以便将其与执行实例相合并。(SQL Server有时会将为满足一个查询所作的工作分割到多个线程上,因此需要对一次查询进行多次聚合,然后将结果合并在一起)

上述方法的目的是将同一班级的所有学生的姓名以一个字符串的形式返回。在Accumulate方法中将某个班级的所有学生姓名相加,在Terminate方法中,将最终得到的结果去除多余符号后返回。

测试

澳门新萄京官方网站 27

聚合是SQL CLR的出色应用,因为将待处理的数据值传递给了它们,所以它们仅需要执行计算任务,而不需要进行数据访问。

启用CLR功能
默认情况下,SQL Server中的CLR是关闭的,所以我们需要执行如下命令打开CLR:

打开 Visual Studio 新建一个 SQL Server 数据库项目,这里需要注意 .NET Framework 的版本。
因为我的目标数据库为 SQL Server 2008,所以这里我选择的是 .NET Framework 3.5 的版本。
然后添加新建项,选择 SQL CLR C# 用户自定义函数,先从标量函数开始。

自定义类型(UDT)

从技术上来说,除了数据库本身所提供的类型外,我们可以创建其他类型,比如说对象。

先看例子

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct myType : INullable
{
    private double m_x;
 
    public double X
    {
        get { return m_x; }
        set { m_x = value; }
    }
    private double m_y;
 
    public double Y
    {
        get { return m_y; }
        set { m_y = value; }
    }
 
    private double m_z;
 
    public double Z
    {
        get { return m_z; }
        set { m_z = value; }
    }
public override string ToString()
    {
        if (this.IsNull)
        {
            return "null";
        }
        else
        {
            return this.m_x "-"  this.m_y "-"  this .m_z ;
      }
    }
 
    public bool IsNull
    {
        get
        {
            return m_Null;
 }
}
 
 public static myType Null
    {
        get
        {
            myType h = new myType();
            h.m_Null = true;
            return h;
        }
    }
 
 /// <summary>
    ///组织该类型对外的显示形式
    ///比如,下例是形如“x-y-z”的书写形式,通过该方法,将其分拆为对应字段的值
    /// </summary>
    /// <param name="s"></param>
    /// <returns></returns>
    public static myType Parse(SqlString s)
    {
        if (s.IsNull)
            return Null;
        myType u = new myType();    
        string str = s.ToString();
        string[] xy = str.Split('-' );
        u.X = Convert.ToDouble(xy[0]);
        u.Y = Convert.ToDouble(xy[1]);
        u.Z = Convert.ToDouble(xy[2]);
        return u;
}  
 
    public string show()
    {
        return "X:"  this.Z.ToString() ",Y:"  this.Y.ToString()  ",Z:"  this.Z.ToString();
}
 
    public static double sum(myType p)
    {
        return Math .Sqrt ( p.X * p.X p.Y * p.Y  p.Z  * p.Z );
    }
    public  double GetLength(myType p)
    {
        return Math.Sqrt(p.X * p.X p.Y * p.Y p.Z * p.Z);
    }
    private bool m_Null;
}

 

部署成功后,在表中新建字段,类型选为 myType 类型,保存。

澳门新萄京官方网站 28

测试

澳门新萄京官方网站 29

 

澳门新萄京官方网站 30exec sp_configure 'clr enabled',1   
澳门新萄京官方网站 31reconfigure   
澳门新萄京官方网站 32go 

 

SQLCLR与T-SQL对比

1、 SQLCLR提供编程结构使数据操作和计算更加容易。

T-SQL 专门为数据库中的直接数据访问和操作而设计。尽管 T-SQL 在数据访问和管理方面领先,但是它没有提供编程结构来使数据操作和计算更加容易。例如,T-SQL 不支持数组、集合、for-each 循环、位转移或类。尽管在 T-SQL 中可以模拟其中某些构造,但是托管代码对这些构造提供集成支持。根据方案的不同,这些功能可以为使用托管代码实现某些数据库功能提供令人心动的理由。

2、 对于计算和复杂的执行逻辑,托管代码比 T-SQL 更适合,它全面支持许多复杂的任务,包括字符串处理和正则表达式。

通过 .NET Framework 库中提供的功能,可以访问数千个预生成的类和例程。可以很容易从任何存储过程、触发器或用户定义函数进行访问。基类库 (BCL) 包括的类提供用于字符串操作、高级数学运算、文件访问、加密等的功能。

3、 一般来说,函数和聚合是SQL CLR的出色应用。

SQL CLR 代码的开发人员可以利用 .NET Framework API中存在的大量有用函数和类。这个类/函数库比 T-SQL 中支持的内置函数要丰富得多。此外,CLR 编程语言提供了 T-SQL 中所没有的丰富构造(例如数组和列表等)。与 T-SQL(它是一种解释语言)相比,CLR 编程语言之所以具有更好的性能,是因为托管代码是已编译的。对于涉及算术计算、字符串处理、条件逻辑等的操作,托管代码的性能可能要优于 T-SQL 一个数量级。

4、 托管代码的一个优点是类型安全性,即确保代码只通过正确定义并且权限许可的方式访问类型。

在执行托管代码之前,CLR 将验证代码是否安全。例如,通过检查代码来确保不读取以前未曾写入的内存。CLR 还可以帮助确保代码不操作非托管内存。

5、 开发人员应该将SQLCLR作为一种无法使用T-SQL显式表达逻辑的备选解决方案。

SQLCLR给开发人员提供了另一种编写存储过程的方法,但是利用T-SQL的声明性结构来处理基于集合的数据选择与修改要远远优于在.net中的过程化结构和ADO.NET对象模型中进行处理,因此SQLCLR不能作为实现业务层逻辑的替代品。那么根据这个规则,开发人员应该首先使用T-SQL解决问题。

6、 SQLCLR的局限

尽管其中许多类可以从 SQL Server 的 CLR 代码中使用,但是不适合服务器端使用的类(例如窗口类)将无法使用。

7、 面临的几个选择

1)选择 T-SQL 还是托管代码

在编写存储过程、触发器和用户定义函数时,必须做的一个决定是使用传统的 T-SQL 还是使用 Visual Basic .NET 或 Visual C# 等 .NET Framework 语言。对于几乎或根本不需要过程逻辑的数据访问,请使用 T-SQL。对于具有复杂逻辑的 CPU 密集型函数和过程,或要使用 .NET Framework 的 BCL 时,请使用托管代码。

2)选择在服务器中执行还是在客户端中执行

决定使用 T-SQL 还是托管代码的另一个因素是您希望代码驻留的位置,驻留在服务器计算机上还是客户端计算机上。T-SQL 和托管代码均可以在服务器上运行。这样使代码和数据距离很近,可以利用服务器的处理能力。另一方面,您可能希望避免将处理器密集型任务放在数据库服务器上。现在,大多数客户端计算机非常强大,您可能希望将尽可能多的代码放在客户端上,以利用客户端的处理能力。托管代码可以在客户端计算机上运行,而 T-SQL 不能。

3)选择扩展存储过程还是托管代码

生成的扩展存储过程可以执行 T-SQL 存储过程无法执行的功能。但是,扩展存储过程会影响 SQL Server 进程的完整性,而通过类型安全性验证的托管代码不会。另外,内存管理、线程和构造的调度以及同步服务在 CLR 的托管代码与 SQL Server 之间更深入地集成。通过 CLR 集成,可以通过比扩展存储过程更加安全、可伸缩性更强的方式来编写所需的存储过程,以执行 T-SQL 中无法执行的任务。

注册DLL
为了调用我们写的那个方法,需要在SQL Server中注册我们刚刚编译好的那个DLL。 我们可以在数据库中使用如下命令来注册DLL(路径为你的DLL文件的路径)

1、标量函数

澳门新萄京官方网站 33CREATE ASSEMBLY asmHelloWorld FROM 'C:SQLServerCLRTest.dll'   

public partial class UserDefinedFunctions
{
    /// <summary>
    /// 10进制转16进制
    /// </summary>
    /// <param name="strNumber"></param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true, Name = "ConvertToHexadecimal")]
    public static SqlString ConvertToHexadecimal(SqlString strNumber)
    {
        SqlString result = string.Empty;
        string str = strNumber.ToString();
        int number = 0;
        if (int.TryParse(str, out number))
        {
            result = number.ToString("X");
        }
        return result;
    }

    /// <summary>
    /// 16进制转10进制
    /// </summary>
    /// <param name="strNumber"></param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true, Name = "ConvertToDecimal")]
    public static SqlString ConvertToDecimal(SqlString strNumber)
    {
        SqlString result = string.Empty;
        string str = strNumber.ToString();
        int number = 0;
        try
        {
            number = int.Parse(str, System.Globalization.NumberStyles.HexNumber);
            result = Convert.ToString(number, 10);
        }
        catch
        {
        }
        return result;
    }
}

在SQL Server中调用我们的.NET方法
为了调用.NET方法,我们可以写一个SQL Server自定义函数,并在其内使用“EXTERNAL NAME”来通知SQL Server使用CLR功能。 代码如下:

 

澳门新萄京官方网站 34CREATE FUNCTION dbo.clrHelloWorld   
澳门新萄京官方网站 35(   
澳门新萄京官方网站 36    @name as nvarchar(200)   
澳门新萄京官方网站 37)    
澳门新萄京官方网站 38RETURNS nvarchar(200)   
澳门新萄京官方网站 39AS EXTERNAL NAME asmHelloWorld.[SQLServerCLRTest.CLRFunctions].HelloWorld 

2、表值函数

上面的自定义函数做了两项工作。 首先是声明了一个nvarchar参数,它等同于.NET里的string类型(如果将其设置为varchar并且后面使用了“EXTERNAL NAME”的话就会报错)。然后使用“EXTERNAL NAME”来调用.NET方法。 语法如下:

public partial class UserDefinedFunctions
{
    /// <summary>
    /// SQL Server 字符串分割方法
    /// </summary>
    /// <param name="separator"></param>
    /// <param name="pendingString"></param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction(
        DataAccess = DataAccessKind.Read,
        IsDeterministic = true,
        Name = "SqlSplit",
        FillRowMethodName = "SqlSplit_FillRow",
        TableDefinition = "SerialNumber int,StringValue nvarchar(1024)")]
    public static IEnumerable SqlSplit(SqlString separator, SqlString pendingString)
    {
        string _separator = string.Empty;
        string _pendingString = string.Empty;
        if (separator.IsNull)
        {
            _separator = ",";
        }
        else
        {
            _separator = separator.ToString();
            if (string.IsNullOrEmpty(_separator))
            {
                _separator = ",";
            }
        }

        if (pendingString.IsNull)
        {
            return null;
        }
        else
        {
            _pendingString = pendingString.ToString();
            if (string.IsNullOrEmpty(_pendingString))
            {
                return null;
            }
        }

        string[] strs = _pendingString.Split(new string[] { _separator }, StringSplitOptions.RemoveEmptyEntries);
        if (strs.Length <= 0)
        {
            return null;
        }

        List<ResultData> resultDataList = new List<ResultData>();
        for (int i = 0; i < strs.Length; i  )
        {
            resultDataList.Add(new ResultData(i   1, strs[i]));
        }
        return resultDataList;
    }

    /// <summary>
    /// 填充数据方法
    /// </summary>
    /// <param name="obj"></param>
    /// <param name="serialNumber"></param>
    /// <param name="stringValue"></param>
    public static void SqlSplit_FillRow(Object obj, out SqlInt32 SerialNumber, out SqlString StringValue)
    {
        ResultData resultData = (ResultData)obj;
        SerialNumber = resultData.SerialNumber;
        StringValue = resultData.StringValue;
    }

    /// <summary>
    /// 定义返回类型
    /// </summary>
    public class ResultData
    {
        /// <summary>
        /// 序号,即行号
        /// </summary>
        public SqlInt32 SerialNumber { get; set; }

        /// <summary>
        /// 分割后的每个子字符串
        /// </summary>
        public SqlString StringValue { get; set; }

        public ResultData(SqlInt32 serialNumber, SqlString stringValue)
        {
            SerialNumber = serialNumber;
            StringValue = stringValue;
        }
    }
}

澳门新萄京官方网站 40程序集名.类名.方法名

SqlFunctionAttribute 的属性及介绍:

但是,当我使用这个语法调用.NET方法的时候,SQL Server就会报错,所以为了让它正常工作,我使用了如下语法:

--属性                    --说明
--DataAccess            --指示该函数是否涉及访问存储在SQL Server的数据
--FillRowMethodName        --在同一个类的方法的名称作为表值函数(TVF),这个参数在表值函数中才会用到,用于指定表值函数的数据填充方法
--IsDeterministic        --指示用户定义的函数是否是确定性的
--IsPrecise                --指示函数是否涉及不精确计算,如浮点运算
--Name                    --函数在SQL Server中注册时使用的函数的名称
--SystemDataAccess        --指示该函数是否需要访问存储在系统目录或SQL Server虚拟系统表中的数据
--TableDefinition        --如果方法作为表值函数(TVF),则为一个字符串,该字符串表示表结构的定义

澳门新萄京官方网站 41程序集名.[类名].方法名

标量函数与表值函数可以写在同一个类文件里面,并且可以包含多个,但是聚合函数就不行了,现在需要添加一个新项,选择 SQL CLR C# 聚合。

现在我们就可以通过如下语句调用.NET方法了:

 

澳门新萄京官方网站 42SELECT dbo.clrHelloWorld('Mark')

3、聚合函数

当你运行这段代码的时候,就会得到一个返回结果“Hello Mark”。

我这里写的这个聚合函数的作用是把多个字符串拼为一个字符串,我之前还真有遇到这种情况需要的。

我们通过一个很简单的示例演示了如何实现SQL Server的CLR,它可以给我们带来很多非常有用的帮助。

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
    Format.UserDefined, 
    IsInvariantToDuplicates = false, 
    IsInvariantToNulls = true, 
    IsInvariantToOrder = false, 
    MaxByteSize = 8000, 
    Name = "SumString")]
public struct UserDefinedSqlAggregate : IBinarySerialize
{
    private StringBuilder stringBuilder;

    /// <summary>
    /// 查询处理器使用此方法初始化聚合的计算
    /// </summary>
    public void Init()
    {
        stringBuilder = new StringBuilder();
    }

    /// <summary>
    /// 查询处理器使用此方法累计聚合值
    /// </summary>
    /// <param name="Value"></param>
    public void Accumulate(SqlString Value)
    {
        stringBuilder.Append(string.Format("{0},", Value));
    }

    /// <summary>
    /// 查询处理器使用此方法合并聚合的多个部分计算的值
    /// </summary>
    /// <param name="Group"></param>
    public void Merge(UserDefinedSqlAggregate Group)
    {
        stringBuilder.Append(Group.stringBuilder);
    }

    /// <summary>
    /// 此方法用于返回完成聚合计算的结果
    /// </summary>
    /// <returns></returns>
    public SqlString Terminate()
    {
        return new SqlString(stringBuilder.ToString());
    }

    #region Implement interface IBinarySerialize
    /// <summary>
    /// 读
    /// </summary>
    /// <param name="r"></param>
    public void Read(System.IO.BinaryReader r)
    {
        stringBuilder = new StringBuilder(r.ReadString());
    }

    /// <summary>
    /// 写
    /// </summary>
    /// <param name="w"></param>
    public void Write(System.IO.BinaryWriter w)
    {
        w.Write(stringBuilder.ToString());
    }
    #endregion
}

SqlUserDefinedAggregateAttribute 的属性及介绍:

--属性                        --说明
--Format                    --选择序列化的 Format 格式,默认选择 Native,表示使用本地序列化格式。如果选择 UserDefined,则聚合类需要实现 IBinarySerialize 接口
--IsInvariantToDuplicates    --指示聚合是否与重复的值相计算保持不变
--IsInvariantToNulls        --指示聚合是否与空值相计算保持不变
--IsInvariantToOrder        --指示聚合最后计算的结果是否与顺序无关
--IsNullIfEmpty                --指示在没有对任何值进行累计时,聚合返回值是否为 null 
--MaxByteSize                 --聚合实例的最大大小(以字节为单位)
--Name                        --聚合函数的名称

然后生成项目,接下来注册程序集和注册函数就可以使用了。

 

4、注册 CLR 程序集

注册程序集的方式有以下两种:

第一种,这种方式注册程序集比较简单,但是缺点就是程序集不能移动或删除。

--注册CLR程序集方式一,指定程序集DLL的路径
USE Test 
GO 
CREATE ASSEMBLY UserDefinedClrAssembly 
--AUTHORIZATION sa        --指定数据库所有者,默认为当前用户
FROM 'C:UsersAdministratorDesktopCLR AssemblyUserDefinedSqlClr.dll'        --指定文件路径
WITH PERMISSION_SET = UNSAFE;        --指定程序集的权限
                                --SAFE:无法访问外部系统资源;
                                --EXTERNAL_ACCESS:可以访问某些外部系统资源;
                                --UNSAFE:可以不受限制的访问外部系统资源
GO 

这里如果发生因为程序集拒绝访问的错误,那就把计算机用户 Everyone 的权限改为完全控制就可以了。

第二种,这种方式注册程序集稍微复杂一些,但是好处就是注册成功之后,可以移动甚至删除DLL文件,只要不是变更迁移数据库,都不用重新注册。

--注册CLR程序集方式二,指定程序集DLL的16进制文件流
USE Test 
GO 
CREATE ASSEMBLY UserDefinedClrAssembly 
--AUTHORIZATION sa        --指定数据库所有者,默认为当前用户
FROM 0x4D5A90000300000004000000FFFF0000B8000000000000004000000000    --指定DLL的16进制文件流(当然没这么少,我删掉了)
WITH PERMISSION_SET = UNSAFE;        --指定程序集的权限
                                --SAFE:无法访问外部系统资源;
                                --EXTERNAL_ACCESS:可以访问某些外部系统资源;
                                --UNSAFE:可以不受限制的访问外部系统资源
GO 

获取DLL的16进制文件流,可以使用 UltraEdit 这个软件,具体操作方法这里就不多说了。

注册成功之后,可以使用下面的 SQL 语句查看程序集的信息,还包括查询自定义的函数、存储过程等的SQL语句,这个下面注册函数之后可以用到。

--查看程序集信息
SELECT * FROM sys.assemblies 

--查看模块信息,即自定义函数、视图、存储过程、触发器等等
SELECT * FROM sys.sql_modules
GO 

 

5、注册函数

下面是三种函数的注册方式的 SQL 语句。

USE Test 
GO 

--注册标量函数 ConvertToHexadecimal 
CREATE FUNCTION [dbo].[ConvertToHexadecimal](@strNumber NVARCHAR(128))
RETURNS NVARCHAR(128) 
WITH EXECUTE AS CALLER        --用于在用户在执行函数的时候对引用的对象进行权限检查
AS 
EXTERNAL NAME [UserDefinedClrAssembly].[UserDefinedFunctions].[ConvertToHexadecimal]    --EXTERNAL NAME 程序集名.类名.方法名
GO 

--注册标量函数 ConvertToDecimal 
CREATE FUNCTION [dbo].[ConvertToDecimal](@strNumber NVARCHAR(128))
RETURNS NVARCHAR(128) 
WITH EXECUTE AS CALLER        --用于在用户在执行函数的时候对引用的对象进行权限检查
AS 
EXTERNAL NAME [UserDefinedClrAssembly].[UserDefinedFunctions].[ConvertToDecimal]    --EXTERNAL NAME 程序集名.类名.方法名
GO 

--注册表值函数 SqlSplit 
CREATE FUNCTION [dbo].[SqlSplit](@separator NVARCHAR(32),@string NVARCHAR(MAX))
RETURNS TABLE 
(
    SerialNumber INT,
    StringValue NVARCHAR(1024)
)
WITH EXECUTE AS CALLER        --用于在用户在执行函数的时候对引用的对象进行权限检查
AS 
EXTERNAL NAME [UserDefinedClrAssembly].[UserDefinedFunctions].[SqlSplit]    --EXTERNAL NAME 程序集名.类名.方法名
GO 

--注册聚合函数 SumString 
CREATE AGGREGATE [dbo].[SumString](@params NVARCHAR(128))
RETURNS NVARCHAR(MAX) 
EXTERNAL NAME [UserDefinedClrAssembly].[UserDefinedSqlAggregate]    --EXTERNAL NAME 程序集名.类名
GO 

注册函数成功之后,接下来测试一下。

DECLARE @TempTable TABLE
(
    Id INT NOT NULL,
    Name NVARCHAR(32) NOT NULL 
)
INSERT INTO @TempTable (
    Id,
    [Name]
)
SELECT '1','小张' UNION ALL 
SELECT '2','小明' UNION ALL 
SELECT '2','小丽' UNION ALL 
SELECT '2','小李' UNION ALL 
SELECT '3','小王' UNION ALL 
SELECT '3','小舞' 

SELECT dbo.ConvertToHexadecimal('15')

SELECT dbo.ConvertToDecimal('FC')

SELECT * FROM SqlSplit(',',',123,456,789,')

SELECT Id,dbo.SumString([Name]) Names 
FROM @TempTable 
GROUP BY Id 

结果如图。

澳门新萄京官方网站 43

 

下面是删除函数和删除程序集的 SQL 语句,虽然可能用不到,但是还是贴出来吧。

这里需要注意的是,删除程序集时要保证不存在函数、存储过程、触发器等对程序集的引用。

--删除标量函数 ConvertToHexadecimal 
DROP FUNCTION dbo.ConvertToHexadecimal

--删除标量函数 ConvertToDecimal 
DROP FUNCTION dbo.ConvertToDecimal

--删除表值函数 SqlSplit 
DROP FUNCTION dbo.SqlSplit

--删除聚合函数 SumString 
DROP FUNCTION dbo.SumString

--删除程序集 UserDefinedClrAssembly 
DROP ASSEMBLY UserDefinedClrAssembly

 

本想一篇写完的,还是算了,存储过程和触发器留待下一篇。

其实存储过程和触发器也没什么了,只是 C# 代码不一样而已,其他注册之类的大同小异。

 

这里推荐一篇博客,大家也可以去看这篇,写得还是挺完整的,有些地方都是借鉴于此。

 

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:澳门新萄京官方网站:自定义函数

关键词: