SQL Server CLR全功略之二---CLR存储过程
2024-08-31 00:53:07
供稿：网友
从这一节开始呢，我们就要开始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
下面附带完整程序源代码
