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