public Hashtable CreateUser(RegisterModel register, String createBy)
{
if (String.IsNullOrEmpty(register.UserName))
{
throw new ArgumentException("Value cannot be null or empty.", "username");
}
if (String.IsNullOrEmpty(register.Email))
{
throw new ArgumentException("Value cannot be null or empty.", "email");
}
if (String.IsNullOrEmpty(register.CitizenID))
{
throw new ArgumentException("Value cannot be null or empty.", "citizen");
}
if (String.IsNullOrEmpty(createBy))
{
throw new ArgumentException("Value cannot be null or empty.", "createBy");
}
MySqlConnection conn = null;
MySqlTransaction tran = null;
Hashtable result = new Hashtable();
bool process = false;
string msg = "";
try
{
using (conn = new MySqlConnection(GetConnectionString()))
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
using (MySqlCommand cmd = new MySqlCommand(Resources.SQLResource.USP_INS_USERS, conn, tran))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.Add("p_user_name", MySqlDbType.VarChar).Value = register.UserName;
cmd.Parameters.Add("p_password", MySqlDbType.VarChar).Value = ContentHelpers.Isnull(register.Password) ?
ContentHelpers.MD5Hash(Resources.ConfigResource.PASSWORD_DEFAULT) :
ContentHelpers.MD5Hash(register.Password);
cmd.Parameters.Add("p_roleid", MySqlDbType.VarChar).Value = register.RoleID;
cmd.Parameters.Add("p_citizenid", MySqlDbType.VarChar).Value = register.CitizenID;
cmd.Parameters.Add("p_name", MySqlDbType.VarChar).Value = register.Name;
cmd.Parameters.Add("p_email", MySqlDbType.VarChar).Value = register.Email;
cmd.Parameters.Add("p_phone", MySqlDbType.VarChar).Value = register.Phone;
cmd.Parameters.Add("p_create_by", MySqlDbType.VarChar).Value = createBy;
cmd.Parameters.Add(new MySqlParameter("oMessage", MySqlDbType.VarChar)).Direction = ParameterDirection.Output;
cmd.Parameters.Add(new MySqlParameter("oUserID", MySqlDbType.Int32)).Direction = ParameterDirection.Output;
cmd.ExecuteScalar();
//
int userId = cmd.Parameters["oUserID"].Value == System.DBNull.Value ? 0 : Convert.ToInt32(cmd.Parameters["oUserID"].Value);
if (userId > 0)
{
tran.Commit();
process = true;
}
msg = Convert.ToString(cmd.Parameters["oMessage"].Value);
}
}
}
catch (MySqlException ms)
{
throw new Exception("MySqlException: " + ms.Message);
}
catch (Exception)
{
tran.Rollback();
throw;
}
finally
{
conn.Close();
conn.Dispose();
}
result["Status"] = process;
result["Message"] = msg;
return(result);
}
public Boolean UpdateUser(RegisterModel register, String updateBy)
{
if (String.IsNullOrEmpty(register.UserName))
{
throw new ArgumentException("Value cannot be null or empty.", "username");
}
if (String.IsNullOrEmpty(register.Email))
{
throw new ArgumentException("Value cannot be null or empty.", "email");
}
if (String.IsNullOrEmpty(register.CitizenID))
{
throw new ArgumentException("Value cannot be null or empty.", "citizen");
}
if (String.IsNullOrEmpty(updateBy))
{
throw new ArgumentException("Value cannot be null or empty.", "updateBy");
}
MySqlConnection conn = null;
MySqlTransaction tran = null;
bool process = false;
try
{
using (conn = new MySqlConnection(GetConnectionString()))
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
tran = conn.BeginTransaction(IsolationLevel.ReadCommitted);
using (MySqlCommand cmd = new MySqlCommand(Resources.SQLResource.USP_UPD_USERS, conn, tran))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.Add("p_user_name", MySqlDbType.VarChar).Value = register.UserName;
cmd.Parameters.Add("p_password", MySqlDbType.VarChar).Value = ContentHelpers.Isnull(register.Password) ?
ContentHelpers.MD5Hash(Resources.ConfigResource.PASSWORD_DEFAULT) :
ContentHelpers.MD5Hash(register.Password);
cmd.Parameters.Add("p_roleid", MySqlDbType.VarChar).Value = register.RoleID;
cmd.Parameters.Add("p_citizenid", MySqlDbType.VarChar).Value = register.CitizenID;
cmd.Parameters.Add("p_name", MySqlDbType.VarChar).Value = register.Name;
cmd.Parameters.Add("p_email", MySqlDbType.VarChar).Value = register.Email;
cmd.Parameters.Add("p_phone", MySqlDbType.VarChar).Value = register.Phone;
cmd.Parameters.Add("p_update_by", MySqlDbType.VarChar).Value = updateBy;
int excute = cmd.ExecuteNonQuery();
//
if (excute > 0)
{
tran.Commit();
process = true;
}
}
}
}
catch (MySqlException ms)
{
throw new Exception("MySqlException: " + ms.Message);
}
catch (Exception)
{
tran.Rollback();
throw;
}
finally
{
conn.Close();
conn.Dispose();
}
return(process);
}
public Hashtable ValidateUser(string userName, string password)
{
if (String.IsNullOrEmpty(userName))
{
throw new ArgumentException("Value cannot be null or empty.", "userName");
}
if (String.IsNullOrEmpty(password))
{
throw new ArgumentException("Value cannot be null or empty.", "password");
}
MySqlConnection conn = null;
MySqlCommand cmd = null;
Hashtable result = new Hashtable();
bool process = false;
string msg = "";
string userId = "";
try
{
using (conn = new MySqlConnection(GetConnectionString()))
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
using (cmd = new MySqlCommand(Resources.SQLResource.USP_GET_USERS_LOGIN, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.Add("iUsername", MySqlDbType.VarChar).Value = userName;
cmd.Parameters.Add("iPassword", MySqlDbType.VarChar).Value = ContentHelpers.MD5Hash(password);
cmd.Parameters.Add(new MySqlParameter("oMessage", MySqlDbType.VarChar)).Direction = ParameterDirection.Output;
cmd.Parameters.Add(new MySqlParameter("oUserID", MySqlDbType.Int32)).Direction = ParameterDirection.Output;
cmd.ExecuteScalar();
int ouserId = cmd.Parameters["oUserID"].Value == System.DBNull.Value ? 0 : Convert.ToInt32(cmd.Parameters["oUserID"].Value);
if (ouserId > 0)
{
userId = ouserId.ToString();
using (cmd = new MySqlCommand(Resources.SQLResource.USP_GET_USERS_PERMISSION, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.Add("iUsername", MySqlDbType.VarChar).Value = userName;
cmd.Parameters.Add(new MySqlParameter("oMessage", MySqlDbType.VarChar)).Direction = ParameterDirection.Output;
cmd.Parameters.Add(new MySqlParameter("oRoleCode", MySqlDbType.VarChar)).Direction = ParameterDirection.Output;
cmd.ExecuteScalar();
string roleCode = cmd.Parameters["oRoleCode"].Value == System.DBNull.Value ? "" :
Convert.ToString(cmd.Parameters["oRoleCode"].Value);
if (ContentHelpers.IsNotnull(roleCode))
{
result["RoleCode"] = roleCode;
process = true;
}
}
}
msg = Convert.ToString(cmd.Parameters["oMessage"].Value);
}
}
}
catch (Exception)
{
throw;
}
finally
{
conn.Close();
conn.Dispose();
}
result["Status"] = process;
result["Message"] = msg;
result["userId"] = userId;
return(result);
}