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

原文:.NET编程和SQL Server ——Sql Server 与CLR集成 (学习笔记整理-1)

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

三、创建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(class_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函数

四、创建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存储过程

 

 

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

 

 

 

六、创建用户定义聚合函数(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

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

 

时间: 2014-11-18

.NET编程和SQL Server ——Sql Server 与CLR集成 (学习笔记整理-1)的相关文章

使用 SQL Server 2005中的 CLR 集成

本文描述了数据库应用程序开发人员和架构师如何利用 SQL Server 2005 中的 CLR 集成功能.本文对基于 CLR 的编程方式与 SQL Server 中支持的现有编程模型(如 TransacT-SQL 和扩展存储过程)进行了比较,并且强调了各自相对的优缺点.还提供了一组选择合适的编程替代方法的高级指导,以及一些示例和代码示例. 一.简介 Microsoft 通过宿主 Microsoft .NET Framework 2.0 公共语言运行库 (CLR),SQL Server 2005显

Ruby元编程基础学习笔记整理_python

笔记一:代码中包含变量,类和方法,统称为语言构建(language construct). # test.rb class Greeting def initialize(text) @text = text end def welcome @text end end my_obj = Greeting.new("hello") puts my_obj.class puts my_obj.class.instance_methods(false) #false means not in

SQL Server学习笔记

<SQL Server从入门到精通>学习笔记 1 数据管理技术的三个阶段:人工管理.文件系统.数据库系统. 2 数据库的组成:数据库.数据库管理系统.数据库管理员.硬件平台.软件平台5部分. 3 数据库的三级模式:内模式.模式.外模式. 4 三级模式之间的映射:外模式/模式,模式/内模式. 5 常用的数据库数据模型:层次模型.网状模型.关系模型. 6 关系数据库的规范化: (1)1NF:原子性,字段不可分. (2)2NF:唯一性,有主键,非主键关键字依赖于主键. (3)3NF:去除传递依赖(非

初探SQL Server CLR 集成

sqlserver 2005中,加入了很多新特性.对于开发人员来讲,最值得一提的是对XML的支持和CLR集成.当然,还有一些关于"高可用"(数据库集群/数据库镜像/日志传送等)的新特性,比较适合DBA,感兴趣的朋友可以了解一下. 相信很多人和我一样,一直很希望在sqlserver中非常灵活地编程,实现各种功能(能像在VS中使用C#编程一样的爽).但是未能如愿,暂不说开发环境的问题(代码补全,配色等).最主要的是,T-SQL这东西确实不适合做复杂的运算(对我这种菜鸟来讲确实有难度.对那些

《SQL Server企业级平台管理实践》读书笔记——关于SQL Server数据库的备份方式

原文:<SQL Server企业级平台管理实践>读书笔记--关于SQL Server数据库的备份方式 数据备份一直被认为数据库的生命,也就是一个DBA所要掌握的主要技能之一,本篇就是介绍SQL Server备份原则,SQL Server数据库分为数据文件和日志文件.为了使得数据库能够恢复一致点,备份不仅需要拷贝数据数据文件里的内容,还要拷贝日志文件里的内容.那么根据每次备份的目标不同,我们可以将备份分为数据备份和日志备份. 数据备份的范围可以是完整的数据库.部分数据库.一组文件或文件组.所以根

《SQL Server企业级平台管理实践》读书笔记——几个系统库的备份与恢复

原文:<SQL Server企业级平台管理实践>读书笔记--几个系统库的备份与恢复 master数据库 master作为数据库的主要数据库,记录着SQL Server系统的所有系统级信息,例如登录用户.系统配置设置.端点和凭证以及访问其他数据服务器所需要的信息.master数据库还记录着启动服务器实例所需要的初始化信息,每个其它数据库的主文件位置.master数据库是SQL Server启动的时候打开的第一个数据库.SQL Server是从这个数据库里找到其它数据的信息的.如果master数据

《SQL Server企业级平台管理实践》读书笔记——SQL Server中数据文件空间使用与管理

原文:<SQL Server企业级平台管理实践>读书笔记--SQL Server中数据文件空间使用与管理 1.表和索引存储结构 在SQL Server2005以前,一个表格是以一个B树或者一个堆(heap)存放的.每个B树或者堆,在sysindexes里面都有一条记录相对应.SQL Server2005以后,引入了分区表的概念(Table Partition),在存储组织上,现有的分区基本上替代了原来表格的概念,原先表的概念成为了一个逻辑概念.一个分区就是一个B树或者一个堆.而一张表格则是一个

《SQL Server企业级平台管理实践》读书笔记——SQL Server数据库文件分配方式

原文:<SQL Server企业级平台管理实践>读书笔记--SQL Server数据库文件分配方式 1.文件分配方式以及文件空间检查方法 最常用的检查数据文件和表大小的命令就是:sp_spaceused 此命令有三个缺陷:1.无法直观的看出每个数据文件和日志文件的使用情况.2.这个存储过程依赖SQL Server存储在一些系统视图里的空间使用统计信息计算出的结果,如果没有更新空间统计信息,比如刚刚发生大数据插入,sp_spaceused的结果就不准确.3.这个命令主要是针对普通用户的数据库,对

《SQL Server企业级平台管理实践》读书笔记——当我们的备份都已经损坏的时候该怎么办

原文:<SQL Server企业级平台管理实践>读书笔记--当我们的备份都已经损坏的时候该怎么办 作为数据库管理员最最痛苦的莫过于,当数据库宕机的时候需要找备份,但在这个时候突然发现备份文件也是坏的,这就意味着数据会丢失,为此可能会丢掉职位,饭碗不保,所以为此,我们一定要保证好备份的完整性,一般发生这种情况的原因莫过于一下几种: 1.备份文件和数据库放在同一个(或一组)的物理磁盘上.磁盘出现故障,备份也保不住了. 2.备份介质随坏,或者做的是网络备份,数据在网络传输中发生了损坏. 3.数据库在