public List <tbl_EmployeeDTO> GetAllEmployee() { try { using (con = DBConnection.MakeConnection(con)) { tbl_RoleDAO roleDao = new tbl_RoleDAO(); con.Open(); SqlCommand command = new SqlCommand("Select id, name, address, birthday, phone, email, roleId, salary from tbl_Employee where deleteStatus = 'False' order by name ASC", con); SqlDataReader reader = command.ExecuteReader(); List <tbl_EmployeeDTO> list = new List <tbl_EmployeeDTO>(); while (reader.Read()) { int id = reader.GetInt32(0); string name = reader.GetString(1); string address = reader.GetString(2); DateTime birthday = reader.GetDateTime(3); string phone = reader.GetString(4); string email = reader.GetString(5); string[] role = roleDao.GetByID(reader.GetInt32(6)).Split('-'); float salary = float.Parse(reader["salary"].ToString()); tbl_EmployeeDTO dto = new tbl_EmployeeDTO(id, name, address, birthday, phone, email, role[1].Trim(), salary); list.Add(dto); } return(list); } } catch (Exception) { throw; } }
public bool Update(tbl_EmployeeDTO dto) { try { using (con = DBConnection.MakeConnection(con)) { tbl_RoleDAO roleDAO = new tbl_RoleDAO(); con.Open(); SqlCommand command = new SqlCommand("Update tbl_Employee set name = @name, address = @address, birthday = @birthday, phone = @phone, email = @email, roleId = @role, salary = @salary where id = @id", con); command.Parameters.AddWithValue("@name", dto.Name); command.Parameters.AddWithValue("@address", dto.Address); command.Parameters.AddWithValue("@birthday", dto.Birthday); command.Parameters.AddWithValue("@phone", dto.Phone); command.Parameters.AddWithValue("@email", dto.Email); command.Parameters.AddWithValue("@role", roleDAO.GetIdByName(dto.Role)); command.Parameters.AddWithValue("@salary", dto.Salary); command.Parameters.AddWithValue("@id", dto.Id); return(command.ExecuteNonQuery() > 0); } } catch (Exception) { throw; } }
public tbl_EmployeeDTO GetById(int id) { try { using (con = DBConnection.MakeConnection(con)) { con.Open(); SqlCommand command = new SqlCommand("Select name, address, birthday, phone, email, roleId, salary from tbl_Employee where id=@id and deleteStatus = 'False'", con); command.Parameters.AddWithValue("@id", id); SqlDataReader reader = command.ExecuteReader(); if (reader.Read()) { string name = reader.GetString(0); string address = reader.GetString(1); DateTime birthday = reader.GetDateTime(2); string phone = reader.GetString(3); string email = reader.GetString(4); int roleId = reader.GetInt32(5); double salary = reader.GetDouble(6); tbl_RoleDAO roleDAO = new tbl_RoleDAO(); tbl_EmployeeDTO dto = new tbl_EmployeeDTO(id, name, address, birthday, phone, email, roleDAO.GetByID(roleId), float.Parse(salary + "")); return(dto); } } } catch (Exception) { throw; } return(null); }