SQL ServerCLR全功略之二---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