.NET编程和SQL Server ——Sql Server 与CLR集成 （学习笔记整理-1）

kidys

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

1、 SQL Server 提供的存储过程、函数等十分有限，经常需要外部的代码来执行一些繁重的移植；

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

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

4、.NET的操作代码和执行的速度比T-SQL快的很多。.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 {|}
[with permission_set={safe|external_access|unsafe}]

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

：表示组成程序集和依赖程序集的二进制值的列表。

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

（3）修改程序集

alter assembly assembly_name

[from |]
[with [,....n]]
[drop file{file_name[,....n]|all}]
[add file from client_file_specifier [as file_name]|file_bits as file_name}[,....n]][;]

其中，：：=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 |]

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和所有相关程序集。

三、创建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  [,...n]]
[as]external name assembly_name.class_name.method_name

其中external name assembly_name.class_name.method_name：指定将程序集与函数绑定的方法。：：=｛[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
[with [,...n] ]
[order()]
[as]external name assembly_name.class_name.method_name[;]
其中，::=({column_name data_type}[,...n])定义CLR函数的表数据类型。表声明仅包含列名称和数据类型。表始终放在主文件组中。 order()指定从表值函数中返回结果的顺序。

3、在T-SQL中使用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  [,...n]]
[for replication]
as external name assembly_name.class_name.method_name [;]

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

::=[encryption] [recompile]

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

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

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

五、创建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