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); }