public bool Update(Employee data)
{
int rowsAffected = 0;
using (SqlConnection connection = new SqlConnection(this.connectionString))
{
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = @"UPDATE Employees
SET LastName = @LastName
,FirstName = @FirstName
,Title = @Title
,Roles = @Roles
,BirthDate = @BirthDate
,HireDate = @HireDate
,Email = @Email
,Address = @Address
,City = @City
,Country = @Country
,HomePhone = @HomePhone
,Notes = @Notes
,PhotoPath = @PhotoPath
,Password = @Password
WHERE EmployeeID = @EmployeeID";
cmd.CommandType = CommandType.Text;
cmd.Connection = connection;
cmd.Parameters.AddWithValue("@EmployeeID", data.EmployeeID);
cmd.Parameters.AddWithValue("@LastName", data.LastName);
cmd.Parameters.AddWithValue("@FirstName", data.FirstName);
cmd.Parameters.AddWithValue("@Title", data.Title);
cmd.Parameters.AddWithValue("@Roles", data.Roles);
cmd.Parameters.AddWithValue("@BirthDate", data.BirthDate);
cmd.Parameters.AddWithValue("@HireDate", data.HireDate);
cmd.Parameters.AddWithValue("@Email", data.Email);
cmd.Parameters.AddWithValue("@Address", data.Address);
cmd.Parameters.AddWithValue("@City", data.City);
cmd.Parameters.AddWithValue("@Country", data.Country);
cmd.Parameters.AddWithValue("@HomePhone", data.HomePhone);
cmd.Parameters.AddWithValue("@Notes", data.Notes);
cmd.Parameters.AddWithValue("@PhotoPath", data.PhotoPath);
cmd.Parameters.AddWithValue("@Password", EncodePass.EncodeMD5(data.Password));
rowsAffected = Convert.ToInt32(cmd.ExecuteNonQuery());
connection.Close();
}
return(rowsAffected > 0);
}
public UserAccount Authorize(string userName, string password)
{
//TODO: Kiểm tra thông tin đăng nhập tư bảng employee
UserAccount data = null;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = @"SELECT EmployeeID, LastName, PhotoPath, Title, Roles FROM Employees WHERE Email = @Email and Password = @Password";
cmd.CommandType = CommandType.Text;
cmd.Connection = connection;
cmd.Parameters.AddWithValue("@Email", userName);
cmd.Parameters.AddWithValue("@Password", EncodePass.EncodeMD5(password));
using (SqlDataReader dbReader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
if (dbReader.Read())
{
data = new UserAccount()
{
UserID = Convert.ToString(dbReader["EmployeeID"]),
FullName = Convert.ToString(dbReader["LastName"]),
Photo = Convert.ToString(dbReader["PhotoPath"]),
Title = Convert.ToString(dbReader["Title"]),
Roles = Convert.ToString(dbReader["Roles"])
};
}
}
connection.Close();
}
return(data);
//return new UserAccount()
//{
// UserID = userName,
// FullName = "Đặng Văn Hiền",
// Photo = "5a053a74-a925-4e01-b686-cfb7dac5f8c0avatar.png"
//};
}
public bool UpdatePass(ChangePass data)
{
int rowsAffected = 0;
using (SqlConnection connection = new SqlConnection(this.connectionString))
{
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = @"UPDATE Employees
SET Password = @Password
WHERE EmployeeID = @EmployeeID";
cmd.CommandType = CommandType.Text;
cmd.Connection = connection;
cmd.Parameters.AddWithValue("@EmployeeID", data.EmployeeID);
cmd.Parameters.AddWithValue("@Password", EncodePass.EncodeMD5(data.NewPassWord));
rowsAffected = Convert.ToInt32(cmd.ExecuteNonQuery());
connection.Close();
}
return(rowsAffected > 0);
}
public int Add(Employee data)
{
int employeeId = 0;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = @"INSERT INTO Employees
(
LastName,
FirstName,
Title,
Roles,
BirthDate,
HireDate,
Email,
Address,
City,
Country,
HomePhone,
Notes,
PhotoPath,
Password
)
VALUES
(
@LastName,
@FirstName,
@Title,
@Roles,
@BirthDate,
@HireDate,
@Email,
@Address,
@City,
@Country,
@HomePhone,
@Notes,
@PhotoPath,
@Password
);
SELECT @@IDENTITY;";
cmd.CommandType = CommandType.Text;
cmd.Connection = connection;
cmd.Parameters.AddWithValue("@LastName", data.LastName);
cmd.Parameters.AddWithValue("@FirstName", data.FirstName);
cmd.Parameters.AddWithValue("@Title", data.Title);
cmd.Parameters.AddWithValue("@Roles", data.Roles);
cmd.Parameters.AddWithValue("@BirthDate", data.BirthDate);
cmd.Parameters.AddWithValue("@HireDate", data.HireDate);
cmd.Parameters.AddWithValue("@Email", data.Email);
cmd.Parameters.AddWithValue("@Address", data.Address);
cmd.Parameters.AddWithValue("@City", data.City);
cmd.Parameters.AddWithValue("@Country", data.Country);
cmd.Parameters.AddWithValue("@HomePhone", data.HomePhone);
cmd.Parameters.AddWithValue("@Notes", data.Notes);
cmd.Parameters.AddWithValue("@PhotoPath", data.PhotoPath);
cmd.Parameters.AddWithValue("@Password", EncodePass.EncodeMD5(data.Password));
employeeId = Convert.ToInt32(cmd.ExecuteScalar());
connection.Close();
}
return(employeeId);
}