//驗證使用者
public bool ValidateUser()
{
string sqlcmd = @"SELECT * FROM Users WHERE Username = @Username AND Password = @Password";
using (SqlConnection conn = new SqlConnection(sqlconn))
{
using (SqlCommand cmd = new SqlCommand(sqlcmd, conn))
{
cmd.CommandType = CommandType.Text;
SqlParameter pUser = new SqlParameter("@Username", SqlDbType.NVarChar, 16);
pUser = new SqlParameter("@Username", SqlDbType.NVarChar, 16);
pUser.Direction = ParameterDirection.Input;
pUser.Value = Username;
cmd.Parameters.Add(pUser);
SqlParameter pPassword = new SqlParameter("@Password", SqlDbType.VarBinary, 32);
pPassword.Direction = ParameterDirection.Input;
pPassword.Value = IDo.HashPw(Password, GetRanNum(Username));
cmd.Parameters.Add(pPassword);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
return(true);
}
else
{
return(false);
}
}
}
}
//使用者更改密碼 清除資料庫驗證碼
public static bool UpdateUser(string Username, string nPassword)
{
string sqlcmd = @"UPDATE Users SET Password=@NewPW,RanNum=@NewRanNum,AuthCode=@AuthCode WHERE Username = @Username";
using (SqlConnection conn = new SqlConnection(sqlconn))
{
using (SqlCommand cmd = new SqlCommand(sqlcmd, conn))
{
cmd.CommandType = CommandType.Text;
SqlParameter pUsername = new SqlParameter("@Username", SqlDbType.NVarChar, 16);
pUsername.Direction = ParameterDirection.Input;
pUsername.Value = Username;
cmd.Parameters.Add(pUsername);
SqlParameter pAuthCode = new SqlParameter("@AuthCode", SqlDbType.NVarChar, 8);
pAuthCode.Direction = ParameterDirection.Input;
pAuthCode.Value = String.Empty;
cmd.Parameters.Add(pAuthCode);
string RanNum = Guid.NewGuid().ToString("N");
SqlParameter pRannum = new SqlParameter("@NewRanNum", SqlDbType.NVarChar, 32);
pRannum.Direction = ParameterDirection.Input;
pRannum.Value = RanNum;
cmd.Parameters.Add(pRannum);
SqlParameter pNewPW = new SqlParameter("@NewPW", SqlDbType.VarBinary, 32);
pNewPW.Direction = ParameterDirection.Input;
pNewPW.Value = IDo.HashPw(nPassword, RanNum);//新雜湊
cmd.Parameters.Add(pNewPW);
conn.Open();
int n = (int)cmd.ExecuteNonQuery();
if (n != 0)
{
return(true);
}
else
{
return(false);
}
}
}
}
//新建使用者
public void CreateUser()
{
string sqlcmd = @"Insert into Users(Username,Password,Email,RanNum) Values(@Username,@Password,@Email,@RanNum) ";
using (SqlConnection conn = new SqlConnection(sqlconn))
{
using (SqlCommand cmd = new SqlCommand(sqlcmd, conn))
{
cmd.CommandType = CommandType.Text;
SqlParameter pUsername = new SqlParameter("@Username", SqlDbType.NVarChar, 16);
pUsername.Direction = ParameterDirection.Input;
pUsername.Value = Username;
cmd.Parameters.Add(pUsername);
string RanNum = Guid.NewGuid().ToString("N");
SqlParameter pRannum = new SqlParameter("@RanNum", SqlDbType.NVarChar, 32);
pRannum.Direction = ParameterDirection.Input;
pRannum.Value = RanNum;
cmd.Parameters.Add(pRannum);
SqlParameter pPassword = new SqlParameter("@Password", SqlDbType.VarBinary, 32);
pPassword.Direction = ParameterDirection.Input;
pPassword.Value = IDo.HashPw(Password, RanNum);
cmd.Parameters.Add(pPassword);
SqlParameter pEmail = new SqlParameter("@Email", SqlDbType.NVarChar, 64);
pEmail.Direction = ParameterDirection.Input;
pEmail.Value = Email;
cmd.Parameters.Add(pEmail);
conn.Open();
cmd.ExecuteNonQuery();
}
}
}
//更新密碼跟雜湊用亂碼
/// <summary>
/// Username為註冊的帳號 , Email為註冊的Email, np 新密碼
/// </summary>
/// <param name="Username"></param>
/// <param name="Email"></param>
/// <param name="np"></param>
public static void UpdatePW(string Username, string Email, string np)
{
string sqlcmd = @"UPDATE Users SET Password=@NewPW,RanNum=@NewRanNum WHERE Username = @Username AND Email = @Email";
using (SqlConnection conn = new SqlConnection(sqlconn))
{
using (SqlCommand cmd = new SqlCommand(sqlcmd, conn))
{
cmd.CommandType = CommandType.Text;
SqlParameter pUsername = new SqlParameter("@Username", SqlDbType.NVarChar, 16);
pUsername.Direction = ParameterDirection.Input;
pUsername.Value = Username;
cmd.Parameters.Add(pUsername);
SqlParameter pEmail = new SqlParameter("@Email", SqlDbType.NVarChar, 64);
pEmail.Direction = ParameterDirection.Input;
pEmail.Value = Email;
cmd.Parameters.Add(pEmail);
string RanNum = Guid.NewGuid().ToString("N");
SqlParameter pRannum = new SqlParameter("@NewRanNum", SqlDbType.NVarChar, 32);
pRannum.Direction = ParameterDirection.Input;
pRannum.Value = RanNum;
cmd.Parameters.Add(pRannum);
SqlParameter pNewPW = new SqlParameter("@NewPW", SqlDbType.VarBinary, 32);
pNewPW.Direction = ParameterDirection.Input;
pNewPW.Value = IDo.HashPw(np, RanNum);//新雜湊
cmd.Parameters.Add(pNewPW);
conn.Open();
cmd.ExecuteNonQuery();
}
}
}
