------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------存储过程验证登陆-------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------
/****** Object: StoredProcedure [dbo].[sp_SZW_Login] Script Date: 02/17/2014 23:15:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[sp_SZW_Login]
(@UserName nvarchar(50), @UserPwd nvarchar(50),@ReturnInfo int output)
as
begin
if EXISTS(select userid from SZW.dbo.SysUser where UserName=@UserName and UserPwd=@UserPwd)
set @ReturnInfo=0 --通过验证
else if exists(select userid from SZW.dbo.SysUser where UserName=@UserName)
set @ReturnInfo=1 --用户名存在,密码错误
else
set @ReturnInfo=2 --用户不存在
return isnull(@ReturnInfo,3)
end
return
GO
----------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------执行------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------
---执行存储过程
declare @result int
exec sp_SZW_Login @UserName='admin',
@UserPwd='admin123',
@ReturnInfo=@result output
print @result
go
---执行带有输出参数的存储过程必须声明变量,传递参数时的格式必须一致
----------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------后台代码用----------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------
using System.Data;
using System.Data.SqlClient;
public partial class Show1 : System.Web.UI.UserControl
{
//枚举登录可能遇到的情况
public enum LoginInfo
{
登录成功 = 0, //登录成功
密码错误 = 1, //密码错误
用户名不存在 = 2 //用户名不存在
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
HttpCookie Coo_UserLogin_Info = Request.Cookies["UserLogin_Info"];
if (Coo_UserLogin_Info != null)
{
this.txt_UserName.Value = Coo_UserLogin_Info["C_UserName"];
this.txt_Upwd.Attributes.Add("value", Coo_UserLogin_Info["C_UserPwd"]);
//选中记住登录状态
this.chk_RePwd.Checked = true;
}
}
}
protected void btn_Login_Click(object sender, EventArgs e)
{
//获取页面数据
string UserName =txt_UserName.Value.Trim();
string UserPwd = txt_Upwd.Value.Trim();
//返回验证结果
int res;
//连接数据库
string connstring = "uid=sa;pwd=sa;server=.;database=SZW";
using (SqlConnection conn = new SqlConnection(connstring))
{
//构建命令
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "sp_SZW_Login";
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
//配置参数
SqlParameter username = cmd.Parameters.Add(new SqlParameter("@UserName", SqlDbType.NVarChar, 20));
//指明@UserName是输入参数
username.Direction = ParameterDirection.Input;
//为@UserName参数赋值
username.Value = UserName;
SqlParameter userpwd = cmd.Parameters.Add(new SqlParameter("@UserPwd", SqlDbType.NVarChar, 50));
userpwd.Direction = ParameterDirection.Input;
userpwd.Value = UserPwd;
//配置输出参数
SqlParameter returninfo = cmd.Parameters.Add(new SqlParameter("@ReturnInfo", SqlDbType.Int, 5));
//指明输出参数类型
returninfo.Direction = ParameterDirection.Output;
//打开连接
conn.Open();
//执行命令
cmd.ExecuteNonQuery();
//将返回参数赋值给验证结果
res = (int)cmd.Parameters["@ReturnInfo"].Value;
//对返回的结果进行判断
switch (res)
{
case 0:
//登录成功后保存密码
HttpCookie Coo_ReUserInfo = new HttpCookie("UserLogin_Info");
if (this.chk_RePwd.Checked)
{
Coo_ReUserInfo.Values.Add("C_UserName", this.txt_UserName.Value.Trim());
Coo_ReUserInfo.Values.Add("C_UserPwd", this.txt_Upwd.Value.Trim());
//设置cookie的过期时间为一周
Coo_ReUserInfo.Expires = System.DateTime.Now.AddDays(7);
//保存cookie对象
HttpContext.Current.Response.Cookies.Add(Coo_ReUserInfo);
}
else
{
if (Coo_ReUserInfo["UserLogin_Info"] != null)
{
Response.Cookies["UserLogin_Info"].Expires = System.DateTime.Now;
}
}
Response.Redirect("Register.aspx"); ;
break;
case 1:
Response.Write(LoginInfo.密码错误);
break;
case 2:
Response.Write(LoginInfo.用户名不存在);
break;
}
}
}
}