• C#深入.NET平台的软件系统分层开发


     今天我们来讲讲分层开发,你从标题能不能简单的认识一下什么是分层呢?

    不懂也没关系,接下来我来给你讲讲。

    第一章 软件系统的分层开发

    (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);
            }
        }

    这个需要你自己多练习,现在学习是在学习思想,记得没事多练习.......

  • 相关阅读:
    闲谈系列之一——数据库主键GUID
    一个简单通用权限管理系统,求各位帮忙看看
    php 计算指定年份的周总数与及第几周的开始日期和结束日期(从周一开始)
    创建虚拟机流程详细过程链接
    阿里云CDN加速设置
    sublime Text3 快捷键
    Linux命令(centos7)
    分布式数据库
    mysql 分区和分表
    Linux crontab 命令格式与详细例子
  • 原文地址:https://www.cnblogs.com/wl0000-03/p/5941566.html
Copyright © 2020-2023  润新知