SQL Server CLR 使用 C# 自定義存儲過程和觸發器

這一篇博客接著上一篇博客繼續介紹 SQL CLR Stored Procedure 和 CLR Trigger，上一篇博客介紹了 SQL CLR Function 的使用，以及 CLR 程式集的註冊和 CLR Function 的註冊。我的上一篇博客：SQL Server CLR 使用 C# 自 ...

這一篇博客接著上一篇博客繼續介紹 SQL CLR Stored Procedure 和 CLR Trigger，

上一篇博客介紹了 SQL CLR Function 的使用，以及 CLR 程式集的註冊和 CLR Function 的註冊。

我的上一篇博客：SQL Server CLR 使用 C# 自定義函數

四、CLR Stored Procedure

接下來在之前的項目選擇添加新項，選擇 SQL CLR C# 存儲過程。

public partial class StoredProcedures
{
    /// <summary>
    /// 無輸入參數，無輸出參數，無輸出結果，有輸出消息，無返回值的存儲過程
    /// </summary>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "HelloWorld")]
    public static void HelloWorld()
    {
        SqlContext.Pipe.Send("Hello World");
    }

    /// <summary>
    /// 有輸入參數，無輸出參數，無輸出結果，無輸出消息，有返回值的存儲過程
    /// </summary>
    /// <param name="name"></param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStrLength")]
    public static SqlInt32 GetStrLength(SqlString str)
    {
        return str.ToString().Length;
    }

    /// <summary>
    /// 有輸入參數，有輸出參數，無輸出結果，無輸出消息，無返回值的存儲過程
    /// </summary>
    /// <param name="name"></param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "SayHello")]
    public static void SayHello(SqlString name,out SqlString sayHello)
    {
        sayHello = "Hello " + name.ToString();
    }
}

註冊程式集和註冊存儲過程的 SQL 後面再貼出來，這裡我們先看看結果。

PS：如果你用的是 Visual Studio 2015，那麼你可以在【項目路徑>obj>Debug】文件夾下麵找到自動生成的註冊程式集和存儲過程的 SQL 語句。至於其他版本大家可以試試。

執行存儲過程 HelloWorld：

--執行存儲過程 HelloWorld
exec [dbo].[HelloWorld]

結果：

這就是輸出消息，輸出消息和輸出結果是不一樣的，輸出消息是沒辦法獲取的（我沒辦法），而輸出結果就相當於用 Select 語句查詢出來的結果一樣，是可以獲取的。

執行存儲過程 GetStrLength：

--執行存儲過程 GetStrLength
declare @res int 
exec @res=[dbo].[GetStrLength] '123456' 
select @res

結果：

這個 C# 代碼裡面是有返回值的，也可以通過這種方式獲取到返回值，但是這種返回值的方式只能返回 int 類型的返回值。

如果需要多個返回值呢？看下麵的存儲過程，可以通過設置多個輸出參數來達到。

執行存儲過程 SayHello：

--執行存儲過程 SayHello
declare @SayHello nvarchar(32)
exec [dbo].[SayHello] 'Brambling',@SayHello output 

select @SayHello

結果：

其實弄明白輸入參數、輸出參數、輸出消息、輸出結果和返回值這幾個問題，CLR 存儲過程的介紹就可以完了。

但是存儲過程裡面總是免不了要操作數據的，那麼下麵就看看對於資料庫數據的操作和輸出結果集的方法吧。

　　 /// <summary>
    /// 根據學生學號獲取學生姓名
    /// </summary>
    /// <param name="Id"></param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStudentNameByStuNo")]
    public static void GetStudentNameByStuNo(SqlString stuNo,out SqlString stoName)
    {
        stoName = string.Empty;

        //因為程式是在SQL Server內執行，所以連接字元串寫成"context connection=true"即可
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "select StuName from StudentInfo where StuNo=@StuNo;";

            SqlParameter param = new SqlParameter("@StuNo", SqlDbType.NVarChar, 4000);
            param.SqlValue = stuNo;
            comm.Parameters.Add(param);

            comm.Connection = conn;
            conn.Open();
            SqlDataReader dataReader = comm.ExecuteReader();
            if (dataReader.Read())
            {
                stoName = dataReader.GetString(0);
            }
            dataReader.Close();
        }
    }

執行存儲過程 GetStudentNameByStuNo：

declare @StuName nvarchar(32)
exec [GetStudentNameByStuNo] 'A001',@StuName output 
select @StuName 
exec [GetStudentNameByStuNo] 'A003',@StuName output 
select @StuName

結果：

可以看到我們通過輸出參數獲取到了返回值。如果現在我需要獲取整個學生的所有信息呢？

雖然可以通過設置多個輸出參數得到，但是學生信息的欄位過多呢？下麵看看輸出結果集的方式。

　　 /// <summary>
    /// 根據學生的學號獲取該學生的所有信息
    /// 返回的是一個結果集，即有多少條數據就返回多少條數據
    /// </summary>
    /// <param name="stuNo"></param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStudentInfoByStuNo_First")]
    public static void GetStudentInfoByStuNo_First(SqlString stuNo)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "select ID,StuNo,StuName,StuAge from StudentInfo where StuNo=@StuNo;";

            SqlParameter param = new SqlParameter("@StuNo", SqlDbType.NVarChar, 4000);
            param.SqlValue = stuNo;
            comm.Parameters.Add(param);

            comm.Connection = conn;
            conn.Open();
            SqlDataReader dataReader = comm.ExecuteReader();
            SqlContext.Pipe.Send(dataReader);
            dataReader.Close();
        }
    }

    /// <summary>
    /// 根據學生的學號獲取該學生的所有信息
    /// 這種方式效率比較高，是通過直接執行 SqlCommand 指令，然後把數據發送到客戶端，不需要經過托管記憶體
    /// </summary>
    /// <param name="stuNo"></param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStudentInfoByStuNo_Second")]
    public static void GetStudentInfoByStuNo_Second(SqlString stuNo)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "select ID,StuNo,StuName,StuAge from StudentInfo where StuNo=@StuNo;";

            SqlParameter param = new SqlParameter("@StuNo", SqlDbType.NVarChar, 4000);
            param.SqlValue = stuNo;
            comm.Parameters.Add(param);

            comm.Connection = conn;
            conn.Open();
            SqlContext.Pipe.ExecuteAndSend(comm);
        }
    }

    /// <summary>
    /// 根據學生的學號獲取該學生的所有信息
    /// </summary>
    /// <param name="stuNo"></param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStudentInfoByStuNo_Third")]
    public static void GetStudentInfoByStuNo_Third(SqlString stuNo)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "select ID,StuNo,StuName,StuAge from StudentInfo where StuNo=@StuNo;";

            SqlParameter param = new SqlParameter("@StuNo", SqlDbType.NVarChar, 4000);
            param.SqlValue = stuNo;
            comm.Parameters.Add(param);

            comm.Connection = conn;
            conn.Open();
            SqlDataReader dataReader = comm.ExecuteReader();

            SqlDataRecord dataRecord = new SqlDataRecord(
                new SqlMetaData[]
                {
                    new SqlMetaData("ID",SqlDbType.Int),
                    new SqlMetaData("StuNo",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuName",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuAge",SqlDbType.Int)
                }
            );

            if(dataReader.Read())
            {
                dataRecord.SetInt32(0,(int)dataReader["ID"]);
                dataRecord.SetString(1,(string)dataReader["StuNo"]);
                dataRecord.SetString(2,(string)dataReader["StuName"]);
                dataRecord.SetInt32(3,(int)dataReader["StuAge"]);
                SqlContext.Pipe.Send(dataRecord);
            }
            dataReader.Close();
        }
    }

執行存儲過程：

--執行存儲過程 GetStudentInfoByStuNo_First
exec [GetStudentInfoByStuNo_First] 'A003'

--執行存儲過程 GetStudentInfoByStuNo_Second
exec [GetStudentInfoByStuNo_Second] 'A003'

--執行存儲過程 GetStudentInfoByStuNo_Third
exec [GetStudentInfoByStuNo_Third] 'A003'

結果：

上面三個方法中，第一個方法和第二個方法都是直接返回查詢結果的，但是在實際存儲過程當中是不會這樣寫的，裡面應該包含有邏輯操作等等，所以就有了第三個方法。

那麼現在是返回的一條數據，如果是返回多條數據呢？第一種方法和第二種方法就不說了，因為這兩種方法都是返回結果集的。

　　 /// <summary>
    /// 根據年齡查詢學生的信息
    /// 這種方式是一條數據返回一個結果集
    /// </summary>
    /// <param name="stuAge"></param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStudentsInfoByStuAge_Single")]
    public static void GetStudentsInfoByStuAge_Single(SqlInt32 stuAge)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "select ID,StuNo,StuName,StuAge from StudentInfo where StuAge=@StuAge;";

            SqlParameter param = new SqlParameter("@StuAge", SqlDbType.Int);
            param.SqlValue = stuAge;
            comm.Parameters.Add(param);

            comm.Connection = conn;
            conn.Open();
            SqlDataReader dataReader = comm.ExecuteReader();

            SqlDataRecord dataRecord = new SqlDataRecord(
                new SqlMetaData[]
                {
                    new SqlMetaData("ID",SqlDbType.Int),
                    new SqlMetaData("StuNo",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuName",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuAge",SqlDbType.Int)
                }
            );

            while (dataReader.Read())
            {
                dataRecord.SetInt32(0, (int)dataReader["ID"]);
                dataRecord.SetString(1, (string)dataReader["StuNo"]);
                dataRecord.SetString(2, (string)dataReader["StuName"]);
                dataRecord.SetInt32(3, (int)dataReader["StuAge"]);
                //發送結果集到客戶端
                SqlContext.Pipe.Send(dataRecord);
            }
            dataReader.Close();
        }
    }

    /// <summary>
    /// 根據年齡查詢學生的信息
    /// 這種方式是所有的數據返回一個結果集
    /// </summary>
    /// <param name="stuAge"></param>
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStudentsInfoByStuAge_Multiple")]
    public static void GetStudentsInfoByStuAge_Multiple(SqlInt32 stuAge)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "select ID,StuNo,StuName,StuAge from StudentInfo where StuAge=@StuAge;";

            SqlParameter param = new SqlParameter("@StuAge", SqlDbType.Int);
            param.SqlValue = stuAge;
            comm.Parameters.Add(param);

            comm.Connection = conn;
            conn.Open();
            SqlDataReader dataReader = comm.ExecuteReader();

            SqlDataRecord dataRecord = new SqlDataRecord(
                new SqlMetaData[]
                {
                    new SqlMetaData("ID",SqlDbType.Int),
                    new SqlMetaData("StuNo",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuName",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuAge",SqlDbType.Int)
                }
            );

            //標記結果集的開始
            SqlContext.Pipe.SendResultsStart(dataRecord);
            while (dataReader.Read())
            {
                dataRecord.SetInt32(0, (int)dataReader["ID"]);
                dataRecord.SetString(1, (string)dataReader["StuNo"]);
                dataRecord.SetString(2, (string)dataReader["StuName"]);
                dataRecord.SetInt32(3, (int)dataReader["StuAge"]);
                //填充數據到結果集
                SqlContext.Pipe.SendResultsRow(dataRecord);
            }
            //標記結果集的結束
            SqlContext.Pipe.SendResultsEnd();
            dataReader.Close();
        }
    }

執行存儲過程：

--執行存儲過程 GetStudentsInfoByStuAge_Single
exec [dbo].[GetStudentsInfoByStuAge_Single] '18'

--執行存儲過程 GetStudentsInfoByStuAge_Multiple
exec [dbo].[GetStudentsInfoByStuAge_Multiple] '18'

結果：

可以很清楚的看到，方法一是一條數據返回一個結果集，方法二是所有數據返回一個結果集。

下麵貼出註冊存儲過程的 SQL 語句，註冊程式集的就不貼了，我的上一篇博客有過介紹。

--註冊存儲過程 HelloWorld
CREATE PROCEDURE [dbo].[HelloWorld] 
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[HelloWorld];    --EXTERNAL NAME 程式集名.類名.方法名
GO

--註冊存儲過程 GetStrLength
CREATE PROCEDURE [dbo].[GetStrLength] 
    @str [nvarchar](MAX)
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStrLength];        --EXTERNAL NAME 程式集名.類名.方法名
GO

--註冊存儲過程 SayHello
CREATE PROCEDURE [dbo].[SayHello] 
    @name [nvarchar](MAX), 
    @sayHello [nvarchar](MAX) OUTPUT
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[SayHello];        --EXTERNAL NAME 程式集名.類名.方法名
GO

--註冊存儲過程 GetStudentNameByStuNo
CREATE PROCEDURE [dbo].[GetStudentNameByStuNo] 
    @stuNo [nvarchar](MAX), 
    @stoName [nvarchar](MAX) OUTPUT
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStudentNameByStuNo];    --EXTERNAL NAME 程式集名.類名.方法名
GO

--註冊存儲過程 GetStudentInfoByStuNo_First
CREATE PROCEDURE [dbo].[GetStudentInfoByStuNo_First] 
    @stuNo [nvarchar](MAX)
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStudentInfoByStuNo_First];    --EXTERNAL NAME 程式集名.類名.方法名
GO

--註冊存儲過程 GetStudentInfoByStuNo_Second
CREATE PROCEDURE [dbo].[GetStudentInfoByStuNo_Second] 
    @stuNo [nvarchar](MAX)
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStudentInfoByStuNo_Second];    --EXTERNAL NAME 程式集名.類名.方法名
GO

--註冊存儲過程 GetStudentInfoByStuNo_Third
CREATE PROCEDURE [dbo].[GetStudentInfoByStuNo_Third] 
    @stuNo [nvarchar](MAX)
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStudentInfoByStuNo_Third];    --EXTERNAL NAME 程式集名.類名.方法名
GO

--註冊存儲過程 GetStudentsInfoByStuAge_Single
CREATE PROCEDURE [dbo].[GetStudentsInfoByStuAge_Single] 
    @stuAge [int]
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStudentsInfoByStuAge_Single];    --EXTERNAL NAME 程式集名.類名.方法名
GO

--註冊存儲過程 GetStudentsInfoByStuAge_Multiple
CREATE PROCEDURE [dbo].[GetStudentsInfoByStuAge_Multiple] 
    @stuAge [int]
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStudentsInfoByStuAge_Multiple];    --EXTERNAL NAME 程式集名.類名.方法名
GO

五、CLR Trigger

接下來選擇添加新項，選擇 SQL CLR C# 觸發器。

1、DML 觸發器

(1) after trigger

public partial class Triggers
{
    /// <summary>
    /// 輸出操作的數據
    /// </summary>
    [Microsoft.SqlServer.Server.SqlTrigger(Name = "FirstSqlTrigger", Target = "StudentInfo", Event = "FOR INSERT,UPDATE,DELETE")]
    public static void FirstSqlTrigger()
    {
        switch (SqlContext.TriggerContext.TriggerAction)
        {
            case TriggerAction.Insert:
                GetInsertedOrDeleted(InsOrDel.Inserted);
                break;
            case TriggerAction.Update:
                GetInsertedOrDeleted(InsOrDel.Inserted);
                GetInsertedOrDeleted(InsOrDel.Deleted);
                break;
            case TriggerAction.Delete:
                GetInsertedOrDeleted(InsOrDel.Deleted);
                break;
            default:
                break;
        }
    }

    /// <summary>
    /// 獲取操作的數據或之後的數據
    /// </summary>
    /// <param name="insOrDel"></param>
    /// <returns></returns>
    private static void GetInsertedOrDeleted(InsOrDel insOrDel)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand comm = new SqlCommand();
            comm.CommandText = "select ID,StuNo,StuName,StuAge from " + insOrDel.ToString() + ";";
            comm.Connection = conn;
            conn.Open();
            SqlDataReader dataReader = comm.ExecuteReader();

            SqlDataRecord dataRecord = new SqlDataRecord(
                new SqlMetaData[]
                {
                    new SqlMetaData("ID",SqlDbType.Int),
                    new SqlMetaData("StuNo",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuName",SqlDbType.NVarChar,128),
                    new SqlMetaData("StuAge",SqlDbType.Int)
                }
            );

            if (dataReader.Read())
            {
                dataRecord.SetInt32(0, (int)dataReader["ID"]);
                dataRecord.SetString(1, (string)dataReader["StuNo"]);
                dataRecord.SetString(2, (string)dataReader["StuName"]);
                dataRecord.SetInt32(3, (int)dataReader["StuAge"]);
                //發送結果集到客戶端
                SqlContext.Pipe.Send(dataRecord);
            }
            dataReader.Close();
        }
    }

    private enum InsOrDel
    {
        Inserted,
        Deleted
    }
}

測試 SQL 語句：

  -- Insert 操作
  insert into StudentInfo(StuNo,StuName,StuAge)
  values('A006','小飛',20)

  -- Update 操作
  update StudentInfo set StuName='小飛飛' where StuNo='A006' 

  -- Delete 操作
  delete from StudentInfo where StuNo='A006'

結果：

這裡說明一下，Microsoft.SqlServer.Server.SqlTrigger 有三個屬性。

Name：表示觸發器的名稱。

Target：表示觸發器的目標表的名稱。

Event：表示觸發執行觸發器的動作。

(2) instead of trigger

public partial class Triggers
{
    /// <summary>
    /// 輸出操作類型
    /// </summary>
    [Microsoft.SqlServer.Server.SqlTrigger(Name = "InsteadOfTrigger",Target = "StudentInfo",Event = "INSTEAD OF INSERT,UPDATE,DELETE")]
    public static void InsteadOfTrigger()
    {
        SqlDataRecord dataRecord = new SqlDataRecord(
            new SqlMetaData[]
            {
                new SqlMetaData("Message",SqlDbType.NVarChar,128)
            }
        );

        switch (SqlContext.TriggerContext.TriggerAction)
        {
            case TriggerAction.Insert:
                dataRecord.SetString(0, "Insert操作");
                break;
            case TriggerAction.Update:
                dataRecord.SetString(0, "Update操作");
                break;
            case TriggerAction.Delete:
                dataRecord.SetString(0, "Delete操作");
                break;
            default:
                dataRecord.SetString(0, "Nothing");
                break;
        }
        SqlContext.Pipe.Send(dataRecord);
    }
}

測試 SQL 語句：

-- Insert 操作
insert into StudentInfo(StuNo,StuName,StuAge)
values('A006','小飛',20)

-- Update 操作
update StudentInfo set StuName='小飛飛' where StuNo='A006' 

-- Delete 操作
delete from StudentInfo where StuNo='A006'

結果：

Instead of 是一種特殊的觸發器，它只執行觸發器本身，也就是觸發器裡面的操作，

所以 Insert、Update、Delete 操作是不執行的，只是用於觸發該觸發器，而且 Instead of 觸發器會覆蓋掉 after 觸發器。

2、DDL 觸發器

DDL 觸發器又分為資料庫級別的觸發器和伺服器級別的觸發器，這裡只介紹資料庫級別的觸發器。

public partial class Triggers
{
    /// <