SQL Server CLR全功略之二---CLR存储过程

2019-11-03 08:36:13

字体：大中小

来源：转载

供稿：网友

从这一节开始呢，我们就要开始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

下面附带完整程序源代码

上一篇： MySQL处理同时存在order by 和 limit 语句时出现的bug

下一篇：sql server unicode 支持