//驗證使用者 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(); } } }