使用sqlhelper

/****************************************************************************************************

* *

* * 文件名 : LoginRegisterData.cs

* * 创建者 :

* * 创建日期 : 2005-3-3

* * 功能说明 : 网站用户登录注册的数据类.

* *

* * 备注 :

* ****************************************************************************************************/

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)