界面效果
练习重点
1. 关系表的创建
2. 增删改查的操作，及sqlhelper的封装
3. 跨页面数据传递，编辑页数据提交后数据局步刷新到列表数据
项目源码
FrmStuddentList
代码语言：javascript复制public partial class FrmStudentList : Form
{
private Action reload = null;
public FrmStudentList()
{
InitializeComponent();
}
private static FrmStudentList frmStudentList = null;
public static FrmStudentList CreateInstance()
{
if(frmStudentList is null || frmStudentList.IsDisposed)
{
frmStudentList = new FrmStudentList();
}
return frmStudentList;
}
private void FrmStudentList_Load(object sender, EventArgs e)
{
LoadClasse();//加载班级列表
LoadAllStudentList();//加载所有学生信息
}
private void LoadAllStudentList()
{
string sql = "select StuId,StuName,c.ClassName,GradeName,Sex,Phone from StudentInfo s "
"inner join ClassInfo c on c.ClassId=s.ClassId "
"inner join GradeInfo g on g.GradeId=c.GradeId";
//加载数据
DataTable dtStudents = SqlHelper.GetDataTable(sql);
//组装
if (dtStudents.Rows.Count > 0)
{
foreach (DataRow dr in dtStudents.Rows)
{
string className = dr["ClassName"].ToString();
string gradeName = dr["GradeName"].ToString();
dr["ClassName"] = className "--" gradeName;
}
}
//我只想显示固定的列
dgvStudentList .AutoGenerateColumns = false;
// dtStudents.Columns.Remove(dtStudents.Columns[3]);
//绑定数据
dgvStudentList.DataSource = dtStudents;
}
private void LoadClasse()
{
//获取数据 ---- 查询 ---写sql
string sql = "select ClassId,ClassName,GradeName from ClassInfo c,GradeInfo g where c.GradeId=g.GradeId";
DataTable dtClasse = SqlHelper.GetDataTable(sql);
//组合班级列表显示项的过程
if (dtClasse.Rows.Count > 0)
{
foreach (DataRow dr in dtClasse.Rows)
{
string className = dr["ClassName"].ToString();
string gradeName = dr["GradeName"].ToString();
dr["ClassName"] = className "--" gradeName;
}
}
//添加默认选择项
DataRow drNew = dtClasse.NewRow();
drNew["ClassId"] = 0;
drNew["ClassName"] = "请选择";
dtClasse.Rows.InsertAt(drNew, 0);
//指定数据源
cmbClassName.DataSource = dtClasse;
cmbClassName.DisplayMember = "ClassName";
cmbClassName.ValueMember = "ClassId";
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
}
private void btnSearch_Click(object sender, EventArgs e)
{
//接收条件设置信息
int classId = (int)cmbClassName .SelectedValue;
string stuName = txtStuName.Text.Trim();
//查询sql
string sql = "select StuId,StuName,c.ClassName,GradeName,Sex,Phone from StudentInfo s "
"inner join ClassInfo c on c.ClassId=s.ClassId "
"inner join GradeInfo g on g.GradeId=c.GradeId";
sql = " where 1=1 ";
if (classId > 0)
{
sql = " and s.ClassId=@ClassId";
}
if (!string.IsNullOrEmpty(stuName))
{
sql = " and StuName like @StuName";
}
sql = " order by StuId";
SqlParameter[] paras =
{
new SqlParameter("@ClassId",classId),
new SqlParameter("@StuName","%" stuName "%")
};
//加载数据
DataTable dtStudents = SqlHelper.GetDataTable(sql, paras);
//组装
if (dtStudents.Rows.Count > 0)
{
foreach (DataRow dr in dtStudents.Rows)
{
string className = dr["ClassName"].ToString();
string gradeName = dr["GradeName"].ToString();
dr["ClassName"] = className "--" gradeName;
}
}
//我只想显示固定的列
dgvStudentList.AutoGenerateColumns = false;
//绑定数据
dgvStudentList .DataSource = dtStudents;
}
private void dgvStudentList_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (e.RowIndex !=-1)
{
DataGridViewCell cell= dgvStudentList.Rows[e.RowIndex].Cells[e.ColumnIndex];
if (cell is DataGridViewLinkCell && cell.FormattedValue .ToString ()=="修改")
{
reload = LoadAllStudentList ;
DataRow dr = (dgvStudentList.Rows[e.RowIndex].DataBoundItem as DataRowView).Row;
int stuId = int.Parse(dr["StuId"].ToString());
FrmEditStudent frmEdit = new FrmEditStudent();
//传值
frmEdit.Tag = new TagObject() { EditId = stuId, Reload = reload };
frmEdit.MdiParent = this.MdiParent;
frmEdit.Show();
}
else if (cell is DataGridViewLinkCell && cell.FormattedValue.ToString() == "删除")
{
if(MessageBox .Show ("您确定要删除该学生信息吗？","删除学生提示",MessageBoxButtons.YesNo,MessageBoxIcon.Question) == DialogResult.Yes)
{
DataRow dr = (dgvStudentList.Rows[e.RowIndex].DataBoundItem as DataRowView).Row;
int stuId = int.Parse(dr["StuId"].ToString());
string sqlDel = "delete StudentInfo where StuId=@StuId";
SqlParameter para = new SqlParameter("@StuId", stuId);
int count = SqlHelper.ExecuteNonQuery(sqlDel, para);
if (count > 0)
{
MessageBox.Show("该学生信息删除成功！", "删除学习提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
DataTable dtStudent = (DataTable)dgvStudentList.DataSource;
dtStudent.Rows.Remove(dr);
dgvStudentList.DataSource = dtStudent;
}
else
{
MessageBox.Show("该学生信息删除失败！", "删除学习提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
}
}
}
private void btnDel_Click(object sender, EventArgs e)
{
List<int> listIds = new List<int>();
int count = 0;
for (int i = 0; i < dgvStudentList .Rows .Count ; i )
{
DataGridViewCheckBoxCell cell = dgvStudentList.Rows[i].Cells["colCheck"] as DataGridViewCheckBoxCell;
bool chk = Convert.ToBoolean(cell.Value);
if (chk)
{
DataRow dr = (dgvStudentList.Rows[i].DataBoundItem as DataRowView).Row;
int stuId = int.Parse(dr["StuId"].ToString());
listIds.Add(stuId);
}
}
if(listIds .Count == 0)
{
MessageBox.Show("请选择要删除的数据！", "删除学生提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if(listIds .Count > 0)
{
if (MessageBox.Show("您确定要删除该学生信息吗？", "删除学生提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
using (SqlConnection conn=new SqlConnection(SqlHelper.connString))
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
SqlCommand cmd = conn.CreateCommand();
cmd.Transaction = trans;
try
{
foreach (int id in listIds)
{
cmd.CommandText = "delete from StudentInfo where StuId=@StuId";
SqlParameter para = new SqlParameter("@StuId", id);
cmd.Parameters.Clear();
cmd.Parameters.Add(para);
count = cmd.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception)
{
trans.Rollback();
MessageBox.Show("删除学生出现了异常！", "删除学生提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
}
if(count==listIds.Count)
{
MessageBox.Show("这些学生信息删除成功！", "删除学生提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
//手动刷新
DataTable dtStudents = (DataTable)dgvStudentList.DataSource;
string idStr = string.Join(",", listIds);
DataRow[] rows= dtStudents.Select("StuId in (" idStr ")");
foreach (DataRow dr in rows)
{
dtStudents.Rows.Remove(dr);
}
dgvStudentList.DataSource = dtStudents;
}
}
}
}FrmAddStudent
代码语言：javascript复制 public partial class FrmAddStudent : Form
{
public FrmAddStudent()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
//1）获取页面信息输入
string stuName = txtStuName.Text.Trim();
int classId = (int)cmbClassName .SelectedValue;
string sex = rdoMan .Checked ? rdoMan .Text.Trim() : rdoWoman.Text.Trim();
string phone = txtPhone.Text.Trim();
//2）判空处理 姓名不可以为空 电话不可以为空
if (string.IsNullOrEmpty(stuName))
{
MessageBox.Show("姓名不能为空!", "添加学生提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (string.IsNullOrEmpty(phone))
{
MessageBox.Show("电话不能为空!", "添加学生提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//3）判断 姓名 电话 是否在数据库里已存在 姓名 电话
string sql = "select count(1) from StudentInfo where StuName=@StuName and Phone=@phone";
SqlParameter[] paras =
{
new SqlParameter("@StuName",stuName),
new SqlParameter("@phone",phone)
};
object o = SqlHelper.ExecuteScalar(sql, paras);
if (o != null && o != DBNull.Value && ((int)o) > 0)
{
MessageBox.Show("该学生已存在!", "添加学生提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//4）添加入库 sql 参数 执行 完成返回受影响行数
string sqlAdd = "insert into StudentInfo(StuName,ClassId,Sex,Phone) values(@StuName,@ClassId,@Sex,@Phone)";
SqlParameter[] parasAdd =
{
new SqlParameter("@StuName",stuName),
new SqlParameter("@ClassId",classId),
new SqlParameter("@Sex",sex),
new SqlParameter("@phone",phone)
};
int count = SqlHelper.ExecuteNonQuery(sqlAdd, parasAdd);
if (count > 0)
{
MessageBox.Show($"学生:{stuName} 添加成功!", "添加学生提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("该学生添加失败,请检查!", "添加学生提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
private void FrmAddStudent_Load(object sender, EventArgs e)
{
InitClasse();//加载班级列表
rdoMan .Checked = true;
}
private void InitClasse()
{
//获取数据 ---- 查询 ---写sql
string sql = "select ClassId,ClassName,GradeName from ClassInfo c,GradeInfo g where c.GradeId=g.GradeId";
DataTable dtClasses = SqlHelper.GetDataTable(sql);
//组合班级列表显示项的过程
if (dtClasses.Rows.Count > 0)
{
foreach (DataRow dr in dtClasses.Rows)
{
string className = dr["ClassName"].ToString();
string gradeName = dr["GradeName"].ToString();
dr["ClassName"] = className "--" gradeName;
}
}
//指定数据源
cmbClassName .DataSource = dtClasses;
cmbClassName.DisplayMember = "ClassName";
cmbClassName.ValueMember = "ClassId";
cmbClassName.SelectedIndex = 0;
}
private void btnClose_Click(object sender, EventArgs e)
{
this.Close();
}
}FrmEditStudent public partial class FrmEditStudent : Form
{
public FrmEditStudent()
{
InitializeComponent();
}
private Action reload = null;
private int stuId;
private void FrmEditStudent_Load(object sender, EventArgs e)
{
IntiClass();//加载班级列表
InitStuInfo();//加载学生信息
}
private void IntiClass()
{
//获取数据 ---- 查询 ---写sql
string sql = "select ClassId,ClassName,GradeName from ClassInfo c,GradeInfo g where c.GradeId=g.GradeId";
DataTable dtClasses = SqlHelper.GetDataTable(sql);
//组合班级列表显示项的过程
if (dtClasses.Rows.Count > 0)
{
foreach (DataRow dr in dtClasses.Rows)
{
string className = dr["ClassName"].ToString();
string gradeName = dr["GradeName"].ToString();
dr["ClassName"] = className "--" gradeName;
}
}
//指定数据源
cmbClassName.DataSource = dtClasses;
cmbClassName.DisplayMember = "ClassName";
cmbClassName.ValueMember = "ClassId";
cmbClassName.SelectedIndex = 0;
}
private void InitStuInfo()
{
//获取stuid
if(this.Tag!=null)
{
TagObject tagObject = (TagObject)this.Tag;
this.stuId = tagObject.EditId;
this.reload = tagObject.Reload;
}
//查询出来
string sql = @"select StuName,Sex,ClassId,Phone from StudentInfo where StuId=@StuId";
SqlParameter paraId = new SqlParameter("@StuId", stuId);
SqlDataReader dr = SqlHelper.ExecuteReader(sql, paraId);
if (dr.Read())
{
txtStuName.Text = dr["StuName"].ToString();
txtPhone.Text = dr["Phone"].ToString();
string sex = dr["Sex"].ToString();
if (sex == "男")
{
rdoMan.Checked = true;
}
else
{
rdoWoman.Checked = true;
}
int classId = (int)dr["ClassId"];
cmbClassName.SelectedValue = classId;
}
dr.Close();
}
private void btnEdit_Click(object sender, EventArgs e)
{
//1）获取页面信息输入
string stuName = txtStuName.Text.Trim();
int classId = (int)cmbClassName.SelectedValue;
string sex = rdoMan.Checked ? rdoMan.Text.Trim() : rdoWoman.Text.Trim();
string phone = txtPhone.Text.Trim();
//2）判空处理 姓名不可以为空 电话不可以为空
if (string.IsNullOrEmpty(stuName))
{
MessageBox.Show("姓名不能为空!", "修改学生提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (string.IsNullOrEmpty(phone))
{
MessageBox.Show("电话不能为空!", "修改学生提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//3）判断 姓名 电话 是否在数据库里已存在 姓名 电话
string sql = "select count(1) from StudentInfo where StuName=@StuName and Phone=@phone and StuId<>@StuId";
SqlParameter[] paras =
{
new SqlParameter("@StuName",stuName),
new SqlParameter("@phone",phone),
new SqlParameter ("@StuId",stuId)
};
object o = SqlHelper.ExecuteScalar(sql, paras);
if (o != null && o != DBNull.Value && ((int)o) > 0)
{
MessageBox.Show("该学生已存在,请重新修改!", "修改学生提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//4）修改入库 sql 参数 执行 完成返回受影响行数
string sqlEdit = " update StudentInfo "
" set stuName=@StuName, ClassId=@ClassId,Sex=@Sex,Phone=@Phone "
" where StuId=@StuId ";
SqlParameter[] parasAdd =
{
new SqlParameter("@StuName",stuName),
new SqlParameter("@ClassId",classId),
new SqlParameter("@Sex",sex),
new SqlParameter("@phone",phone),
new SqlParameter ("@StuId",stuId)
};
int count = SqlHelper.ExecuteNonQuery(sqlEdit, parasAdd);
if (count > 0)
{
MessageBox.Show($"学生:{stuName} 修改成功!", "添加学生提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.reload.Invoke();
}
else
{
MessageBox.Show("该学生修改失败,请检查!", "添加学生提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
private void btnClose_Click(object sender, EventArgs e)
{
this.Close();
}
}有兴趣研究的。可以进下面QQ群，在群在线文档里面进行下载。
