因为一般都用T-SQL来执行数据库操作,偶尔现在转到用存储过程,竟然写不来调用过程的返回值了,查了很多资料,都不详细,或者没到点上,也许是我比较笨,呵呵。
C# 源代码
1
///
<summary>
2 /// 校验用户
3 /// </summary>
4 /// <param name="user"> 用户信息 </param>
5 /// <returns></returns>
6 public static int VerifyUser(clsUser user)
7 {
8 int iRet;
9 string sql = String.Format( " EXECUTE VerifyUser @myVerifyReader OUTPUT,'{0}','{1}' " ,user.username,user.password); // 执行的T-SQL串
10 SqlCommand scmd = new SqlCommand(sql, conn);
11 scmd.Parameters.Add( new SqlParameter( " @myVerifyReader " ,SqlDbType.Int));
12 scmd.Parameters[ " @myVerifyReader " ].Direction = ParameterDirection.Output;
13 //
14
15 DBOpen(conn);
16 // 在返回值上有问题
17 scmd.ExecuteNonQuery();
18 iRet = ( int )scmd.Parameters[ " @myVerifyReader " ].Value;
19 DBClose(conn);
20 return iRet;
21 }
2 /// 校验用户
3 /// </summary>
4 /// <param name="user"> 用户信息 </param>
5 /// <returns></returns>
6 public static int VerifyUser(clsUser user)
7 {
8 int iRet;
9 string sql = String.Format( " EXECUTE VerifyUser @myVerifyReader OUTPUT,'{0}','{1}' " ,user.username,user.password); // 执行的T-SQL串
10 SqlCommand scmd = new SqlCommand(sql, conn);
11 scmd.Parameters.Add( new SqlParameter( " @myVerifyReader " ,SqlDbType.Int));
12 scmd.Parameters[ " @myVerifyReader " ].Direction = ParameterDirection.Output;
13 //
14
15 DBOpen(conn);
16 // 在返回值上有问题
17 scmd.ExecuteNonQuery();
18 iRet = ( int )scmd.Parameters[ " @myVerifyReader " ].Value;
19 DBClose(conn);
20 return iRet;
21 }
SQL SERVER存储过程
--
----------------------------
/*
* VerifyUser 存储过程
* 用途:验证用户登陆
*/
CREATE PROCEDURE VerifyUser
(
@myVerifyReader int OUTPUT, -- 返回结果
@username varchar ( 50 ), -- 用户名
@userpassword varchar ( 50 ) -- 用户密码
)
AS
IF EXISTS ( SELECT [ id ] FROM [ Users ] WHERE username = @username AND userpassword = @userpassword )
SET @myVerifyReader = 0 -- 通过验证
ELSE IF EXISTS ( SELECT [ id ] FROM [ Users ] WHERE username = @username )
SET @myVerifyReader = 1 -- 用户存在,密码不正确
ELSE
SET @myVerifyReader = 2 -- 用户不存在
RETURN isnull ( @myVerifyReader , 3 )
-- -----------------------------------------------
GO
/*
* VerifyUser 存储过程
* 用途:验证用户登陆
*/
CREATE PROCEDURE VerifyUser
(
@myVerifyReader int OUTPUT, -- 返回结果
@username varchar ( 50 ), -- 用户名
@userpassword varchar ( 50 ) -- 用户密码
)
AS
IF EXISTS ( SELECT [ id ] FROM [ Users ] WHERE username = @username AND userpassword = @userpassword )
SET @myVerifyReader = 0 -- 通过验证
ELSE IF EXISTS ( SELECT [ id ] FROM [ Users ] WHERE username = @username )
SET @myVerifyReader = 1 -- 用户存在,密码不正确
ELSE
SET @myVerifyReader = 2 -- 用户不存在
RETURN isnull ( @myVerifyReader , 3 )
-- -----------------------------------------------
GO
先简单写一下吧,就是这么用的。
转自Achilles.NET的博客,原文地址:http://www.cnblogs.com/lizhi/archive/2007/07/10/812336.html