/**/
/**************************************************************************************************** * * * * 文件名 : LoginRegisterData.cs * * 创建者 : * * 创建日期 : 2005-3-3 * * 功能说明 : 网站用户登录注册的数据类. * * * * 备注 : * * Copyright (c) xxxx Corporation. All rights reserved. * ****************************************************************************************************/
using
System;
using
System.Data;
using
System.Data.SqlClient;
using
System.Web;
using
com.xxxx.Common ;
using
com.xxxx.DataAccess;
namespace
com.xxxx.DataAccess
{ /**//// <summary> /// LoginRegisterDate 的摘要说明。 /// </summary> public class LoginRegisterData :IDisposable { //用户选择使用卡号或用户名登录 private const string Sql_Member_Card_No = "SELECT top 1 card_no,password,status_id,mem_name,mobile,proxy,confirm_key,user_id FROM member WHERE card_no = @Card_No"; private const string Sql_Member_User_Id = "SELECT top 1 card_no,password,status_id,mem_name,mobile,proxy,confirm_key,user_id FROM member WHERE user_id = @User_Name"; private const string Sql_Member_User_Name = "SELECT count(user_id) FROM member where user_id = @User_Name"; //将会员信息插入member表 private const string Insert_Member = "INSERT INTO member () VALUES()"; //用户选择使用手机号或邮箱登录 private const string Sql_Member_MobileToCard = "SELECT top 1 card_no,password,status_id,mem_name,mobile,proxy,confirm_key,user_id FROM member WHERE card_no =(SELECT top 1 Card_No FROM MobileToCard WHERE Mobile=@Mobile)"; private const string Sql_Member_Mobile = "SELECT card_no,password,status_id,mem_name,mobile,proxy,confirm_key,user_id FROM member WHERE Contains(mobile, @Mobile)"; private const string Sql_Member_Email = "SELECT card_no,password,status_id,mem_name,mobile,proxy,confirm_key,user_id FROM member WHERE Contains(email, @Email)"; private const string Sql_MobileToCard_Card_No = "SELECT * FROM mobiletocard WHERE card_no = @card_no"; private const string Sql_Insert_MobileToCard = "INSERT INTO MobileToCard (mobile,card_no,operator,op_date) VALUES (@Mobile,@CardNo,@Operator,getdate())"; //查询是否是带密码的卡号 private const string Sql_Card_No = "SELECT top 1 * FROM card_no WHERE card_no = @Card_No and status =1"; private const string Sql_Update_MobileToCard = "UPDATE MobileToCard SET mobile=@mobile WHERE mobile = @mobile"; //每周统计各种登录方式的次数 private const string Sql_Login_Stat = "SELECT top 1 id,datediff(d,getdate(),date_end) as dateEndNow FROM login_stat ORDER BY id DESC"; private const string Sql_Insert_Login_Stat = "INSERT INTO login_stat(login_mobile,login_email,login_userid,login_card_no,date_start,date_end) VALUES(@p_login_mobile,@p_login_email,@p_login_userid,@p_login_card_no,getdate(),getdate()+6)"; private const string Sql_Update_Login_Stat = "UPDATE login_stat SET login_mobile=login_mobile+@p_login_mobile,login_email=login_email+@p_login_email,login_userid=login_userid+@p_login_userid,login_card_no=login_card_no+@p_login_card_no WHERE id=@id"; private string error_code; private string ConnStr ; private string CardNoString; private string PassWordStr; private string StatusIdStr; private string NameStr; private string MobileStr; private string ProxyStr; private string ConfirmKeyStr; private string UserNameStr; public LoginRegisterData() { // // TODO: 在此处添加构造函数逻辑 // } public LoginRegisterData(string connstr) { // // TODO: 在此处添加构造函数逻辑 // ConnStr = connstr; } /**//// <summary> /// 根据卡号，得到用户信息. /// </summary> public string LoginCardNo(string Card_No,string passWord,string loginType) { string error_code = string.Empty; SqlParameter arParms = new SqlParameter(); arParms = new SqlParameter("@Card_No", SqlDbType.BigInt ); arParms.Value = Card_No; using (SqlDataReader rdr = SqlHelper.ExecuteReader(ConnStr,CommandType.Text,Sql_Member_Card_No,arParms)) { if (rdr.Read()) { CardNoString = rdr.IsDBNull(0) ? string.Empty : rdr.GetInt64(0).ToString(); PassWordStr = rdr.IsDBNull(1) ? string.Empty : rdr.GetString(1).Trim(); StatusIdStr = rdr.IsDBNull(2) ? string.Empty : rdr.GetString(2); NameStr = rdr.IsDBNull(3) ? string.Empty : rdr.GetString(3).Trim(); //真实名 MobileStr = rdr.IsDBNull(4) ? string.Empty : rdr.GetString(4); ProxyStr = rdr.IsDBNull(5) ? string.Empty : rdr.GetString(5); ConfirmKeyStr= rdr.IsDBNull(6) ? string.Empty : rdr.GetString(6); UserNameStr = rdr.IsDBNull(7) ? string.Empty : rdr.GetString(7); //用户名 if (PassWordStr != passWord || PassWordStr.Length==0) { error_code = "password_error"; } else { if (StatusIdStr =="C" || StatusIdStr=="P") { error_code = "member_cancel"; } else { SelectLoginStat(loginType); SessionAdd(); error_code = "ok"; } } } else { error_code = "member_cardno"; } return error_code; } } /**//// <summary> /// 手机号或邮箱对应多张卡号，选出对应的卡号，得到用户信息. /// </summary> public string LoginCardNo(string Card_No,string passWord,string loginType,string mobile) { string error_code = string.Empty; SqlParameter arParms = new SqlParameter(); arParms = new SqlParameter("@Card_No", SqlDbType.BigInt ); arParms.Value = Card_No; using (SqlDataReader rdr = SqlHelper.ExecuteReader(ConnStr,CommandType.Text,Sql_Member_Card_No,arParms)) { if (rdr.Read()) { CardNoString = rdr.IsDBNull(0) ? string.Empty : rdr.GetInt64(0).ToString(); PassWordStr = rdr.IsDBNull(1) ? string.Empty : rdr.GetString(1).Trim(); StatusIdStr = rdr.IsDBNull(2) ? string.Empty : rdr.GetString(2); NameStr = rdr.IsDBNull(3) ? string.Empty : rdr.GetString(3).Trim(); //真实名 MobileStr = rdr.IsDBNull(4) ? string.Empty : rdr.GetString(4); ProxyStr = rdr.IsDBNull(5) ? string.Empty : rdr.GetString(5); ConfirmKeyStr= rdr.IsDBNull(6) ? string.Empty : rdr.GetString(6); UserNameStr = rdr.IsDBNull(7) ? string.Empty : rdr.GetString(7); //用户名 if (PassWordStr != passWord || PassWordStr.Length==0) { error_code = "password_error"; } else { if (StatusIdStr =="C" || StatusIdStr=="P") { error_code = "member_cancel"; } else { //卡号和密码验证通过，直接绑定。 InsertMobileToCard(CardNoString,mobile,NameStr); //插入统计表 SelectLoginStat(loginType); //生成Session SessionAdd(); error_code = "ok"; } } } else { error_code = "member_cardno"; } return error_code; } } /**//// <summary> /// 根据用户名，得到用户信息. /// </summary> public string LoginUserName(string User_Name,string passWord) { SqlParameter arParms = new SqlParameter(); arParms = new SqlParameter("@User_Name", SqlDbType.VarChar); arParms.Value = User_Name; using (SqlDataReader rdr = SqlHelper.ExecuteReader(ConnStr,CommandType.Text,Sql_Member_User_Id,arParms)) { if (rdr.Read()) { CardNoString = rdr.IsDBNull(0) ? string.Empty : rdr.GetInt64(0).ToString(); PassWordStr = rdr.IsDBNull(1) ? string.Empty : rdr.GetString(1).Trim(); StatusIdStr = rdr.IsDBNull(2) ? string.Empty : rdr.GetString(2); NameStr = rdr.IsDBNull(3) ? string.Empty : rdr.GetString(3).Trim(); //真实名 MobileStr = rdr.IsDBNull(4) ? string.Empty : rdr.GetString(4); ProxyStr = rdr.IsDBNull(5) ? string.Empty : rdr.GetString(5); ConfirmKeyStr= rdr.IsDBNull(6) ? string.Empty : rdr.GetString(6); UserNameStr = rdr.IsDBNull(7) ? string.Empty : rdr.GetString(7); //用户名 if (PassWordStr != passWord || PassWordStr.Length==0) { error_code = "password_error"; } else { if (StatusIdStr =="C" || StatusIdStr=="P") { error_code = "member_cancel"; } else { //插入统计表 SelectLoginStat("pName"); //生成Session SessionAdd(); error_code = "ok"; } } } else { error_code = "member_cardno"; } return error_code; } } /**//// <summary> /// 根据邮箱或手机号，得到用户信息. /// </summary> public string LoginEmailMobile(string Mobile,string passWord,string loginType) { SqlParameter arParms = new SqlParameter(); arParms = new SqlParameter("@Mobile", SqlDbType.VarChar); arParms.Value = Mobile; using (SqlDataReader rdr = SqlHelper.ExecuteReader(ConnStr,CommandType.Text,Sql_Member_MobileToCard,arParms)) { if (rdr.Read()) { CardNoString = rdr.IsDBNull(0) ? string.Empty : rdr.GetInt64(0).ToString(); PassWordStr = rdr.IsDBNull(1) ? string.Empty : rdr.GetString(1).Trim(); StatusIdStr = rdr.IsDBNull(2) ? string.Empty : rdr.GetString(2); NameStr = rdr.IsDBNull(3) ? string.Empty : rdr.GetString(3).Trim(); //真实名 MobileStr = rdr.IsDBNull(4) ? string.Empty : rdr.GetString(4); ProxyStr = rdr.IsDBNull(5) ? string.Empty : rdr.GetString(5); ConfirmKeyStr= rdr.IsDBNull(6) ? string.Empty : rdr.GetString(6); UserNameStr = rdr.IsDBNull(7) ? string.Empty : rdr.GetString(7); //用户名 if (PassWordStr != passWord || PassWordStr.Length==0) { error_code = "password_error"; } else { if (StatusIdStr =="C" || StatusIdStr=="P") { error_code = "member_cancel"; } else { //插入统计表 SelectLoginStat(loginType); //生成Session SessionAdd(); error_code = "ok"; } } } else { error_code = "EmailMobileNo"; } return error_code; } } /**//// <summary> /// 检查 Email 对应多少张卡号 /// </summary> public string MoreCardNoEmail(string Email,string passWord,string loginType) { SqlParameter arParms = new SqlParameter(); arParms = new SqlParameter("@Email", SqlDbType.VarChar ); arParms.Value = Email ; using (DataSet ds = SqlHelper.ExecuteDataset(ConnStr, CommandType.Text, Sql_Member_Email, arParms)) { int SumNumber = ds.Tables[0].Rows.Count; if (SumNumber==1) { CardNoString = ds.Tables[0].Rows[0][0].ToString(); PassWordStr = ds.Tables[0].Rows[0][1].ToString(); StatusIdStr = ds.Tables[0].Rows[0][2].ToString(); NameStr = ds.Tables[0].Rows[0][3].ToString(); MobileStr = ds.Tables[0].Rows[0][4].ToString(); ProxyStr = ds.Tables[0].Rows[0][5].ToString(); ConfirmKeyStr= ds.Tables[0].Rows[0][6].ToString(); UserNameStr = ds.Tables[0].Rows[0][7].ToString(); if (PassWordStr != passWord || PassWordStr.Length==0) { error_code = "password_error"; } else { if (StatusIdStr =="C" || StatusIdStr=="P") { error_code = "member_cancel"; } else { //如果邮箱只对应一张卡号，并且卡号和密码相对应，直接绑定。 InsertMobileToCard(CardNoString,Email,NameStr); //插入统计表 SelectLoginStat(loginType); //生成Session SessionAdd(); error_code = "ok"; } } } else { if (SumNumber==0) { error_code = "member_cardno"; } else { for (int i=0;i<SumNumber;i++) { CardNoString = CardNoString + ds.Tables[0].Rows[i][0].ToString() +","; } //去掉未尾的"," SessionManage.MoreCardEmailMobile = CardNoString.Substring(0,CardNoString.Length-1); error_code = "more_cardno"; //SessionManage.CardNo = Email; } } return error_code; } } /**//// <summary> /// 检查 手机号 对应多少张卡号 /// </summary> public string MoreCardNoMobile(string Mobile,string passWord,string loginType) { SqlParameter arParms = new SqlParameter(); arParms = new SqlParameter("@Mobile", SqlDbType.VarChar ); arParms.Value = Mobile; using (DataSet ds = SqlHelper.ExecuteDataset(ConnStr,CommandType.Text,Sql_Member_Mobile,arParms)) { int SumNumber = ds.Tables[0].Rows.Count; if (SumNumber==1) { CardNoString = ds.Tables[0].Rows[0][0].ToString(); PassWordStr = ds.Tables[0].Rows[0][1].ToString(); StatusIdStr = ds.Tables[0].Rows[0][2].ToString(); NameStr = ds.Tables[0].Rows[0][3].ToString(); MobileStr = ds.Tables[0].Rows[0][4].ToString(); ProxyStr = ds.Tables[0].Rows[0][5].ToString(); ConfirmKeyStr= ds.Tables[0].Rows[0][6].ToString(); UserNameStr = ds.Tables[0].Rows[0][7].ToString(); if (PassWordStr != passWord || PassWordStr.Length==0) { error_code = "password_error"; } else { if (StatusIdStr =="C" || StatusIdStr=="P") { error_code = "member_cancel"; } else { //如果手机号只对应一张卡号，并且卡号和密码相对应，直接绑定。 InsertMobileToCard(CardNoString,Mobile,NameStr); //插入统计表 SelectLoginStat(loginType); //生成Session SessionAdd(); error_code = "ok"; } } } else { if (SumNumber==0) { error_code = "member_cardno"; } else { for (int i=0;i<SumNumber;i++) { CardNoString = CardNoString + ds.Tables[0].Rows[i][0].ToString() +","; } //去掉未尾的"," SessionManage.MoreCardEmailMobile = CardNoString.Substring(0,CardNoString.Length-1); error_code = "more_cardno"; //SessionManage.CardNo = Mobile; } } return error_code; } } /**//// <summary> /// 登录成功生成 Session /// </summary> private void SessionAdd() { SessionManage.mem_name = NameStr; SessionManage.UserName = UserNameStr; SessionManage.AccountName=""; SessionManage.Member = CardNoString; SessionManage.Card_No = CardNoString; SessionManage.CardNo = CardNoString; SessionManage.LoginTime= DateTime.Now; } /**//// <summary> /// 验证 Mobile 是否存过 /// </summary> public string CheckMemberMobile(string Mobile) { string emailInfo =string.Empty; SqlParameter arParms = new SqlParameter(); arParms = new SqlParameter("@Mobile", SqlDbType.VarChar ); arParms.Value = Mobile ; using (SqlDataReader rdr = SqlHelper.ExecuteReader(ConnStr, CommandType.Text, Sql_Member_Mobile, arParms)) { if (rdr.Read()) { emailInfo = rdr.IsDBNull(0) ? string.Empty : rdr.GetString(0); return emailInfo; } return ""; } } //查询loginStat表，用来判断是选择插入操作还是更新操作？ public string SelectLoginStat(string loginType) { int p_login_mobile =0; int p_login_email =0; int p_login_userid =0; int p_login_card_no=0; switch(loginType) { case "pName": p_login_userid =1; break; case "pEmail": p_login_email =1; break; case "1": p_login_mobile =1; break; default : p_login_card_no=1; break; } using (SqlDataReader rdr = SqlHelper.ExecuteReader(ConnStr, CommandType.Text,Sql_Login_Stat)) { if (rdr.Read()) { int id = rdr.IsDBNull(0) ? 0 : rdr.GetInt32(0); int dateEndNow = rdr.IsDBNull(1) ? 0 : rdr.GetInt32(1); if (dateEndNow >= 0) { UpdateLoginStat(p_login_mobile,p_login_email,p_login_userid,p_login_card_no,id); } else { InsertLoginStat(p_login_mobile,p_login_email,p_login_userid,p_login_card_no); } } return "ok"; } } //插入每周各种登录类型的次数 public bool InsertLoginStat(int p_login_mobile,int p_login_email, int p_login_userid,int p_login_card_no) { SqlParameter [] arParms = new SqlParameter[4]; arParms[0] = new SqlParameter("@p_login_mobile", SqlDbType.VarChar ); arParms[0].Value = p_login_mobile; arParms[1] = new SqlParameter("@p_login_email", SqlDbType.VarChar ); arParms[1].Value = p_login_email; arParms[2] = new SqlParameter("@p_login_userid", SqlDbType.VarChar ); arParms[2].Value = p_login_userid; arParms[3] = new SqlParameter("@p_login_card_no", SqlDbType.VarChar ); arParms[3].Value = p_login_card_no; using (SqlConnection conn = new SqlConnection(ConnStr)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { SqlHelper.ExecuteNonQuery(trans, CommandType.Text,Sql_Insert_Login_Stat,arParms); trans.Commit(); return true; } catch { trans.Rollback(); throw; } } } } //更新本周各种登录类型的次数 public bool UpdateLoginStat(int p_login_mobile,int p_login_email,int p_login_userid,int p_login_card_no,int id) { SqlParameter [] arParms = new SqlParameter[5]; arParms[0] = new SqlParameter("@p_login_mobile", SqlDbType.VarChar ); arParms[0].Value = p_login_mobile ; arParms[1] = new SqlParameter("@p_login_email", SqlDbType.VarChar ); arParms[1].Value = p_login_email; arParms[2] = new SqlParameter("@p_login_userid", SqlDbType.VarChar ); arParms[2].Value = p_login_userid; arParms[3] = new SqlParameter("@p_login_card_no", SqlDbType.VarChar ); arParms[3].Value = p_login_card_no; arParms[4] = new SqlParameter("@id", SqlDbType.VarChar ); arParms[4].Value = id; using (SqlConnection conn = new SqlConnection(ConnStr)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { SqlHelper.ExecuteNonQuery(trans, CommandType.Text,Sql_Update_Login_Stat,arParms); trans.Commit(); return true; } catch { trans.Rollback(); throw; } } } } //插入到会员绑定表 MobileToCard 中 public bool InsertMobileToCard(string CardNo,string Mobile,string Operator) { SqlParameter [] arParms = new SqlParameter[3]; arParms[0] = new SqlParameter("@Mobile", SqlDbType.VarChar ); arParms[0].Value = Mobile ; arParms[1] = new SqlParameter("@CardNo", SqlDbType.VarChar ); arParms[1].Value = CardNo; arParms[2] = new SqlParameter("@Operator", SqlDbType.VarChar ); arParms[2].Value = Operator; using (SqlConnection conn = new SqlConnection(ConnStr)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { try { SqlHelper.ExecuteNonQuery(trans, CommandType.Text, Sql_Insert_MobileToCard, arParms); trans.Commit(); return true; } catch { trans.Rollback(); throw; } } } } //检验用户名是否存在 public int CheckUserName(string User_Nmae) { SqlParameter arParms = new SqlParameter(); arParms = new SqlParameter("@User_Name", SqlDbType.VarChar ); arParms.Value = User_Nmae ; int count = 0; using (SqlDataReader rdr = SqlHelper.ExecuteReader(ConnStr,CommandType.Text,Sql_Member_User_Name,arParms)) { if (rdr.Read()) { count = rdr.IsDBNull(0) ? 0 : rdr.GetInt32(0); return count; } return -1; } } IDisposable 成员#region IDisposable 成员 //供使用者显式调用的Dispose方法 public void Dispose() { //调用带参数的Dispose方法，释放托管和非托管资源 Dispose(true); //手动调用了Dispose释放资源，以防止垃圾回收器对不需要终止的对象调用 Object.Finalize。 GC.SuppressFinalize(this); } //释放对象的实例变量.传入bool值disposing以确定是否释放托管资源 protected virtual void Dispose(bool disposing) { if (! disposing) return; //在这里加入清理"托管资源"的代码，应该是xxx.Dispose(); // if (_Helper != null) // { // _Helper.Dispose(); // } } #endregion }}
转载于:https://www.cnblogs.com/Elong/archive/2005/09/01/227995.html
相关资源：使用SQLHelper类调用带输出、返回参数的存储过程
