在VS2010中创建SQL SERVER系统存储过程

在VS2010中创建SQL SERVER系统存储过程

参照：http://www.cnblogs.com/webabcd/archive/2007/06/24/793941.html

一：准备工作：

1. 首先在某数据库(如DbTest)中创建一个表，如t1，SQL语句如下：

CREATE TABLE t1(tid int primary key, tname nvarchar(20));

2. 启用CLR集成

在你开始用C#写存储过程之前，必须要启用你的SQL Server的CLR集成特性。 默认情况它是不启用的。代码如下：

sp_configure 'clr enabled',1

GO

RECONFIGURE WITH OVERRIDE

GO

注：许多资料中没加WITH OVERRIDE选项，在执行时可能会报错：

Msg 5808, Level 16, State 1, Line 1
不支持对系统目录进行即席更新。
二：创建系统存储过程：

1.       在VS 2010的IDE环境中新建项目：在“已安装的模板”中依次展开Database、SQL Server，选择“Visual C# SQL CLR Database Project”。（注意选择.NET框架的版本，最好与要发布到的数据库版本匹配，如SQL 2005我选择的是.NET框架2.0）

2.       点击确定按钮后，系统会自动弹出“添加数据库引用”对话框，在这里可以选择已有的数据库连接，或添加新的连接，将数据库连接到DbTest中。当然也可以取消后稍候配置。

3.       在新建的项目中添加新项，选择“Stored Procedure”，取名为sp_t1。

4.       在新打开的类文件中，头部已经添加了一些命名空间的引用，注意最后两个：System.Data.SqlTypes命名空间包含了很多不同的类型，它们可以用来代替SQL Server的数据类型。 Microsoft.SqlServer.Server命名空间下的类负责SQL Server的CLR集成。

5.       自动生成的静态方法sp_t1其实就是我们要编写的存储过程。（注意：该方法上面已经添加了特性SqlProcedure。 在这个类中其实可以编写许多存储过程）

6.       修改sp_t1方法为如下代码：

[Microsoft.SqlServer.Server.SqlProcedure]

    public static void sp_t1(SqlInt32 tid, SqlString tname)

    {       

        // Put your code here

        SqlConnection con = new SqlConnection("context connection=true");

        SqlCommand com = new SqlCommand("insert into t1 values(@tid,@tname)", con);

        SqlParameter par1 = new SqlParameter("@tid", tid);

        SqlParameter par2 = new SqlParameter("@tname", tname);

        con.Open();

        com.Parameters.Add(par1);

        com.Parameters.Add(par2);

        int cnt = com.ExecuteNonQuery();

        con.Close();

        SqlContext.Pipe.Send(cnt.ToString());

    }

7.       上面基本上是ADO.NET操作数据库的代码。但注意

（1）方法参数的数据类型SqlInt32 与SqlString，分别对应数据库中的int与nvarchar数据库型。

（2）连库字符串的写法，“上下文连接”可以让你使用当前登录到数据库的用户作为你的登录数据库的验证信息。 将来存储过程创建好后，用登录用户来执行此存储过程。

（3）代码最后一句调用了Send方法，向调用方返回信息（SQL语句影响的行数），当然如果不需要也可以不返回。

8.       编译项目成功后再在项目的右键菜单中选择“部署（deploy）”，如果出现成功的信息，表明存储过程可以到SQL Server中使用了。

我在测试时，win7+SQL Server2005开发版没有部署成功，但在另一台机器windows 2003+SQL Server标准版部署成功。

9. 在SQL Server Management Studio中展开DbTest数据库的存储过程，可以看到带锁标记的系统存储过程sp_t1。

三：调试系统存储过程

系统存储过程与普通的存储过程的执行没有什么区别：exec sp_t1 1,'aa'

查询后表t1中添加了一条记录，并且在消息空格中返回了SQL语句影响的结果1。