学生管理系统----(分层开发)

一：分层架构

搭建DAL层(数据访问层)、UI层(表示层)、BLL层(业务逻辑层)以及Model层(实体层)

各层的引用关系：

DAL、UI、BLL层引用Model层

UI层引用BLL层

BLL层引用DAL层

二：实现登录功能

DAL层：（为了防止sql注入需要使用SqlParameter）

  //登录
       public bool LoginStudent(Student stu) 
       {
           bool flag = false;
           string sql = "select count(1) from Student where StudentName=@name and LoginPwd=@pwd";
           SqlParameter[] para = 
           {
           new SqlParameter("@name",stu.StudentName),
           new SqlParameter("@pwd",stu.LoginPwd)
           };
           int count=Convert.ToInt32(SQLHelper.ExecuteScalar(sql,para));
           if (count > 0)
           {
               flag = true;
           }
           return flag;
       }

BLL层：（只做数据传递的载体）

 StudentDAL dal = new StudentDAL();
       //登录
       public bool IsLogin(Student stu) 
       {
           bool result = dal.LoginStudent(stu);
           return result;
       }

UI层：

private void btnLogin_Click(object sender, EventArgs e)
        {
            StudentBLL bll = new StudentBLL();
            Student stu = new Student();
            stu.StudentName = txtName.Text;
            stu.LoginPwd = txtPwd.Text;
            bool result=bll.IsLogin(stu);
            if (result)
            {
                MessageBox.Show("登录成功！");
                FrmMain frm = new FrmMain();
                this.Hide();
                frm.Show();
            }
            else
            {
                MessageBox.Show("失败啦！");
            }
        }

效果：

              

登录成功后进入主界面

三 实现新增学生用户

  效果：

DAL层：

//添加学生
       public bool AddStudent(Student stu)
       {
           bool flag = false;
           string sql = "insert into Student values('"+stu.StudentNo+"','"+stu.LoginPwd+"','"+stu.StudentName+"','"+stu.Sex+"','"+stu.GradeId+"','"+stu.Phone+"','"+stu.Address+"','"+stu.BornDate+"','"+stu.Email+"')";
           int count = SQLHelper.ExecuteNonQuery(sql);
           if (count > 0) 
           {
               flag = true;
           }
           return flag;
       }

BLL层：

//添加
       public bool Add(Student stu) 
       {
           bool result = dal.AddStudent(stu);
           return result;
       }

 UI层：

private void btnEdit_Click(object sender, EventArgs e)
        {
            stu.StudentNo = txtStudentNo.Text;
            stu.LoginPwd = txtPwd.Text;
            stu.StudentName = txtName.Text;
            stu.Sex = rbtnFemale.Checked ? "0" : "1";
            stu.GradeId = Convert.ToInt32(cboGrade.SelectedValue);
            stu.Phone = txtPhone.Text;
            stu.Address = txtAddress.Text;
            stu.BornDate = dpBirthday.Value;
            stu.Email = txtEmail.Text;      

            if (txtPwd.Text.Trim() != txtRePwd.Text.Trim())
            {
                MessageBox.Show("密码请输入一致!");
                return;
            }
            
                bool re = bll.Add(stu);
                if (re)
                {
                    MessageBox.Show("添加成功!");
                }
                else
                {
                    MessageBox.Show("添加失败!");
                }          
         }

四 加载学生信息<查询>

DAL层

返回的是泛型集合、可使用MyTool工具类或者foreach遍历

 //泛型
        public List<Student> GetAllStudent()
        {
           // List<Student> list = new List<Student>();

            string sql = "select * from Student";

            DataTable dt= SQLHelper.ExecuteDataTable(sql);

            MyTool tool = new MyTool();

            List<Student> list = tool.DataTableToList<Student>(dt);


            //foreach (DataRow item in dt.Rows)
            //{
            //    Student stu = new Student();
            //    stu.StudentName = item["StudentName"].ToString();
            //    stu.StudentNo = Convert.ToInt32(item["StudentNo"]);
            //    stu.LoginPwd =item["LoginPwd"].ToString();

            //    list.Add(stu);
            //}


            return list;
        
        }

BLL层

//泛型
     public List<Student> GetAllStudent()
     {
         List<Student> list = dal.GetAllStudent();
         return list;    
     
     }

UI层

 StudentBLL stuBll = new StudentBLL();
        GradeBLL GBll = new GradeBLL();

        //主窗体
        private void FrmSearchByGrade_Load(object sender, EventArgs e)
        {
            //删除英文列
            dgvList.AutoGenerateColumns = false;

           List<Student>list= stuBll.GetAllStudent();
           dgvList.DataSource = list;

           List<Grade> glist = GBll.GetAllGrade();
            //绑定年级下拉框值
          cboGrade.ValueMember = "GradeId";
          cboGrade.DisplayMember = "GradeName";
          cboGrade.DataSource = glist;
        }

        //查询
        private void btnSearch_Click(object sender, EventArgs e)
        {
            int gradeid =Convert.ToInt32(cboGrade.SelectedValue);

            //使用BLL层
            List<Student> list = stuBll.ByGradeId(gradeid);
            dgvList.DataSource = list;
           
        }

五 删除学生信息

DAL层

//删除
        public bool Delete(int stuNo)
        {
            bool flag = false;
            string sql="delete from Student where StudentNo=@No";
            SqlParameter para = new SqlParameter("@No",stuNo);
            int count = SQLHelper.ExecuteNonQuery(sql,para);
        if(count>0)
        {
            flag = true;
        }
        return flag;
        }

BLL层

  //删除
     public bool Delete(int stuNo)
     {
         return dal.Delete(stuNo);

     }

UI层 

注：光标位置

记录选中行的索引值 定义变量index、光标回到index-1

//删除
        private void tsmi_del_Click(object sender, EventArgs e)
        {
            int stuno = Convert.ToInt32(dgvList.SelectedRows[0].Cells["Column2"].Value);

            //记录选中行的索引值
            int index = dgvList.CurrentRow.Index;

            //MessageBox.Show(index.ToString());

            bool flag = stuBll.Delete(stuno);

            if(flag)
            {
                MessageBox.Show("删除成功！");
                List<Student> list = stuBll.GetAllStudent();
                dgvList.DataSource = list;

                //光标回到index-1
                dgvList[0, index].Selected = true;
                dgvList.CurrentCell=dgvList[0,index];
            }
        }

六 修改学生信息

DAL层

 //修改
        public bool UpdateInfo(Student stu)
        {
            bool flag = false;
            string sql = "update Student set StudentName=@stuName,Sex=@gender,BornDate=@birthday where StudentNo=@No";
            SqlParameter[] para =
            {
            new SqlParameter("@stuName",stu.StudentName),
            new SqlParameter("@Sex",stu.Gender),
            new SqlParameter("@BornDate",stu.Birthday),
            new SqlParameter("@No",stu.StudentNo)
            };
            int count = SQLHelper.ExecuteNonQuery(sql,para);
            if(count>0)
            {
                flag = true;
            }
            return flag;
        }

BLL层：

 //修改
     public bool UpdateInfo(Student stu)
     {
         return dal.UpdateInfo(stu);
     }

窗体的关联：

private void tsmi_update_Click(object sender, EventArgs e)
        {
            FrmUpdate frm = new FrmUpdate();
            frm.dgvList = dgvList;
            frm.Show();
        }

UI层  Load事件中实现窗体的传值

 StudentBLL stubll = new StudentBLL();
        public DataGridView dgvList;
        Student stu = new Student();

        private void FrmUpdate_Load(object sender, EventArgs e)
        {

            stu.StudentNo = Convert.ToInt32(dgvList.SelectedRows[0].Cells["Column2"].Value);
            stu.StudentName = dgvList.SelectedRows[0].Cells["name"].Value.ToString();
            stu.Sex= dgvList.SelectedRows[0].Cells["Column3"].Value.ToString();
            stu.BornDate= Convert.ToDateTime(dgvList.SelectedRows[0].Cells["Column4"].Value);

           // MessageBox.Show(stu.StudentName.ToString());
            this.txtNo.Text = stu.StudentNo.ToString();
            this.txtName.Text = stu.StudentName;
            this.txtGender.Text = stu.Sex;
            this.txtBirthday.Text = stu.BornDate.ToString();
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {

            stu.StudentName = txtName.Text;
            stu.Gender = txtGender.Text;
            stu.Birthday = Convert.ToDateTime(txtBirthday.Text);
            bool flag = stubll.UpdateInfo(stu);
            if(flag)
            {
                MessageBox.Show("修改成功！！");
                List<Student> list = stubll.GetAllStudent();
                dgvList.DataSource = list;
            }
        }