/// <summary> /// Login /// </summary> /// <param name="username"></param> /// <param name="password"></param> /// <param name="expiredDays"></param> /// <param name="result"></param> /// <returns>Return all feature of this user</returns> /// <remarks> /// Author: PhatLT. FPTSS. /// Created date: 14/02/2011 /// </remarks> public DataTable Login(string username, string password, int expiredDays, out int result) { clsCryptography crypto = new clsCryptography(); SqlConnection con = Connection; SqlCommand cmd = null; DataTable dt = null; try { cmd = new SqlCommand("sp_CheckValidLogin", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@i_sUsername", SqlDbType.VarChar, 20).Value = username; cmd.Parameters.Add("@i_sPassword", SqlDbType.VarChar, 20).Value = crypto.Encode(password); cmd.Parameters.Add("@i_nPwdExpired", SqlDbType.Int, 4).Value = expiredDays; cmd.Parameters.Add("@o_nResult", SqlDbType.Int, 4); cmd.Parameters["@o_nResult"].Direction = ParameterDirection.Output; cmd.CommandTimeout = 3600; dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); result = Convert.ToInt32(cmd.Parameters["@o_nResult"].Value); return(dt); } catch (Exception ex) { log.Error(ex.ToString()); throw new Exception("clsAutUserDAO.LogIn error"); } }
/// <summary> /// Insert one row into GENERAL_AUT_USER table /// </summary> /// <param name="row"></param> /// <returns></returns> /// <remarks> /// Author: PhatLT. FPTSS. /// Created date: 14/02/2011 /// </remarks> public int Insert(DataRow row) { SqlConnection con = Connection; SqlTransaction trans = null; SqlCommand cmd = null; clsCryptography crypto = new clsCryptography(); int count = 0; try { if (con.State != ConnectionState.Open) { con.Open(); } trans = con.BeginTransaction(); cmd = new SqlCommand("INSERT INTO GENERAL_AUT_USER(USERNAME, PASSWORD, FIRSTNAME, LASTNAME, EMAIL, ADDRESS, PHONE, START_DATE, END_DATE, PWD_CHG_DATE, STATUS, UROLE_ID, DESCRIPTION) VALUES(@USERNAME, @PASSWORD, @FIRSTNAME, @LASTNAME, @EMAIL, @ADDRESS, @PHONE, @START_DATE, @END_DATE, getdate(), @STATUS, @UROLE_ID, @DESCRIPTION)", con, trans); cmd.Parameters.Add("@USERNAME", SqlDbType.VarChar).Value = row["USERNAME"]; cmd.Parameters.Add("@PASSWORD", SqlDbType.VarChar).Value = crypto.Encode(row["PASSWORD"].ToString()); cmd.Parameters.Add("@FIRSTNAME", SqlDbType.NVarChar).Value = row["FIRSTNAME"]; cmd.Parameters.Add("@LASTNAME", SqlDbType.NVarChar).Value = row["LASTNAME"]; cmd.Parameters.Add("@EMAIL", SqlDbType.VarChar).Value = row["EMAIL"]; cmd.Parameters.Add("@ADDRESS", SqlDbType.NVarChar).Value = row["ADDRESS"]; cmd.Parameters.Add("@PHONE", SqlDbType.VarChar).Value = row["PHONE"]; cmd.Parameters.Add("@START_DATE", SqlDbType.DateTime).Value = row["START_DATE"]; cmd.Parameters.Add("@END_DATE", SqlDbType.DateTime).Value = row["END_DATE"]; //cmd.Parameters.Add("@PWD_CHG_DATE", SqlDbType.DateTime).Value = row["PWD_CHG_DATE"]; cmd.Parameters.Add("@STATUS", SqlDbType.Char).Value = row["STATUS"]; cmd.Parameters.Add("@UROLE_ID", SqlDbType.VarChar).Value = row["UROLE_ID"]; cmd.Parameters.Add("@DESCRIPTION", SqlDbType.NVarChar).Value = row["DESCRIPTION"]; count = cmd.ExecuteNonQuery(); trans.Commit(); } catch (SqlException ex) { log.Error(ex.Message, ex); if (trans != null) { trans.Rollback(); } throw ex; } catch (Exception ex) { log.Error(ex.Message, ex); if (trans != null) { trans.Rollback(); } throw ex; } finally { if (con != null && con.State == ConnectionState.Open) { con.Close(); } } return(count); }
/// <summary> /// Update one row of GENERAL_AUT_USER by USERNAME /// </summary> /// <param name="row"></param> /// <returns></returns> /// <remarks> /// Author: PhatLT. FPTSS. /// Created date: 14/02/2011 /// </remarks> public int Update(DataRow row) { SqlConnection con = Connection; SqlTransaction trans = null; SqlCommand cmd = null; clsCryptography crypto = new clsCryptography(); int count = 0; try { string newPassword = row["PASSWORD"].ToString(); if (con.State != ConnectionState.Open) { con.Open(); } trans = con.BeginTransaction(); string strPWDChangeDate = ""; if (!row["PASSWORD"].Equals(row["OLDPASSWORD"])) { strPWDChangeDate = " , PWD_CHG_DATE = getdate() "; } string cmdText = string.Format("UPDATE GENERAL_AUT_USER SET PASSWORD = @PASSWORD, FIRSTNAME = @FIRSTNAME, LASTNAME = @LASTNAME, EMAIL = @EMAIL, ADDRESS = @ADDRESS, PHONE = @PHONE, START_DATE = @START_DATE, END_DATE = @END_DATE, STATUS = @STATUS, UROLE_ID = @UROLE_ID, DESCRIPTION = @DESCRIPTION {0} WHERE USERNAME = @USERNAME", strPWDChangeDate); cmd = new SqlCommand(cmdText, con, trans); cmd.Parameters.Add("@USERNAME", SqlDbType.VarChar).Value = row["USERNAME"]; cmd.Parameters.Add("@PASSWORD", SqlDbType.VarChar).Value = crypto.Encode(newPassword); cmd.Parameters.Add("@FIRSTNAME", SqlDbType.NVarChar).Value = row["FIRSTNAME"]; cmd.Parameters.Add("@LASTNAME", SqlDbType.NVarChar).Value = row["LASTNAME"]; cmd.Parameters.Add("@EMAIL", SqlDbType.VarChar).Value = row["EMAIL"]; cmd.Parameters.Add("@ADDRESS", SqlDbType.NVarChar).Value = row["ADDRESS"]; cmd.Parameters.Add("@PHONE", SqlDbType.VarChar).Value = row["PHONE"]; cmd.Parameters.Add("@START_DATE", SqlDbType.DateTime).Value = row["START_DATE"]; cmd.Parameters.Add("@END_DATE", SqlDbType.DateTime).Value = row["END_DATE"]; cmd.Parameters.Add("@STATUS", SqlDbType.Char).Value = row["STATUS"]; cmd.Parameters.Add("@UROLE_ID", SqlDbType.VarChar).Value = row["UROLE_ID"]; cmd.Parameters.Add("@DESCRIPTION", SqlDbType.NVarChar).Value = row["DESCRIPTION"]; count = cmd.ExecuteNonQuery(); trans.Commit(); } catch (SqlException ex) { log.Error(ex.Message, ex); if (trans != null) { trans.Rollback(); } throw ex; } catch (Exception ex) { log.Error(ex.Message, ex); if (trans != null) { trans.Rollback(); } throw ex; } finally { if (con != null && con.State == ConnectionState.Open) { con.Close(); } } return(count); }
/// <summary> /// Changed password by username, oldPassword, newPassword /// </summary> /// <param name="username"></param> /// <param name="oldPassword"></param> /// <param name="newPassword"></param> /// <returns></returns> /// <remarks> /// Author: PhatLT. FPTSS. /// Created date: 14/02/2011 /// </remarks> public int ChangePassword(string username, string oldPassword, string newPassword) { SqlConnection con = Connection; SqlTransaction trans = null; SqlCommand cmd = null; clsCryptography crypto = new clsCryptography(); int count = 0; try { if (con.State != ConnectionState.Open) { con.Open(); } trans = con.BeginTransaction(); cmd = new SqlCommand("UPDATE GENERAL_AUT_USER SET PASSWORD = @PASSWORD, PWD_CHG_DATE = getdate() WHERE USERNAME = @USERNAME AND PASSWORD = @ORIGINAL_PASSWORD", con, trans); cmd.Parameters.Add("@USERNAME", SqlDbType.VarChar, 20).Value = username; cmd.Parameters.Add("@ORIGINAL_PASSWORD", SqlDbType.VarChar, 255).Value = crypto.Encode(oldPassword); cmd.Parameters.Add("@PASSWORD", SqlDbType.VarChar, 255).Value = crypto.Encode(newPassword); count = cmd.ExecuteNonQuery(); trans.Commit(); } catch (SqlException ex) { log.Error(ex.Message, ex); if (trans != null) { trans.Rollback(); } throw ex; } catch (Exception ex) { log.Error(ex.Message, ex); if (trans != null) { trans.Rollback(); } throw ex; } finally { if (con != null && con.State == ConnectionState.Open) { con.Close(); } } return(count); }