一：
二：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的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 = ; 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 - ; i <= DateTime.Today.Year + ; i++)
{
listYears.Add(i);
} //声明一个存放月份的泛型变量
List<int> months = new List<int>();
for (int i = ; i <= ; 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[].Value;
list.BaseSalary = (decimal)dgv_Salary.CurrentRow.Cells[].Value;
list.Fine = (decimal)dgv_Salary.CurrentRow.Cells[].Value;
list.Other = (decimal)dgv_Salary.CurrentRow.Cells[].Value;
list.Id = (Guid)dgv_Salary.CurrentRow.Cells[].Value;
SalarySheetDAL dal = new SalarySheetDAL();
dal.UpdateSalaryList(list);
MessageBox.Show("更新成功!"); } }
}
七：事务服务启动、添加引用和使用事项
a.
b.
c.
事物应该具有4个属性：原子性、一致性、隔离性、持续性。这四个属性通常称为ACID特性.
原子性（atomicity）: 一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
一致性（consistency）: 事物必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性（isolation）: 一个事物的执行不能被其他事务干扰。即一个事物内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（durability）: 持续性也称永久性（permanence），指一个事物一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
d.
