CLR 使用 C# 自定义存储过程和触发器

这一篇接着上一篇博客继续介绍 SQL CLR Stored Procedure 和 CLR Trigger，

四、CLR Stored Procedure
接下来在之前的项目选择添加新项，选择 SQL CLR C# 存储过程。

public partial class StoredProcedures
{
///

/// <summary><br />    /// 有输入参数，无输出参数，无输出结果，无输出消息，有返回值的存储过程<br />    /// </summary><br />    /// <param name="name"></param><br />    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStrLength")]<br />    public static SqlInt32 GetStrLength(SqlString str)<br />    {<br />        return str.ToString().Length;<br />    }
/// <summary><br />    /// 有输入参数，有输出参数，无输出结果，无输出消息，无返回值的存储过程<br />    /// </summary><br />    /// <param name="name"></param><br />    [Microsoft.SqlServer.Server.SqlProcedure(Name = "SayHello")]<br />    public static void SayHello(SqlString name,out SqlString sayHello)<br />    {<br />        sayHello = "Hello " + name.ToString();<br />    }<br />}<br />![](https://cdn.nlark.com/yuque/0/2020/gif/446847/1579164978273-05cd6913-9991-4d87-ae99-2eafc8052698.gif#align=left&display=inline&height=20&originHeight=20&originWidth=20&size=0&status=done&style=none&width=20)<br />注册程序集和注册存储过程的 SQL 后面再贴出来，这里我们先看看结果。<br />PS：如果你用的是 Visual Studio 2015，那么你可以在【项目路径>obj>Debug】文件夹下面找到自动生成的注册程序集和存储过程的 SQL 语句。至于其他版本大家可以试试。<br />执行存储过程 HelloWorld：<br />--执行存储过程 HelloWorld<br />exec [dbo].[HelloWorld]<br />结果：<br />![](https://cdn.nlark.com/yuque/0/2020/jpeg/446847/1579164978334-522bd19c-5290-4974-a35f-e305111a07ba.jpeg#align=left&display=inline&height=95&originHeight=95&originWidth=450&size=0&status=done&style=none&width=450)<br />这就是输出消息，输出消息和输出结果是不一样的，输出消息是没办法获取的（我没办法），而输出结果就相当于用 Select 语句查询出来的结果一样，是可以获取的。<br />执行存储过程 GetStrLength：<br />--执行存储过程 GetStrLength<br />declare @res int<br />exec @res=[dbo].[GetStrLength] '123456'<br />select @res<br />结果：<br />![](https://cdn.nlark.com/yuque/0/2020/jpeg/446847/1579164978461-96f7f019-ab9c-4c82-aaae-eb09267ad8fb.jpeg#align=left&display=inline&height=94&originHeight=94&originWidth=402&size=0&status=done&style=none&width=402)<br />这个 C# 代码里面是有返回值的，也可以通过这种方式获取到返回值，但是这种返回值的方式只能返回 int 类型的返回值。<br />如果需要多个返回值呢？看下面的存储过程，可以通过设置多个输出参数来达到。<br />执行存储过程 SayHello：<br />--执行存储过程 SayHello<br />declare @SayHello nvarchar(**32**)<br />exec [dbo].[SayHello] 'Brambling',@SayHello output 

select @SayHello
结果：

其实弄明白输入参数、输出参数、输出消息、输出结果和返回值这几个问题，CLR 存储过程的介绍就可以完了。
但是存储过程里面总是免不了要操作数据的，那么下面就看看对于数据库数据的操作和输出结果集的方法吧。

///

    //因为程序是在SQL Server内执行，所以连接字符串写成"context connection=true"即可<br />        using (SqlConnection conn = new SqlConnection("context connection=true"))<br />        {<br />            SqlCommand comm = new SqlCommand();<br />            comm.CommandText = "select StuName from StudentInfo where StuNo=@StuNo;";
        SqlParameter param = new SqlParameter("@StuNo", SqlDbType.NVarChar, 4000);<br />            param.SqlValue = stuNo;<br />            comm.Parameters.Add(param);
        comm.Connection = conn;<br />            conn.Open();<br />            SqlDataReader dataReader = comm.ExecuteReader();<br />            if (dataReader.Read())<br />            {<br />                stoName = dataReader.GetString(0);<br />            }<br />            dataReader.Close();<br />        }<br />    }<br />![](https://cdn.nlark.com/yuque/0/2020/gif/446847/1579164978280-ca2c1b4c-e857-45be-9ab4-5856f3baa217.gif#align=left&display=inline&height=20&originHeight=20&originWidth=20&size=0&status=done&style=none&width=20)<br />执行存储过程 GetStudentNameByStuNo：<br />declare @StuName nvarchar(**32**)<br />exec [GetStudentNameByStuNo] 'A001',@StuName output <br />select @StuName<br />exec [GetStudentNameByStuNo] 'A003',@StuName output <br />select @StuName<br />结果：<br />![](https://cdn.nlark.com/yuque/0/2020/jpeg/446847/1579164978475-7684bc03-bfd6-4df3-be02-ef98de2efa2a.jpeg#align=left&display=inline&height=136&originHeight=136&originWidth=397&size=0&status=done&style=none&width=397)<br />可以看到我们通过输出参数获取到了返回值。如果现在我需要获取整个学生的所有信息呢？<br />虽然可以通过设置多个输出参数得到，但是学生信息的字段过多呢？下面看看输出结果集的方式。<br />![](https://cdn.nlark.com/yuque/0/2020/gif/446847/1579164978318-c45e1e7c-27af-408e-9007-7328d1248915.gif#align=left&display=inline&height=20&originHeight=20&originWidth=20&size=0&status=done&style=none&width=20)<br />   /// <summary><br />    /// 根据学生的学号获取该学生的所有信息<br />    /// 返回的是一个结果集，即有多少条数据就返回多少条数据<br />    /// </summary><br />    /// <param name="stuNo"></param><br />    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStudentInfoByStuNo_First")]<br />    public static void GetStudentInfoByStuNo_First(SqlString stuNo)<br />    {<br />        using (SqlConnection conn = new SqlConnection("context connection=true"))<br />        {<br />            SqlCommand comm = new SqlCommand();<br />            comm.CommandText = "select ID,StuNo,StuName,StuAge from StudentInfo where StuNo=@StuNo;";
        SqlParameter param = new SqlParameter("@StuNo", SqlDbType.NVarChar, 4000);<br />            param.SqlValue = stuNo;<br />            comm.Parameters.Add(param);
        comm.Connection = conn;<br />            conn.Open();<br />            SqlDataReader dataReader = comm.ExecuteReader();<br />            SqlContext.Pipe.Send(dataReader);<br />            dataReader.Close();<br />        }<br />    }
/// <summary><br />    /// 根据学生的学号获取该学生的所有信息<br />    /// 这种方式效率比较高，是通过直接执行 SqlCommand 指令，然后把数据发送到客户端，不需要经过托管内存<br />    /// </summary><br />    /// <param name="stuNo"></param><br />    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStudentInfoByStuNo_Second")]<br />    public static void GetStudentInfoByStuNo_Second(SqlString stuNo)<br />    {<br />        using (SqlConnection conn = new SqlConnection("context connection=true"))<br />        {<br />            SqlCommand comm = new SqlCommand();<br />            comm.CommandText = "select ID,StuNo,StuName,StuAge from StudentInfo where StuNo=@StuNo;";
        SqlParameter param = new SqlParameter("@StuNo", SqlDbType.NVarChar, 4000);<br />            param.SqlValue = stuNo;<br />            comm.Parameters.Add(param);
        comm.Connection = conn;<br />            conn.Open();<br />            SqlContext.Pipe.ExecuteAndSend(comm);<br />        }<br />    }
/// <summary><br />    /// 根据学生的学号获取该学生的所有信息<br />    /// </summary><br />    /// <param name="stuNo"></param><br />    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStudentInfoByStuNo_Third")]<br />    public static void GetStudentInfoByStuNo_Third(SqlString stuNo)<br />    {<br />        using (SqlConnection conn = new SqlConnection("context connection=true"))<br />        {<br />            SqlCommand comm = new SqlCommand();<br />            comm.CommandText = "select ID,StuNo,StuName,StuAge from StudentInfo where StuNo=@StuNo;";
        SqlParameter param = new SqlParameter("@StuNo", SqlDbType.NVarChar, 4000);<br />            param.SqlValue = stuNo;<br />            comm.Parameters.Add(param);
        comm.Connection = conn;<br />            conn.Open();<br />            SqlDataReader dataReader = comm.ExecuteReader();
        SqlDataRecord dataRecord = new SqlDataRecord(<br />                new SqlMetaData[]<br />                {<br />                    new SqlMetaData("ID",SqlDbType.Int),<br />                    new SqlMetaData("StuNo",SqlDbType.NVarChar,128),<br />                    new SqlMetaData("StuName",SqlDbType.NVarChar,128),<br />                    new SqlMetaData("StuAge",SqlDbType.Int)<br />                }<br />            );
        if(dataReader.Read())<br />            {<br />                dataRecord.SetInt32(0,(int)dataReader["ID"]);<br />                dataRecord.SetString(1,(string)dataReader["StuNo"]);<br />                dataRecord.SetString(2,(string)dataReader["StuName"]);<br />                dataRecord.SetInt32(3,(int)dataReader["StuAge"]);<br />                SqlContext.Pipe.Send(dataRecord);<br />            }<br />            dataReader.Close();<br />        }<br />    }<br />![](https://cdn.nlark.com/yuque/0/2020/gif/446847/1579164978328-93ca5519-51dc-4eb6-af4a-01da5cebacfa.gif#align=left&display=inline&height=20&originHeight=20&originWidth=20&size=0&status=done&style=none&width=20)<br />执行存储过程：<br />![](https://cdn.nlark.com/yuque/0/2020/gif/446847/1579164978311-81a66d71-27f9-475d-8fa5-578abaf50d5c.gif#align=left&display=inline&height=20&originHeight=20&originWidth=20&size=0&status=done&style=none&width=20)<br />--执行存储过程 GetStudentInfoByStuNo_First<br />exec [GetStudentInfoByStuNo_First] 'A003'

—执行存储过程 GetStudentInfoByStuNo_Second
exec [GetStudentInfoByStuNo_Second] ‘A003’

—执行存储过程 GetStudentInfoByStuNo_Third
exec [GetStudentInfoByStuNo_Third] ‘A003’

结果：

上面三个方法中，第一个方法和第二个方法都是直接返回查询结果的，但是在实际存储过程当中是不会这样写的，里面应该包含有逻辑操作等等，所以就有了第三个方法。
那么现在是返回的一条数据，如果是返回多条数据呢？第一种方法和第二种方法就不说了，因为这两种方法都是返回结果集的。

///

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

        comm.Connection = conn;<br />            conn.Open();<br />            SqlDataReader dataReader = comm.ExecuteReader();

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

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

/// <summary><br />    /// 根据年龄查询学生的信息<br />    /// 这种方式是所有的数据返回一个结果集<br />    /// </summary><br />    /// <param name="stuAge"></param><br />    [Microsoft.SqlServer.Server.SqlProcedure(Name = "GetStudentsInfoByStuAge_Multiple")]<br />    public static void GetStudentsInfoByStuAge_Multiple(SqlInt32 stuAge)<br />    {<br />        using (SqlConnection conn = new SqlConnection("context connection=true"))<br />        {<br />            SqlCommand comm = new SqlCommand();<br />            comm.CommandText = "select ID,StuNo,StuName,StuAge from StudentInfo where StuAge=@StuAge;";

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

        comm.Connection = conn;<br />            conn.Open();<br />            SqlDataReader dataReader = comm.ExecuteReader();

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

        //标记结果集的开始<br />            SqlContext.Pipe.SendResultsStart(dataRecord);<br />            while (dataReader.Read())<br />            {<br />                dataRecord.SetInt32(0, (int)dataReader["ID"]);<br />                dataRecord.SetString(1, (string)dataReader["StuNo"]);<br />                dataRecord.SetString(2, (string)dataReader["StuName"]);<br />                dataRecord.SetInt32(3, (int)dataReader["StuAge"]);<br />                //填充数据到结果集<br />                SqlContext.Pipe.SendResultsRow(dataRecord);<br />            }<br />            //标记结果集的结束<br />            SqlContext.Pipe.SendResultsEnd();<br />            dataReader.Close();<br />        }<br />    }<br />![](https://cdn.nlark.com/yuque/0/2020/gif/446847/1579164978329-94b1aca3-e11e-42c9-b617-a63f3a31417a.gif#align=left&display=inline&height=20&originHeight=20&originWidth=20&size=0&status=done&style=none&width=20)<br />执行存储过程：<br />--执行存储过程 GetStudentsInfoByStuAge_Single<br />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
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStrLength]; —EXTERNAL NAME 程序集名.类名.方法名GO

—注册存储过程 SayHello
CREATE PROCEDURE [dbo].[SayHello]
@name nvarchar,
@sayHello nvarchar OUTPUT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[SayHello]; —EXTERNAL NAME 程序集名.类名.方法名GO

—注册存储过程 GetStudentNameByStuNo
CREATE PROCEDURE [dbo].[GetStudentNameByStuNo]
@stuNo nvarchar,
@stoName nvarchar OUTPUT
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStudentNameByStuNo]; —EXTERNAL NAME 程序集名.类名.方法名GO

—注册存储过程 GetStudentInfoByStuNo_First
CREATE PROCEDURE [dbo].[GetStudentInfoByStuNo_First]
@stuNo nvarchar
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStudentInfoByStuNo_First]; —EXTERNAL NAME 程序集名.类名.方法名GO

—注册存储过程 GetStudentInfoByStuNo_Second
CREATE PROCEDURE [dbo].[GetStudentInfoByStuNo_Second]
@stuNo nvarchar
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [UserDefinedSqlClr].[StoredProcedures].[GetStudentInfoByStuNo_Second]; —EXTERNAL NAME 程序集名.类名.方法名GO

—注册存储过程 GetStudentInfoByStuNo_Third
CREATE PROCEDURE [dbo].[GetStudentInfoByStuNo_Third]
@stuNo nvarchar
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><br />    /// 获取操作的数据或之后的数据<br />    /// </summary><br />    /// <param name="insOrDel"></param><br />    /// <returns></returns><br />    private static void GetInsertedOrDeleted(InsOrDel insOrDel)<br />    {<br />        using (SqlConnection conn = new SqlConnection("context connection=true"))<br />        {<br />            SqlCommand comm = new SqlCommand();<br />            comm.CommandText = "select ID,StuNo,StuName,StuAge from " + insOrDel.ToString() + ";";<br />            comm.Connection = conn;<br />            conn.Open();<br />            SqlDataReader dataReader = comm.ExecuteReader();

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

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

private enum InsOrDel<br />    {<br />        Inserted,<br />        Deleted<br />    }<br />}<br />![](https://cdn.nlark.com/yuque/0/2020/gif/446847/1579164978362-c2eb40a6-4627-4bfa-8175-22fed0113425.gif#align=left&display=inline&height=20&originHeight=20&originWidth=20&size=0&status=done&style=none&width=20)<br />测试 SQL 语句：<br />![](https://cdn.nlark.com/yuque/0/2020/gif/446847/1579164978362-4a788705-6a98-4a60-84a0-7ba2c3fbf16e.gif#align=left&display=inline&height=20&originHeight=20&originWidth=20&size=0&status=done&style=none&width=20)<br />-- Insert 操作<br />  insert into StudentInfo(StuNo,StuName,StuAge)<br />  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
{
///

    switch (SqlContext.TriggerContext.TriggerAction)<br />        {<br />            case TriggerAction.Insert:<br />                dataRecord.SetString(0, "Insert操作");<br />                break;<br />            case TriggerAction.Update:<br />                dataRecord.SetString(0, "Update操作");<br />                break;<br />            case TriggerAction.Delete:<br />                dataRecord.SetString(0, "Delete操作");<br />                break;<br />            default:<br />                dataRecord.SetString(0, "Nothing");<br />                break;<br />        }<br />        SqlContext.Pipe.Send(dataRecord);<br />    }<br />}<br />![](https://cdn.nlark.com/yuque/0/2020/gif/446847/1579164978390-e8ca532f-36b1-4c66-b626-2c2725cccd7d.gif#align=left&display=inline&height=20&originHeight=20&originWidth=20&size=0&status=done&style=none&width=20)<br />测试 SQL 语句：<br />![](https://cdn.nlark.com/yuque/0/2020/gif/446847/1579164978414-47c5a623-5b9c-4568-82f0-ccb9ae92cc6d.gif#align=left&display=inline&height=20&originHeight=20&originWidth=20&size=0&status=done&style=none&width=20)<br />-- Insert 操作<br />insert into StudentInfo(StuNo,StuName,StuAge)<br />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
{
///

—注册触发器 InsteadOfTrigger
CREATE TRIGGER [InsteadOfTrigger]
ON StudentInfo —目标表
INSTEAD OF INSERT,UPDATE,DELETE —指定触发的操作
AS
EXTERNAL NAME [UserDefinedSqlClr].[Triggers].[InsteadOfTrigger]; —EXTERNAL NAME 程序集名.类名.方法名
GO

—注册触发器 SecondSqlTrigger
CREATE TRIGGER [SecondSqlTrigger]
ON database —数据库级别触发器
for drop_table,drop_procedure —指定触发的操作
AS
EXTERNAL NAME [UserDefinedSqlClr].[Triggers].[SecondSqlTrigger]; —EXTERNAL NAME 程序集名.类名.方法名
GO

删除存储过程和删除触发器的 SQL 语句类似，唯一需要注意的就是删除数据库级别的触发器时，需要在后面加上 on database，例如：
—删除数据库级别触发器 SecondSqlTrigger
drop trigger [SecondSqlTrigger] on database
其实触发器本身就很少用到，因为对于数据量大的时候，特别影响性能，所以这里不多做介绍。
可以参考这里：CLR 触发器.aspx)

六、总结
总算写完了。。。
其实 CLR 自定义函数、存储过程和触发器等，不一定比 T-SQL 好用，准确来说性能稍微差点。
但是这只是提供一种方法，遇到 T-SQL 不能解决时可以考虑的一种方法。
毕竟了解的越多，会的越多，遇到问题处理的方法就越多。