SQL Server 2005中创建CLR存储过程

在2005之前的版本创建存储过程都是在数据库里面写Transact-SQL语言实现的，不过现在SQL Server 2005支持用其他面向对象的语言编写CLR存储过程了，关于这样做的好处，官方有很多解释了，这里就直接说明实现方法了。
假设服务器里面有个test数据库，数据库有个架构user，还有一个表test1，然后有个sql登陆用户叫test_user，将这个用户设置成VS2005里面数据库连接的登陆用户。
在VS2005中创建一个项目，类别是SQL Server数据库项目，然后往项目里面添加一个存储过程。接着在该文件里面编写如下存储过程代码：

using System;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void SelectAll(int qty)
    {
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            SqlCommand command = new SqlCommand(string.Format("Select top {0} * From Account ",qty), connection);

            SqlContext.Pipe.ExecuteAndSend(command);
	    
           
        }
    }
};

将上面代码保存，命名为testProcedure.cs文件。接着有2中方法想数据库里面添加这个存储过程。
1. 打开.NET的命令行工具，然后将testProcedure.cs编译成dll程序集testProcedure.dll

csc /t:library /out:testProcedure.dll testProcedure.cs 
接着打开数据库test，在数据库里面的程序集右键菜单中点击“添加新的程序集”项，然后浏览刚刚编译好的testProcedure.dll程序集。
这样就添加了这个程序集，接着新建sql查询，在里面创建一个存储过程如下：
CREATE PROCEDURE [user].SelectAll
AS EXTERNAL NAME testProcedure.StoredProcedures.SelectAll
GO
执行上面的查询语句后，就在数据库中成功添加了一个CLR存储过程。注意上面的测试存储过程是没有参数的，
如果有的话，那么数据库中的存储过程原型必须和CLR语言中的原型一样。
推荐使用这个方法创建CLRC存储过程。
2. 使用VS来自动为sql创建存储过程。 编写一个调试脚本添加到项目里面，脚本可以只是简单执行编好的
存储过程。然后将上面的项目编译并生成然后部署即可，还可以启动调试来完成。VS会自动为数据库添加相应的存储过程。
不过第一种方法更加灵活和可靠。
另外使用第2种方法如果出现 错误：用户未能执行存储过程 sp_enable_sql_debug

这可能是由于：

• 连接问题。需要有一个到服务器的稳定连接。

• 在服务器上缺少必要的权限。若要在 SQL Server 2005 上调试，运行 Visual Studio 的帐户和用于连接 SQL Server 的帐户都必须是 sysadmin 角色的成员。用于连接 SQL Server 的帐户要么是 Windows 用户帐户（如果您正在使用 Windows 身份验证），要么是具有用户 ID 和密码的帐户（如果您使用 SQL 身份验证）。

也就是说那个test_user必须是sysadmin角色才行。

以上就是创建CLR存储过程的全部内容。


在SQL Server 2005 中使用.net程序集的一项注意

在使用CLR存储过程中遇到的一些问题，在这里进行记录：
打开CLR的支持

--在Sql Server中执行这段代码可以开启CLR
exec sp_configure 'show advanced options', '1';
go
reconfigure;
go
exec sp_configure 'clr enabled', '1'
go
reconfigure;
exec sp_configure 'show advanced options', '1';
go 

如果需要访问外部资源的话，会有打开TRUSTWORTHY 属性的提示

--打开数据库的TRUSTWORTHY 属性
ALTER DATABASE DB_Name set TRUSTWORTHY on;

通过上述的设置，应该能轻易地加入程序集了
这个可以用鼠标完成就不贴代码了

最后，手动添加存储过程

--添加存储过程
CREATE PROCEDURE [dbo].[存储过程名称]
    @from [nvarchar](50), -- 参数列表
    @to [nvarchar](50)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [程序集的名称].[类名称].[方法名称]