SQL Server CLR存储过程的使用

发表于 2015-10-08 07:29

从SQL Server 2005开始，SQL Server 集成了 .NET Framework 的公共语言运行时 (CLR) 组件。这意味着现在可以使用任何 .NET Framework 语言（包括 VB.NET 和 C#）来编写存储过程、触发器、用户定义类型、用户定义函数、用户定义聚合和流式表值函数。

使用CLR集成功能主要有以下好处：

更好的编程模型。 .NET Framework 语言在许多方面都比 Transact-SQL 丰富，它为 SQL Server 开发人员提供了以前没有的构造和功能。开发人员还可以利用 .NET Framework 库的功能，它提供了大量可用于快速有效地解决编程问题的类。

改进了安全和安全性。 托管代码在数据库引擎承载的公共语言运行时环境中运行。SQL Server 利用这一特点为在 SQL Server 早期版本中提供的扩展存储过程提供更安全更可靠的替代方法。

能够定义数据类型和聚合函数。 用户定义类型和用户定义聚合是两个新的托管数据库对象，这两个对象扩展了 SQL Server 的存储和查询功能。

通过标准化环境简化了开发。 数据库开发集成到将来版本的 Microsoft Visual Studio .NET 开发环境中。开发人员在开发和调试数据库对象和脚本时所使用的工具与他们编写中间层或客户端层的 .NET Framework 组件和服务时所使用的工具相同。

具备改善性能和可扩展性的潜力。 在多数情况下，.NET Framework 语言编译和执行模型通过 Transact-SQL 提高性能。

下面将通过一个简单的例子来讲解一下怎么使用CLR存储过程。使用环境为SQL Server 2008和VS 2010。

启用CLR集成

默认情况下CLR集成功能是关闭的，使用之前必须先启用。可以使用如下语句：

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

创建CLR项目

VS 2010，新建项目，在模板中选择数据库->SQL Server下的Visual C# SQL CLR 数据库项目。由于我们使用的是SQL Server 2008，所以需要把Framework的版本改为3.5，不能是4.0

SQL Server CLR存储过程的使用图1

编写CLR存储过程代码

新建存储过程，该方法与普通方法不同之处在于添加了SqlProcedureAttribute。写个简单的列子，该存储过程查询出指定表的所有记录，写上如下代码，Build一下：

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void StoredProcedureTest(string strTableName)
    {
        SqlConnection conn = new SqlConnection("context connection=true");
        SqlCommand cmd = new SqlCommand("", conn);

        try
        {
            cmd.CommandText = string.Format("exec ('select * from {0}')", strTableName);
            conn.Open();

            SqlContext.Pipe.ExecuteAndSend(cmd);
        }
        finally
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
            }
        }
    }
}

创建存储过程

存储过程的代码上面我们已经写好了，现在我们要将它加载到SQL Server中。

创建程序集。假设将上述生成的DLL放在C:\Temp目录下。使用Create Assembly语句创建程序集：

CREATE ASSEMBLY SqlServerProject from 'C:\Temp\SqlServerProject.dll' WITH PERMISSION_SET = SAFE

成功后，可以在数据库中看到我们刚才创建的程序集

SQL Server CLR存储过程的使用图2

创建存储过程。创建好程序集之后就可以创建存储过程了，使用Create Procedure语句创建名为StoredProcedureTest的存储过程：

CREATE PROCEDURE StoredProcedureTest
@strTableName nvarchar(255) 
AS
EXTERNAL NAME SqlServerProject.StoredProcedures.StoredProcedureTest

SqlServerProject为程序集名称，StoredProcedures为Class名称，StoredProcedureTest为方法名。

成功后，也可以在数据库中看到创建的存储过程

SQL Server CLR存储过程的使用图3

测试CLR存储过程

可以先用下面的Script来创建一个表方便测试：

CREATE TABLE [dbo].[Product](
	[Name] [nvarchar](50) NOT NULL,
	[Price] [decimal](18, 2) NOT NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
	[Name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

insert into [dbo].[Product] values('iPhone',5000.00)
insert into [dbo].[Product] values('XiaoMi',5000.00)
GO

测试我们创建的StoredProcedureTest存储过程：

exec StoredProcedureTest @strTableName = 'Product'

正确的返回了结果

SQL Server CLR存储过程的使用图4

删除CLR存储过程

分为两步，先删除存储过程再删除程序集：

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'StoredProcedureTest')
BEGIN
   drop procedure StoredProcedureTest
END

IF EXISTS (SELECT name FROM sys.assemblies WHERE name = 'SqlServerProject')
BEGIN
   drop assembly SqlServerProject
END

现在大家可能对CLR存储过程的使用有了基本的了解，下一篇我们将介绍如何通过SqlPipe将结果返回到客户端。

本文章由创风网原创，转载请注明出处：http://www.windite.com/article/details/k6io4v4