原稿地址：http://www.dotnetbips.com/articles/70eff218-3da0-4f6f-8f8d-eeea65193f2c.aspx
[原文源码下载]
[翻译]使用C#开创SQL Server的积存过程（Visual Studio 2005 + SQL Server
2005）
原文宣布日期：2007.06.17
作者：Bipin Joshi
翻译：webabcd
介绍
万般，开发人士使用的是T-SQL来创立SQL Server的储存过程、函数和触发器。
而如今的SQL Server 2005曾经完全援助.NET通用语言运行时（CLR）了。
这就意味着，你可以使用.NET的言语，如C#、VB.NET之类的来开发SQL
Server的贮存过程、函数和触发器。 SQL Server 和 CLR
的合并给我们带来了n多好处，如实时编译、类型安全、增强的安全性以及加强的编程模型等。
本文中，我将向大家演示如何使用C#始建SQL Server的储存过程。
背景
我们在使用SQL
Server存储过程时，最常做的干活就是从数据库中读取或保存数据。
其常用利用如下：
·执行一些简单的逻辑，没有另外重回值。 也一直不出口参数。
·执行一些逻辑，并通过一个或更多的输出参数重临结果。
·执行一些逻辑，并重回从表中读取的一条或多条记下。
·执行一些逻辑，并赶回一行或多行记录。
这么些记录不是从表中读取的，而是你自定义的有些数据行。
为了演示怎么着用C#支付出这三种拔取的SQL
Server存储过程，我将一个一个地举出示例。
启用CLR集成
在你起首用C#写存储过程在此以前，必须要启用你的SQL Server的CLR集成特性。
默认情况它是不启用的。 打开你的SQL Server Management
Studio并推行如下脚本。
sp_configure ‘clr enabled’, 1
GO
RECONFIGURE
GO
这里，我们进行了系统存储过程“sp_configure”，为其提供的多少个参数分别为：“clr
enabled”和“1”。假若要停用CLR集成的话也是执行这些蕴藏过程，只然而第二个参数要变成“0”而已。此外，为了使新的安装发生效能，不要忘记调用“RECONFIGURE”。
SQL Server项目
前几天打开Visual Studio，并从文件菜单中采纳“新建项目”。
在“新建项目”对话框中采取“Visual C#”下的“Database”。 然后选取“SQL
Server项目”模板。
起好项目名称后就单击“确定”按钮。
很快，你所创制的品种就要求你挑选一个SQL Server数据库。
按照指示一步一步地做就好了，固然你采纳了撤废，也得以在“项目”–“属性”对话框中再两次拔取数据库。
举个例证，倘使你的电脑上有一个诺思wind数据库，那么就在“新建数据库引用”对话框中选中它，然后单击“确定”按钮。
之后，SQL
Server项目在部署的时候就会将我们开发的积存过程写入那些数据库（继续以后看你就了解是怎么回事了）。
接下去，右键单击你新建的这几个类型，选拔“添加”-“存储过程”。
然后将会冒出如下图所示的对话框：
选料“存储过程”模板，并起一个适当的名字，然后单击“添加”按钮。
添加完后您就会发觉，实际上这是创办了一个早就导入了亟待动用的命名空间的类。
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
在意一下加粗显示的命名空间（译者注：后五个using）。
System.Data.SqlTypes命名空间包含了诸多不一的门类，它们可以用来代替SQL
Server的数据类型。 Microsoft.SqlServer.Server命名空间下的类负责SQL
Server的CLR集成。
从没再次回到值的存储过程
在这一节中，我们将会看出什么写一个推行了部分逻辑，不过却没有其他重回值和出口参数的蕴藏过程。
在这些事例里，我们将成立一个名为“ChangeCompanyName”的贮存过程，它用来修改Customers表中CompanyName字段的值。
这一个蕴藏过程需要五个参数 –
CustomerID（需要变更集团名称的客户的ID）和CompanyName（新的商店名称）。
“ChangeCompanyName”存储过程完成后的代码如下：
[SqlProcedure]
public static void ChangeCompanyName
(SqlString CustomerID, SqlString CompanyName)
{
SqlConnection cnn = new SqlConnection
(“context connection=true”);
cnn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = “update customers set
companyname=@p1 where customerid=@p2″;
SqlParameter p1 = new SqlParameter(“@p1”, CompanyName);
SqlParameter p2 = new SqlParameter(“@p2”, CustomerID);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
int i=cmd.ExecuteNonQuery();
cnn.Close();
SqlContext.Pipe.Send(i.ToString());
}
密切看一下这些ChangeCompanyName()方法。
它是一个静态方法并且没有重返值（void）。
它需要几个名为CustomerID和CompanyName的参数。
请注意这两个参数的数据类型都是SqlString。 SqlString可以用来代表SQL
Server中的nvarchar数据类型。
那多少个模式用了一个[SqlProcedure]特性来修饰。
该属性用于标记ChangeCompanyName()方法是一个SQL Server存储过程。
在章程内我们创制了一个SqlConnection对象，并设置其总是字符串为“context
connection = true”。
“上下文连接”可以让您利用当前登录到数据库的用户作为你的登录数据库的表达消息。
本例中，ChangeCompanyName()方法将会变换为存储过程，然后保留到诺思(North)wind数据库里。
所以在此间的“上下文连接”指的就是Northwind数据库。
这样你就不需要再写任何有关登录数据库的证实消息了。
接下去是开辟数据库连接。
然后经过设置SqlCommand对象的Connection和CommandText属性，让其履行更新操作。
同时，我们还需要设置五个参数。
这样经过调用ExecuteNonQuery()方法就足以实施更新操作了。
再接下去就是关门大吉连接。
末尾，将ExecuteNonQuery()方法的重回值发送到客户端。
当然你也得以不做这一步。 现在大家来打听一下SqlContext类的施用。
SqlContext类用于在服务端和客户端之间传递处理结果。
本例使用了Send()方法发送一个字符串再次来到给调用者。
回到从表中读取的一条或多条记下的积存过程
我们在行使存储过程时，经常会SELECT一条或多条记下。
你可以选用两种方法来创立这样的贮存过程。
先是我们创立一个名为GetAllCustomers()的形式，代码如下：
[SqlProcedure]
public static void GetAllCustomers()
{
SqlConnection cnn = new SqlConnection
(“context connection=true”);
cnn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = “select * from customers”;
SqlDataReader reader = cmd.ExecuteReader();
SqlContext.Pipe.Send(reader);
reader.Close();
cnn.Close();
}
以此GetAllCustomers()方法用了一个[SqlProcedure]属性来修饰。
在措施内创立一个SqlConnection和一个SqlCommand对象。
然后使用ExecuteReader()方法来实施SELECT语句。
接下来用Send()方法将获取的SqlDataReader数据发送到客户端。
最终就是关门SqlDataReader和SqlConnection。
在那种办法中，是我们团结创造的SqlDataReader。
其实，我们也得以把这些任务交给SqlContext类去完成，代码如下：
[SqlProcedure]
public static void GetCustomerByID
(SqlString CustomerID)
{
SqlConnection cnn = new SqlConnection
(“context connection=true”);
cnn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = “select * from customers
where customerid=@p1″;
SqlParameter p1 = new SqlParameter(“@p1”, CustomerID);
cmd.Parameters.Add(p1);
SqlContext.Pipe.ExecuteAndSend(cmd);
cnn.Close();
}
GetCustomerByID()方法需要一个参数 –
CustomerID，它将从Customers表中回到某个customer的笔录。
那些法子内的代码，除了ExecuteAndSend()方法外，你应当都早已相比熟谙了。
ExecuteAndSend()方法接收一个SqlCommand对象作为参数，执行它就会再次回到数据集给客户端。
有出口参数的仓储过程
俺们在利用存储过程时，平日会由此输出参数重返一个通过测算的值。
所以，现在让大家来看一看怎样创制具有一个或四个出口参数的仓储过程。
[SqlProcedure]
public static void GetCompanyName
(SqlString CustomerID,out SqlString CompanyName)
{
SqlConnection cnn = new SqlConnection
(“context connection=true”);
cnn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandText = “select companyname from
customers where customerid=@p1″;
SqlParameter p1 = new SqlParameter
(“@p1”, CustomerID);
cmd.Parameters.Add(p1);
object obj = cmd.ExecuteScalar();
cnn.Close();
CompanyName = obj.ToString();
}
这是一个名为GetCompanyName()的章程，它需要五个参数。
第一个参数是CustomerID，它是一个输入参数；第二个参数是CompanyName，它是一个输出参数（用关键字out来指明）。
这两个参数都是SqlString类型的。
GetCompanyName()方法会接收一个CustomerID参数，然后重返CompanyName（作为出口参数）。
该措施内的代码首先设置了SqlConnection和SqlCommand对象。
然后，使用ExecuteScalar()方法来执行SELECT语句。
ExecuteScalar()方法重回的值是一个object类型，它事实上就是信用社名称。
最终将出口参数CompanyName设置为这么些值。
回来一行或多行自定义数据的储存过程
咱俩在运用存储过程时，更多的要么从某些表中读取数据。
然而，某些情形下我们需要的数目或者不在任何表里。
例如，你恐怕会基于某些总结来生成一个数码表格。
因为它的多少不是从表中得到的，所以地方的点子就不在适用了。 幸运的是，SQL
Server的CLR集成特性给大家提供了一个解决这么些题材的法门。 请看如下代码：
[SqlProcedure]
public static void GetCustomRow()
{
SqlMetaData[] metadata = new SqlMetaData[2];
metadata[0] = new SqlMetaData
(“CustomerID”, SqlDbType.NVarChar,50);
metadata[1] = new SqlMetaData
(“CompanyName”, SqlDbType.NVarChar,50);
SqlDataRecord record = new SqlDataRecord(metadata);
record.SetString(0, “ALFKI”);
record.SetString(1, “Alfreds Futterkiste”);
SqlContext.Pipe.Send(record);
}
GetCustomRow()方法会再次回到一条记下并发送给客户端。
那一个艺术首先表明了一个SqlMetaData对象。
当你要用到自定义列的时候，就足以使用这么些SqlMetaData类。
在我们的示范中，创立了七个连串为NVarChar，长度为50的列。然后创设了一个SqlDataRecord对象。
SqlDataRecord类可以用来代表一个自定义行。
它的构造函数需要一个SqlMetaData数组作为参数。
SqlDataRecord对象的SetString()方法用来设置列的值。
其余，还有许多不等的切近SetString()那样的办法，可以用来处理不同的数据类型。
最后，调用Send()方法将SqlDataRecord对象发送到客户端。
在上头的以身作则中，大家只回去了一行数据给调用者。 那么，就算要回到多行呢？
请看下边的代码：
[SqlProcedure]
public static void GetMultipleCustomRows()
{
SqlMetaData[] metadata = new SqlMetaData[2];
metadata[0] = new SqlMetaData
(“CustomerID”, SqlDbType.NVarChar, 50);
metadata[1] = new SqlMetaData
(“CompanyName”, SqlDbType.NVarChar, 50);
SqlDataRecord record = new SqlDataRecord(metadata);
SqlContext.Pipe.SendResultsStart(record);
record.SetString(0, “ALFKI”);
record.SetString(1, “Alfreds Futterkiste”);
SqlContext.Pipe.SendResultsRow(record);
record.SetString(0, “ANATR”);
record.SetString(1, “Ana Trujillo Emparedados y helados”);
SqlContext.Pipe.SendResultsRow(record);
SqlContext.Pipe.SendResultsEnd();
}
GetMultipleCustomRows()方法将会回去五个SqlDataRecord对象到客户端。
接下来创制自定义列和设置列的值都和事先的事例一样。
但是，我们应用的是SendResutlsStart()方法来传输数据。
SendResultsRow()方法也是殡葬一个SqlDataRecord对象到客户端，不过我们得以频繁调用它，从而完成发送多条记下。
最终，调用SendResultsEnd()方法用来标记已经做到多少传输操作。
我们已经支付完了仓储过程。
现在就足以将以此类型编译为一个先后集（.DLL）。
不过大家的办事并没有到此截止。 我们还索要配备这些程序集和仓储过程到SQL
Server数据库。 有二种办法可以成功这些工作 – 手动和自动。
手动方法是行使T-SQL语句注册你的程序集，并将积存过程部署到SQL
Server数据库中。 在本例中，我将使用机动的措施来布局存储过程到SQL
Server数据库。
右键单击你的种类，然后在菜单中拔取“部署”选项。
这般就会自行地成功登记程序集和部署存储过程的工作。
注意，只有在您创立项目时添加了数据库引用的时候，才相会世“部署”选项。
假使因为某些原因你没能添加数据库引用，那么你可以因此品种性质对话框来安装它。
只要你在SQL Server Management
Studio查看Northwind数据库的话，那么就相应可以看看和下图相似的结果。
小心，在蕴藏过程节点下冒出了俺们创造的有所办法（有“锁”图标的），并且在先后集节点下出现了大家的先后集。
就是这些东西，很粗略吗。 现在你就可以在您的顺序中调用这一个囤积过程了。
你也得以在SQL Server Management Studio中来测试它们。
作者：Bipin Joshi
Email：http://www.dotnetbips.com/contact.aspx
简介：Bipin
乔希i是DotNetBips.com的总指挥。他是http://www.binaryintellect.com/的倡导者，那一个公司提供.NET
framwork的作育和提问服务。他在印度洛杉矶为开发者提供培训。他也是微软的MVP(ASP.Net)和ASPInsiders的会员。