public SalarySheetItem[] GetSalarySheetItems(int year, int month, Guid deptId) { DataTable tableMain = SqlHelper.ExecuteDataTable(@"select * from T_SalarySheet where Year=@Year and Month=@Month and DepartmentId=@DepartmentId", new SqlParameter("@Year", year), new SqlParameter("@Month", month), new SqlParameter("@DepartmentId", deptId)); //先查询指定年月、部门的工资数据主表Id。再查询子表信息 //todo:可以使用“子查询”技术来简化 if (tableMain.Rows.Count == 1) { Guid sheetId = (Guid)tableMain.Rows[0]["Id"]; DataTable table = SqlHelper.ExecuteDataTable(@"select * from T_SalarySheetItem where SheetId=@SheetId", new SqlParameter("@SheetId",sheetId)); SalarySheetItem[] items = new SalarySheetItem[table.Rows.Count]; for (int i = 0; i < table.Rows.Count; i++) { DataRow row = table.Rows[i]; SalarySheetItem item = new SalarySheetItem(); item.Id = (Guid)row["Id"]; item.BaseSalary = (decimal)row["BaseSalary"]; item.Bonus = (decimal)row["Bonus"]; item.Fine = (decimal)row["Fine"]; item.Other = (decimal)row["Other"]; item.EmployeeId = (Guid)row["EmployeeId"]; item.SheetId = (Guid)row["SheetId"]; items[i] = item; } return items; } else if (tableMain.Rows.Count <= 0) { return new SalarySheetItem[0]; } else { throw new Exception(); } }
public void Update(SalarySheetItem item) { SqlHelper.ExecuteNonQuery(@"Update T_SalarySheetItem Set BaseSalary=@BaseSalary,Bonus=@Bonus, Fine=@Fine,Other=@Other where Id=@Id", new SqlParameter("@BaseSalary",item.BaseSalary), new SqlParameter("@Bonus", item.Bonus), new SqlParameter("@Fine", item.Fine), new SqlParameter("@Other", item.Other), new SqlParameter("@Id", item.Id)); }