学生管理系统---三层架构
Student.UI
我们看一下窗体框架(参照上图顺序)
接下来我们看一下Model层
我们就以Model里面Studeng.cs类里面谁写的代码为例(数据根据库表里面的数据来写)。
public class Student {
//编号 private int studentNo; public int StudentNo { get { return studentNo; } set { studentNo = value; } } //密码 private string loginPwd; public string LoginPwd { get { return loginPwd; } set { loginPwd = value; } } ... }
紧接着我们来看一下DAL层(StudentDAL)
GradeDAL.cs
public class GradeDAL { //01.检索所有的年级名称集合,返回的是泛型集合List<Grade> public List<Grade> getAllGradeList() { string sql = "select * from grade"; //1.1 强SQL 转成内存中的一个张表 DataTable dt=SQLHelper.ExecuteDataTable(sql); MyTool tool=new MyTool(); //dt转List List<Grade> list = tool.DataTableToList<Grade>(dt); return list; } }
MyTool.cs(自定义工具类)
public class MyTool { /// <summary> /// DataSetToList /// </summary> /// <typeparam name="T">转换类型</typeparam> /// <param name="dataSet">数据源</param> /// <param name="tableIndex">需要转换表的索引</param> /// <returns></returns> public List<T> DataTableToList<T>(DataTable dt) { //确认参数有效 if (dt == null ) return null; List<T> list = new List<T>(); for (int i = 0; i < dt.Rows.Count; i++) { //创建泛型对象 T _t = Activator.CreateInstance<T>(); //获取对象所有属性 PropertyInfo[] propertyInfo = _t.GetType().GetProperties(); for (int j = 0; j < dt.Columns.Count; j++) { foreach (PropertyInfo info in propertyInfo) { //属性名称和列名相同时赋值 if (dt.Columns[j].ColumnName.ToUpper().Equals(info.Name.ToUpper())) { if (dt.Rows[i][j] != DBNull.Value) { info.SetValue(_t, dt.Rows[i][j], null); } else { info.SetValue(_t, null, null); } break; } } } list.Add(_t); } return list; } }
ResultDAL
public class ResultDAL { public List<ResultExt> getAllResult() { string sql = "select studentname,subjectname,studentresult,examdate from student,subject,result where student.studentno=result.studentno and result.subjectid=subject.subjectid"; DataTable dt=SQLHelper.ExecuteDataTable(sql); MyTool tool=new MyTool(); List<ResultExt> list = tool.DataTableToList<ResultExt>(dt); return list; } public List<ResultExt> getResultByParameter(int subjectid,string stuname) { string sql = "select studentname,subjectname,studentresult,examdate from student,subject,result where student.studentno=result.studentno and result.subjectid=subject.subjectid "; if (subjectid!=-1) { sql += " and result.subjectid=@subid"; } if (!string.IsNullOrEmpty(stuname))//null "" { sql += " and studentname like @name"; } SqlParameter[] para = { new SqlParameter("@subid",subjectid), new SqlParameter("@name",'%'+stuname+'%') }; DataTable dt = SQLHelper.ExecuteDataTable(sql,para); MyTool tool = new MyTool(); List<ResultExt> list = tool.DataTableToList<ResultExt>(dt); return list; } }
SQLHelper
public static class SQLHelper { public static string Constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString; public static int id; /// <summary> /// 执行NonQuery命令 /// </summary> /// <param name="cmdTxt"></param> /// <param name="parames"></param> /// <returns></returns> public static int ExecuteNonQuery(string cmdTxt, params SqlParameter[] parames) { return ExecuteNonQuery(cmdTxt, CommandType.Text, parames); } //可以使用存储过程的ExecuteNonquery public static int ExecuteNonQuery(string cmdTxt, CommandType cmdtype, params SqlParameter[] parames) { //判断脚本是否为空 ,直接返回0 if (string.IsNullOrEmpty(cmdTxt)) { return 0; } using (SqlConnection con = new SqlConnection(Constr)) { using (SqlCommand cmd = new SqlCommand(cmdTxt, con)) { if (parames != null) { cmd.CommandType = cmdtype; cmd.Parameters.AddRange(parames); } con.Open(); return cmd.ExecuteNonQuery(); } } } public static SqlDataReader ExecuteDataReader(string cmdTxt, params SqlParameter[] parames) { return ExecuteDataReader(cmdTxt, CommandType.Text, parames); } //SQLDataReader存储过程方法 public static SqlDataReader ExecuteDataReader(string cmdTxt, CommandType cmdtype, params SqlParameter[] parames) { if (string.IsNullOrEmpty(cmdTxt)) { return null; } SqlConnection con = new SqlConnection(Constr); using (SqlCommand cmd = new SqlCommand(cmdTxt, con)) { cmd.CommandType = cmdtype; if (parames != null) { cmd.Parameters.AddRange(parames); } con.Open(); //把reader的行为加进来。当reader释放资源的时候,con也被一块关闭 return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); } } //params 可变参数 public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parames) { return ExecuteDataTable(sql, CommandType.Text, parames); } //调用存储过程的类,关于(ExecuteDataTable) public static DataTable ExecuteDataTable(string sql, CommandType cmdType, params SqlParameter[] parames) { if (string.IsNullOrEmpty(sql)) { return null; } DataTable dt = new DataTable(); using (SqlDataAdapter da = new SqlDataAdapter(sql, Constr)) { da.SelectCommand.CommandType = cmdType; if (parames != null) { da.SelectCommand.Parameters.AddRange(parames); } da.Fill(dt); return dt; } } /// <summary> /// ExecuteScalar /// </summary> /// <param name="cmdTxt">第一个参数,SQLServer语句</param> /// <param name="parames">第二个参数,传递0个或者多个参数</param> /// <returns></returns> public static object ExecuteScalar(string cmdTxt, params SqlParameter[] parames) { return ExecuteScalar(cmdTxt, CommandType.Text, parames); } //可使用存储过程的ExecuteScalar public static object ExecuteScalar(string cmdTxt, CommandType cmdtype, params SqlParameter[] parames) { if (string.IsNullOrEmpty(cmdTxt)) { return null; } using (SqlConnection con = new SqlConnection(Constr)) { using (SqlCommand cmd = new SqlCommand(cmdTxt, con)) { cmd.CommandType = cmdtype; if (parames != null) { cmd.Parameters.AddRange(parames); } con.Open(); return cmd.ExecuteScalar(); } } } //调用存储过程的DBHelper类(关于ExeceutScalar,包含事务,只能处理Int类型,返回错误号) public static object ExecuteScalar(string cmdTxt, CommandType cmdtype,SqlTransaction sqltran, params SqlParameter[] parames) { if (string.IsNullOrEmpty(cmdTxt)) { return 0; } using (SqlConnection con = new SqlConnection(Constr)) { int sum = 0; using (SqlCommand cmd = new SqlCommand(cmdTxt, con)) { cmd.CommandType=cmdtype; if (parames != null) { cmd.Parameters.AddRange(parames); } con.Open(); sqltran = con.BeginTransaction(); try { cmd.Transaction = sqltran; sum=Convert.ToInt32( cmd.ExecuteScalar()); sqltran.Commit(); } catch (SqlException ex) { sqltran.Rollback(); } return sum; } } } }
StudentDAL
public class StudentDAL { public List<Student> GetAllStudentList() { string sql = "select * from student"; DataTable dt = SQLHelper.ExecuteDataTable(sql); MyTool tool=new MyTool(); List<Student> list = tool.DataTableToList<Student>(dt); return list; } public DataTable GetAllStudent() { string sql = "select * from student"; DataTable dt = SQLHelper.ExecuteDataTable(sql); return dt; } public bool IsLogin(Student stu) { bool flag = false; string sql = "select count(1) from student where studentno='"+stu.SName+"' and loginpwd='"+stu.LoginPwd+"'"; int count=Convert.ToInt32(SQLHelper.ExecuteScalar(sql)); if (count>0) { flag = true; } return flag; } }
SubjectDAL
public class SubjectDAL { //01.写一个根据年级编号检索该年级下所有科目集合的方法 public List<Subject> getAllSubjectByGradeId(int id) { string sql = "select * from subject where gradeid=@id"; SqlParameter para = new SqlParameter("@id",id); DataTable dt=SQLHelper.ExecuteDataTable(sql, para); MyTool tool=new MyTool(); List<Subject> list = tool.DataTableToList<Subject>(dt); return list; } }
我们在来看一下BLL层(StudentBLL)BLL层用于连接UI层和DAL层
在这里我们就简单的看一下StudentBLL.cs类里面的代码
public class StudentBLL { public List<Student> GetAllStudent() { List<Student> list=dal.GetAllStudentList(); //DataTable dt= dal.GetAllStudent(); return list; } StudentDAL dal=new StudentDAL(); public bool IsLogin(Student stu) { bool result= dal.IsLogin(stu); return result; } }
最后就是向UI层填写完整代码了
登录窗体
登录
StudentBLL bll = new StudentBLL(); //确定按钮 private void btnLogin_Click(object sender, EventArgs e) { //1.1 拼接处一个Student对象,并且赋值 Student stu=new Student(); stu.SName = txtName.Text; stu.LoginPwd = txtPwd.Text; //1.2 调用 bll层的对应的登录方法,传入student对象 bool flag = bll.IsLogin(stu); if (flag) { MessageBox.Show("success"); } }
登录类型
查询窗体
GradeBLL gradebll=new GradeBLL(); SubjectBLL subBll=new SubjectBLL(); ResultBLL resultBll=new ResultBLL(); private bool flag; //Load事件 private void FrmSelectResultM1_Load(object sender, EventArgs e) { dgvResult.AutoGenerateColumns = false; LoadGrade(); List<ResultExt> list = resultBll.getAllResult(); dgvResult.DataSource = list; } public void LoadGrade() { cboSubject.DropDownHeight = 106; //01.对年级下拉框绑定数据 List<Grade> list = gradebll.getAllGradeList(); //两行代码() cboGrade.ValueMember = "gradeId"; cboGrade.DisplayMember = "gradeName"; cboGrade.DataSource = list; flag = true; } //年级下拉框选中项改变的事件 private void cboGrade_SelectedIndexChanged(object sender, EventArgs e) { //1.1 先获取到选中的年级名称对应的年级编号 //如何获取下拉框的隐藏值 () int selectid = Convert.ToInt32(cboGrade.SelectedValue); //code a little ,debug a little //1.2 介入BLL ,调用方法,传入ID,获取List<Subject> List<Subject> list = subBll.getAllSubjectByGradeId(selectid); Subject sub=new Subject(); sub.SubjectId = -1; sub.SubjectName = "请选择"; list.Insert(0,sub); cboSubject.DataSource = list; //两行代码() cboSubject.ValueMember = "subjectId"; cboSubject.DisplayMember = "subjectName"; } //查询 private void btnSearch_Click(object sender, EventArgs e) { //下拉框隐藏值 int subid = Convert.ToInt32(cboSubject.SelectedValue); //文本框的值 string name = txtName.Text; List<ResultExt> list = resultBll.getResultByParameter(subid, name); dgvResult.DataSource = list; }
显示全部信息窗体
StudentBLL bll=new StudentBLL(); private void FrmStuList_Load(object sender, EventArgs e) { List<Student> list= bll.GetAllStudent(); dgvList.DataSource = list; }