一:
二:SalarySheetDAL.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlClient; using HRMSys.Model; using System.Data; namespace HRMSys.DAL { public class SalarySheetDAL { /// <summary> /// 判断是否是否生成指定年月和部门的工资单 /// </summary> /// <param name="year"></param> /// <param name="month"></param> /// <param name="deptId"></param> /// <returns></returns> public bool IsExists(int year, int month, Guid deptId) { object obj= sqlhelper.ExecuteSca(@"select count(*) from T_SalarySheet where Year=@Year and Month=@Month and DepartmentId=@DepartmentId", new SqlParameter("@Year",year) ,new SqlParameter("@Month",month) ,new SqlParameter("@DepartmentId",deptId)); return Convert.ToInt32(obj) > 0;//将大于0的bool结果返回 } /// <summary> /// 清理生成的账套和该账套下的员工工资 /// </summary> /// <param name="year"></param> /// <param name="month"></param> /// <param name="deptId"></param> public void Clear(int year, int month, Guid deptId) { object obj = sqlhelper.ExecuteSca(@"select Id from T_SalarySheet where Year=@Year and Month=@Month and DepartmentId=@DepartmentId", new SqlParameter("@Year",year) ,new SqlParameter("@Month",month) , new SqlParameter("@DepartmentId", deptId)); Guid sheetId = (Guid)obj; sqlhelper.ExecuteNon("delete from T_SalarySheetItem where SheetId=@SheetId", new SqlParameter("@SheetId",sheetId)); sqlhelper.ExecuteNon("delete from T_SalarySheetItem where Id=@Id", new SqlParameter("@Id",sheetId)); } /// <summary> /// 建立账套,和该账套下的员工工资 /// </summary> /// <param name="year"></param> /// <param name="month"></param> /// <param name="deptId"></param> public Guid Build(int year, int month, Guid deptId) { //插入一条账套信息 Guid sheetId = Guid.NewGuid(); sqlhelper.ExecuteNon(@"insert into T_SalarySheet(Id,Year,Month,DepartmentId) values(@Id,@Year,@Month,@DepartmentId)", new SqlParameter("@Id",sheetId) ,new SqlParameter("@Year",year), new SqlParameter("@Month", month), new SqlParameter("@DepartmentId", deptId)); //查询符合该账套的所有员工 Employee[] employees = new EmployeeDAL().ListByDepment(deptId); //账套下的所有员工生成对应的员工工资 foreach (Employee employee in employees) { sqlhelper.ExecuteNon(@"insert into T_SalarySheetItem(Id,SheetId,EmployeeId,Bonus,BaseSalary,Fine,Other) values (newid(),@SheetId,@EmployeeId,500,3000,0,0)", new SqlParameter("@SheetId",sheetId), new SqlParameter("@EmployeeId", employee.Id)); } return sheetId; } /// <summary> /// 将数据库的表的行转换为SalarySheetItem的字段格式 /// </summary> /// <param name="row"></param> /// <returns></returns> public SalarySheetItem ToSalaryItemModel(DataRow row) { SalarySheetItem item = new SalarySheetItem(); item.Id =(Guid) row["Id"]; item.BaseSalary = (decimal)row["BaseSalary"]; item.Bonus=(decimal)row["Bonus"]; item.EmployeeId=(Guid)row["EmployeeId"]; item.Fine=(decimal)row["Fine"]; item.Other=(decimal)row["Other"]; return item; } /// <summary> /// 将数据库的表的行转换为SalarySheetItemList的字段格式 /// </summary> /// <param name="row"></param> /// <returns></returns> public SalarySheetItemList ToSalaryItemListModel(DataRow row) { SalarySheetItemList item = new SalarySheetItemList(); item.Id = (Guid)row["Id"]; item.BaseSalary = (decimal)row["BaseSalary"]; item.Bonus = (decimal)row["Bonus"]; item.EmployeeName = (string)sqlhelper.ExecuteSca("select Name from T_Employee where Id=@Id", new SqlParameter("@Id", row["EmployeeId"])); item.Fine = (decimal)row["Fine"]; item.Other = (decimal)row["Other"]; return item; } /// <summary> /// 将SalarySheetItem中的guid格式转换为对应的中文名字 /// </summary> /// <param name="items"></param> /// <returns></returns> public SalarySheetItemList ToModelList(SalarySheetItem items) { SalarySheetItemList list = new SalarySheetItemList(); list.Id = items.Id; list.SheetId = items.SheetId; list.EmployeeName = (string)sqlhelper.ExecuteSca("select Name from T_Employee where Id=@Id", new SqlParameter("@Id", items.EmployeeId)); list.BaseSalary = items.BaseSalary; list.Bonus = items.Bonus; list.Fine = items.Fine; list.Other = items.Other; return list; } /// <summary> /// 得到该账套下的所有员工的工资的信息 /// </summary> /// <param name="sheetid"></param> /// <returns></returns> public SalarySheetItemList[] GetSalaryItems(Guid sheetid) { DataTable table=sqlhelper.datatable("select * from T_SalarySheetItem where SheetId=@SheetId" ,new SqlParameter("@SheetId",sheetid)); SalarySheetItemList[] items = new SalarySheetItemList[table.Rows.Count]; for (int i = 0; i < table.Rows.Count; i++) { //将数据库的表的行转换为SalarySheetItemList的字段格式 items[i] = ToSalaryItemListModel(table.Rows[i]); } return items; } /// <summary> /// 更新指定员工的工资信息 /// </summary> public void UpdateSalaryList(SalarySheetItemList list) { sqlhelper.ExecuteNon(@"Update T_SalarySheetItem set BaseSalary=@BaseSalary,Bonus=@Bonus, Fine=@Fine,Other=@Other where Id=@Id" , new SqlParameter("@BaseSalary",list. BaseSalary) , new SqlParameter("@Bonus", list.Bonus) , new SqlParameter("@Fine", list.Fine) , new SqlParameter("@Other", list.Other) , new SqlParameter("@Id", list.Id)); } } }
三:SalarySheet.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace HRMSys.Model { public class SalarySheet { public Guid Id { get; set; } public int Year { get; set; } public int Month { get; set; } public Guid DepartmentId { get; set; } } }
四:SalarySheetItem.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace HRMSys.Model { public class SalarySheetItem { public Guid Id { get; set; } public Guid SheetId { get; set; } public Guid EmployeeId { get; set; } public decimal Bonus { get; set; } public decimal BaseSalary { get; set; } public decimal Fine { get; set; } public decimal Other { get; set; } } }
五:SalarySheetItemLIst.cs
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace HRMSys.Model { public class SalarySheetItemList { public Guid Id { get; set; } public Guid SheetId { get; set; } public string EmployeeName { get; set; } public decimal Bonus { get; set; } public decimal BaseSalary { get; set; } public decimal Fine { get; set; } public decimal Other { get; set; } } }
六:BuildSalarySheet.cs
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using HRMSys.DAL; using HRMSys.Model; using System.Transactions; namespace HYMSys.UI.EmployeeMgr { public partial class BuildSalarySheet : Form { public BuildSalarySheet() { InitializeComponent(); } /// <summary> /// 自动载入事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void BuildSalarySheet_Load(object sender, EventArgs e) { //声明一个存放年份的泛型变量 List<int> listYears = new List<int>(); for (int i = DateTime.Today.Year - 5; i <= DateTime.Today.Year + 5; i++) { listYears.Add(i); } //声明一个存放月份的泛型变量 List<int> months = new List<int>(); for (int i = 1; i <= 12; i++) { months.Add(i); } //绑定数据源 cb_year.DataSource = listYears; cb_month.DataSource = months; cb_department.DataSource = new DepartmentDAL().GetAllDepartName(); } /// <summary> /// 生成工资表 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btn_createSalary_Click(object sender, EventArgs e) { int year =(int ) cb_year.SelectedValue; int month = (int)cb_month.SelectedValue; Guid departId = new DepartmentDAL().getIdByName((string)cb_department.SelectedValue); SalarySheetDAL dal = new SalarySheetDAL(); using (TransactionScope ts = new TransactionScope())//使用事务 { if (dal.IsExists(year, month, departId) == true) { if (MessageBox.Show("工资单已经生成,是否重新生成?", "警告!", MessageBoxButtons.OKCancel) == DialogResult.OK) { dal.Clear(year, month, departId); MessageBox.Show("工资单已删除!"); } } Guid sheet = dal.Build(year, month, departId); dgv_Salary.DataSource = dal.GetSalaryItems(sheet); ts.Complete(); } MessageBox.Show("重新生成工资单了!"); } /// <summary> /// 单元格编辑完后事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void dgv_Salary_CellEndEdit(object sender, DataGridViewCellEventArgs e) { SalarySheetItemList list = new SalarySheetItemList(); list.Bonus =(decimal) dgv_Salary.CurrentRow.Cells[3].Value; list.BaseSalary = (decimal)dgv_Salary.CurrentRow.Cells[4].Value; list.Fine = (decimal)dgv_Salary.CurrentRow.Cells[5].Value; list.Other = (decimal)dgv_Salary.CurrentRow.Cells[6].Value; list.Id = (Guid)dgv_Salary.CurrentRow.Cells[0].Value; SalarySheetDAL dal = new SalarySheetDAL(); dal.UpdateSalaryList(list); MessageBox.Show("更新成功!"); } } }
七:事务服务启动、添加引用和使用事项
a.
b.
c.
事物应该具有4个属性:原子性、一致性、隔离性、持续性。这四个属性通常称为ACID特性.
原子性(atomicity):
一个事务是一个不可分割的工作单位,事务中包括的诸操作要么都做,要么都不做。
一致性(consistency): 事物必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性(isolation): 一个事物的执行不能被其他事务干扰。即一个事物内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰。
持久性(durability): 持续性也称永久性(permanence),指一个事物一旦提交,它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
d.