26.空白窗体的妙用及动态列的梳理
1.由于没有开发系数管理,所以点击系数管理菜单会报错
解决办法:
- 在FrmMain.cs中,Tag后面加了一个问号,表示当Tag为空时也赋值,但是需要注意的是,根据老师的课程,CreateForm中传的参数是null,但是我这里传的是"",不确定是VS版本问题还是什么,这就导致了第二步的代码不同。
Form form = FormFactory<int>.CreateForm(e.Node.Tag?.ToString());
- 在FormFactory.cs代码的CreateForm函数中添加三元表达式
formName = formName == "" ? "FrmNone" : formName;
结果:将系统管理显示为空窗体特定显示页面。
2.动态列的梳理
实现功能:在人员绩效页面,计算每个用户请假加班等等的次数,依次来计算用户实际应发的年终奖。
这一段代码相当于就是在datagridview中添加了需要显示的这些列,分别给每一列赋属性值。
27.数据表行列转换1
1. 确认一下数据表最终显示成果
那么前几列的内容其实和用户管理中的内容是一样的。
第一个窗体模型类是UserAppraisalBases.cs,用的是两个数据表的左连接。
2. 动态拓展只能用DataTable,List集合很难动态加属性,那么将拆分两个函数为
// 获取数据表
public static DataTable GetDtJoinAppraisal()
{
DataTable dt = SqlHelper.ExecuteTable("SELECT u.Id,u.UserName,u.Sex,u.BaseTypeId,u.IsDel,a.BaseType,a.AppraisalBase FROM Users u LEFT JOIN AppraisalBases a ON u.BaseTypeId= a.Id");
return dt;
}
public static List<UserAppraisalBases> GetListJoinAppraisal()
{
List<UserAppraisalBases> userAppraisalBases = new List<UserAppraisalBases>();
DataTable dt = GetDtJoinAppraisal();
foreach (DataRow dr in dt.Rows)
{
userAppraisalBases.Add(ToModel(dr));
}
return userAppraisalBases;
}
3.在FrmUserAppraisal.cs中创建一个与datagridview对应的DataTable,为其动态绑定列
//获取需要扩展的表
DataTable dtUser = UserAppraisalBases.GetDtJoinAppraisal();
//获取系数表集合
List<AppraisalCoefficients> appraisalCoefficients = AppraisalCoefficients.ListAll();
//通过系数表来填充dtUser
foreach (var item in appraisalCoefficients)
{
//添加系数名
dtUser.Columns.Add(new DataColumn
{
ColumnName = "AppraisalType" + item.Id
});
//添加系数值
dtUser.Columns.Add(new DataColumn
{
ColumnName = "AppraisalCoefficient" + item.Id
});
//添加计算方式
dtUser.Columns.Add(new DataColumn
{
ColumnName = "CalculationMethod" + item.Id
});
}
//添加考核年度
dtUser.Columns.Add(new DataColumn
{
ColumnName = "AssessmentYear"
});
//添加实发年终奖
dtUser.Columns.Add(new DataColumn
{
ColumnName = "YearBonus"
});
28.数据表行列转换2
1.通过在数据库中编写sql语句获取需要给datatable动态绑定的值
SELECT ua.*,ac.AppraisalType,ac.AppraisalCoefficient,ac.CalculationMethod FROM UserAppraisals ua LEFT JOIN AppraisalCoefficients ac ON ua.CoefficientId = ac.Id
2.在Models类中添加类UserAppraisalCoefficients.cs
public class UserAppraisalCoefficients
{
public int Id { get; set; }
public int UserId { get; set; }
public int CoefficientId { get; set; }
public double Count { get; set; }
public int AssessmentYear { get; set; }
public string AppraisalType { get; set; }
public double AppraisalCoefficient { get; set; }
public int CalculationMethod { get; set; }
public bool IsDel { get; set; }
public static List<UserAppraisalCoefficients> ListAll()
{
List<UserAppraisalCoefficients> userAppraisalCoefficients = new List<UserAppraisalCoefficients>();
DataTable dt = SqlHelper.ExecuteTable("SELECT ua.*,ac.AppraisalType,ac.AppraisalCoefficient,ac.CalculationMethod FROM UserAppraisals ua LEFT JOIN AppraisalCoefficients ac ON ua.CoefficientId = ac.Id");
foreach (DataRow dr in dt.Rows)
{
userAppraisalCoefficients.Add(dr.DataRowToModel<UserAppraisalCoefficients>());
}
return userAppraisalCoefficients;
}
29.表单行列转换后的值绑定
在FrmUserAppraisal.cs中给27中创建的dtUser填充数据
//给dtUser填充数据
//在实体中,既有属性又有方法的叫充血模型,只有属性没有方法的叫贫血模型
List<UserAppraisalCoefficients> userAppraisalCoefficients = UserAppraisalCoefficients.ListAll();
for (int i = 0; i < dtUser.Rows.Count; i++)
{
var uacFilter = userAppraisalCoefficients.FindAll(m => m.UserId == (int)dtUser.Rows[i]["Id"] && m.AssessmentYear == Convert.ToInt32(cbxYear。Text));
for (int j = 0; j < uacFilter.Count; i++)
{
//获取AppraisalType对应的dtUser的ColumnName的值
//获取考核次数
string appraisalTypeKey = "AppraisalType" + uacFilter[j].CoefficientId;
double appraisalTypeCountValue = uacFilter[j].Count;
//获取考核系数
string appraisalCoefficientKey = "AppraisalCoefficient" + uacFilter[j].CoefficientId;
double appraisalCoefficientValue = uacFilter[j].AppraisalCoefficient;
//获取计算方式
string calculationMethodKey = "CalculationMethod" + uacFilter[j].CoefficientId;
int calculationMethodValue = uacFilter[j].CalculationMethod;
//给dtUser绑定值
dtUser.Rows[i][appraisalTypeKey] = appraisalTypeCountValue;
dtUser.Rows[i][appraisalCoefficientKey] = appraisalCoefficientValue;
dtUser.Rows[i][calculationMethodKey] = calculationMethodValue;
}
dtUser.Rows[i]["AssessmentYear"] = cbxYear.Text;
}
30.行列转换后的动态值计算绑定
1.计算
- 在FrmUserAppraisal.cs中声明一个系数计算的数组
//系数计算的数组,用于存放每个考核类型的总系数
double[] yearBonusArray = new double[uacFilter.Count];
- 通过dtUser的值获取考核项系数
//计算考核项系数
//"考核系数"*"考核次数"*"计算方式"
yearBonusArray[j] = appraisalTypeCountValue * appraisalCoefficientValue * calculationMethodValue;
3.计算实发年终奖
//结算实发年终奖
double yearBonusAll = 0;
for (int j = 0; j < yearBonusArray.Length; j++)
{
yearBonusAll += yearBonusArray[j];
}
//计算实发年终奖
double yearBonus = (1 + yearBonusAll) * Convert.ToDouble(dtUser.Rows[i]["AppraisalBase"]);
//由于很有可能这个人迟到太多,钱被扣成负数,如果是负数则为0
dtUser.Rows[i]["YearBonus"] = yearBonus < 0 ? 0 : yearBonus;
2.绑定
dgvUserAppraisal.AutoGenerateColumns = false;
dgvUserAppraisal.DataSource = dtUser;
31.表格右键菜单制作
实现功能:在人员绩效菜单表达中,点击右键,显示录入考核信息,点击空白处,右键菜单消失。
由于右键菜单中录入考核信息修改考核次数,每个考核类型是动态改变的,如果数据库表AppraisalCoefficients中的内容改变,页面中显示的考核类型就会变,所以不能直接在页面上添加控件,修改次数之后表格内容也对应更新。
1.左键选中时,直接选中某一行,且取消多行选中的功能
- 属性值SelectionMode=FullRowSelect
- 属性值MultiSelect=False
2.右键点击选中,点击空白处不出现右键菜单,选中行时才出现菜单
在设置事件时,要注意事件执行优先级的顺序
MouseDown和CellMouseDown的执行顺序:先执行MouseDown,再执行CellMouseDown.
- 新建一个ContextMenuStrip
- 将当前datagridview和这个ContextMenuStrip绑定,绑定方法就说修改属性值
- 新建两个事件
private void dgvUserAppraisal_MouseDown(object sender, MouseEventArgs e)
{
//隐藏右键菜单里的内容
tsmEdit.Visible = false;
}
private void dgvUserAppraisal_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)
{
tsmEdit.Visible = true;
}
- 设置右键选中当前行的时候是选中一整行
private void dgvUserAppraisal_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)
{
//如果右键点击,则点击这行选中
if(e.Button == MouseButtons.Right)
{
if(e.RowIndex > -1)
{
//清除之前的选择,增加程序健壮性
dgvUserAppraisal.ClearSelection();
dgvUserAppraisal.Rows[e.RowIndex].Selected = true;
}
}
tsmEdit.Visible = true;
}
32.类与部分类
1.新建一个窗体FrmUserAppraisalEdit,为其添加控件
在上方的groupbox1中为了可以动态的添加里面的内容,使用FlowLayoutPanel控件,目的是,在FlowLayoutPanel中添加控件,控件会自动进行排列。
2.双击右键菜单编辑考核项,打开窗体
private void tsmEdit_Click(object sender, EventArgs e)
{
FrmUserAppraisalEdit frmUserAppraisalEdit = new FrmUserAppraisalEdit();
frmUserAppraisalEdit.ShowDialog();
}
3.FrmUserAppraisalEdit是部分类
public partial class FrmUserAppraisalEdit : Form
FrmUserAppraisalEdit.cs和FrmUserAppraisalEdit.Designer.cs共同组成一个完整的类
33.动态控件的创建
private void FrmUserAppraisalEdit_Load(object sender, EventArgs e)
{
List<AppraisalCoefficients> appraisalCoefficients = AppraisalCoefficients.ListAll();
foreach (var ac in appraisalCoefficients)
{
Panel panel = new Panel();
Label label = new Label
{
Text = ac.AppraisalType,
Location = new Point(0, 4),
Width = 60
};
TextBox textBox = new TextBox
{
Location = new Point(66, 0),
Width = 120,
Name = "txtAppraisalType_" + ac.Id,
Height = 26
};
panel.Controls.Add(label);
panel.Controls.Add(textBox);
flp.Controls.Add(panel);
}
}
34.动态控件赋值
- FrmUserAppraisalEdit.cs中声明两个变量
private int _userId;
private string _year;
- 重载构造方法
//构造方法重载
//this()表示执行到该构造方法时,先执行一个没有参数的构造方法
public FrmUserAppraisalEdit(int userId, string year):this()
{
_userId = userId;
_year = year;
}
- 在上一级FrmUserAppraisal.cs的tsmEdit_Click函数中调用构造方法
private void tsmEdit_Click(object sender, EventArgs e)
{
string year = cbxYear.Text;
//所有选中行中的第一行
int userId = (int)dgvUserAppraisal.SelectedRows[0].Cells["Id"].Value;
FrmUserAppraisalEdit frmUserAppraisalEdit = new FrmUserAppraisalEdit(userId,year);
frmUserAppraisalEdit.ShowDialog();
}
35.动态控件数据绑定
1.新建一个UserAppraisals.cs类
namespace Appraisal_System.Models
{
public class UserAppraisals
{
public int Id { get; set; }
public int UserId { get; set; }
public int CoefficientId { get; set; }
public double Count { get; set; }
public int AssessmentYear { get; set; }
public bool IsDel { get; set; }
// 通过List集合存储指定的类型对象的集合
public static List<UserAppraisals> ListByUserIdAndYear(int userId, string year)
{
List<UserAppraisals> userAppraisals = new List<UserAppraisals>();
DataTable dt = SqlHelper.ExecuteTable("SELECT * FROM UserAppraisals WHERE UserId = @UserId AND AssessmentYear = @AssessmentYear",
new SqlParameter("@UserId", userId),
new SqlParameter("@AssessmentYear", year));
foreach (DataRow dr in dt.Rows)
{
userAppraisals.Add(dr.DataRowToModel<UserAppraisals>());
}
return userAppraisals;
}
// 添加
public static void Insert(UserAppraisals userAppraisal)
{
// 采用Sql注入,方法是Sql语句参数化
SqlHelper.ExecuteNonQuery("INSERT INTO UserAppraisals(UserId,CoefficientId,Count,AssessmentYear,IsDel) VALUES(@UserId,@CoefficientId,@Count,@AssessmentYear,@IsDel)",
new SqlParameter("@UserId", userAppraisal.UserId),
new SqlParameter("@CoefficientId", userAppraisal.CoefficientId),
new SqlParameter("@Count", userAppraisal.Count),
new SqlParameter("@AssessmentYear", userAppraisal.AssessmentYear),
new SqlParameter("@IsDel", userAppraisal.IsDel)
);
}
//删除
public static void Delete(int UserId, string AssessmentYear, int CoefficientId)
{
// 三点定位一条数据
SqlHelper.ExecuteNonQuery("DELETE FROM UserAppraisals WHERE UserId = @UserId AND AssessmentYear = @AssessmentYear AND CoefficientId= @CoefficientId",
new SqlParameter("@UserId", UserId),
new SqlParameter("@CoefficientId", CoefficientId),
new SqlParameter("@AssessmentYear", AssessmentYear)
);
}
}
}
2.给FrmUserAppraisalEdit.cs中的TextBox控件绑定数据
//获得这个人在这一年度所有的绩效情况
List<UserAppraisals> userAppraisals = UserAppraisals.ListByUserIdAndYear(_userId, _year);
//给控件绑定数据
foreach (var ua in userAppraisals)
{
var flCtrs = flp.Controls;
foreach (Control flCtr in flCtrs)
{
if(flCtr is Panel)
{
var plCtrs = flCtr.Controls;
//为了获取TextBox的值,所以在TextBox控件定义一个属性Name = "txtAppraisalType_" + ac.Id
foreach (var plCtr in plCtrs)
{
if (plCtr is TextBox)
{
int acId = Convert.ToInt32(((TextBox)plCtr).Name.Split('_')[1]);
((TextBox)plCtr).Text = userAppraisals.Find(m => m.CoefficientId == acId).Count.ToString();
}
}
}
}
}
36.动态控件数据添加和更新
1.实现功能:点击保存按钮之后,在页面中修改了次数,数据库中的次数随之修改
private void btnSave_Click(object sender, EventArgs e)
{
var flCtrs = flp.Controls;
foreach (Control flCtr in flCtrs)
{
if (flCtr is Panel)
{
var plCtrs = flCtr.Controls;
foreach (var plCtr in plCtrs)
{
if (plCtr is TextBox)
{
int acId = Convert.ToInt32(((TextBox)plCtr).Name.Split('_')[1]);
double count = Convert.ToDouble(((TextBox)plCtr).Text);
//先把所有的数据都删除,再在里面添加数据
UserAppraisals.Delete(_userId, _year, acId);
UserAppraisals userAppraisals = new UserAppraisals
{
UserId = _userId,
CoefficientId = acId,
AssessmentYear = Convert.ToInt32(_year),
Count = count,
IsDel = false
};
UserAppraisals.Insert(userAppraisals);
}
}
}
}
}
2.实现功能:页面中的表格内容也更新
采用委托
- 在FrmUserAppraisalEdit的上一级父类FrmUserAppraisal中声明一个委托
Action bindDgv;
bindDgv = BindDgvUserAppraisal;
- 在FrmUserAppraisalEdit中调用委托变量
public FrmUserAppraisalEdit(int userId, string year, Action bindDgv):this()
在FrmUserAppraisal中传参
FrmUserAppraisalEdit frmUserAppraisalEdit = new FrmUserAppraisalEdit(userId, year, bindDgv);
- 在FrmUserAppraisalEdit中声明一个私有变量,用私有变量接收2传过来的参数
private Action _bindDgv;
_bindDgv = bindDgv;
- 在btnSave_Click执行完保存操作后,调用委托,实现对父级页面的更新
MessageBox.Show("数据修改成功");
_bindDgv();
this.Close();