public int UpdateUser(string value, string column, string id_user)
{
int result = -1;
StringBuilder sb = new StringBuilder();
sb.Append(" UPDATE TBL_UserInfo SET @column = '@value' WHERE _ID_User = @id_user; ");
sb.Replace("@column", column);
sb.Replace("@value", value);
sb.Replace("@id_user", id_user);
try
{
using (
SqlServerMethod sql = new SqlServerMethod(ConnectionStringDB))
{
sql.ExcuteQuery(sb);
}
}
catch (Exception)
{
result = -1;
}
result = CheckValueExisted(value, column, id_user);
return(result);
}
/// <summary>
/// -1 : lỗi
/// 0 : Xóa không thành công
/// 1 : Xóa tài khoản thành công
/// </summary>
/// <param name="account_name"></param>
/// <returns></returns>
public int DeleteUser(string account_name)
{
int result = -1;
StringBuilder query = new StringBuilder();
query.Append(" DELETE FROM TBL_UserInfo WHERE _AccountName = '@accountname'; ");
query.Replace("@accountname", account_name);
try
{
using (SqlServerMethod sql = new SqlServerMethod(ConnectionStringDB))
{
sql.ExcuteQuery(query);
}
}
catch (Exception)
{
result = -1;
}
if (CheckAccountExist(account_name))
{
result = 0;
}
else
{
result = 1;
}
return(result);
}
/// <summary>
///
/// </summary>
/// <param name="value"></param>
/// <param name="column"></param>
/// <param name="iduser"></param>
/// <returns></returns>
public int CheckValueExisted(string value, string column, string id_user)
{
int result = -1;
StringBuilder query = new StringBuilder();
query.Append(" SELECT * FROM TBL_UserInfo WHERE @column = '@value' AND _ID_User = '******'; ");
query.Replace("@column", column);
query.Replace("@value", value);
query.Replace("@id_user", id_user);
try
{
using (SqlServerMethod sql = new SqlServerMethod(ConnectionStringDB))
{
DataTable data = sql.GetDataSqlToTable(query.ToString());
int icheck = data.Rows.Count;
if (icheck == 0)
{
result = 0; //giá trị không tồn tại
}
else if (icheck == 1)
{
result = icheck; //giá trị tồn tại
}
}
}
catch (Exception)
{
result = -1;
}
return(result);
}
/// <summary>
/// Đổi mật khẩu thông qua 3 thông số chính. Acount, oldpass, newpass
/// Hành động sau khi đổi mật khẩu sẽ thoát ra ngoài.
/// -1: Error / 1: thành công.
/// </summary>
/// <param name="accountName"></param>
/// <param name="oldPass"></param>
/// <param name="newPass"></param>
/// <returns></returns>
public int ChangePassword(string accountName, string oldPass, string newPass)
{
int result = -1;
if (!CheckAccountExist(accountName, oldPass)) //kiểm tra tài khoảng và mật khẩu cũ có tồn tại hay ko chưa?
{
result = -1; //Giá trị có nghĩa tài khoảng hoặc mật khẩu không đúng.
}
else
{
//thực hiện update mật khẩu tại đây.
string hashPass = CryptData.StringCipher.Encrypt(newPass, accountName); //má hóa mật khẩu mới.
StringBuilder query = new StringBuilder();
query.Append("UPDATE TBL_UserInfo set _Pass = '******',_HashPass = '******'");
query.Append("WHERE _AccountName ='@AccountName';");
query.Replace("@AccountName", accountName);
query.Replace("@pass", newPass);
query.Replace("@hashpass", hashPass);
using (SqlServerMethod sqlMethod = new SqlServerMethod(ConnectionStringDB))
{
sqlMethod.ExcuteQuery(query);
}
if (CheckAccountExist(accountName, newPass)) // kiểm tra mật khẩu mới có hoạt động hay ko
{
result = 1;
}
}
return(result);
}
public bool CheckAccountExist(string user)
{
bool result = false;
StringBuilder sb = new StringBuilder();
sb.Append("SELECT COUNT(0) FROM TBL_UserInfo WHERE _AccountName = '@AccountName';");
sb.Replace("@AccountName", user);
try
{
using (SqlServerMethod sql = new SqlServerMethod(ConnectionStringDB))
{
DataTable data = sql.GetDataSqlToTable(sb.ToString());
int count = data.Rows[0].Field <int>(0);
if (count >= 1)
{
result = true;
}
//_AccountName = user;
}
}
catch (Exception ex)
{
}
//GetStatusLogin = result;
return(result);
}
public bool CheckAccountExist(string user, string pass)
{
bool result = false;
StringBuilder sb = new StringBuilder();
sb.Append("SELECT _HashPass FROM TBL_UserInfo WHERE _AccountName = '@AccountName';");
sb.Replace("@AccountName", user);
try
{
using (SqlServerMethod sql = new SqlServerMethod(ConnectionStringDB))
{
DataTable data = sql.GetDataSqlToTable(sb.ToString());
string hashpass = data.Rows[0].Field <string>(0);
string EnCryptPass = CryptData.StringCipher.Decrypt(hashpass, user);
if (pass == EnCryptPass)
{
result = true;
}
//_AccountName = user;
}
}
catch (Exception ex)
{
}
//GetStatusLogin = result;
return(result);
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public DataTable GetAllUserInfo()
{
if (GetStatusLogin)
{
string query = "SELECT * FROM TBL_UserInfo";
using (SqlServerMethod sql = new SqlServerMethod(ConnectionStringDB))
{
DataTable data = sql.GetDataSqlToTable(query);
return(data);
}
}
return(null);
}
/// <summary>
/// Get infomation of the user login
/// </summary>
/// <returns></returns>
public DataTable GetUserInfoByLongin()
{
if (GetStatusLogin)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * FROM TBL_UserInfo WHERE _AccountName = '@AccountName';");
sb.Replace("@AccountName", _AccountName);
using (SqlServerMethod sql = new SqlServerMethod(ConnectionStringDB))
{
DataTable data = sql.GetDataSqlToTable(sb.ToString());
_Status = "Login";
GetStatusLogin = true;
return(data);
}
}
return(null);
}
public DataTable SearchUser(string infoSearch, string collum)
{
DataTable result = null;
StringBuilder query = new StringBuilder();
query.Append(" SELECT * FROM TBL_UserInfo WHERE @collum LIKE '%@infoSearch%'; ");
query.Replace("@collum", collum);
query.Replace("@infoSearch", infoSearch);
try
{
using (SqlServerMethod sql = new SqlServerMethod(ConnectionStringDB))
{
DataTable data = sql.GetDataSqlToTable(query.ToString());
result = data;
}
}
catch { }
return(result);
}
//Hashtable buffUserInfo = new Hashtable(10);
//Menthods
/// <summary>
/// Tạo user dựa trên các thông số cài đặt
/// </summary>
public void CreateUser()
{
using (SqlServerMethod sql = new SqlServerMethod(ConnectionStringDB))
{
_CryptPass = CryptData.StringCipher.Encrypt(_Pass, _AccountName);
StringBuilder sb = new StringBuilder();
sb.Append(@" INSERT INTO TBL_UserInfo(_AccountName, _FullName, _StaffCode, _Email, _Pass, _HashPass, _Permission, _DateCreate, _Enable,_Status ) ");
sb.Append(@" VALUES ('@AccountName','@FullName','@StaffCode','@Email','@Pass','@HashPass', '@Permission', '@DateCreate','@Enable','@Status'); ");
sb.Replace("@AccountName", _AccountName);
sb.Replace("@FullName", _FullName);
sb.Replace("@StaffCode", _StaffCode);
sb.Replace("@Email", _Email);
sb.Replace("@Pass", _Pass);
sb.Replace("@HashPass", _CryptPass);
sb.Replace("@Permission", _Permission);
sb.Replace("@DateCreate", _DateCreate);
sb.Replace("@Enable", _Enable);
sb.Replace("@status", _Status);
sql.ExcuteQuery(sb);
}
}