学生管理系统<分层开发>
一：分层架构
搭建DAL层(数据访问层)、UI层(表示层)、BLL层(业务逻辑层)以及Model层(实体层)
各层的引用关系：
DAL、UI、BLL层引用Model层
UI层引用BLL层
BLL层引用DAL层
二：实现登录功能
StudentDAL层
定义一个bool变量，将Student对象作为参数，使用SqlParameter对象进行填充，判定count值，若大于0.返回真
//01.写一个用于登录的方法 public bool IsLogin(Student stu) { bool flag = false; //string sql = "select count(1) from student where studentname='" + stu.StudentName + "' and loginpwd='" + stu.LoginPwd + "'"; 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) }; //02.将sql语句交给SQL服务器执行 int count = Convert.ToInt32(SQLHelper.ExecuteScalar(sql,para)); if (count > 0) { flag = true; } return flag; }
StudentBLL层 数据传递
StudentDAL dal = new StudentDAL(); //登录 public bool isLogin(Student stu) { bool result = dal.IsLogin(stu); return result; }
UI层
获取两个文本框的值，定义bool变量，拿到登录的方法，传入参数
//登录 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) { frmMain frm = new frmMain(); this.Hide(); frm.Show(); }
三 实现新增学生用户
StudentDAL层
//添加学生 public bool AddStudent(Student stu) { bool flag = false; string sql = "insert into Student Values(@LoginPwd,@StudentName,@Gender,@GradeId,@Phone,@Address,@Birthday,@Email)"; SqlParameter []para= { new SqlParameter("@LoginPwd",stu.LoginPwd), new SqlParameter("@StudentName",stu.StudentName), new SqlParameter("@Gender",stu.Gender), new SqlParameter("@GradeId",stu.GradeId), new SqlParameter("@Phone",stu.Phone), new SqlParameter("@Address",stu.Address), new SqlParameter("@Birthday",stu.Birthday), new SqlParameter("@Email",stu.Email), }; int count = SQLHelper.ExecuteNonQuery(sql,para); if(count>0) { flag = true; } return flag; }
StudentBLL层
//添加学生 public bool AddStudent(Student stu) { bool result = dal.AddStudent(stu); return result; }
UI层
//保存 private void btnEdit_Click(object sender, EventArgs e) { //从界面获取各个值 Student stu = new Student(); stu.LoginPwd = txtPwd.Text; stu.StudentName = txtName.Text; stu.Gender = rbtnFemale.Checked?"0":"1"; stu.Phone = txtPhone.Text; stu.Address = txtAddress.Text; stu.Email = txtEmail.Text; //出生日期 stu.Birthday = dpBirthday.Value; //年级编号 stu.GradeId = Convert.ToInt32(cboGrade.SelectedValue); bool result = studal.AddStudent(stu); if (result) { 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,Gender=@gender,Birthday=@birthday where StudentNo=@No"; SqlParameter[] para = { new SqlParameter("@stuName",stu.StudentName), new SqlParameter("@gender",stu.Gender), new SqlParameter("@birthday",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.Gender = dgvList.SelectedRows[0].Cells["Column3"].Value.ToString(); stu.Birthday = 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.Gender; this.txtBirthday.Text = stu.Birthday.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; } }
