CLR 存储过程及其分步创建

Virat Kothari

4.07/5 (19投票s)

2009年6月29日

CPOL

6分钟阅读

258993

5820

CLR 存储过程非常简单，在处理数据库时，可以在大多数复杂场景中使用。

引言

当我们需要在存储过程和其他数据库对象中实现一些复杂逻辑时，通常会遇到问题。当我们试图在数据库对象中实现复杂的逻辑和业务规则时，发现性能低下。在许多情况下，我们发现 C# 或 VB 类更适合实现这些功能。Microsoft 在 SQL Server 2005 中添加了一项新功能来解决这些问题，称为“CLR 存储过程”。

什么是 CLR 存储过程？

现在，让我们来理解 CLR 存储过程。如大多数 .NET 程序员所知，CLR 是公共语言运行时，而存储过程是数据库的常规存储过程。因此，CLR 存储过程是两者的结合。正如我们所知，公共语言运行时是 .NET 的核心组件。公共语言运行时是运行时执行环境，它为托管代码提供各种服务，如跨语言集成、代码访问安全、对象生命周期管理、资源管理、线程、调试和类型安全等。因此，CLR 存储过程是运行在数据库内存中的 .NET 对象。

CLR 存储过程的第一个用途可以说是访问系统资源。访问系统资源也可以通过扩展存储过程来实现，而扩展存储过程也是数据库对象，类似于存储过程、函数等。扩展存储过程可以执行标准可执行程序所能执行的大部分操作。那么，为什么要有 CLR 存储过程呢？CLR 存储过程的第一个优势是它是一个托管对象，不像扩展存储过程那样是 것입니다。它们之间共同点是它们都在数据库内存下运行。这样，CLR 存储过程就提供了托管对象的全部优点。下面的屏幕截图解释了执行 CLR 存储过程时的内存分配。

何时应使用 CLR 存储过程？

扩展存储过程与数据库引擎在同一个进程空间中运行，内存泄漏、错误等会影响数据库引擎的性能。CLR 存储过程解决了这些问题，因为它们是托管对象，并且按照公共语言运行时的规范运行。CLR 存储过程可以替代包含复杂逻辑和业务规则的标准存储过程。CLR 存储过程利用 .NET 类，从而可以轻松实现标准存储过程难以实现的复杂逻辑、计算、密集的字符串操作、复杂的迭代、数据加密等。标准存储过程仍然最适合面向数据的任务。CLR 存储过程不仅包括存储过程，还包括函数、触发器等。CLR 存储过程是已编译的，因此性能更好。

CLR 存储过程的优点

在执行复杂逻辑、密集的字符串操作或字符串处理、加密、访问系统资源和文件管理等方面能提供更好的结果。
CLR 存储过程是托管代码，因此确保了类型安全、内存管理等。
更好的代码管理，提供面向对象的编程能力，从而实现封装、多态和继承。
对程序员来说很方便，因为 CLR 存储过程可以用 C#、VB 或 .NET Framework 支持的任何其他语言编写。
CLR 存储过程也可以与 Oracle 10g Release 2 或更高版本一起使用。

CLR 存储过程的缺点

并非在所有情况下都方便，例如，不应使用它们来执行简单查询。在这种情况下，标准存储过程会提供更好的结果。
在某些场景下，部署可能会很困难。

标准存储过程与 CLR 存储过程

您最清楚何时使用常规存储过程，何时使用 CLR 存储过程。CLR 存储过程可以在以下场景中使用：

当程序需要复杂的逻辑或业务规则时。
当流程是 CPU 密集型时。CLR 存储过程由于是已编译和托管的，因此能提供更好的结果。
TSQL 中不可能完成的任务，如访问系统资源、加密、访问 Web 服务等。
作为扩展存储过程的替代方案。在选择扩展存储过程之前，应始终考虑 CLR 存储过程。
操作需要更高的数据安全性。

逐步创建 CLR 存储过程

让我们创建一个简单的 CLR 存储过程，它从数据库的一个表中获取所有行。我在“演示中使用的 SQL 语句”部分列出了用于创建数据库、创建表、插入虚拟记录等的所有 SQL 语句。

应用程序开发规范

IDE：Visual Studio 2008
框架：3.5 SP 1
语言：C# 3.0
数据库：Microsoft SQL Server 2005 Express 版

创建 CLR 存储过程的步骤

打开 Microsoft Visual Studio >> 单击新建项目 >> 选择数据库项目 >> SQL Server 项目。
您可以选择现有数据库连接的引用，或者单击添加新引用。
如果选择现有引用，则跳过步骤 3，否则按以下图像所示添加新的数据库引用，然后单击测试连接以测试连接。
单击确定按钮后，Visual Studio 会询问您是否要在选定的连接上启用 SQL/CLR 调试。您可以选择“是”启用调试，或选择“否”禁用。
一旦选择了数据库引用和调试选项，项目将在解决方案资源管理器中显示。选择项目，右键单击解决方案资源管理器 >> 单击添加 >> 存储过程。
从已安装的模板中添加新过程，如下图所示。为其命名。
选择模板后，将创建一个 .cs 文件，其中包含以下图像所示的内容。
将以下代码添加到已创建的方法中。在创建新的 SqlConnection 时，将“context connection=true”作为连接字符串传递给构造函数。此 CLR 存储过程将成为数据库的一部分，因此它将是数据库的内部部分，无需从外部连接数据库。因此，无需提供通常在应用程序中提供的连接字符串。然后，单击生成菜单 >> 单击生成解决方案。这将把程序集部署到我们最初连接的数据库。
现在，选择数据库 >> 可编程性。右键单击存储过程 >> 单击刷新。存储过程列表中应显示一个新添加的存储过程。同时右键单击程序集 >> 单击刷新。这应该会显示新添加的程序集。另外，通过以下查询启用 CLR 存储过程。
```
sp_configure 'clr enabled', 1 
```
运行以下查询或上述查询以生效。
```
RECONFIGURE
```
现在，执行存储过程。它应该会产生与以下屏幕所示类似的结果。

Transact-SQL 语句

以下是创建数据库、创建表、向表中插入记录等的查询。--为演示创建一个新数据库

CREATE DATABASE DbForClrDemo --Use database USE DbFo

以下是创建数据库、创建表、向表中插入记录等的查询。

–Create a new database for demo

CREATE

–Use database

USE DbForClrDemo

–Create table for CustomerSalesInformation

CREATE
TABLE [dbo].[CustomerSalesInformation](

[Id] [int] IDENTITY(1,1)
NOT
NULL,

[Name] [varchar](50)
NOT
NULL,

[Sales] [decimal](18, 2)
NOT
NULL
DEFAULT
((0)),


CONSTRAINT [PK_CustomerSalesInformation] PRIMARY
KEY
CLUSTERED

(

[Id] ASC

)WITH
(PAD_INDEX =
OFF, IGNORE_DUP_KEY =
OFF)
ON [PRIMARY]

)
ON [PRIMARY]

–Insert dummy data to CustomerSalesInformation table

INSERT
INTO [dbo].[CustomerSalesInformation]([Name], [Sales])
VALUES
(‘Virat Kothari’, 50000)

INSERT
INTO [dbo].[CustomerSalesInformation]([Name], [Sales])
VALUES
(‘Dhruval Shah’, 5000)

INSERT
INTO [dbo].[CustomerSalesInformation]([Name], [Sales])
VALUES
(‘Urvish Sheth’, 15000)

INSERT
INTO [dbo].[CustomerSalesInformation]([Name], [Sales])
VALUES
(‘Rakesh Bajania’, 25000)

INSERT
INTO [dbo].[CustomerSalesInformation]([Name], [Sales])
VALUES
(‘Dhaval Shah’, 150000)

–Enable CLR Stored Procedure in database

sp_configure ‘clr enabled’, 1

–Run following statement to take effect of above statement

RECONFIGURE

–Now execute our CLR Stored Procedure. Remember “ClrDemo” 
-is name of our Stored Procedure
EXEC [dbo].ClrDemo

rClrDemo