通过在 Microsoft SQL Server 中托管 CLR(称为 CLR 集成),开发人员可以在托管代码中编写存储过程、触发器、用户定义函数、用户定义类型和用户定义聚合函数, 改变了以前只能通过T-SQL语言来实现这些功能的局面。因为托管代码在执行之前会编译为本机代码,所以,在有些方案中可以大大提高性能。
1. 编写C#代码,编译成.NET 3.5的dll
public class Program
{
[SqlTrigger(Name = @"UsersAudit", Target = "[dbo].[users]", Event = "FOR INSERT")]
public static void UsersAudit()
{
SqlContext.Pipe.Send("UsersAudit start");
// Get the trigger context.
string userName;
string realName;
SqlCommand command;
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlDataReader reader;
switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
// Retrieve the connection that the trigger is using.
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
// Get the inserted row.
command = new SqlCommand(@"SELECT * FROM INSERTED;",
connection);
// Get the user name and real name of the inserted user.
reader = command.ExecuteReader();
reader.Read();
userName = (string)reader[0];
realName = (string)reader[1];
reader.Close();
// Insert the user name and real name into the auditing table.
command = new SqlCommand(@"INSERT [dbo].[UserNameAudit] (userName, realName) "
+ @"VALUES (@userName, @realName);", connection);
command.Parameters.Add(new SqlParameter("@userName", userName));
command.Parameters.Add(new SqlParameter("@realName", realName));
command.ExecuteNonQuery();
}
break;
}
SqlContext.Pipe.Send("UsersAudit end");
}
[Microsoft.SqlServer.Server.SqlProcedure]
public static void HelloWorld(out string text)
{
SqlContext.Pipe.Send("Hello world!" + Environment.NewLine);
text = "Hello world!";
}
}
2. SMSS中选择“可编程性” -> "程序集"->“右键”-》“新建程序集”,将编译的dll注册
3.打开SMSS查询窗口,执行一下的sql语句将触发器和存储过程注册。注意程序集名称和命名空间名称的格式。
CREATE PROCEDURE hello
@i nchar(25) OUTPUT
AS
EXTERNAL NAME SqlTriggerContextTest.Program.HelloWorld
GO
-- if the HelloWorldProc class is inside a namespace (called MyNS),
-- the last line in the create procedure statement would be
-- EXTERNAL NAME helloworld.[MyNS.HelloWorldProc].HelloWorld
CREATE TRIGGER UsersAudit
ON [dbo].[users]
AFTER INSERT, UPDATE
AS
EXTERNAL NAME SqlTriggerContextTest.Program.UsersAudit
GO
4. 可以执行sql语句查看效果
运行 select * from sys.assemblies 可以查看注册的程序集
如果dll中需要使用TCP功能,那么程序集必须签名。
参考:https://www.cnblogs.com/alexcodinglife/articles/5563148.html