在 SQL Server 2005 或更高版本中处理 CLR 对象：第一部分

• 下载源代码 [C#] - 4.51 KB
• 下载源代码 [VB.NET] - 5.72 KB

引言

本文是两部分系列文章中的第一部分。如果您想阅读第二部分，请**点击这里**。

我从未想过要写一篇关于数据库的文章。我非常感谢 CodeProject MVP、我的同事 Abhijit Jana[^]，他一直鼓励我撰写有关新主题的文章。我将这篇文章献给我所有喜欢我文章的人。

另外，我不是 DBA，所以请在我犯错时告诉我，以便我能更新文章并在未来进一步充实它。

这是一个系列文章，因为 CLR 是一个庞大的主题。此外，我正试图提供 C# 和 VB.NET 格式的示例应用程序，以便每个人都能从本文中受益。

目录

• 概述
• 背景
• 优点
• 支持的对象类型
• 创建你的第一个 SQL Server 项目
• 自动部署你的项目
• 手动部署你的项目
• 永久删除对象
• 测试 SQL Server 项目
• 结论
• 参考文献
• 历史

概述

SQL Server 2005 为 .NET 开发者引入了一种编写数据库对象的新方式。除了扩展存储过程外，我们现在可以使用 CLR 存储过程来获取或存储数据、编写**触发器**、**用户定义函数**等，并通过 SQL Server 获得强大的 .NET 框架的全部功能。

例如，假设你想创建一个 XML 解析器来解析数据并给出输出。让我们考虑一下如果我们想在数据库中完成这项工作，我们将有哪些选择。

1. 你可以编写一个普通的存储过程来处理创建 XML 输出的复杂逻辑。
2. 使用 OpenXML 读取 XML；使用游标只读取 OpenXML 输出表中的数据。
3. 使用手动字符串解析技术。
4. 使用 SQL Server 2005 中引入的 **XML** 数据类型。

在上述几种可用技术中，最好的方法是使用 **XML** 数据类型。如果你曾经使用过 XML 数据类型，你可能已经体会到了 CLR 类型。**XML** 是一个可序列化的数据类型，你可以在 SQL Server 2005 中使用它。

在本文中，我的目的是让你理解如何在 SQL Server 中构建你自己的数据类型、对象等。

背景

我认为数据库是存储业务逻辑的理想场所。我们可以创建存储过程、函数等来创建业务逻辑，以便我们可以从我们的应用程序中使用这些接口并存储数据，而无需创建插入/更新语句。我们甚至可以在运行查询之前检查用户权限，例如会话、身份验证令牌以及所有存储在数据库中的内容。

因此，如果我们可以使用 .NET 类编写复杂的数据库逻辑，我们的任务将变得最容易，而且我们还可以获得 .NET 类在其存储过程中提供的所有优势。

优点

扩展存储过程在早期版本的数据库中就已经存在了。这些对象可以像普通可执行文件一样在系统中执行任何操作。CLR 相对于扩展存储过程的主要优势是：

• CLR 存储过程旨在在托管环境中运行。因此，所有托管环境的好处（如垃圾回收）都适用于这些对象。
• 我们可以利用 .NET 库中庞大的类库的优势。
• 这两者都使用数据库内存，因此不会创建新的进程来运行你的代码。

当我们调用这些对象时，它首先会转到注册到数据库的程序集，并查找与被调用对象关联的类。然后，它在数据库连接的上下文中调用该对象。

从上图可以看出，主程序集包含了所有的对象。当外部世界（在本例中是应用程序对象）调用这些对象时，SQL Server 会获取数据库中对象的定义。然后，它会读取当前对象关联的程序集并自动调用该方法。

SQL 数据库中的每个对象，包括 .NET CLR 对象，都共享 SQL Server 数据库引擎的内存。因此，如果我们直接调用 CLR 对象，它不会重新登录到数据库，而是继续使用现有的登录连接。我们稍后会讨论这一点。

支持的对象类型

SQL Server 2005 的 CLR 支持五种类型的对象：

• 存储过程
• 用户定义函数
• 用户定义聚合函数
• 触发器
• 用户定义数据类型

我们可以根据需要使用它们中的每一个，并将它们导入数据库引擎。在讨论它们中的每一个之前，让我们先在 Visual Studio 中创建一个示例应用程序。

创建你的第一个 SQL Server 项目

启动你的 Visual Studio，然后选择**新建项目**。你将看到**新建项目**对话框。在左侧树中选择**数据库**，然后选择**SQL Server 项目**。选择你想要的路径，然后点击确定。

将出现一系列消息框。首先出现的是：

在这里，你可以选择数据库连接。你也可以选择添加新引用来添加新的数据库连接。*此连接将由 Visual Studio 用于部署你的应用程序*。之后，会出现两个警告消息框：

如果你使用的是测试数据库连接，请对这些消息框选择“是”。

完成这些步骤后，将创建一个新项目，其中包含一个名为“*Test*”的文件夹和一个 SQL 文件。我们稍后会再讨论它。

在解决方案资源管理器中，你会发现在文件中已经创建了一个存储过程。你可以删除该文件，然后根据你的需求选择一个新对象。

让我们先选择存储过程。

系统将提示你输入一个新对话框来命名存储过程。随意命名。在我的示例中，我创建了一个名为 `MyFirstCLRSP` 的类。让我们在下面编写它的代码：

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void MyFirstCLRSP()
    {
        SqlPipe p;    // Pipe object to send data to Database Engine
        p = SqlContext.Pipe;
        p.Send("Not Implemented!!");

    }
};

编译后，你可以手动或自动在数据库中运行它。

自动部署你的项目

如果你想自动完成此操作，只需右键单击解决方案资源管理器 -> 部署解决方案。它将自动部署到数据库。

手动部署你的项目

要手动执行此操作到数据库，你需要遵循以下步骤：

1. 首先，你需要构建应用程序以创建 DLL。
2. 使用下面的代码将你的程序集注册到 SQL Server：

CREATE ASSEMBLY MyFirstCLRSP 
FROM 'C:\CLR\CLRproject\CLR\bin\Debug\SqlClassLibrary.dll' 
-- REPLACE this with your path
WITH PERMISSION_SET = SAFE;   -- DEFAULT as most restrictive

3. 创建一个新存储过程，该存储过程存在于程序集中，以便使用下面的代码直接访问它：

CREATE PROCEDURE ClrDemo
AS
EXTERNAL NAME MyFirstCLRSP.StoredProcedures.MyFirstCLRSP

创建存储过程后，正常运行它即可。

EXEC ClrDemo

你将看到打印的文本：“*尚未实现！*”

现在，让我们来谈谈创建程序集时的 `PERMISSION_SET` 选项：

• `PERMISSION_SET`: 在数据库中注册程序集时，你可以使用 `PERMISSION_SET=SAFE`；这是默认的权限选项。这是最严格的选项。如果我们将其设置为 `SAFE`（我在上面的代码中就是这样做的），那么整个程序集中的代码将以安全权限运行，即代码不能访问外部系统资源，如文件、网络、环境变量或注册表。
• `EXTERNAL_ACCESS`: 它允许程序集访问外部文件、网络、环境变量、注册表等。只有具有 `EXTERNAL_ACCESS` 权限的 SQL Server 登录才能创建 `EXTERNAL_ACCESS` 程序集。
• `UNSAFE`: 此权限允许对系统所有资源进行不受限制的访问，无论是从 SQL Server 内部还是外部。来自 `UNSAFE` 程序集中的代码甚至可以调用非托管代码。

**注意**：你必须拥有 *sysadmin* 角色才能创建 `UNSAFE` 程序集。

永久删除对象

要删除一个对象，只需执行：

DROP ASSEMBLY MyFirstCLRSP

这将删除程序集及其在 SQL Server 中创建的所有文件。删除程序集会从数据库中移除一个程序集及其所有关联文件，如源代码和调试文件。

我们也可以指定 `WITH NO DEPENDENTS`，它只会删除程序集，而不会删除与程序集关联的任何依赖文件。

**注意**：要删除程序集，你需要拥有该程序集的所有权，或者对其拥有 `CONTROL` 权限。

测试 SQL Server 项目

首先，你必须记住，Visual Studio Express 或 Professional 版本无法进行 SQL Server CLR 调试。只有 Team System Visual Studio 才能调试 SQL Server CLR 存储过程。

要开始调试，你必须**为当前连接启用 SQL Server 调试**。

1. 打开服务器资源管理器。
2. 在服务器资源管理器中，右键单击要调试的连接，然后选择“允许 SQL CLR 调试”。
3. 将出现一个消息框，显示：“SQL CLR 调试将导致服务器上的所有托管线程停止。是否继续？”
4. 如果你不在生产服务器上，请单击“是”启用调试。

打开 *Test\Test.sql* 文件。在其中输入 `EXEC CLRDEMO`，然后单击“运行”。

你也可以在测试脚本中设置断点。结果将在“输出”窗口中显示。

结论

这是 CLR 存储过程的初步介绍。我将在下一部分文章中讨论更多与 CLR 程序集相关的对象类型。请随时对文章发表评论。

由于内容篇幅过大，我们将继续在另一篇文章中进行更深入的探讨。你可以在**这里**查看本文的下一部分。.

参考文献

• http://msdn.microsoft.com/en-us/library/84b1se47(VS.80).aspx
• http://www.sqlteam.com/article/writing-clr-stored-procedures-in-charp-introduction-to-charp-part-1

历史

• 初版：2009 年 8 月 2 日。