SQL Server CLR全功略之二---CLR存储过程_mssql学习_编程技术-你的首页-uuhomepage.com从这一节开始呢，我们就要开始CLR的编程之旅了。在这之前，我先把本节中需要了解的两个新类SqlDataRecord和SqlMetaData，及五个新方法SqlContext.Pipe.SendResultsStart，SqlContext.Pipe.SendResultsRow，SqlContext.Pipe.SendResultsEnd，SqlContext.Pipe.Send和SqlContext.Pipe.ExecuteAndSend进行一下必要的说明，方便大家阅读后续的代码。首先SqlDataRecord和SqlMetaData是数据集合和原数据的意思。可以简单的把SqlDataRecord理解成DataTable，把SqlMetaData理解成DataColumn。我们再向SqlDataRecord里面填充数据之前要先执行SqlContext.Pipe.SendResultStart()方法，告诉数据库下面开始填充数据，使用SqlContext.Pipe.SendResultRow方法来填充数据，填充结束后使用SqlContext.Pipe.SendResultEnd方法来结束填充。这些都是基本流程，没什么好解释的，只要照着去做就可以了。SqlContext.Pipe.Send是向客户端发送一条结果，SqlContext.Pipe.ExecuteAndSend是执行一条语句。下面我将用几个实际的简单例子来说明如何使用这几个方法。1.使用SqlContext.Pipe.Send构建无参无返回值的存储过程 [Microsoft.SqlServer.Server.SqlProcedure] public static void USP_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.SqlProcedure] 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内执行，所以连接字符串写成"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自带的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 = new 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_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 < 5; count++) { //SqlDataRecord.SetString类似DataRow的功能，像Table中填充值 dataRecord.SetString(0, count.ToString()); dataRecord.SetInt32(1, count); //通过Send来发送 SqlContext.Pipe.Send(dataRecord); } }7.使用PipeSendResult来发送结果集 [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("Col2", SqlDbType.Int) }); //开始填充 SqlContext.Pipe.SendResultsStart(dataRecord); for (int count = 0; count < 5; count++) { //SqlDataRecord.SetString类似DataRow的功能，像Table中填充值 dataRecord.SetString(0, count.ToString()); dataRecord.SetInt32(1, count); //通过SendResultsRow把数据填充到Table，相关于Table.Rows.Add(DataRow); SqlContext.Pipe.SendResultsRow(dataRecord); } //填充结束，返回结果集 SqlContext.Pipe.SendResultsEnd(); }其中6与7的不同在于：6返回5个结果集，其中每个结果集只有一条数据。7返回一个结果集，里面有5条数据。最后说一下CLR存储过程的部署: Create proc 存储过程名 as EXTERNAL NAME 数据库中Assembly名称.程序集中Assembly名称.程序方法名。注意，如果你的程序中有命名空间的话，要这样写：Create proc 存储过程名 as EXTERNAL NAME 数据库中Assembly名称.“程序命名空间.程序集中Assembly名称”.程序方法名。下面的SQL是创建CLR程序集和CLR存储过程的SQL语句，假设我们编译好的dll位于C:\CLRDemo.dll，我们的程序没有命名空间(默认情况下，新建的SQL工程都没有命名空间，请注意) use pubs go create assembly CLRDemoAssemly from 'c:\CLRDemo.dll' go create proc USP_SayHello as EXTERNAL NAME CLRDemoAssemly.StoredProcedures.USP_SayHello go create proc USP_SayHelloByParameter (@Msg nvarchar(128)) as EXTERNAL NAME CLRDemoAssemly.StoredProcedures.USP_SayHelloByParameter go create proc USP_SayHelloByReturn (@Msg nvarchar(128)) as EXTERNAL NAME CLRDemoAssemly.StoredProcedures.USP_SayHelloByReturn go create proc USP_ExecuteBySqlCommand (@Id nvarchar(4),@Name nvarchar(32)) as EXTERNAL NAME CLRDemoAssemly.StoredProcedures.USP_ExecuteBySqlCommand go create proc USP_ExecuteByExecuteAndSend (@Id nvarchar(4),@Name nvarchar(32)) as EXTERNAL NAME CLRDemoAssemly.StoredProcedures.USP_ExecuteByExecuteAndSend go create proc UPS_PipeSendSqlDataRecord as EXTERNAL NAME CLRDemoAssemly.StoredProcedures.UPS_PipeSendSqlDataRecord go create proc UPS_PipeSendResultSqlDataRecord as EXTERNAL NAME CLRDemoAssemly.StoredProcedures.UPS_PipeSendResultSqlDataRecord go调用方式： exec USP_SayHello go exec USP_SayHelloByParameter 'Hello,TJVictor again' go declare @Result int exec @Result=USP_SayHelloByReturn 'Hello,TJVictor again' select @Result go exec USP_ExecuteBySqlCommand '1234','Test USP_ExecuteBySqlCommand' go exec USP_ExecuteByExecuteAndSend '5678','Test USP_ExecuteByExecuteAndSend' go exec UPS_PipeSendSqlDataRecord go exec UPS_PipeSendResultSqlDataRecord go删除方式：注意删除Assembly时，一定要先把引用此Assembly的所有东西删除。 drop proc USP_SayHello drop proc USP_SayHelloByParameter drop proc USP_SayHelloByReturn drop proc USP_ExecuteBySqlCommand drop proc USP_ExecuteByExecuteAndSend drop proc UPS_PipeSendSqlDataRecord drop proc UPS_PipeSendResultSqlDataRecord go drop assembly CLRDemoAssemly下面附带完整程序源代码