这一篇博客接着上一篇博客继续介绍 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 { /// <summary> /// 禁止删除表和删除存储过程的 DDL 触发器 /// </summary> [Microsoft.SqlServer.Server.SqlTrigger(Name = "SecondSqlTrigger")] public static void SecondSqlTrigger() { switch (SqlContext.TriggerContext.TriggerAction) { case TriggerAction.DropTable: try { Transaction tran = Transaction.Current; tran.Rollback(); } catch { } SqlContext.Pipe.Send("You have no authority"); break; case TriggerAction.DropProcedure: try { Transaction tran = Transaction.Current; tran.Rollback(); } catch { } SqlContext.Pipe.Send("You have no authority"); break; default: break; } } }
这里 DDL 的触发器，只需要指定触发器名称的属性就可以了。
测试 SQL 语句：
--删除表 StudentInfo drop table StudentInfo
结果：
下面贴出注册触发器的 SQL 语句。
--注册触发器 FirstSqlTrigger CREATE TRIGGER [FirstSqlTrigger] ON StudentInfo --目标表 FOR INSERT,UPDATE,DELETE --指定触发的操作 AS EXTERNAL NAME [UserDefinedSqlClr].[Triggers].[FirstSqlTrigger]; --EXTERNAL NAME 程序集名.类名.方法名 GO --注册触发器 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 触发器
六、总结
总算写完了。。。
其实 CLR 自定义函数、存储过程和触发器等，不一定比 T-SQL 好用，准确来说性能稍微差点。
但是这只是提供一种方法，遇到 T-SQL 不能解决时可以考虑的一种方法。
毕竟了解的越多，会的越多，遇到问题处理的方法就越多。
微信关注我们
转载内容版权归作者及来源网站所有！
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。
马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。
为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。
Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。
扫码在手机上查看文章
扫描二维码，手机阅读更方便
有任何问题或合作意向欢迎联系我们
Email: 99873273@qq.com
QQ: 99873273