存储过程创建与.net调用存储过程 原理及实例
一 存储过程简介
简单的说，存储过程是由一些 SQL语句和控制语句组成的被封装起来的过程，它驻留 在数据库中，可以被客户应用程序调用，也可以从另一个过程或触发器调用。它的参数可以被传递和返回。与应用程序中的函数过程类似，
存储过程可以通过名字来 调用，而且它们同样有输入参数和输出参数。
根据返回值类型的不同，我们可以将存储过程分为三类：
返回记录集的存储过程， 典型的例子是从数据库中检索 出符合某一个或几个条件的记录
返回数值的存储过程（也可以称为标量存储过程），返回数值的存储过程执行完以后返回一个值，例如在数据库中执行一个有返回值的函数或命令
以及行为存储过程。行为存储过程仅仅是用来实 现数据库的某个功能，而没有返回值，例如在数据库中的更新和删除操作
二 使用存储过程的好处
相对于直接使用SQL语句，在应用程序中直接调用存储过 程有以下好处：
(1)减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百 行SQL语句，那么其性能绝对比一条一条的调用SQL语句要高得多。
(2)执行速度更快。有两个原因：首先，在存储过程创建的时候，数据库已经对其进 行了一次解析和优化。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用。
(3)更 强的适应性：由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动 不会对应用程序造成影响。
(4) 布式工作：应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。
三 存储过程的建立
要在应用程序中访问数据库，一般性的步骤是：首先声明一个数据库连接SqlConnection，然后声明一个数据库命令SqlCommand，用来执 行SQL语句和存储过程。有了这两个对象后，就可以根据自己的需要采用不同的执行方式达到目的。需要补充的是，不要忘记在页面上添加如下的引用语句： using System.Data.SqlClient。
就执行存储过程来说，如果执行的是第一类存储过程，那么就要用一个 DataAdapter将结果填充到一个DataSet中，然后就可以使用数据网格控件将结果呈现在页面上了；如果执行的是第二和第三种存储过程，则不需 要此过程，只需要根据特定的返回判定操作是否成功完成即可。
CREATE PROC 存储过程名称
[参数列表]
AS
SQL语句
GO
例如：
CREATE PROC upGetUserName
@intUserId INT,
@ostrUserName NVARCHAR(20) OUTPUT -- 要输出的参数
AS
-- 将uName的值赋给 @ostrUserName 变量，即要输出的参数
SELECT @ostrUserName=uName FROM uUser WHERE uId=@intUserId
GO
用户登录验证的存储过程
CREATE PROC upUserLogin
@strLoginName NVARCHAR(20),
@strLoginPwd NVARCHAR(20),
@blnReturn BIT OUTPUT
AS
-- 定义一个临时用来保存密码的变量
DECLARE @strPwd NVARCHAR(20)
BEGIN
-- 从表中查询当前用户的密码，赋值给 @strPwd 变量，下面要对他进行比较
SELECT @strPwd=uLoginPwd FROM uUser WHERE uLoginName=@strLoginName
IF @strLoginPwd = @strPwd
BEGIN
SET @blnReturn = 1
-- 更新用户最后登录时间
UPDATE uUser SET uLastLogin=GETDATE() WHERE uLoginName=@strLoginName
END
ELSE
SET @blnReturn = 0
END
又一个例子
CREATE procedure query_by_id @emp_id int,@emp_addr varchar(50) output
as
select @emp_addr=address
from employee
where employee_id=@emp_id
order by employee_id;
go
四 C#调用存储过程
1执行没有参数的存储过程
SqlConnection conn=new SqlConnection(“connectionString”);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand=new SqlCommand();
da.SelectCommand.Connection=conn;
da.SelectCommand.CommandText = "NameOfProcedure";（存储过程名）
da.SelectCommand.CommandType = CommandType.StoredProcedure;
(2)执行一个有参数的存 储过程的代码如下（我们可以将调用存储过程的函数声明为ExeProcedure(string inputdate)）：
SqlConnection conn=new SqlConnection(“connectionString”);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand();
da.SelectCommand.Connection=conn;
da.SelectCommand.CommandText = "NameOfProcedure";
da.SelectCommand.CommandType = CommandType.StoredProcedure;
（以上代码相同，以下为要添加的代码）
param = new SqlParameter("@ParameterName", SqlDbType.DateTime);
//SqlParameter对象用于将变量参数化，方便对数据库进行操作，则SQL语句只要用@参数作为变量。
//将SqlDbType.DateTime变量的值赋值给@ParameterName参数名
param.Direction=ParameterDirection.Input;
//定义参数为输入参数
param.Value= Convert.ToDateTime(inputdate);
da.SelectCommand.Parameters.Add(param);
//将参数的关联 添加到SQL语句。
这 样就添加了一个输入参数。若需要添加输出参数：
param = new SqlParameter("@ParameterName", SqlDbType.DateTime);
param.Direction=ParameterDirection.Output;
//定义参数为输出参数
param.Value=Convert.ToDateTime(inputdate);
da.SelectCommand.Parameters.Add(param);
若 要获得参储过程的返回值：
param = new SqlParameter("@ParameterName", SqlDbType.DateTime);
param.Direction= ParameterDirection.ReturnValue;
param.Value = Convert.ToDateTime(inputdate);
da.SelectCommand.Parameters.Add(param);
从上面的代码我们可以看出，当存储过程比较多或者存储过程的参数比较多时，这种方法会大大影响开发的速度；另外一方面，如果项目比较大，那么这些用于数 据库逻辑的函数在以后的维护中也是一个很大的负担。那么，有没有一种改进的方法可以解决这个问题呢？想到在执行没有参数的存储过程时只需要传入一个存储过 程的名字就可以调用相应的存储过程，而且在SqlServer数据库中我们可以直接在查询分析器中敲入“存储过程名（参数列表）”样的字符串就可以执行存 储过程，那么，是否可以把这种思想应用到应用程序中呢？ 于是在编译器中键入相应代码。这些代码是在调用不带参数的存储过程的代码的基础上改的。具体代码如下：
SqlConnection conn=new SqlConnection(“connectionString”);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand();
da.SelectCommand.Connection = conn;
da.SelectCommand.CommandText = "NameOfProcedure（’para1’,’para2’,para3）;
da.SelectCommand.CommandType = CommandType.StoredProcedure;
为了使代码更具有代表性，要调用的存储过程的第一个和第二个参数都为字符串类型，第三个参 数为整型。执行以后发现，完全可以达到预期的效果！ 两种调用方法的比较 通过比较我们可以看到，第二种方法具有一个很明显的优点，那就是可以提高开发速度，节省开发时间，而且代码容易维护，在一定程度上也减少了系统大小。但 是，由于对存储过程参数的处理比较笼统，如果要获取输出参数或者得到存储过程的返回值，这种方法就不能满足需要了。虽然如此，但是，这种方法毕竟可以让开 发人员少些很大一部分的代码。如果不需要获取输出参数和返回值，那么几乎可以做到“一劳永逸”。因此在实际的程序开发中，这种方法还是具有一定的实用价值 的。
浙公网安备 33010602011771号