简单的winform学生管理系统Demo

界面效果

 

 

 练习重点

1. 关系表的创建

2. 增删改查的操作，及sqlhelper的封装

3. 跨页面数据传递，编辑页数据提交后数据局步刷新到列表数据

项目源码

FrmStuddentList

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

 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群，在群在线文档里面进行下载。