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

 

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

下面附带完整程序源代码

 

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值