教师信息管理系统 ----三层架构
任务描述：
拿到这个项目，首先我们要了解教师信息管理系统，增加教师信息的维护功能
主要功能主要包括以下几个方面：
1.列表显示教师信息
2.增加教师信息
3.删除教师信息
首先我们要搭建三层框架（UI DAL BLL）
先搭建第一个框架 UI:
首先我们先创建UI层
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;
}
