SQL_Server_CLR全攻略.doc

1、SQL Server CLR 全攻略CLR 介绍和配置Microsoft SQL Server 现在具备与 Microsoft Windows .NET Framework 的公共语言运行时 (CLR) 组件集成的功能。CLR 为托管代码提供服务，例如跨语言集成、代码访问安全性、对象生存期管理以及调试和分析支持。对于 SQL Server 用户和应用程序开发人员来说，CLR 集成意味着您现在可以使用任何 .NET Framework 语言（包括 Microsoft Visual Basic .NET 和 Microsoft Visual C#）编写存储过程、触发器、用户定义类型、用户定义函数（

2、标量函数和表值函数）以及用户定义的聚合函数。 下面列出了这一集成的其中一些主要优点： 更好的编程模型。 改进了安全和安全性。 能够定义数据类型和聚合函数。 通过标准化环境简化了开发。 具备改善性能和可扩展性的潜力。 既然 CLR 这么灵活，那么我们如何在 T-SQL 和 CLR 之间做出选择呢？一般情况下，我们当以 T-SQL 为主，在下面几种情况下，应该考虑使用 CLR： SQL 中涉及大量的逻辑判断和逻辑运算。比如需要在数据库级别自定义加密算法，解密算法等。 T-SQL 无法处理需求。比如需要在 SQL 中进行正规表达式的判断等。 逻辑判断过于复杂，需要使用大量游标进行处理。这种情况一般要

3、进行测试，因为 CLR 不一定比游标快，关键是看你怎么写。下面介绍一下如何开启 CLR 和两种创建 CLR 的方法。关于如何在 VS 中编写 CLR 程序，请看后续章节。 开启 CLR。 在 SQL Server2005/2008 里面， CLR 默认是关闭的。可以使用如下 SQL 语句开启 CLR。 sp_configure show advanced options, 1; GO RECONFIGURE; GO sp_configure clr enabled, 1; GO RECONFIGURE; GO创建 CLR。 方法一：使用 DLL 文件进行创建 CREATE ASSEMBLY A

4、ssemblyName from DLLPath 例如：CREATE ASSEMBLY AssemblyName from c:hello.dll 方法二:使用文件 16 进制流进行创建 CREATE ASSEMBLY AssemblyName from 文件十六进制流 例如:CREATE ASSEMBLY AssemblyName from 0x123344 其中 0x12334是文件 c:hello.dll 的十六进制流。可以使用 UltraEdit 等编辑器把相关 dll文件的十六进制流 copy 出来。相比较，方法一简单，属于外部引用，通过创建就可以知道是哪个文件。但是 dll 文件路

5、径必须可以被 SQLServer 所访问，且部署时 dll 要随 SQLServer 变化而变化。 方法二是把流写进 SQLServer 中，从一定程度上屏蔽了代码。如果此 dll 不大的话，推荐使用方法二来创建 CLR。本文来自 CSDN 博客，转载请标明出处：http:/ 存储过程从这一节开始呢，我们就要开始 CLR 的编程之旅了。在这之前，我先把本节中需要了解的两个新类 SqlDataRecord 和 SqlMetaData，及五个新方法SqlContext.Pipe.SendResultsStart，SqlContext.Pipe.SendResultsRow，SqlContext.P

6、ipe.SendResultsEnd，SqlContext.Pipe.Send 和 SqlContext.Pipe.ExecuteAndSend 进行一下必要的说明，方便大家阅读后续的代码。首先 SqlDataRecord 和 SqlMetaData 是数据集合和原数据的意思。可以简单的把SqlDataRecord 理解成 DataTable，把 SqlMetaData 理解成 DataColumn。我们再向SqlDataRecord 里面填充数据之前要先执行 SqlContext.Pipe.SendResultStart()方法，告诉数据库下面开始填充数据，使用 SqlContext.Pip

7、e.SendResultRow 方法来填充数据，填充结束后使用 SqlContext.Pipe.SendResultEnd 方法来结束填充。这些都是基本流程，没什么好解释的，只要照着去做就可以了。SqlContext.Pipe.Send 是向客户端发送一条结果， SqlContext.Pipe.ExecuteAndSend 是执行一条语句。下面我将用几个实际的简单例子来说明如何使用这几个方法。1.使用 SqlContext.Pipe.Send 构建无参无返回值的存储过程 Microsoft.SqlServer.Server.SqlProcedure public static void USP

8、_SayHello() SqlContext.Pipe.Send(“USP:Hello TJVictor!“); 2.使用 SqlContext.Pipe.Send 构建带参无返回值的存储过程 Microsoft.SqlServer.Server.SqlProcedure public static void USP_SayHelloByParameter(SqlString msg) SqlContext.Pipe.Send(msg.ToString(); 3.使用 SqlContext.Pipe.Send 构建带参有返回值的存储过程 Microsoft.SqlServer.Server.S

9、qlProcedure public static SqlInt32 USP_SayHelloByReturn(SqlString msg) return msg.ToString().Length; 4.使用 SqlCommand 来执行语句，注意这里使用了 SQL Server 自带的 pubs 数据库 Microsoft.SqlServer.Server.SqlProcedure public static void USP_ExecuteBySqlCommand(SqlString stor_id, SqlString stor_name) /由于程序是在 SQL Server 内执行

10、，所以连接字符串写成“context connection=true“即可 using (SqlConnection con = new SqlConnection(“context connection=true“) con.Open(); SqlCommand com = new SqlCommand( string.Format(“insert into stores values(0,1)“, stor_id, stor_name), con); com.ExecuteNonQuery(); 5.使用 ExecuteAndSend 来执行语句，注意这里使用了 SQL Server 自带

11、的 pubs 数据库 Microsoft.SqlServer.Server.SqlProcedure public static void USP_ExecuteByExecuteAndSend(SqlString stor_id, SqlString stor_name) /由于程序是在 SQL Server 内执行，所以连接字符串写成“context connection=true“即可 using (SqlConnection con = new SqlConnection(“context connection=true“) con.Open(); SqlCommand com = n

12、ew SqlCommand( string.Format(“insert into stores values(0,1)“, stor_id, stor_name), con); SqlContext.Pipe.ExecuteAndSend(com); 4 和 5 的执行结果一样，但是在 CLR 中推荐使用方式 5，这是将结果返回到客户端的最高效方法，因为数据不必复制到托管内存即传输到网络缓冲区。6.使用 PipeSend 来发送单条记录 Microsoft.SqlServer.Server.SqlProcedure public static void UPS_PipeSendSqlData

13、Record() /像构造 Table 一样来构造 SqlDataRecord，其中 SqlMetaData 类似 DataColumn SqlDataRecord dataRecord = new SqlDataRecord(new SqlMetaData new SqlMetaData(“Col1“, SqlDbType.NVarChar,100), new SqlMetaData(“Col2“, SqlDbType.Int) ); for (int count = 1; count / 使用 SqlContext.Pipe.Send 构建无参无返回值的存储过程 / Microsoft.S

14、qlServer.Server.SqlProcedure public static void USP_SayHello() SqlContext.Pipe.Send(“USP:Hello TJVictor!“); / / 使用 SqlContext.Pipe.Send 构建带参无返回值的存储过程 / Microsoft.SqlServer.Server.SqlProcedure public static void USP_SayHelloByParameter(SqlString msg) SqlContext.Pipe.Send(msg.ToString(); / / 使用 SqlCon

15、text.Pipe.Send 构建带参有返回值的存储过程 / Microsoft.SqlServer.Server.SqlProcedure public static SqlInt32 USP_SayHelloByReturn(SqlString msg) return msg.ToString().Length; / / 使用 SqlCommand 来执行语句，注意这里使用了 SQL Server 自带的 pubs 数据库 / / Microsoft.SqlServer.Server.SqlProcedure public static void USP_ExecuteBySqlComma

16、nd(SqlString stor_id, SqlString stor_name) /由于程序是在 SQL Server 内执行，所以连接字符串写成“context connection=true“即可 using (SqlConnection con = new SqlConnection(“context connection=true“) con.Open(); SqlCommand com = new SqlCommand( string.Format(“insert into stores (stor_id,stor_name) values(0,1)“, stor_id, sto

17、r_name), con); com.ExecuteNonQuery(); / / 使用 ExecuteAndSend 来执行语句，注意这里使用了 SQL Server 自带的 pubs 数据库 / / Microsoft.SqlServer.Server.SqlProcedure public static void USP_ExecuteByExecuteAndSend(SqlString stor_id, SqlString stor_name) /由于程序是在 SQL Server 内执行，所以连接字符串写成“context connection=true“即可 using (SqlC

18、onnection con = new SqlConnection(“context connection=true“) con.Open(); SqlCommand com = new SqlCommand( string.Format(“insert into stores (stor_id,stor_name) values(0,1)“, stor_id, stor_name), con); SqlContext.Pipe.ExecuteAndSend(com); / / 使用 PipeSend 来发送单条记录 / Microsoft.SqlServer.Server.SqlProced

19、ure public static void UPS_PipeSendSqlDataRecord() /像构造 Table 一样来构造 SqlDataRecord，其中 SqlMetaData 类似 DataColumn SqlDataRecord dataRecord = new SqlDataRecord(new SqlMetaData new SqlMetaData(“Col1“, SqlDbType.NVarChar,100), new SqlMetaData(“Col2“, SqlDbType.Int) ); for (int count = 1; count / 使用 PipeSe

20、ndResult 来发送结果集 / Microsoft.SqlServer.Server.SqlProcedure public static void UPS_PipeSendResultSqlDataRecord() /像构造 Table 一样来构造 SqlDataRecord，其中 SqlMetaData 类似 DataColumn SqlDataRecord dataRecord = new SqlDataRecord(new SqlMetaData new SqlMetaData(“Col1“, SqlDbType.NVarChar,100), new SqlMetaData(“Co

21、l2“, SqlDbType.Int) ); /开始填充 SqlContext.Pipe.SendResultsStart(dataRecord); for (int count = 0; count 10; count+) /SqlDataRecord.SetString 类似 DataRow 的功能，像 Table 中填充值 dataRecord.SetString(0, count.ToString(); dataRecord.SetInt32(1, count); /通过 SendResultsRow 把数据填充到 Table，相关于 Table.Rows.Add(DataRow);

22、SqlContext.Pipe.SendResultsRow(dataRecord); /填充结束，返回结果集 SqlContext.Pipe.SendResultsEnd(); ; CLR 标量函数、表值函数和聚合函数(UDA)本节主要介绍使用 CLR 创建标量函数，表值函数和聚合函数。所谓标量函数指的就是此函数只返回一个值。表值函数返回值是一个表。聚合函数是在select 语句中使用的，用来聚合一个结果集，类似于 Sum()或是 Count()等内置的函数，而且真正的自定义聚合函数目前只能用 CLR 来实现。下面的例子使用了 SQLServer 自带的 pubs 数据库。1.CLR 标量函

23、数1.1 无参函数 / / 标量函数，不带参数 / / Microsoft.SqlServer.Server.SqlFunction( DataAccess = DataAccessKind.Read, IsDeterministic = true) public static SqlString UF_Scalar_SayHello() string returnValue = “null“; /由于程序是在 SQL Server 内执行，所以连接字符串写成“context connection=true“即可 using (SqlConnection conn = new SqlConne

24、ction(“context connection=true“) conn.Open(); SqlCommand com = new SqlCommand(“select top 1 au_lname from dbo.authors“,conn); using (SqlDataReader dr = com.ExecuteReader(CommandBehavior.CloseConnection) if (dr.Read() returnValue = dr.GetString(0);/返回 au_lname return returnValue;/返回“null ” CLR 函数用 Mi

25、crosoft.SqlServer.Server.SqlFunction 特征进行修饰。里面的参数含义为：DataAccess = DataAccessKind.Read 表示可访问数据表。关于 SqlFunctionAttribute 的属性将附录在文章的最后。/ / 标量函数，带参数 / / / Microsoft.SqlServer.Server.SqlFunction public static SqlString UF_Scalar_SayHelloByPar(SqlString par) return par; 2.CLR 表值函数表值与标量函数有些不同。因为要返回一个数据集合，所以一定要用一个填充数据的方法，在属性中用 FillRowMethodName 来表示，且返回值应该为 IEnumerable 类型。代码如下：1.首先自定义返回类型