/// <summary>
/// 分页绑定数据
/// </summary>
/// <param name="currentPage">要显示的当前页码数</param>
private void BindPagedEmployees(int pageIndex)
{
string employeeName = txtEmployeeName.Text;
string userName = txtUserName.Text;
EmployeeStatusType status = (EmployeeStatusType)Convert.ToInt32(rblStatus.SelectedValue);
int totalResults; // 返回的员工总行数
List <Employee> results = BLLStaff.SearchPagedEmployees(employeeName, userName, status,
pageSize, pageIndex, out totalResults);
int totalPages = totalResults / pageSize + (totalResults % pageSize == 0 ? 0 : 1); // 计算总分页数
if (totalPages == 0) // 如果没有返回记录
{
divNoResults.Visible = true;
divResults.Visible = false;
}
else
{
divNoResults.Visible = false;
divResults.Visible = true;
lblTotalResults.Text = totalResults.ToString(); // 显示总行数
lblTotalPages.Text = totalPages.ToString(); // 显示总分页数
lblCurrentPage.Text = pageIndex.ToString(); // 显示当前页码数
}
repEmployees.DataSource = results;
repEmployees.DataBind();
}
/// <summary>
/// 获得所有指定状态的员工信息列表
/// </summary>
/// <param name="employeeStatusType">员工状态</param>
/// <returns>满足条件的员工集合；如果没有满足条件的结果，则集合中元素个数为0</returns>
public static List <Employee> SelectEmployeesByStatus(EmployeeStatusType employeeStatusType)
{
List <Employee> list = new List <Employee>();
using (SqlConnection conn = new SqlConnection(DBUtil.ConnectionString))
{
SqlCommand cmd = new SqlCommand(SELECT_EMPLOYEES_BY_STATUS, conn);
cmd.Parameters.AddWithValue("@Status", (int)employeeStatusType);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Employee employee = new Employee();
employee.EmployeeID = Convert.ToInt32(dr["EmployeeID"]);
employee.EmployeeName = dr["EmployeeName"].ToString();
employee.UserName = dr["UserName"].ToString();
employee.Phone = dr["Phone"].ToString();
employee.Email = dr["Email"].ToString();
employee.Status = (EmployeeStatusType)Convert.ToInt32(dr["Status"]);
Department department = new Department();
department.DepartmentID = Convert.ToInt32(dr["DepartmentID"]);
department.DepartmentName = dr["DepartmentName"].ToString();
list.Add(employee);
}
}
return(list);
}
public static string EnumToString(this EmployeeStatusType type)
{
switch (type)
{
case EmployeeStatusType.Active: return("ACTIVE");
case EmployeeStatusType.Inactive: return("INACTIVE");
default: throw new ArgumentOutOfRangeException();
}
}
public bool ChangeStatus(string employeeID, EmployeeStatusType status)
{
if (!_emplDict.ContainsKey(employeeID))
{
return(false);
}
if (!_emplDict.TryGetValue(employeeID, out var changeEmployeeModel))
{
return(false);
}
if (changeEmployeeModel.Status == status)
{
return(true);
}
EmployeeModel newEmployeeModel = new EmployeeModel
{
ID = changeEmployeeModel.ID,
Status = status
};
if (!_emplDict.TryUpdate(employeeID, newEmployeeModel, changeEmployeeModel))
{
return(false);
}
if (changeEmployeeModel.Status == EmployeeStatusType.Work && status == EmployeeStatusType.Free)
{
Interlocked.Increment(ref _countFreeEmployees);
}
if (changeEmployeeModel.Status == EmployeeStatusType.Free && status == EmployeeStatusType.Work)
{
Interlocked.Decrement(ref _countFreeEmployees);
}
using (EmployeeContext employeeContext = new EmployeeContext())
{
var emplDTO = Mapper.Map <EmployeeDTO>(newEmployeeModel);
employeeContext.Employees.AddOrUpdate(emplDTO);
employeeContext.SaveChangesAsync();
}
return(true);
}
/// <summary>
/// 根据条件综合搜索，并分页返回员工信息列表
/// </summary>
/// <param name="employeeName">员工姓名查询条件。如果为null，则不针对此项筛选</param>
/// <param name="userName">员工(账号)登录名查询条件，如果为null，则不针对此项筛选</param>
/// <param name="status">状态查询条件</param>
/// <param name="pageSize">分页查询中，每页包含的最大行数</param>
/// <param name="pageIndex">分页查询中，带查询的页数编号。编号从1开始。</param>
/// <param name="totalResults">输出参数，返回满足条件的总记录数</param>
/// <returns>满足查询条件，并且位于当前页中的员工集合；如果没有满足条件的结果，则集合中元素个数为0</returns>
public static List <Employee> SearchEmployeePaged(string employeeName, string userName, EmployeeStatusType status, int pageSize, int pageIndex, out int totalResults)
{
string sql = SEARCH_EMPLOYEE_PAGED;
List <SqlParameter> parameters = new List <SqlParameter>();
if (employeeName != null)
{
sql += " AND EmployeeName LIKE @EmployeeName";
parameters.Add(new SqlParameter("@EmployeeName", employeeName));
}
if (userName != null)
{
sql += " AND UserName LIKE @UserName";
parameters.Add(new SqlParameter("@UserName", userName));
}
sql += " AND Status=@Status";
parameters.Add(new SqlParameter("@Status", (int)status));
sql += " SELECT @TotalResults=@@ROWCOUNT"; // 获得总行数
SqlParameter paramTotal = new SqlParameter("@TotalResults", SqlDbType.Int);
paramTotal.Direction = ParameterDirection.Output;
parameters.Add(paramTotal);
// 获取本页内的数据
sql += " SELECT * FROM @tmp";
sql += " WHERE RowID>" + pageSize * (pageIndex - 1);
sql += " AND RowID<=" + pageSize * pageIndex;
List <Employee> list = new List <Employee>();
using (SqlConnection conn = new SqlConnection(DBUtil.ConnectionString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddRange(parameters.ToArray());
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Employee employee = new Employee();
employee.EmployeeID = Convert.ToInt32(dr["EmployeeID"]);
employee.EmployeeName = dr["EmployeeName"].ToString();
employee.UserName = dr["UserName"].ToString();
employee.Phone = dr["Phone"].ToString();
employee.Email = dr["Email"].ToString();
employee.Status = (EmployeeStatusType)Convert.ToInt32(dr["Status"]);
Department department = new Department();
department.DepartmentID = Convert.ToInt32(dr["DepartmentID"]);
department.DepartmentName = dr["DepartmentName"].ToString();
employee.RelatedDepartment = department;
list.Add(employee);
}
dr.Close();
totalResults = Convert.ToInt32(paramTotal.Value);
}
return(list);
}
/// <summary>
/// 根据条件综合搜索，并分页返回员工信息列表
/// </summary>
/// <param name="employeeName">员工姓名查询条件。如果为Empty，则不针对此项筛选</param>
/// <param name="userName">员工(账号)登录名查询条件，如果为Empty，则不针对此项筛选</param>
/// <param name="status">状态查询条件</param>
/// <param name="pageSize">分页查询中，每页包含的最大行数</param>
/// <param name="pageIndex">分页查询中，带查询的页数编号。编号从1开始。</param>
/// <param name="totalResults">输出参数，返回满足条件的总记录数</param>
/// <returns>满足查询条件，并且位于当前页中的员工集合；如果没有满足条件的结果，则集合中元素个数为0</returns>
public static List <Employee> SearchPagedEmployees(string employeeName, string userName, EmployeeStatusType status,
int pageSize, int pageIndex, out int totalResults)
{
employeeName = string.IsNullOrWhiteSpace(employeeName) ? null : "%" + employeeName + "%";
userName = string.IsNullOrWhiteSpace(userName) ? null : "%" + userName + "%";
return(DALEmployee.SearchEmployeePaged(employeeName, userName, status, pageSize, pageIndex, out totalResults));
}
