2023年6月21日发(作者：)

C#SQL带传⼊、输出参数及返回值的存储过程带传⼊参数与输出参数及返回值的存储过程在使⽤时的注意事项总结：1.传⼊参数的参数名需与存储过程中定义的参数名⼀直，且区分⼤⼩写2.传⼊参数需指定其参数类型（Direction）为传⼊参数类型（input）3.输出参数需指定其参数类型（Direction）为传⼊参数类型（output）4.输出参数需指定输出参数长度（size）5.输出参数的参数名需与存储过程中定义的参数名⼀直，且区分⼤⼩写6.返回值参数需指定其参数类型（Direction）为返回值参数类型（ReturnValue）7.返回值参数不需指定返回值参数名以及长度8.在存储过程中返回值参数为int型 ///

/// 带传⼊参数的存储过程 /// /// /// protected void Button3_Click(object sender, EventArgs e) { if (!OrEmpty()) { if (!OrEmpty()) { string name = ; string pwd = ; //传⼊参数 SqlParameter p_username = new SqlParameter(); p_ = name; p_ = ; p_terName = "@username"; SqlParameter p_userpassword = new SqlParameter(); p_ = pwd; p_ = ; p_terName = "@userpassword"; SqlParameter p_isallow = new SqlParameter(); p_ = false;//默认为false p_ = n; p_terName = "@isallow"; ExecutePrucedure("ADDStudentByInput", p_username, p_userpassword, p_isallow);//调⽤执⾏存储过程⽅法 } else { (""); } } else { (""); } } /// /// 存储过程登录验证 /// /// /// protected void btn_Login_Click(object sender, EventArgs e) { if (!OrEmpty()) { if (!OrEmpty()) { string name = ; string pwd = ; //传⼊参数 SqlParameter p_username = new SqlParameter(); p_ = name; p_ = ; p_terName = "@name";//与存储过程相⼀致 p_ion = ;//指定参数为输⼊类型 SqlParameter p_userpassword = new SqlParameter() { Value = pwd, ParameterName = "@pwd", DbType = , Direction = Parame //创建输出参数 SqlParameter p_isallow = new SqlParameter(); p_ = ; p_terName = "@result";//输出参数名 p_ = 50;//指定输出参数长度 p_ion = ;//指定参数为输出参数类型 //创建返回值参数 SqlParameter p_result = new SqlParameter() { DbType = , Direction = Value };//指定参数为返回值类型 SqlCommand cmd = ExecutePrucedure("UserLogin", p_username, p_userpassword, p_isallow, p_result);//调⽤执⾏存储过程⽅法 (""); (""); } else { (""); } } else { (""); } } /// /// 存储过程执⾏⽅法 /// /// /// /// public SqlCommand ExecutePrucedure(string Proc, params SqlParameter[] Parameter) { string strConn = tionStrings["conn"].ConnectionString;//数据库连接字符串 SqlConnection conn = new SqlConnection(strConn);//建⽴数据库连接 ();//打开数据库连接 SqlCommand cmd = new SqlCommand(); dText = Proc;//要执⾏的存储过程 tion = conn;//关联数据库连接 dType = Procedure;//存储过程模式解释命令字符串 ge(Parameter); eNonQuery();//执⾏存储过程 ();//关闭数据库连接 nd(); return cmd; }存储过程CREATE procedure ADDStudentByInput@username nvarchar(20),@userpassword nvarchar(20),@isallow bitAS

BEGININSERT INTO UserInfo VALUES(@username,@userpassword,@isallow)ENDGOUSE [UserManage]GO执⾏存储过程USE [UserManage]GODECLARE @return_value int, @result nvarchar(50)EXEC @return_value = [dbo].[UserLogin] @name = N'存储过程', @pwd = N'123', @result = @result OUTPUTSELECT @result as N'@result'SELECT 'Return Value' = @return_valueGO创建存储过程--CREATE PROC UserLogin

ALTER PROC UserLogin

@name nvarchar(20), --传⼊参数@pwd nvarchar(20),@result nvarchar(50) out--输出参数ASDECLARE

@UserName nvarchar(20),--变量声明@UserName nvarchar(20),--变量声明@UserPwd nvarchar(20),@Count int,@IsAllow BITBEGIN--获取⽤户名SELECT @Count= COUNT(1) FROM UserInfo WHERE UserName = @name IF(@Count = 0 OR @Count IS NULL) BEGIN SET @result = '⽤户名不存在！'; END ELSE IF(@Count = 1) BEGIN SELECT @UserPwd = UserPassword,@IsAllow = IsAllow FROM UserInfo WHERE UserName = @name IF( @UserPwd = @pwd) BEGIN IF(@IsAllow = 1) BEGIN SET @result = '登陆成功！'; END ELSE BEGIN SET @result = '没有权限登陆系统！'; END END ELSE BEGIN SET @result = '密码错误！'; END END ELSE

BEGIN SET @result = '⽤户名不唯⼀'; END return 1--返回值ENDGO

2023年6月21日发(作者：)

C#SQL带传⼊、输出参数及返回值的存储过程带传⼊参数与输出参数及返回值的存储过程在使⽤时的注意事项总结：1.传⼊参数的参数名需与存储过程中定义的参数名⼀直，且区分⼤⼩写2.传⼊参数需指定其参数类型（Direction）为传⼊参数类型（input）3.输出参数需指定其参数类型（Direction）为传⼊参数类型（output）4.输出参数需指定输出参数长度（size）5.输出参数的参数名需与存储过程中定义的参数名⼀直，且区分⼤⼩写6.返回值参数需指定其参数类型（Direction）为返回值参数类型（ReturnValue）7.返回值参数不需指定返回值参数名以及长度8.在存储过程中返回值参数为int型 ///

/// 带传⼊参数的存储过程 /// /// /// protected void Button3_Click(object sender, EventArgs e) { if (!OrEmpty()) { if (!OrEmpty()) { string name = ; string pwd = ; //传⼊参数 SqlParameter p_username = new SqlParameter(); p_ = name; p_ = ; p_terName = "@username"; SqlParameter p_userpassword = new SqlParameter(); p_ = pwd; p_ = ; p_terName = "@userpassword"; SqlParameter p_isallow = new SqlParameter(); p_ = false;//默认为false p_ = n; p_terName = "@isallow"; ExecutePrucedure("ADDStudentByInput", p_username, p_userpassword, p_isallow);//调⽤执⾏存储过程⽅法 } else { (""); } } else { (""); } } /// /// 存储过程登录验证 /// /// /// protected void btn_Login_Click(object sender, EventArgs e) { if (!OrEmpty()) { if (!OrEmpty()) { string name = ; string pwd = ; //传⼊参数 SqlParameter p_username = new SqlParameter(); p_ = name; p_ = ; p_terName = "@name";//与存储过程相⼀致 p_ion = ;//指定参数为输⼊类型 SqlParameter p_userpassword = new SqlParameter() { Value = pwd, ParameterName = "@pwd", DbType = , Direction = Parame //创建输出参数 SqlParameter p_isallow = new SqlParameter(); p_ = ; p_terName = "@result";//输出参数名 p_ = 50;//指定输出参数长度 p_ion = ;//指定参数为输出参数类型 //创建返回值参数 SqlParameter p_result = new SqlParameter() { DbType = , Direction = Value };//指定参数为返回值类型 SqlCommand cmd = ExecutePrucedure("UserLogin", p_username, p_userpassword, p_isallow, p_result);//调⽤执⾏存储过程⽅法 (""); (""); } else { (""); } } else { (""); } } /// /// 存储过程执⾏⽅法 /// /// /// /// public SqlCommand ExecutePrucedure(string Proc, params SqlParameter[] Parameter) { string strConn = tionStrings["conn"].ConnectionString;//数据库连接字符串 SqlConnection conn = new SqlConnection(strConn);//建⽴数据库连接 ();//打开数据库连接 SqlCommand cmd = new SqlCommand(); dText = Proc;//要执⾏的存储过程 tion = conn;//关联数据库连接 dType = Procedure;//存储过程模式解释命令字符串 ge(Parameter); eNonQuery();//执⾏存储过程 ();//关闭数据库连接 nd(); return cmd; }存储过程CREATE procedure ADDStudentByInput@username nvarchar(20),@userpassword nvarchar(20),@isallow bitAS

BEGININSERT INTO UserInfo VALUES(@username,@userpassword,@isallow)ENDGOUSE [UserManage]GO执⾏存储过程USE [UserManage]GODECLARE @return_value int, @result nvarchar(50)EXEC @return_value = [dbo].[UserLogin] @name = N'存储过程', @pwd = N'123', @result = @result OUTPUTSELECT @result as N'@result'SELECT 'Return Value' = @return_valueGO创建存储过程--CREATE PROC UserLogin

ALTER PROC UserLogin

@name nvarchar(20), --传⼊参数@pwd nvarchar(20),@result nvarchar(50) out--输出参数ASDECLARE

@UserName nvarchar(20),--变量声明@UserName nvarchar(20),--变量声明@UserPwd nvarchar(20),@Count int,@IsAllow BITBEGIN--获取⽤户名SELECT @Count= COUNT(1) FROM UserInfo WHERE UserName = @name IF(@Count = 0 OR @Count IS NULL) BEGIN SET @result = '⽤户名不存在！'; END ELSE IF(@Count = 1) BEGIN SELECT @UserPwd = UserPassword,@IsAllow = IsAllow FROM UserInfo WHERE UserName = @name IF( @UserPwd = @pwd) BEGIN IF(@IsAllow = 1) BEGIN SET @result = '登陆成功！'; END ELSE BEGIN SET @result = '没有权限登陆系统！'; END END ELSE BEGIN SET @result = '密码错误！'; END END ELSE

BEGIN SET @result = '⽤户名不唯⼀'; END return 1--返回值ENDGO