C#[翻译]使用C#创设SQL Server的仓储过程(Visual Studio 2005 + SQL Server 2005)

初稿地址: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并实施如下脚本。

C# 1sp_configure ‘clr enabled’, 1 
C# 2GO 
C# 3RECONFIGURE 
C# 4GO 

这边,大家履行了系统存储过程“sp_configure”,为其提供的五个参数分别为:“clr
enabled”和“1”。倘若要停用CLR集成的话也是推行这么些蕴藏过程,只不过第二个参数要改成“0”而已。其余,为了使新的安装产生效果,不要忘记调用“RECONFIGURE”。

SQL Server项目
目前打开Visual Studio,并从文件菜单中选用“新建项目”。
在“新建项目”对话框中挑选“Visual C#”下的“Database”。 然后选取“SQL
Server项目”模板。
C# 5

起好项目名称后就单击“确定”按钮。

高速,你所创立的门类就要求你挑选一个SQL Server数据库。
C# 6

按照指示一步一步地做就好了,即使你选取了收回,也可以在“项目”–“属性”对话框中再两遍接纳数据库。
举个例子,假使你的微处理器上有一个诺思wind数据库,那么就在“新建数据库引用”对话框中当选它,然后单击“确定”按钮。
之后,SQL
Server项目在部署的时候就会将我们开发的蕴藏过程写入那些数据库(继续未来看你就精晓是怎么回事了)。

接下去,右键单击你新建的这么些序列,接纳“添加”-“存储过程”。
然后将会油但是生如下图所示的对话框:
C# 7

慎选“存储过程”模板,并起一个适度的名字,然后单击“添加”按钮。

添加完后你就会意识,实际上这是开创了一个早已导入了亟待动用的命名空间的类。

C# 8using System;
C# 9using System.Data;
C# 10using System.Data.SqlClient;
C# 11using System.Data.SqlTypes;
C# 12using Microsoft.SqlServer.Server;

留意一下加粗展现的命名空间(译者注:后五个using)。
System.Data.SqlTypes命名空间包含了很多例外的门类,它们能够用来取代SQL
Server的数据类型。 Microsoft.SqlServer.Server命名空间下的类负责SQL
Server的CLR集成。

尚无再次回到值的囤积过程
在这一节中,我们将会看到哪些写一个履行了一部分逻辑,可是却绝非任何重回值和出口参数的仓储过程。
在这些事例里,我们将创造一个名为“ChangeCompanyName”的蕴藏过程,它用来修改Customers表中CompanyName字段的值。
这些蕴藏过程需要两个参数 –
CustomerID(需要转移集团名称的客户的ID)和CompanyName(新的公司名称)。
“ChangeCompanyName”存储过程一呵而就后的代码如下:

C# 13[SqlProcedure]
C# 14public static void ChangeCompanyName
C# 15(SqlString CustomerID, SqlString CompanyName)
C# 16C# 17C# 18{
C# 19SqlConnection cnn = new SqlConnection
C# 20(“context connection=true”);
C# 21cnn.Open();
C# 22SqlCommand cmd = new SqlCommand();
C# 23cmd.Connection = cnn;
C# 24cmd.CommandText = “update customers set 
C# 25companyname=@p1 where customerid=@p2″;
C# 26SqlParameter p1 = new SqlParameter(“@p1”, CompanyName);
C# 27SqlParameter p2 = new SqlParameter(“@p2”, CustomerID);
C# 28cmd.Parameters.Add(p1);
C# 29cmd.Parameters.Add(p2);
C# 30int i=cmd.ExecuteNonQuery();
C# 31cnn.Close();
C# 32SqlContext.Pipe.Send(i.ToString());
C# 33}

细心看一下这几个ChangeCompanyName()方法。
它是一个静态方法并且没有重返值(void)。
它需要六个名为CustomerID和CompanyName的参数。
请注意这五个参数的数据类型都是SqlString。 SqlString能够用来代表SQL
Server中的nvarchar数据类型。
这多少个法子用了一个[SqlProcedure]特性来修饰。
该属性用于标记ChangeCompanyName()方法是一个SQL Server存储过程。

在章程内大家创制了一个SqlConnection对象,并安装其连续字符串为“context
connection = true”。
“上下文连接”可以让您使用当前报到到数据库的用户作为你的登录数据库的认证音信。
本例中,ChangeCompanyName()方法将会更换为存储过程,然后保留到诺思(North)wind数据库里。
所以在此处的“上下文连接”指的就是诺思wind数据库。
这样您就不需要再写任何关于登录数据库的表明信息了。

接下去是开辟数据库连接。
然后经过安装SqlCommand对象的Connection和CommandText属性,让其实践更新操作。
同时,咱们还需要安装多少个参数。
那样经过调用ExecuteNonQuery()方法就足以推行更新操作了。
再接下去就是关门大吉连接。

末段,将ExecuteNonQuery()方法的再次回到值发送到客户端。
当然你也足以不做这一步。 现在我们来打探一下SqlContext类的行使。
SqlContext类用于在服务端和客户端之间传递处理结果。
本例使用了Send()方法发送一个字符串重回给调用者。

回去从表中读取的一条或多条记下的储存过程
俺们在动用存储过程时,通常会SELECT一条或多条记下。
你可以使用三种方法来创建这样的蕴藏过程。

先是我们创制一个名为GetAllCustomers()的艺术,代码如下:

C# 34[SqlProcedure]
C# 35public static void GetAllCustomers()
C# 36C# 37C# 38{
C# 39SqlConnection cnn = new SqlConnection
C# 40(“context connection=true”);
C# 41cnn.Open();
C# 42SqlCommand cmd = new SqlCommand();
C# 43cmd.Connection = cnn;
C# 44cmd.CommandText = “select * from customers”;
C# 45SqlDataReader reader = cmd.ExecuteReader();
C# 46SqlContext.Pipe.Send(reader);
C# 47reader.Close();
C# 48cnn.Close();
C# 49}

以此GetAllCustomers()方法用了一个[SqlProcedure]属性来修饰。
在点子内创立一个SqlConnection和一个SqlCommand对象。
然后使用Execute里德r()方法来执行SELECT语句。
接下来用Send()方法将获取的SqlDataReader数据发送到客户端。
最后就是关门SqlDataReader和SqlConnection。
在这种形式中,是我们协调创造的SqlDataReader。
其实,大家也得以把这些职责交给SqlContext类去完成,代码如下:

C# 50[SqlProcedure]
C# 51public static void GetCustomerByID
C# 52(SqlString CustomerID)
C# 53C# 54C# 55{
C# 56SqlConnection cnn = new SqlConnection
C# 57(“context connection=true”);
C# 58cnn.Open();
C# 59SqlCommand cmd = new SqlCommand();
C# 60cmd.Connection = cnn;
C# 61cmd.CommandText = “select * from customers 
C# 62where customerid=@p1″;
C# 63SqlParameter p1 = new SqlParameter(“@p1”, CustomerID);
C# 64cmd.Parameters.Add(p1);
C# 65SqlContext.Pipe.ExecuteAndSend(cmd);
C# 66cnn.Close();
C# 67}

GetCustomerByID()方法需要一个参数 –
CustomerID,它将从Customers表中回到某个customer的笔录。
这一个法子内的代码,除了ExecuteAndSend()方法外,你应当都早就相比较熟谙了。
ExecuteAndSend()方法接收一个SqlCommand对象作为参数,执行它就会回来数据集给客户端。

有出口参数的积存过程
俺们在利用存储过程时,平常会由此输出参数再次回到一个通过测算的值。
所以,现在让我们来看一看咋样创制具有一个或三个出口参数的贮存过程。

C# 68[SqlProcedure]
C# 69public static void GetCompanyName
C# 70(SqlString CustomerID,out SqlString CompanyName)
C# 71C# 72C# 73{
C# 74SqlConnection cnn = new SqlConnection
C# 75(“context connection=true”);
C# 76cnn.Open();
C# 77SqlCommand cmd = new SqlCommand();
C# 78cmd.Connection = cnn;
C# 79cmd.CommandText = “select companyname from 
C# 80customers where customerid=@p1″;
C# 81SqlParameter p1 = new SqlParameter
C# 82(“@p1”, CustomerID);
C# 83cmd.Parameters.Add(p1);
C# 84object obj = cmd.ExecuteScalar();
C# 85cnn.Close();
C# 86CompanyName = obj.ToString();
C# 87}

这是一个名为GetCompanyName()的法门,它需要两个参数。
第一个参数是CustomerID,它是一个输入参数;第二个参数是CompanyName,它是一个输出参数(用关键字out来指明)。
这五个参数都是SqlString类型的。
GetCompanyName()方法会接收一个CustomerID参数,然后回来CompanyName(作为出口参数)。

该方法内的代码首先设置了SqlConnection和SqlCommand对象。
然后,使用ExecuteScalar()方法来执行SELECT语句。
ExecuteScalar()方法再次来到的值是一个object类型,它实在就是店铺名称。
最终将出口参数CompanyName设置为这么些值。

回来一行或多行自定义数据的蕴藏过程
大家在行使存储过程时,更多的仍旧从某些表中读取数据。
可是,某些情状下大家需要的数码或者不在任何表里。
例如,你恐怕会依据某些统计来生成一个数目表格。
因为它的数据不是从表中拿到的,所以地点的章程就不在适用了。 幸运的是,SQL
Server的CLR集成特性给大家提供了一个缓解这一个问题的艺术。 请看如下代码:

C# 88[SqlProcedure]
C# 89public static void GetCustomRow()
C# 90C# 91C# 92{
C# 93SqlMetaData[] metadata = new SqlMetaData[2];
C# 94metadata[0] = new SqlMetaData
C# 95(“CustomerID”, SqlDbType.NVarChar,50);
C# 96metadata[1] = new SqlMetaData
C# 97(“CompanyName”, SqlDbType.NVarChar,50);
C# 98SqlDataRecord record = new SqlDataRecord(metadata);
C# 99record.SetString(0, “ALFKI”);
C# 100record.SetString(1, “Alfreds Futterkiste”);
C# 101SqlContext.Pipe.Send(record);
C# 102}

GetCustomRow()方法会重返一条记下并发送给客户端。
这些点子首先讲明了一个SqlMetaData对象。
当你要用到自定义列的时候,就足以行使那么些SqlMetaData类。
在大家的言传身教中,创造了五个品种为NVarChar,长度为50的列。然后成立了一个SqlDataRecord对象。
SqlDataRecord类可以用来表示一个自定义行。
它的构造函数需要一个SqlMetaData数组作为参数。
SqlDataRecord对象的SetString()方法用来安装列的值。
另外,还有为数不少例外的类似SetString()这样的法门,可以用来拍卖不同的数据类型。
最终,调用Send()方法将SqlDataRecord对象发送到客户端。

在上边的言传身教中,大家只回去了一条龙数据给调用者。 那么,倘使要回去多行啊?
请看下边的代码:

C# 103[SqlProcedure]
C# 104public static void GetMultipleCustomRows()
C# 105C# 106C# 107{
C# 108SqlMetaData[] metadata = new SqlMetaData[2];
C# 109metadata[0] = new SqlMetaData
C# 110(“CustomerID”, SqlDbType.NVarChar, 50);
C# 111metadata[1] = new SqlMetaData
C# 112(“CompanyName”, SqlDbType.NVarChar, 50);
C# 113SqlDataRecord record = new SqlDataRecord(metadata);
C# 114SqlContext.Pipe.SendResultsStart(record);
C# 115record.SetString(0, “ALFKI”);
C# 116record.SetString(1, “Alfreds Futterkiste”);
C# 117SqlContext.Pipe.SendResultsRow(record);
C# 118record.SetString(0, “ANATR”);
C# 119record.SetString(1, “Ana Trujillo Emparedados y helados”);
C# 120SqlContext.Pipe.SendResultsRow(record);
C# 121SqlContext.Pipe.SendResultsEnd();
C# 122}

GetMultipleCustomRows()方法将会回去五个SqlDataRecord对象到客户端。
接下来创造自定义列和安装列的值都和前边的事例一样。
可是,大家运用的是SendResutlsStart()方法来传输数据。
SendResultsRow()方法也是发送一个SqlDataRecord对象到客户端,可是我们可以频繁调用它,从而做到发送多条记下。
最终,调用SendResultsEnd()方法用来标记已经到位数据传输操作。

咱俩早就付出完了仓储过程。
现在就足以将以此项目编译为一个顺序集(.DLL)。
可是我们的工作并没有到此截至。 大家还索要配备这么些程序集和存储过程到SQL
Server数据库。 有两种办法可以成功这一个工作 – 手动和活动。
手动方法是采取T-SQL语句注册你的程序集,并将积存过程部署到SQL
Server数据库中。 在本例中,我将应用机动的不二法门来布局存储过程到SQL
Server数据库。

右键单击你的项目,然后在菜单中采取“部署”选项。
C# 123

诸如此类就会活动地形成注册程序集和安排存储过程的干活。
注意,唯有在你创制项目时添加了数据库引用的时候,才会现出“部署”选项。
假使因为某些原因你没能添加数据库引用,那么你可以通过项目性质对话框来设置它。
C# 124

只要你在SQL Server Management
Studio查看诺思(North)wind数据库的话,那么就应当可以见到和下图相似的结果。
C# 125

留意,在仓储过程节点下冒出了俺们创设的所有办法(有“锁”图标的),并且在先后集节点下出现了大家的先后集。

就是这么些东西,很粗略吗。 现在你就可以在您的顺序中调用这个囤积过程了。
你也得以在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的会员。

相关文章