今天我们来讲讲分层开发,你从标题能不能简单的认识一下什么是分层呢?
不懂也没关系,接下来我来给你讲讲。
第一章 软件系统的分层开发
(1)其实分层模式可以这样定义:将解决方案中功能不同的模块分到不同的项目中实现,每一层中的组件应保持内聚性,每一层都应该与它下面的各层保持松耦合。
分层模式是最常见的一种架构模式,甚至可以说分层模式是很多架构模式的基础。
数据访问层:
这一层处于最底层,负责与数据库的交互,也成为DAL(Data Access Layer)
表示层:
这一层直接与用户打交道,负责显示或者获取数据,也称为UI(User Interface Layer)
(2).NET程序集是任何.NET Framework应用程序的基本构造块。程序集由描述它的程序集清单(包含版本号,程序集名称等),类型元数据,MSIL代码和资源组成,
这些部分都分布在一个文件中。
程序集主要包括以下两类:
(1)可执行文件,即.exe文件 (2)类库文件,即.dll文件
(3)程序集,解决方案,项目和命名空间的关系如下:
(1)一个解决方案可以由一个或者多个项目组成,这些项目可以是Windows应用程序,类库等。
(2)一个程序集可以包含多个命名空间,程序集默认的命名空间名称就是程序集的名称。
(3)命名空间是组织C#程序的一种逻辑架构,一个命名空间可以有多个类。
(4)分层开发的优点:
(1)代码的复用(2)分离开发人员的关注(3)无损提换(4)降低了系统间的依赖
(5)异常处理
常见的异常类型:
System.Exception :这个类提供系统异常和应用程序异常之间的区别
System.SQLException :当SQL Server返回警告或者错误时引发的异常
ArgumentNullException :当将空引用传递给不接受它作为有效参数的方法时引发的异常
FileNotFoundException :试图访问磁盘上不存在的文件失败时引发的异常
IOException :当出现I/O错误时,引发此异常
ApplicationException :在应用程序执行过程中检测到由应用程序定义的异常
异常类的常用属性:
Message :提供引起异常的详细信息
Source :表示导致异常发生的应用程序或者对象的名称
StackTrace :提供在栈堆上所调用方法的详细信息,并首先显示最近调用的方法
InnerException :对内部异常的引用,如果此异常基于前一个异常,则内部异常指最初发生的异常
(6)异常处理回顾
语法:
try-catch try { //可能引发异常的工作代码 } catch(异常对象) { //异常处理 } try-finally try { //可能引发异常的工作代码 } finally { //清理相关对象的代码 } try-catch-finally try { //可能引发异常的工作代码 } catch(异常对象) { //异常处理 } finally { //清理相关对象的代码 }
上面的文字都是枯燥的,接下来用代码来演示一下:
/// <summary> /// 数据访问层 /// </summary> public class StudentDAL { public void AddStudent() { } public DataTable SelectStudent() { string str = "Data Source=.; initial catalog=MySchools;user id=sa;"; string sql = "select * from student "; SqlConnection con = new SqlConnection(str); SqlDataAdapter da = new SqlDataAdapter(sql,con); DataSet ds = new DataSet(); try { //int num = 0; //int result = 1 / num; da.Fill(ds, "stuInfo"); return ds.Tables["stuInfo"]; } catch (SqlException ex) { throw new Exception("访问数据库失败" + ex.Message); } catch (DivideByZeroException ex) { throw new Exception("除数不能为0" + ex.Message); } catch (Exception ex) { throw new Exception("失败" + ex.Message); } }
/// <summary> /// 数据访问层 /// </summary> public class GradeDAL { public DataTable SelectGrade() { string str = "data source=.; initial catalog=MySchools; user id=sa;"; string sql = "select * from Grade"; SqlConnection con = new SqlConnection(str); SqlDataAdapter da = new SqlDataAdapter(sql,con); DataSet ds = new DataSet(); da.Fill(ds,"gradeInfo"); return ds.Tables["gradeInfo"]; } }
private void Form1_Load(object sender, EventArgs e) { StudentDAL dal = new StudentDAL(); GradeDAL grade = new GradeDAL(); try { DataTable data = dal.SelectStudent(); DataTable table = grade.SelectGrade(); dgvList.DataSource = data; comboBox1.DataSource = grade.SelectGrade(); comboBox1.DisplayMember = "GradeName"; comboBox1.ValueMember = "GradeId"; } catch (Exception ex) { MessageBox.Show(ex.Message); } }
第二章 OOP典型应用:实体类
(1)实体类是业务对象的基础,它用面向对象的思想消除了关系数据与对象之间的差异
实体类:
/// <summary> /// 部门表 /// </summary> public class Department { public int BId { get; set; } public int BName{ get; set; } }
/// <summary> /// 员工表 /// </summary> public class Employee { public int YId{ get; set; } public int YName{ get; set; } public int BId{ get; set; } public int ZId{ get; set; } }
/// <summary> /// 任务内容表 /// </summary> public class Task { public string Contents {get;set;} public int RId {get;set;} public int YId {get;set;} public DateTime Time {get;set;} public int Hours {get;set;} public string Type { get; set; } }
public class InfoAddDAL { public bool Add(string name) { bool falg = false; string sql = "insert into ProgramInfo(pname) values('"+name+"')"; int num=SQLHelper.ExecuteNonQuery(sql); if(num==1) { falg= true; } return falg; } public DataTable SelectInfo() { List<string> list = new List<string>(); try { string sql = "select pname from ProgramInfo"; DataTable table=SQLHelper.ExecuteDataTable(sql); return table; } catch (SqlException ex) { throw ex; } catch(Exception ex) { throw ex; } } public bool DeleteInfo(string name) { bool falg = false; try { string sql = "delete ProgramInfo where pname='" + name + "'"; int num=SQLHelper.ExecuteNonQuery(sql); if (num == 1) { falg= true; } return falg; } catch (SqlException ex) { throw ex; } catch(Exception ex) { throw ex; } } public bool UpdateInfo(string name,string names) { bool falg = false; string sql = "Update ProgramInfo set pname='"+name+"'where pname='"+names+"'"; int num=SQLHelper.ExecuteNonQuery(sql); if(num==1) { falg= true; } return falg; } }
接下来是一个辅助类,因为以后要经常用,有能力的自己写一个工具吧!
public static class SQLHelper { //用静态的方法调用的时候不用创建SQLHelper的实例 //Execetenonquery // public static string Constr = "server=HAPPYPIG\SQLMODEL;database=shooltest;uid=sa;pwd=6375196;"; 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); } } 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; } } } }
数据访问层:
public class TaskDAL { //查询信息 public DataTable Info() { string sql = "select Contents,YName, Time, Hours, Type from Task,Employee where Task.YId=Employee.YId"; DataTable table =SQLHelper.ExecuteDataTable(sql); return table; } public DataTable RSelectInfo(Task task) { string sql = "select Contents,YName, Time, Hours, Type from Task,Employee where Task.YId=Employee.YId and Contents='"+task.Contents+"'"; DataTable table = SQLHelper.ExecuteDataTable(sql); return table; } //根据条件查询 public DataTable SelectInfo(DateTime time1, DateTime time2) { string sql = "select Contents,YName, Time, Hours, Type from Task,Employee where Time between'" + time1 + "' and '"+ time2 + "' and Task.YId=Employee.YId"; DataTable table = SQLHelper.ExecuteDataTable(sql); return table; } //加载任务名称 public DataTable RInfo() { string sql = "select Contents from Task "; DataTable table = SQLHelper.ExecuteDataTable(sql); return table; } }
在这里再引用一个App.config
<?xml version="1.0" encoding="utf-8" ?> <configuration> <connectionStrings> <add name="constr" connectionString="data source=.; initial catalog=AddInfo; uid=sa;"> </add> </connectionStrings> <startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" /> </startup> </configuration>
窗体加载:
private void FrmMain_Load(object sender, EventArgs e) { txt02.Enabled = false; } private void btn01_Click(object sender, EventArgs e) { InfoAddDAL info = new InfoAddDAL(); if (txt02.Text=="") { MessageBox.Show("请输入你要添加的文本"); } else { try { bool falg = info.Add(txt02.Text); if (falg) { DataTable table = info.SelectInfo(); lb01.DataSource = table; lb01.DisplayMember = "pname"; } txt02.Text = ""; } catch (SqlException) { MessageBox.Show("sql语句错误"); } catch (Exception) { MessageBox.Show("程序出错"); } } } private void btn02_Click(object sender, EventArgs e) { InfoAddDAL info = new InfoAddDAL(); string name = lb01.SelectedItem.ToString(); bool falg = info.DeleteInfo(name); if (falg) { DataTable table = info.SelectInfo(); lb01.DataSource = table; lb01.DisplayMember = "pname"; } } private void lb01_DoubleClick(object sender, EventArgs e) { string name = lb01.SelectedItem.ToString(); txt02.Text = name; } private void btn03_Click(object sender, EventArgs e) { if(txt02.Text=="") { MessageBox.Show("请选择要修改的文本"); } else { string name = lb01.SelectedItem.ToString(); InfoAddDAL info = new InfoAddDAL(); bool falg = info.UpdateInfo(txt02.Text, name); if (falg) { DataTable table = info.SelectInfo(); lb01.DataSource = table; lb01.DisplayMember = "pname"; } txt02.Text = ""; } } private void btn04_Click(object sender, EventArgs e) { txt02.Enabled = true; InfoAddDAL info = new InfoAddDAL(); try { DataTable table = info.SelectInfo(); lb01.DataSource = table; lb01.DisplayMember = "pname"; } catch (SqlException) { MessageBox.Show("sql语句错误"); } catch (Exception) { MessageBox.Show("程序出错"); } } }
TaskDAL DAL = new TaskDAL(); DataTable table = null; private void btn01_Click(object sender, EventArgs e) { DateTime time1 = dateTimePicker1.Value; DateTime time2 = dtp02.Value; if (cbo01.Text == "") { table = DAL.Info(); } else if(time1.ToString() != "" && time2.ToString() != "") { table = DAL.SelectInfo(time1, time2); } else if(cbo01.Text!=""&& time1.ToString() != "" && time2.ToString() != "") { Task task = new Task(); task.Contents = cbo01.SelectedText.ToString(); table = DAL.RSelectInfo(task); } dgvList.DataSource = table; } private void FrmTwo_Load(object sender, EventArgs e) { table= DAL.RInfo(); cbo01.DisplayMember = "Contents"; cbo01.DataSource = table; }
配置文件里保存的是应用程序运行所依赖的常量,这些常量可能会随着多种因素的变化而变化,所以将这些信息写在配置文件中,由程序来动态读取。
读取连接字符串的语法如下:
public static readonly string conString=ConfigurationManager.ConnectionStrings["MySchoolConnectionString"].ToString();
(2)const和readonly的区别
(1)readonly只能修饰类变量 const修饰成员变量和局部变量
(2)readonly在运行时赋值,const在编译时赋值
(3)const只能修饰值类型和特殊的引用类型 readonly可以修饰任何类型
第三章 提高系统性能:从数据访问开始
(1)using语句
using的作用:(1)导入命名空间(2)释放实现了disposable接口的对象非托管资源
语法:
using(SqlConnection con=new SqlConnection(constr))
{
//数据库操作代码.......
}
(2)参数化命令(防止SQL注入攻击)
语法:
(1)使用参数名和参数类型进行设置 public SqlParameter Add(string parameterName,SqlDbType slDbType); public SqlParameter Add(string parameterName,SqlDbType paraValue,int size); (2)使用SqlParameter对象进行填充 public SqlParameter Add(SqlParameter value); //添加单个参数 public void AddRange(SqlParameter[] values); //添加多个参数
(3)SqlParameter类的常用属性
DbType :获取或设置参数的DbType
Direction :获取或设置一个值,该值表示参数是只可输入,是可输出,还是双向存储过程返回值参数
IsNullable :获取或设置一个值,该值指示参数是否接受空值
ParameterName :获取或设置SqlParameter的名称
Size :获取或设置列中数据的最大值(以字节为单位)
SqlDbType :获取或设置参数的SqlDbType
Value :获取或设置该参数的值
第四章 业务的扩展:三层架构
(1)业务逻辑层BLL(Business Logic Layer)
实体类:
public class Grade { public int GradeId { get; set; } public string GradeName { get;set;} }
public class Result { public int StudentNo { get; set; } public int SubjectId { get; set; } public int StudentResult { get; set; } public string ExamDate { get; set; } public int Id { get; set; } }
public class Student { public int StudentNo { get; set; } public string StudentName { get; set; } public string LoginPwd { get; set; } public int GradeId { get; set; } public string Gender { get; set; } public string Address { get; set; } public string Phone { get; set; } public DateTime Birthday { get; set; } public string Email { get; set; } public string IdentityId { get; set; } }
public class Subject { public int SubjectId { get; set; } public string SubjectName { get; set; } public int ClassHour { get; set; } public int GradeId { get; set; } }
数据访问层DAL:
/// <summary> /// 班级表 /// </summary> public class GradeDAL { //加载班级信息 public DataTable GradeInfo() { string sql = "select * from Grade"; DataTable table=SQLHelper.ExecuteDataTable(sql); return table; } }
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; } }
/// <summary> /// 成绩表 /// </summary> public class ResultDAL { //根据科目条件查询成绩 public DataTable SelectResult(int subjectid) { string sql = "select StudentName,SubjectName,StudentResult,ExamDate,student.studentno from student,subject,result where Subject.subjectid=result.subjectid and student.StudentNo=Result.StudentNo and result.subjectid="+subjectid+" "; DataTable table = SQLHelper.ExecuteDataTable(sql); return table; } //根据姓名查询成绩 public DataTable SelectResult(string studentname) { string sql = "select StudentName,SubjectName,StudentResult,ExamDate,student.studentno from student,subject,result where student.studentName like'%" + studentname + "%' and Subject.subjectid=result.subjectid and student.StudentNo=Result.StudentNo "; DataTable table= SQLHelper.ExecuteDataTable(sql); return table; } //查询全部成绩 public DataTable SelectResult() { string sql = "select StudentName,SubjectName,StudentResult,ExamDate,student.studentno from student,subject,result where Subject.subjectid=result.subjectid and student.StudentNo=Result.StudentNo "; DataTable table = SQLHelper.ExecuteDataTable(sql); return table; } // 修改学生成绩 public bool UpdateResult(Result result) { bool flag = false; string sql = "update Result set StudentResult='" + result.StudentResult+ "',ExamDate='"+result.ExamDate+ "',SubjectId='"+result.SubjectId+"' where result.studentno="+result.StudentNo+""; int count= SQLHelper.ExecuteNonQuery(sql); if(count>0) { flag = true; } return flag; } //添加学生成绩 public bool AddResult(Result result) { bool flag = false; string sql = "insert into result values(@studentno,@subjectid,@studentresult,@examdate)"; SqlParameter[] para = { new SqlParameter("@studentno",result.StudentNo), new SqlParameter("@subjectid",result.SubjectId), new SqlParameter("@studentresult",result.StudentResult), new SqlParameter("@examdate",result.ExamDate) }; int num=Convert.ToInt32(SQLHelper.ExecuteScalar(sql,para)); if(num>0) { flag = true; } return flag; } }
/// <summary> /// 学生表 /// </summary> public class StudentDAL { //Login登录 public bool IsLogin(Student stu) { #region 方式一:登录 //bool flag = false; //string sql = "select count(1) from student where studentno=" + stu.StudentNo + " and Loginpwd='" + stu.LoginPwd + "'"; //int count = Convert.ToInt32(SQLHelper.ExecuteScalar(sql)); //if (count > 0) //{ // flag = true; //} //return flag; #endregion #region 方式二:登录 bool flag = false; string sql = "select count(1) from student where studentno=@studentno and loginpwd=@pwd"; SqlParameter[] para = { new SqlParameter("@studentno",stu.StudentNo), new SqlParameter("@pwd",stu.LoginPwd) }; int count = Convert.ToInt32(SQLHelper.ExecuteScalar(sql, para)); if (count > 0) { flag = true; } return flag; #endregion } //根据条件学生信息 // StudentNo, LoginPwd, StudentName, Gender, GradeId, Phone, Address, Birthday, Email, public List<Student> StudentInfo(int gradeid) { #region 查询一 List<Student> list = new List<Student>(); string sql = "select StudentNo, LoginPwd, StudentName, Gender, GradeId, Phone, Address, Birthday, Email,IdentityId from student where GradeId=" + gradeid + ""; SqlDataReader dr = SQLHelper.ExecuteDataReader(sql); if (dr.HasRows) { while (dr.Read()) { Student stu = new Student(); stu.StudentNo = Convert.ToInt32(dr["StudentNo"]); stu.LoginPwd = dr["LoginPwd"].ToString(); stu.StudentName = dr["StudentName"].ToString(); stu.Gender = dr["Gender"].ToString(); stu.GradeId = Convert.ToInt32(dr["GradeId"]); stu.Phone = dr["Phone"].ToString(); stu.Address = dr["Address"].ToString(); stu.Birthday = Convert.ToDateTime(dr["Birthday"]); stu.Email = dr["Email"].ToString(); stu.IdentityId = dr["IdentityId"].ToString(); list.Add(stu); } } dr.Close(); return list; #endregion } public List<Student> StudentInfo(string name) { #region 查询一 List<Student> list = new List<Student>(); string sql = "select StudentNo, LoginPwd, StudentName, Gender, GradeId, Phone, Address, Birthday, Email,IdentityId from student where StudentName=" + name + ""; SqlDataReader dr = SQLHelper.ExecuteDataReader(sql); if (dr.HasRows) { while (dr.Read()) { Student stu = new Student(); stu.StudentNo = Convert.ToInt32(dr["StudentNo"]); stu.LoginPwd = dr["LoginPwd"].ToString(); stu.StudentName = dr["StudentName"].ToString(); stu.Gender = dr["Gender"].ToString(); stu.GradeId = Convert.ToInt32(dr["GradeId"]); stu.Phone = dr["Phone"].ToString(); stu.Address = dr["Address"].ToString(); stu.Birthday = Convert.ToDateTime(dr["Birthday"]); stu.Email = dr["Email"].ToString(); stu.IdentityId = dr["IdentityId"].ToString(); list.Add(stu); } } dr.Close(); return list; #endregion } // 添加学生信息 public int StudentAdd(Student stu) { string sql = "insert into student values('" + stu.LoginPwd + "','" + stu.StudentName + "'," + stu.Gender + ",'" + stu.GradeId + "','" + stu.Phone + "','" + stu.Address + "','" + stu.Birthday + "','" + stu.Email + "','"+stu.IdentityId+"');select @@IDENTITY"; int num = Convert.ToInt32(SQLHelper.ExecuteScalar(sql)); return num; } //修改学生信息 public bool UpdateInfo(Student stu) { bool flag = false; string str = "data source=.; initial catalog=MySchool; uid=sa;"; using (SqlConnection con = new SqlConnection(str)) { SqlCommand com = con.CreateCommand(); com.CommandText = "usp_Update"; com.CommandType = CommandType.StoredProcedure; SqlParameter[] para = { new SqlParameter("@studentno",stu.StudentNo), new SqlParameter("@pwd",stu.LoginPwd), new SqlParameter("@name",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), new SqlParameter("@identitycard",stu.IdentityId) }; com.Parameters.AddRange(para); con.Open(); int count = com.ExecuteNonQuery(); if (count > 0) { flag = true; } return flag; } } //查询全部学生信息 public DataTable Student() { string sql = "select StudentNo, LoginPwd, StudentName, Gender, GradeName, Phone, Address, Birthday, Email,IdentityCard from student,grade where student.gradeid=grade.gradeid"; DataTable table = SQLHelper.ExecuteDataTable(sql); return table; } //条件查询学生信息 public DataTable Student(string name) { string sql = "select StudentNo, LoginPwd, StudentName, Gender, GradeId, Phone, Address, Birthday, Email,IdentityCard from student,grade where gradename=@name and student.gradeid=grade.gradeid"; SqlParameter para = new SqlParameter("@name", name); DataTable table = SQLHelper.ExecuteDataTable(sql, para); return table; } public DataTable SelectStudent(string gradename,string grader) { string sql = "select StudentNo, LoginPwd, StudentName, Gender,student.Gradeid, Phone, Address, Birthday, Email,IdentityCard from student,grade where student.gradeid=grade.gradeid and gradename=@name and gender=@gender"; SqlParameter[] para = { new SqlParameter("@name",gradename), new SqlParameter("@gender",grader) }; DataTable table = SQLHelper.ExecuteDataTable(sql, para); return table; } public DataTable SelectStudent(string gradename) { string sql = "select StudentNo, LoginPwd, StudentName, Gender,student.GradeId, Phone, Address, Birthday, Email,IdentityCard from student,grade where student.gradeid=grade.gradeid and gradename=@name"; SqlParameter para = new SqlParameter("@name",gradename); DataTable table = SQLHelper.ExecuteDataTable(sql, para); return table; } }
/// <summary> /// 科目表 /// </summary> public class SubjectDAL { public DataTable SubjectInfo() { string sql = "select * from subject"; DataTable table = SQLHelper.ExecuteDataTable(sql); return table; } public List<Subject> SubjectALL(int gradeid) { string sql = "select * from subject where gradeid=@gradeid"; SqlParameter para = new SqlParameter("@gradeid",gradeid); DataTable table = SQLHelper.ExecuteDataTable(sql,para); MyTool tool = new MyTool(); List<Subject> list=tool.DataTableToList<Subject>(table); return list; } }
业务逻辑层BLL:
public class GradeBLL { GradeDAL dal = new GradeDAL(); //加载年级信息 public DataTable GradeInfo() { return dal.GradeInfo(); } }
public class ResultBLL { ResultDAL dal = new ResultDAL(); public DataTable SelectResult(int subjectid) { return dal.SelectResult(subjectid); } //条件查询 public DataTable SelectResult(string studentname) { return dal.SelectResult(studentname); } //查询全部 public DataTable SelectResult() { return dal.SelectResult(); } //修改成绩 public bool UpdateResult(Result result) { return dal.UpdateResult(result); } //添加学生成绩 public bool AddResult(Result result) { return dal.AddResult(result); } }
/// <summary> /// 逻辑层 /// </summary> /// public class StudentBLL { StudentDAL dal = new StudentDAL(); //登录 public bool IsLogin(Student stu) { //Common com = new Common(); //string temp = com.GetMD5String(stu.LoginPwd); //stu.LoginPwd = temp; return dal.IsLogin(stu); } ////显示学生信息 public List<Student> StudentInfo(int gradeid) { return dal.StudentInfo(gradeid); } public List<Student> StudentInfo(string name) { return dal.StudentInfo(name); } //增加学生信息 public int StudentAdd(Student stu) { //Common com = new Common(); //string temp = com.GetMD5String(stu.LoginPwd); //stu.LoginPwd = temp; return dal.StudentAdd(stu); } public bool UpdateInfo(Student stu) { return dal.UpdateInfo(stu); } public DataTable SelectStudent(string gradename,string grader ) { return dal.SelectStudent(gradename, grader); } public DataTable SelectStudent(string gradename) { return dal.SelectStudent(gradename); } public DataTable Student() { return dal.Student(); } }
public class SubjectBLL { //加载科目信息 SubjectDAL sdl = new SubjectDAL(); public DataTable SubjectInfo() { return sdl.SubjectInfo(); } public List<Subject> SubjectALL(int gradeid) { return sdl.SubjectALL(gradeid); } }
表示层DAL:
登录窗体:
public partial class FrmIsLogin : Form { public FrmIsLogin() { InitializeComponent(); } StudentBLL bll = new StudentBLL(); private void btn01_Click(object sender, EventArgs e) { Student stu = new Student(); stu.StudentNo = Convert.ToInt32(txt01.Text); stu.LoginPwd = txt02.Text; bool flag= bll.IsLogin(stu); if(flag) { FrmMain mm = new FrmMain(); Hide(); mm.Show(); // MessageBox.Show("Test"); } } }
主窗体:
public partial class FrmMain : Form { public FrmMain() { InitializeComponent(); } private void toolStripMenuItem1_Click(object sender, EventArgs e) { FrmAdd aa = new FrmAdd(); aa.MdiParent = this; aa.Show(); } private void toolStripMenuItem2_Click(object sender, EventArgs e) { FrmInfo info = new FrmInfo(); info.MdiParent = this; info.Show(); } private void 添加学生成绩ToolStripMenuItem_Click(object sender, EventArgs e) { FrmResult result = new FrmResult(); result.MdiParent = this; result.Show(); } private void 查询学生成绩ToolStripMenuItem_Click(object sender, EventArgs e) { FrmSelectResult sr = new FrmSelectResult(); sr.MdiParent = this; sr.Show(); } private void 退出ToolStripMenuItem_Click(object sender, EventArgs e) { Application.Exit(); } }
添加和修改学生信息窗体:
public partial class FrmAdd : Form { public FrmAdd() { InitializeComponent(); } public int no; public string pwd; public string name; public string phone; public string address; public string gender; public string gradename; public string email; public string birthday; public string identitycard; StudentBLL bll = new StudentBLL(); //添加学生 private void btn01_Click(object sender, EventArgs e) { //创建一个学生对象 Student stu = new Student(); stu.LoginPwd = txt02.Text; stu.StudentName = txt04.Text; stu.Gender = rb02.Checked ? "0" : "1"; stu.Phone = txt05.Text; stu.Address = txt06.Text; stu.Birthday = Convert.ToDateTime(txt09.Text); stu.Email = txt07.Text; stu.GradeId = Convert.ToInt32(cbo01.SelectedValue); stu.IdentityId = txt08.Text; //把对象添加到数据表中 if(txt01.Text!="") { stu.StudentNo = Convert.ToInt32(txt01.Text); bool flag = bll.UpdateInfo(stu); if (flag) { MessageBox.Show("修改成功!"); foreach (Control item in gb01.Controls) { if (item is TextBox) { item.Text = ""; } } foreach (Control item in gb02.Controls) { if (item is TextBox) { item.Text = ""; } rb01.Checked = true; cbo01.Text = ""; } } } else { int result = bll.StudentAdd(stu); if (result>0) { txt01.Text = result.ToString(); MessageBox.Show("添加成功!"); foreach (Control item in gb01.Controls) { if (item is TextBox) { item.Text = ""; } } foreach (Control item in gb02.Controls) { if (item is TextBox) { item.Text = ""; } rb01.Checked = true; cbo01.Text = ""; } } } } GradeBLL gbl = new GradeBLL(); //加载班级信息 private void FrmAdd_Load(object sender, EventArgs e) { cbo01.ValueMember = "GradeId"; cbo01.DisplayMember = "gradeName"; cbo01.DataSource= gbl.GradeInfo(); if (this.Text == "添加学生信息") { btn01.Text = "添加"; } else { btn01.Text = "修改"; txt01.Text = no.ToString(); txt02.Text = pwd; txt03.Text = pwd; txt04.Text = name; if (gender.Equals(0)) { rb01.Checked = true; } else { rb02.Checked = true; } txt05.Text = phone; txt06.Text = address; txt09.Text = birthday.ToString(); txt07.Text = email; cbo01.Text = gradename; txt08.Text = identitycard; } } }
树状显示学生信息:
public partial class FrmInfo : Form { public FrmInfo() { InitializeComponent(); } StudentBLL sbl = new StudentBLL(); GradeBLL bll = new GradeBLL(); private void FrmInfo_Load(object sender, EventArgs e) { TreeNode tn= new TreeNode("全部"); DataTable table=bll.GradeInfo(); foreach (DataRow item in table.Rows) { TreeNode node = new TreeNode(); node.Text = item["gradename"].ToString(); TreeNode child = new TreeNode(); child.Text = "男"; child.Tag = "1"; TreeNode childs = new TreeNode(); childs.Text = "女"; childs.Tag = "0"; node.Nodes.Add(child); node.Nodes.Add(childs); //node.Nodes.Add("男"); //node.Nodes.Add("女"); tn.Nodes.Add(node); } tvList.Nodes.Add(tn); } private void tvList_AfterSelect(object sender, TreeViewEventArgs e) { if(tvList.SelectedNode.Level==0) { dgvList.DataSource = sbl.Student(); } else if(tvList.SelectedNode.Level==1) { dgvList.DataSource=sbl.SelectStudent(tvList.SelectedNode.Text); }else if(tvList.SelectedNode.Level == 2) { if(tvList.SelectedNode.Text=="男") { dgvList.DataSource = sbl.SelectStudent(tvList.SelectedNode.Parent.Text, Convert.ToString(tvList.SelectedNode.Tag)); } else { dgvList.DataSource = sbl.SelectStudent(tvList.SelectedNode.Parent.Text, Convert.ToString(tvList.SelectedNode.Tag)); } } } private void 修改ToolStripMenuItem_Click(object sender, EventArgs e) { FrmAdd add = new FrmAdd(); add.Text = "修改学生成绩"; add.no = Convert.ToInt32(dgvList.SelectedRows[0].Cells[0].Value); add.pwd = dgvList.SelectedRows[0].Cells[1].Value.ToString(); add.name = dgvList.SelectedRows[0].Cells[2].Value.ToString(); add.gender = dgvList.SelectedRows[0].Cells[3].Value.ToString(); add.gradename = dgvList.SelectedRows[0].Cells[4].Value.ToString(); add.phone = dgvList.SelectedRows[0].Cells[6].Value.ToString(); add.address = dgvList.SelectedRows[0].Cells[5].Value.ToString(); add.birthday= (dgvList.SelectedRows[0].Cells[7].Value.ToString()); add.email = dgvList.SelectedRows[0].Cells[8].Value.ToString(); add.identitycard = dgvList.SelectedRows[0].Cells[9].Value.ToString(); add.Show(); } private void 取消ToolStripMenuItem_Click(object sender, EventArgs e) { FrmResult fr = new FrmResult(); fr.no = Convert.ToInt32(dgvList.SelectedRows[0].Cells[0].Value); fr.name = dgvList.SelectedRows[0].Cells[2].Value.ToString(); fr.Show(); } }
添加学生成绩窗体:
public partial class FrmResult : Form { public FrmResult() { InitializeComponent(); } GradeBLL bll = new GradeBLL(); SubjectBLL sbl = new SubjectBLL(); ResultBLL rbl = new ResultBLL(); private void FrmResult_Load(object sender, EventArgs e) { cbosubject.ValueMember = "subjectid"; cbosubject.DisplayMember = "subjectname"; cbosubject.DataSource = sbl.SubjectInfo(); txtname.Text = name; } public int no; public string name; private void btnAdd_Click(object sender, EventArgs e) { Result rt = new Result(); rt.StudentNo = no; rt.SubjectId=Convert.ToInt32(cbosubject.SelectedValue); rt.StudentResult = Convert.ToInt32(txtresult.Text); rt.ExamDate = txttime.Text; bool flag=rbl.AddResult(rt); if(flag) { MessageBox.Show("添加成功!"); } } }
ComboBox联动:
public partial class FrmScore : Form { public FrmScore() { InitializeComponent(); } SubjectBLL bll = new SubjectBLL(); GradeBLL gbl = new GradeBLL(); private void cbograde_SelectedIndexChanged(object sender, EventArgs e) { int gradeid = Convert.ToInt32(cbograde.SelectedValue); cbosubject.ValueMember = "subjectid"; cbosubject.DisplayMember = "subjectname"; cbosubject.DataSource = bll.SubjectALL(gradeid); } private void FrmScore_Load(object sender, EventArgs e) { cbograde.ValueMember = "gradeid"; cbograde.DisplayMember = "gradename"; cbograde.DataSource= gbl.GradeInfo(); } }
根据科目和年级查询和修改学生成绩窗体:
public partial class FrmSelectResult : Form { public FrmSelectResult() { InitializeComponent(); } GradeBLL bll = new GradeBLL(); SubjectBLL sbl = new SubjectBLL(); ResultBLL rbl = new ResultBLL(); private void FrmSelectResult_Load(object sender, EventArgs e) { cbo01.ValueMember = "gradeid"; cbo01.DisplayMember = "gradename"; cbo01.DataSource = bll.GradeInfo(); cbo02.ValueMember = "subjectid"; cbo02.DisplayMember = "subjectname"; cbo02.DataSource = sbl.SubjectInfo(); cbo03.ValueMember = "subjectid"; cbo03.DisplayMember = "subjectname"; cbo03.DataSource = sbl.SubjectInfo(); DataTable table = rbl.SelectResult(); dgvList.DataSource = table; } DataTable table; private void button1_Click(object sender, EventArgs e) { if(txt01.Text=="") { table = rbl.SelectResult(Convert.ToInt32(cbo02.SelectedValue)); } else { table = rbl.SelectResult(txt01.Text); } dgvList.DataSource = table; } private void dgvList_DoubleClick(object sender, EventArgs e) { lbl01.Text = dgvList.SelectedRows[0].Cells[0].Value.ToString(); cbo03.Text = dgvList.SelectedRows[0].Cells[1].Value.ToString(); txt02.Text = dgvList.SelectedRows[0].Cells[2].Value.ToString(); txt03.Text = dgvList.SelectedRows[0].Cells[3].Value.ToString(); } private void button2_Click(object sender, EventArgs e) { Result result = new Result(); result.StudentNo = Convert.ToInt32(dgvList.SelectedRows[0].Cells[4].Value); result.StudentResult = Convert.ToInt32(txt02.Text); result.SubjectId = Convert.ToInt32(cbo03.SelectedValue); result.ExamDate = txt03.Text; bool flag= rbl.UpdateResult(result); if(flag) { MessageBox.Show("修改成功"); DataTable table = rbl.SelectResult(); dgvList.DataSource = table; } } private void cbo01_SelectedIndexChanged(object sender, EventArgs e) { int gradeid = Convert.ToInt32(cbo01.SelectedValue); cbo02.ValueMember = "subjectid"; cbo02.DisplayMember = "subjectname"; cbo02.DataSource = sbl.SubjectALL(gradeid); } }
这个需要你自己多练习,现在学习是在学习思想,记得没事多练习.......