以下是学习笔记：
一,添加新学员
1，后台部分：在DAL---StudentService中添加代码
/// <summary>
/// 判断当前身份证号是否已经存在
/// </summary>
/// <param name="studentIdNo"></param>
/// <returns></returns>
public bool IsIdNoExisted(string studentIdNo)
{
string sql = "select count(*) from Students where StudentIdNo={0}";
sql = string.Format(sql, studentIdNo);
int result = Convert.ToInt32(SQLHelper.GetSingleResult(sql));
if (result == 1) return true;
else return false;
}
/// <summary>
/// 添加学员
/// </summary>
/// <param name="objStudent"></param>
/// <returns></returns>
public int AddStudent(Student objStudent)
{
//【1】编写SQL语句
StringBuilder sqlBuilder = new StringBuilder();//如果字符串比较长，可以用StringBuilder
sqlBuilder.Append("insert into Students(StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,CardNo,ClassId)");
sqlBuilder.Append(" values('{0}','{1}','{2}',{3},{4},'{5}','{6}','{7}',{8})");
//【2】解析对象
string sql = string.Format(sqlBuilder.ToString(),
objStudent.StudentName, objStudent.Gender, objStudent.Birthday.ToString("yyyy-MM-dd"),
objStudent.StudentIdNo, objStudent.Age, objStudent.PhoneNumber,
objStudent.StudentAddress, objStudent.CardNo, objStudent.ClassId);
//【3】提交到数据库
try
{
return SQLHelper.Update(sql);
}
catch (SqlException ex)
{
throw new Exception("数据库操作出现异常！具体信息：" + ex.Message);
}
catch (Exception ex)
{
throw ex;
}
}
2，UI部分
确认添加的按钮事件
private void btnAdd_Click(object sender, EventArgs e)
{
#region 验证数据
if (this.txtStudentName.Text.Trim().Length == 0)
{
MessageBox.Show("学生姓名不能为空！", "提示信息");
this.txtStudentName.Focus();
return;
}
if (this.txtCardNo.Text.Trim().Length == 0)
{
MessageBox.Show("考勤卡号不能为空！", "提示信息");
this.txtCardNo.Focus();
return;
}
//验证性别
if (!this.rdoFemale.Checked && !this.rdoMale.Checked)
{
MessageBox.Show("请选择学生性别！", "提示信息");
return;
}
//验证班级
if (this.cboClassName.SelectedIndex == -1)
{
MessageBox.Show("请选择班级！", "提示信息");
return;
}
//验证身份证号是否符合要求
if (!Common.DataValidate.IsIdentityCard(this.txtStudentIdNo.Text.Trim()))
{
MessageBox.Show("身份证号不符合要求！", "验证提示");
this.txtStudentIdNo.Focus();
return;
}
//验证身份证号是否重复
if (objStudentService.IsIdNoExisted(this.txtStudentIdNo.Text.Trim()))
{
MessageBox.Show("身份证号不能和现有学员身份证号重复！", "验证提示");
this.txtStudentIdNo.Focus();
this.txtStudentIdNo.SelectAll();
return;
}
//验证身份证号是否和出生日期相吻合
string month = string.Empty;
string day = string.Empty;
if (Convert.ToDateTime(this.dtpBirthday.Text).Month < 10)
month = "0" + Convert.ToDateTime(this.dtpBirthday.Text).Month;
else
month = Convert.ToDateTime(this.dtpBirthday.Text).Month.ToString();
if (Convert.ToDateTime(this.dtpBirthday.Text).Day < 10)
day = "0" + Convert.ToDateTime(this.dtpBirthday.Text).Day;
else
day = Convert.ToDateTime(this.dtpBirthday.Text).Day.ToString();
string birthday = Convert.ToDateTime(this.dtpBirthday.Text).Year.ToString() + month + day;
if (!this.txtStudentIdNo.Text.Trim().Contains(birthday))
{
MessageBox.Show("身份证号和出生日期不匹配！", "验证提示");
this.txtStudentIdNo.Focus();
this.txtStudentIdNo.SelectAll();
return;
}
//验证出生日期
int age = DateTime.Now.Year - Convert.ToDateTime(this.dtpBirthday.Text).Year;
if (age < 18)
{
MessageBox.Show("学生年龄不能小于18岁！", "验证提示");
return;
}
#endregion
#region 封装学生对象
Student objStudent = new Student()
{
StudentName = this.txtStudentName.Text.Trim(),
Gender = this.rdoMale.Checked ? "男" : "女",
Birthday = Convert.ToDateTime(this.dtpBirthday.Text),
StudentIdNo = this.txtStudentIdNo.Text.Trim(),
PhoneNumber = this.txtPhoneNumber.Text.Trim(),
StudentAddress = this.txtAddress.Text.Trim(),
CardNo = this.txtCardNo.Text.Trim(),
ClassId = Convert.ToInt32(this.cboClassName.SelectedValue),//获取选择班级对应的ClassId
Age = DateTime.Now.Year - Convert.ToDateTime(this.dtpBirthday.Text).Year
};
#endregion
#region 调用后台数据访问方法添加对象
try
{
if (objStudentService.AddStudent(objStudent) == 1)
{
DialogResult result = MessageBox.Show("新学员添加成功！是否继续添加？", "提示信息", MessageBoxButtons.YesNo,
MessageBoxIcon.Question);
if (result == DialogResult.Yes)//清空用户的输入
{
this.cboClassName.SelectedIndex = -1;
this.rdoFemale.Checked = false;
this.rdoMale.Checked = false;
//清除文本框
foreach (Control item in this.Controls)
{
if (item is TextBox)
item.Text = "";
}
this.txtStudentName.Focus();
}
}
else
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
#endregion
}
二，学员信息查询
1，Models中添加2个实体类 ：Models--Students 和扩展实体类Models--Ext-StudentExt
Students:
namespace Models
{
/// <summary>
/// 学员实体类
/// </summary>
public class Student
{
public int StudentId { get; set; }
public string StudentName { get; set; }
public string Gender { get; set; }
public DateTime Birthday{ get; set; }
//将数据库中的18位整数转换成字符串
public string StudentIdNo { get; set; }
public int Age { get; set; }
public string PhoneNumber { get; set; }
public string StudentAddress { get; set; }
public string CardNo { get; set; }
public int ClassId { get; set; }
}
}
StudentExt:
namespace Models
{
/// <summary>
/// 学员信息扩展实体
/// </summary>
public class StudentExt : Student
{
public string ClassName { get; set; }
public int CSharp { get; set; }
public int SQLServerDB { get; set; }
}
}
2,在DAL--StudentService学员信息数访问类添加查询方法
#region 查询学员【根据班级、学号、卡号】
/// <summary>
/// 根据班级名称查询学员信息
/// </summary>
/// <param name="className"></param>
/// <returns></returns>
public List<StudentExt> GetStudentByClass(string className)
{
string sql = "select StudentName,StudentId,Gender,Birthday,ClassName from Students";
sql += " inner join StudentClass on Students.ClassId=StudentClass.ClassId";
sql += " where ClassName='{0}'";
sql = string.Format(sql, className);
SqlDataReader objReader = SQLHelper.GetReader(sql);
List<StudentExt> list = new List<StudentExt>();
while (objReader.Read())
{
list.Add(new StudentExt()
{
StudentId = Convert.ToInt32(objReader["StudentId"]),
StudentName = objReader["StudentName"].ToString(),
Gender = objReader["Gender"].ToString(),
Birthday = Convert.ToDateTime(objReader["Birthday"]),
ClassName = objReader["ClassName"].ToString()
});
}
objReader.Close();
return list;
}
/// <summary>
///根据学号查询学员对象
/// </summary>
/// <param name="studentId"></param>
/// <returns></returns>
public StudentExt GetStudentById(string studentId)
{
string sql = "select StudentId,StudentName,Gender,Birthday,ClassName,StudentIdNo,PhoneNumber,StudentAddress,CardNo from Students";
sql += " inner join StudentClass on Students.ClassId=StudentClass.ClassId";
sql += " where StudentId=" + studentId;
SqlDataReader objReader = SQLHelper.GetReader(sql);
StudentExt objStudent = null;
if (objReader.Read())
{
objStudent = new StudentExt()
{
StudentId = Convert.ToInt32(objReader["StudentId"]),
StudentName = objReader["StudentName"].ToString(),
Gender = objReader["Gender"].ToString(),
Birthday = Convert.ToDateTime(objReader["Birthday"]),
ClassName = objReader["ClassName"].ToString(),
CardNo = objReader["CardNo"].ToString(),
StudentIdNo = objReader["StudentIdNo"].ToString(),
PhoneNumber = objReader["PhoneNumber"].ToString(),
StudentAddress = objReader["StudentAddress"].ToString()
};
}
objReader.Close();
return objStudent;
}
/// <summary>
/// 根据卡号查询学生信息（请思考如何将上面的方法合并，像下面的方法这么简单）
/// </summary>
/// <param name="CardNo"></param>
/// <returns></returns>
public StudentExt GetStudentByCardNo(string CardNo)
{
string whereSql = string.Format(" where CardNo='{0}'", CardNo);
return this.GetStudent(whereSql);
}
private StudentExt GetStudent(string whereSql)
{
string sql = "select StudentId,StudentName,Gender,Birthday,ClassName,";
sql += "StudentIdNo,PhoneNumber,StudentAddress,CardNo from Students";
sql += " inner join StudentClass on Students.ClassId=StudentClass.ClassId ";
sql += whereSql;
SqlDataReader objReader = SQLHelper.GetReader(sql);
StudentExt objStudent = null;
if (objReader.Read())
{
objStudent = new StudentExt()
{
StudentId = Convert.ToInt32(objReader["StudentId"]),
StudentName = objReader["StudentName"].ToString(),
Gender = objReader["Gender"].ToString(),
Birthday = Convert.ToDateTime(objReader["Birthday"]),
ClassName = objReader["ClassName"].ToString(),
CardNo = objReader["CardNo"].ToString(),
StudentIdNo = objReader["StudentIdNo"].ToString(),
PhoneNumber = objReader["PhoneNumber"].ToString(),
StudentAddress = objReader["StudentAddress"].ToString()
};
}
objReader.Close();
return objStudent;
}
#endregion
3，UI部分
查询按钮事件代码：
//按照班级查询
private void btnQuery_Click(object sender, EventArgs e)
{
if (this.cboClass.SelectedIndex == -1)
{
MessageBox.Show("请选择班级！", "提示信息");
return;
}
this.dgvStudentList.AutoGenerateColumns = false;//DataGridView中设置不显示未封装的属性
//执行查询
this.dgvStudentList.DataSource = objStuService.GetStudentByClass(this.cboClass.Text);
}
//根据学号查询
private void btnQueryById_Click(object sender, EventArgs e)
{
if (this.txtStudentId.Text.Trim().Length == 0)
{
MessageBox.Show("请输入学号！", "提示信息");
this.txtStudentId.Focus();
return;
}
//进一步验证学号必须是数字（请使用正则表达式...）
//执行查询
StudentExt objStudent = objStuService.GetStudentById(this.txtStudentId.Text.Trim());
if (objStudent == null)
{
MessageBox.Show("学员信息不存在！", "提示信息");
this.txtStudentId.Focus();
}
else
{
//在学员详细信息窗体显示
FrmStudentInfo objFrmStuInfo = new FrmStudentInfo(objStudent);
objFrmStuInfo.Show();
}
}
学员信息显示：
学员信息显示代码：
public partial class FrmStudentInfo : Form
{
public FrmStudentInfo()
{
InitializeComponent();
}
public FrmStudentInfo(StudentExt objStudent)
: this()//调用默认的构造方法
{
//显示学员信息
this.lblStudentName.Text = objStudent.StudentName;
this.lblStudentIdNo.Text = objStudent.StudentIdNo;
this.lblPhoneNumber .Text = objStudent.PhoneNumber;
this.lblBirthday.Text = objStudent.Birthday.ToShortDateString();
this.lblAddress.Text = objStudent.StudentAddress;
this.lblGender.Text = objStudent.Gender;
this.lblClass.Text = objStudent.ClassName;
this.lblCardNo.Text = objStudent.CardNo;
}
//关闭
private void btnClose_Click(object sender, EventArgs e)
{
this.Close();
}
}
三，学员信息修改
1，后台代码：
在DAL--StudentService中添加修改学员对象的方法
#region 修改学员对象
/// <summary>
/// 修改学员时判断身份证号是否和其他学员重复
/// </summary>
/// <param name="studentIdNo"></param>
/// <param name="studentId"></param>
/// <returns></returns>
public bool IsIdNoExisted(string studentIdNo, string studentId)
{
string sql = "select count(*) from Students where StudentIdNo={0} and StudentId<>{1}";
sql = string.Format(sql, studentIdNo, studentId);
int result = Convert.ToInt32(SQLHelper.GetSingleResult(sql));
if (result == 1) return true;
else return false;
}
/// <summary>
/// 修改学员对象
/// </summary>
/// <param name="objStudent"></param>
/// <returns></returns>
public int ModifyStudent(Student objStudent)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("Update Students set StudentName='{0}',Gender='{1}',Birthday='{2}',");
sqlBuilder.Append("StudentIdNo={3},Age={4},PhoneNumber='{5}',StudentAddress='{6}',CardNo='{7}',ClassId={8}");
sqlBuilder.Append(" where StudentId={9}");//注意where前面有空格
//解析对象
string sql = string.Format(sqlBuilder.ToString(),
objStudent.StudentName, objStudent.Gender, objStudent.Birthday,
objStudent.StudentIdNo, objStudent.Age, objStudent.PhoneNumber,
objStudent.StudentAddress, objStudent.CardNo, objStudent.ClassId, objStudent.StudentId);
try
{
return SQLHelper.Update(sql);
}
catch (SqlException ex)
{
throw new Exception("数据库操作出现异常！具体信息：" + ex.Message);
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
2，前端UI点击修改代码的事件代码
修改的代码：
//修改学员对象
private void btnEidt_Click(object sender, EventArgs e)
{
if (this.dgvStudentList.RowCount == 0)
{
MessageBox.Show("没有任何要修改的学信息！", "提示信息");
return;
}
if (this.dgvStudentList.CurrentRow == null)//判断当前行
{
MessageBox.Show("请选中要修改的学员信息！", "提示信息");
return;
}
//获取学号
string studentId = this.dgvStudentList.CurrentRow.Cells["StudentId"].Value.ToString();
//获取要修改的学员详细信息
StudentExt objStudent = objStuService.GetStudentById(studentId);
//显示要修改的学员信息窗口
FrmEditStudent objEditStudent = new FrmEditStudent(objStudent);
DialogResult result = objEditStudent.ShowDialog();
//判断修改是否成
if (result == DialogResult.OK)
{
btnQuery_Click(null, null);//同步刷新修改的信息（适合查询数据量小的情况）
}
}
3，修改学员信息的代码
通过构造函数把学员信息对象传过来
提交修改的代码
//提交修改
private void btnModify_Click(object sender, EventArgs e)
{
#region 验证信息
if (this.txtStudentName.Text.Trim().Length == 0)
{
MessageBox.Show("学生姓名不能为空！", "提示信息");
this.txtStudentName.Focus();
return;
}
//验证性别
if (!this.rdoFemale.Checked && !this.rdoMale.Checked)
{
MessageBox.Show("请选择学生性别！", "提示信息");
return;
}
//验证班级
if (this.cboClassName.SelectedIndex == -1)
{
MessageBox.Show("请选择班级！", "提示信息");
return;
}
//验证身份证号是否符合要求
if (!Common.DataValidate.IsIdentityCard(this.txtStudentIdNo.Text.Trim()))
{
MessageBox.Show("身份证号不符合要求！", "验证提示");
this.txtStudentIdNo.Focus();
return;
}
//验证身份证号是否重复
if (objStudentService.IsIdNoExisted(this.txtStudentIdNo.Text.Trim(), this.txtStudentId.Text.Trim()))
{
MessageBox.Show("身份证号不能和现有学员身份证号重复！", "验证提示");
this.txtStudentIdNo.Focus();
this.txtStudentIdNo.SelectAll();
return;
}
//验证身份证号是否和出生日期相吻合
string month = string.Empty;
string day = string.Empty;
if (Convert.ToDateTime(this.dtpBirthday.Text).Month < 10)
month = "0" + Convert.ToDateTime(this.dtpBirthday.Text).Month;
if (Convert.ToDateTime(this.dtpBirthday.Text).Day < 10)
day = "0" + Convert.ToDateTime(this.dtpBirthday.Text).Day;
string birthday = Convert.ToDateTime(this.dtpBirthday.Text).Year.ToString() + month + day;
if (!this.txtStudentIdNo.Text.Trim().Contains(birthday))
{
MessageBox.Show("身份证号和出生日期不匹配！", "验证提示");
this.txtStudentIdNo.Focus();
this.txtStudentIdNo.SelectAll();
return;
}
//验证出生日期
int age = DateTime.Now.Year - Convert.ToDateTime(this.dtpBirthday.Text).Year;
if (age < 18)
{
MessageBox.Show("学生年龄不能小于18岁！", "验证提示");
return;
}
#endregion
#region 封装学生对象
Student objStudent = new Student()
{
StudentId = Convert.ToInt32(this.txtStudentId.Text.Trim()),
StudentName = this.txtStudentName.Text.Trim(),
Gender = this.rdoMale.Checked ? "男" : "女",
Birthday = Convert.ToDateTime(this.dtpBirthday.Text),
StudentIdNo = this.txtStudentIdNo.Text.Trim(),
PhoneNumber = this.txtPhoneNumber.Text.Trim(),
StudentAddress = this.txtAddress.Text.Trim(),
CardNo = this.txtCardNo.Text.Trim(),
ClassId = Convert.ToInt32(this.cboClassName.SelectedValue),//获取选择班级对应的ClassId
Age = DateTime.Now.Year - Convert.ToDateTime(this.dtpBirthday.Text).Year
};
#endregion
#region 提交修改
try
{
if (objStudentService.ModifyStudent(objStudent) == 1)
{
MessageBox.Show("学员信息修改成功！", "提示信息");
this.DialogResult = DialogResult.OK;//返回修改成功的信息
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
#endregion
}
4，优化用户体验，双击显示学员信息
//双击选中的学员对象并显示详细信息
private void dgvStudentList_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
if (this.dgvStudentList.CurrentRow != null)
{
string studentId = this.dgvStudentList.CurrentRow.Cells["StudentId"].Value.ToString();
this.txtStudentId.Text = studentId;
btnQueryById_Click(null, null);
}
}
5，优化用户体验，右键就可以修改（查看）学员信息，效果如下：
1，添加contextMenuStrip控件
,2，设置DataGridView的ContextMenuStrip
3，设置ContextMenuStrip的事件代码：
//右键菜单的事件
private void tsmiModifyStu_Click(object sender, EventArgs e)
{
btnEidt_Click(null, null);//修改学员对象按钮的事件
}
四，删除学员信息
1，后台代码
在DAL--StudentService中添加删除学员对象的方法
#region 删除学员对象
public int DeleteStudentById(string studentId)
{
string sql = "delete from Students where StudentId=" + studentId;
try
{
return SQLHelper.Update(sql);
}
catch (SqlException ex)
{
if (ex.Number == 547)
throw new Exception("该学号被其他数据表引用，不能直接删除该学员对象！");//判断外键引用
else
throw new Exception("数据库操作出现异常！具体信息：" + ex.Message);
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
2，前端UI
删除按钮的事件代码：
//删除学员对象
private void btnDel_Click(object sender, EventArgs e)
{
if (this.dgvStudentList.RowCount == 0)
{
MessageBox.Show("没有任何要删除的学信息！", "提示信息");
return;
}
if (this.dgvStudentList.CurrentRow == null)
{
MessageBox.Show("请选中要删除的学员信息！", "提示信息");
return;
}
//删除确认
string studentName = this.dgvStudentList.CurrentRow.Cells["StudentName"].Value.ToString();
DialogResult result = MessageBox.Show("确认要删除学员 [" + studentName + "] 吗？", "删除询问", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (result == DialogResult.Cancel) return;
//获取学号并删除
string studentId = this.dgvStudentList.CurrentRow.Cells["StudentId"].Value.ToString();
try
{
if (objStuService.DeleteStudentById(studentId) == 1)
{
btnQuery_Click(null, null);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "提示信息");
}
}