SQL CLR：存储过程、触发器、函数 – 详细介绍
深入了解 SQL CLR 过程以及如何创建一个执行实际工作的过程。
引言
正如大多数事情一样，这个想法始于一个简单的愿望，脱口而出：“我希望我能在 SQL 中更改表，并且所有 CRUD 存储过程都能自动更新”。每个人，甚至你的朋友，都创建过一种或另一种存储过程生成程序，但它总是必须作为单独的步骤运行。我敢肯定，一些有冒险精神的 DBA 甚至创建过一个创建存储过程的存储过程。
背景
我听说过 SQL CLR，并认为它是解决这个问题的正确软件栈。我开始在网上做一些研究，发现了一些文章，这些文章充其量只是“Hello World”或“看，我编译成功了！”之类的演示，我没有找到多少关于实际应用的资料。经过几天辛苦的工作，我得到了一个可行的解决方案，我遇到的许多问题都是一些小问题，但花了几个小时才解决。由于我没有看到太多关于 SQL CLR 的深入文章，我认为我可以填补这个空白，并回馈 http://CodeProject.com，因为我从其他出版商那里学到了很多关于其他主题的知识。事实上，如果你以前从未创建过 CLR SQL 存储过程，我强烈推荐“Hello World”版本 ClrSp.aspx[^] 来开始。
设置
数据库设置
第一步是创建一个新的空数据库，数据库名称不重要。我的设计中的一些后续步骤将删除存储过程以及创建它们，所以你已经被警告了。下面的存储过程仅用于开发数据库，因此安全不是考虑因素。使用本地 SQL Server 实例，Express 版本非常适合本文中的所有步骤。尝试使用远程 SQL Server 将使调试变得不可能，并使其他步骤复杂化。
首先创建一个新的数据库，使用本地 SQL Server 2005（或更高版本）或 SQL Server Express 2005（或更高版本），两者都可以正常工作。我为本例创建的数据库取名为“TestDB”，它满足上述所有要求。创建数据库后，在新数据库中运行以下 SQL 脚本。
-- Enable CLR Sprocs
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
这将启用 CLR（公共语言运行时）编译对象在数据库中运行。请注意，它必须按数据库配置，而不是按服务器配置。
Visual Studio 2008 设置
创建一个类型为“SQL Server Project”的新项目，它可以在“Visual C# \ Database”或“Database \ Microsoft SQL Server \ SQL CLR”下找到。我将项目命名为“CLRSQLTrigger”。
Visual Studio 现在想知道你的数据库在哪里，这就是为什么我们先创建数据库。单击“Add New Reference…”按钮，然后选择你之前创建的数据库。如果你将来需要更改数据库连接，可以在项目属性的“Database”选项卡中找到，它会显示与此处相同的提示，所以很容易。
右键单击项目并选择“Add\Trigger”。以下大部分建议同样适用于存储过程、触发器和函数。对于此任务，请选择“Trigger”。触发器名称为“SprocGen”。
Visual Studio 只给我们提供了一个简单的程序外壳，当然，最重要的一行被注释掉了。首先取消注释它，这样你就可以获得代码的颜色编码。
[Microsoft.SqlServer.Server.SqlTrigger(Name="SprocGen", Target="Table1",
Event="FOR UPDATE")]
通过理解这个属性行，可以清楚地知道它是一个我们指定的触发器。然后我们来看“Name”，这是它部署到 SQL Server 时的名称，名称可以是任何合法的 C#/SQL 名称，不一定非要与下面方法的名称匹配，但触发器名称和方法名称相似（如果不是相同）是个好主意。Target 默认为一个通用的表名，Event 默认为在 Table1 Update 上触发。
为了我们的目的，将 Target 改为“DATABASE”。更改 Target 也会改变“Event”的可用选项，因此将 Event 改为“FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE”。这将导致我们的触发器响应我们新数据库中所有表可能发生的主要事件。
| 常见 Target |
|---|
| 例如“dbo”的 Schema Name |
| 触发器名称 |
| 存储过程名称 |
| 表或视图的名称 |
| 字面量“Database” |
| 字面量“All Server” |
| 更多… http://msdn.microsoft.com/en-us/library/ms189799.aspx[^] |
另外请注意，将 Target=”All Server” 设置为 True 将无法从 Visual Studio 自动部署。我建议在熟悉安装程序集所需的脚本之前，不要创建服务器级触发器，或者尽可能在数据库级别创建它们，然后再将其更改为“All Server”。
| 常见 Event |
|---|
| ALTER_xxxxx |
| CREATE_xxxxx |
| DROP_xxxxx |
| BIND_xxxxx |
| UNBIND_xxxxx |
| DDL_xxxxx_EVENTS 以触发一组 Events |
| 更多… http://msdn.microsoft.com/en-us/library/bb510452.aspx[^] |
上面“更多”的链接还显示了事件之间的树状关系，并展示了可以用于通过更简单的代码捕获更多事件的组。
安全和限制
关于 CLR SQL 讨论得不够多的是它的限制。这些限制的主要原因几乎总是安全问题，无论是文件系统权限，还是调用外部程序的能力。特别是对于生产服务器，我同意默认情况下使用最低权限并强制开发人员启用他们所需的功能。缺点是可能很难弄清楚为什么某些东西不起作用。
如果某项功能静默失败，可能只是不清楚它在往哪个方向“喊叫”。我建议首先查看 Windows 事件日志。从应用程序事件列表开始，然后检查安全列表。很可能问题就在那里，并且有足够的信息来解决它。
其中一个限制似乎是向项目中添加引用的能力。右键单击项目引用文件夹并选择“Add References…”使用 .NET Framework 2.0 版本，我有 12 个程序集可供直接引用。如果你在其他应用程序上做过同样的事情，你可能会回忆起列表非常长，我猜大约有一百个。显示的列表实际上来自 SQL Server。虽然 SQL Server 拥有 .NET Framework，但它的安装非常基础。如果程序集是您自己创建的，请将项目添加到解决方案中，它将与您的主要程序集无缝部署。如果程序集来自第三方，请使用“Create Assembly”、“Alter Assembly”和“Drop Assembly”SQL 命令将其添加到 SQL Server，然后就可以使用 Add Reference 对话框将其添加到您的项目中。
调试
调试 CLR SQL 程序集的标准工作流程如下……
- 右键单击项目并选择“Deploy”
- Debug | Attach to Process…
- 选择进程“sqlservr.exe”，并确保其类型为“T-SQL, Managed, x86 或 x64”
- 单击 Attach 按钮
- 检查是否设置了断点。
- 执行一个操作（通常在“Microsoft SQL Server Management Studio”中），该操作会触发你的触发器。
- 如果一切按计划进行，Visual Studio 应该会在你的断点处停止执行。
当标准工作流程无效时该怎么办。
有一些幕后为您完成的初始设置允许调试 CLR SQL；在这一点上，您可能已经遇到一个消息框，询问您是否要启用调试。如果您的代码始终在同一台计算机上，这会非常好。当我在这台另一台计算机上处理代码时，我无法使用标准的调试技术，所以我去寻找。
以下是我考虑过的一些选项以及它们为什么是或不是好的选择。- EventLog：安全设置太严格，创建事件源需要管理员权限。
- SqlContext.Pipe：它可能在存储过程中效果很好，因为 SQL Management Studio 的消息窗格默认是可见的，但对于在后台调用的触发器效果不佳。
- 日志记录到文本文件：安全设置太严格（如下解释）
- 写入日志表：如果你仔细想想，你的存储过程可能会遇到写入任何表的麻烦，为什么你的日志表会不一样呢？
首先决定你将在硬盘上的哪个文件夹进行日志记录，如果需要可以创建它。直接写到“C:\outfile.txt”可能听起来更容易，但它不起作用。特别是对于 Windows Vista、Win7 和 Windows 2008 Server，在驱动器根目录下写入文本文件需要管理员权限，而开放该权限是很危险的。以下步骤是创建具有所需权限的新文件夹。
- 创建一个名为“C:\CLRSQLoutput”的新文件夹。
- 单击 Start | Run，输入“services.msc”，按 Enter 键
- 在列表中向下查找“SQL Server(xxxxxx)”，其中 xxxxxx 是你正在使用的 SQL Server 实例名称。
- 默认情况下，实例可能是 SQLEXPRESS
- 双击打开此服务的属性，单击“Log on”选项卡。
- 列出的帐户可能是“Network Service”或“Local System”。
- 返回到你创建的新文件夹，右键单击它并选择“Security”选项卡。
- 弹出窗口等在这里会变得非常混乱，所以你可能想在其他地方查找详细说明，但最终结果是确保 SQL Server 正在运行的帐户（你在步骤 6 中找到的帐户）对你创建的文件夹具有写入权限。
- 然后，在你的存储过程中包含以下新变量和方法。
private const string outfilePath = @" C:\CLRSQLoutput\sqlout.txt"; internal void Log(string content) { File.AppendAllText(outfilePath, content); } - 接下来，在 Visual Studio Solution Explorer 中，右键单击项目并选择“Properties”
- 选择“Database”选项卡，并将“Permission Level”更改为“External”。
- 在“Microsoft SQL Server Management Studio”中，运行以下 SQL 脚本
ALTER DATABASE testDB SET TRUSTWORTHY ON EXEC sp_changedbowner sa
请记住，这一切都只是为了获取一些调试输出。好消息是，当你完成调试后，从第 10 步开始撤销放宽的安全设置。默认的 Permission Level 是“Safe”，DB owner 是“machineName\username”。
部署
当程序集部署后，它在“Microsoft SQL Server Management Studio”中可见。
- 连接到你的本地服务器
- 展开你正在使用的数据库名称
- 展开 Programmability 文件夹
- 每个项目/程序集可以包含多个触发器和/或存储过程
- 触发器位于“Database Triggers”文件夹中。
- 存储过程位于“Stored Procedures”文件夹中。
- 程序集存储在“Assemblies”文件夹中。
- 每个项目/程序集可以包含多个触发器和/或存储过程
- 完全删除存储过程需要先删除/删除程序集中所有存储过程和触发器，然后才能删除程序集。
- 使用存储过程/触发器/函数/程序集的右键单击创建菜单将生成包含已编译程序集的十六进制版本和/或原始源或调试信息的文本。这将允许 CLR SQL 对象通过单个脚本安装在另一台服务器上。
真正的代码（尚未）
接下来要理解的是你的代码正在运行的环境或上下文。在 'SprocGen' 方法中添加以下代码行，替换掉 'SqlContext.Pipe.Send("Trigger FIRED");' 行。
SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;
此上下文有两个关键属性：'EventData'，它是一个 SqlXml 数据类型，包含许多其他属性；以及 'TriggerAction'，它告诉我们导致触发器触发的原因。我们先来看后者，并将以下代码添加到我们的触发器中……
if (myTriggerContext.TriggerAction == TriggerAction.CreateTable)
{
// Create related Sprocs if a Primary Key Exists
}
else if (myTriggerContext.TriggerAction == TriggerAction.AlterTable)
{
// Drop and Recreate related Sprocs if a Primary Key Exists
}
else if (myTriggerContext.TriggerAction == TriggerAction.DropTable)
{
// Drop related Sprocs
}
现在让我们仔细看看 EventData 属性，它以 SqlXml 的形式提供给我们，但对于我们的目的来说，它不是一个非常有用的数据类型。使用 SqlXml 是因为对于发生的每种事件，可用的值都不同。大约有 100 个不同的属性组合，大约有 100 种不同的组合。大多数组合暴露 8 到 12 个属性。下面是一个类，它暴露了 SprocGen 触发器的属性，以及一些其他属性，显示了如何访问其他几个属性类型。复制并重命名你的 CLR SQL 对象所需的属性应该很容易。
using System.Data.SqlTypes;
using System.Xml;
using System.Xml.Serialization;
namespace CLRSQLTrigger
{
public class SqlEventData
{
readonly XmlDocument document = new XmlDocument();
public SqlEventData(SqlXml sqlXml)
{
if (sqlXml != SqlXml.Null)
{
document.LoadXml(sqlXml.Value);
}
}
/// <summary>
/// This method doesn't work in the SQL CLR Permissions
/// because Serialization uses Reflection
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="document"></param>
/// <returns></returns>
public static T SerializeToType<T>(SqlXml document)
{
XmlSerializer deserializer = new XmlSerializer(typeof(T));
using (XmlReader reader = document.CreateReader())
{
return (T)deserializer.Deserialize(reader);
}
}
[XmlElement]
public string EventType
{
get { return document.GetElementsByTagName("EventType")[0].InnerText; }
}
[XmlElement]
public string PostTime
{
get { return document.GetElementsByTagName("PostTime")[0].InnerText; }
}
[XmlElement]
public string SPID
{
get { return document.GetElementsByTagName("SPID")[0].InnerText; }
}
[XmlElement]
public string ServerName
{
get { return document.GetElementsByTagName("ServerName")[0].InnerText; }
}
[XmlElement]
public string LoginName
{
get { return document.GetElementsByTagName("LoginName")[0].InnerText; }
}
[XmlElement]
public string UserName
{
get { return document.GetElementsByTagName("UserName")[0].InnerText; }
}
[XmlElement]
public string DatabaseName
{
get { return document.GetElementsByTagName("DatabaseName")[0].InnerText; }
}
[XmlElement]
public string SchemaName
{
get { return document.GetElementsByTagName("SchemaName")[0].InnerText; }
}
[XmlElement]
public string ObjectName
{
get { return document.GetElementsByTagName("ObjectName")[0].InnerText; }
}
[XmlElement]
public string ObjectType
{
get { return document.GetElementsByTagName("ObjectType")[0].InnerText; }
}
/// <summary />
/// This property is not fully supported at this time
/// The contents are still in XML Format
/// </summary />
[XmlElement]
public string TSQLCommand
{
get
{
XmlNodeList result = document.GetElementsByTagName("TSQLCommand");
if (result.Count > 0)
{
return result[0].InnerText;
}
return string.Empty;
}
}
/// <summary />
/// This property is not fully supported at this time
/// The contents are still in XML Format
/// </summary />
[XmlElement]
public string AlterTableActionList
{
get {XmlNodeList actionList =
document.GetElementsByTagName("AlterTableActionList");
if (actionList.Count > 0)
{
return actionList[0].InnerText;
}
return string.Empty;
}
}
public override string ToString()
{
return document.OuterXml;
}
}
}
代码中有很多注释，但让我们重点介绍它们。我有点失望。我真的很想使用包含的 SerializeToType 方法，但序列化使用反射，这需要比我们默认拥有的更多的权限。我保留了代码，因为它可能在 EventData 保存在存储过程外部时很有用。该类还有一个自定义的 ToString 实现，它实际上返回原始的 SqlXml。这在将调试文本写入日志文件时可能很有用。
在中间添加以下行，因为我们希望在 'If' 和 'Else' 中都能访问这些数据。
SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;
SqlEventData eventData = new SqlEventData(myTriggerContext.EventData);
if (myTriggerContext.TriggerAction == TriggerAction.CreateTable)
{
如果你正在跟随，我建议这是你应该有一个可以做一些事情的有效触发器的最小点。将以下代码添加到 'If' 和 'Else'。
Log(eventData.ToString());
右键单击项目并选择“Deploy”，然后使用“Microsoft SQL Server Management Studio”在你的 TestDB 中创建或修改表。需要注意的是，我收到了一些权限错误，似乎与新文件夹上的“Read-Only”属性有关。更改权限一定修改了只读属性。
当一切正常工作时，查看文本文件的内容。当然，数据格式不漂亮，并且包含你的个人信息而不是我修改过的信息。
<EVENT_INSTANCE>
<EventType>ALTER_TABLE</EventType>
<PostTime>2011-08-25T16:47:42.830</PostTime>
<SPID>57</SPID>
<ServerName>MyComputer</ServerName>
<LoginName>James</LoginName>
<UserName>dbo</UserName>
<DatabaseName>TestDB</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>Table_1</ObjectName>
<ObjectType>TABLE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>ALTER TABLE dbo.Table_1 ADD t nchar(10)
NULL</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
在此文章的链接中，你可以找到我的 Sproc Generator 的源代码，以便看到我完整的可运行示例。对于本文，我将继续研究一些基础设施，以便创建一个很好的模板，使这些 CLR SQL 触发器更容易编写。 本文在没有额外代码示例的情况下发布，因为文章正文可能比实际代码更有价值，并且此处嵌入的示例足以开始使用。
现在是“最终”代码
SQLTrigger 方法必须是静态的，但每个人都想要一个封装的对象，每个对象执行一个特定的任务。让我们朝着这个方向前进，创建一个名为“SprocGenWorker”的新类。我早期发现的一个问题是，你只允许有一个连接对象，但它必须对你的其他方法可用。所以我们不能真的在每个方法中使用 using 语句来包装它。因此，我们将采取一个稍微更大的方法。为新类添加 IDisposable 实现。老实说，此时还重构了一些其他东西，所以让我们来看看代码。
using Microsoft.SqlServer.Server;
namespace CLRSQLTrigger
{
public class TriggerTemplate
{
// Enter existing table or view for the target and
// uncomment the attribute line
[SqlTrigger(Name = "TriggerTemplate", Target = "DATABASE",
Event = "FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE")]
public static void TheTrigger()
{
using (TriggerTemplateBase templateBase = new TriggerTemplateBase())
{
SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;
if (myTriggerContext != null)
{
SqlEventData eventData = new SqlEventData(myTriggerContext.EventData);
if (myTriggerContext.TriggerAction == TriggerAction.CreateTable)
{
//TODO: Perform actions when a new table is created
templateBase.WriteLog(eventData.ToString());
}
else if (myTriggerContext.TriggerAction == TriggerAction.AlterTable)
{
//TODO: Perform actions when a table's structure is modified
templateBase.WriteLog(eventData.ToString());
}
else if (myTriggerContext.TriggerAction == TriggerAction.DropTable)
{
//TODO: Perform actions when a table is deleted
templateBase.WriteLog(eventData.ToString());
}
}
}
}
}
}
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace CLRSQLTrigger
{
public class TriggerTemplateBase : IDisposable
{
private readonly SqlConnection connection =
new SqlConnection("context connection=true");
public SqlEventData EventInstance { get; set; }
public bool ExecuteSQL(string commandText)
{
bool result = false;
if (!string.IsNullOrEmpty(commandText))
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
using (SqlCommand command = new SqlCommand(commandText, connection))
{
try
{
result = (command.ExecuteNonQuery() > 0);
}
catch (Exception exception)
{
#if DEBUG
WriteLog(exception.Message);
#endif
}
}
}
return result;
}
private string fileName = @"C:\CLRSQLoutput\sqlout.txt";
public string OutFileName
{
get { return fileName; }
set { fileName = value; }
}
public void WriteLog(string contents, SqlEventData eventData)
{
File.AppendAllText(OutFileName, eventData.ToString());
WriteLog(contents);
}
public void WriteLog(string contents)
{
File.AppendAllText(OutFileName, contents);
}
#region Implementation of IDisposable
/// <summary>
/// Performs application-defined tasks associated with freeing,
/// releasing, or resetting unmanaged resources.
/// </summary>
public void Dispose()
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
connection.Dispose();
}
#endregion
}
}
结论
使用上面的 3 个主要代码示例，你应该能够做一些非常酷的事情。如果你有疑问，请提出来，我会尽力解答。