今天我们来讲讲分层开发，你从标题能不能简单的认识一下什么是分层呢？
不懂也没关系，接下来我来给你讲讲。
第一章 软件系统的分层开发
（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);
}
}
这个需要你自己多练习，现在学习是在学习思想，记得没事多练习.......