学员信息录入(StuInfoManager) 用分层实现(既MySchool后的一个案例)-白红宇

学员信息录入(StuInfoManager) 用分层实现(既MySchool后的一个案例)

阅读量：5920 次

发布时间：2019-06-19

本文共 13438 字，大约阅读时间需要 44 分钟。

数据库

数据表名	Student		中文表名	学员信息表
字段显示	字段名	数据类型	字段大小	备注和说明
编号	stu_id	int		主键，自增1
学生姓名	stu_name	varchar	50	不允许空
学生性别	stu_sex	varchar	2
学生年龄	stu_age	int
学生邮箱	stu_email	varchar	100

数据库中的数据如表:

stu_id	stu_name	stu_sex	stu_age	stu_email
1	张欣	男	20	zhangxin@163.com
2	王兰	女	21	wanglan@163.com
3	刘亮	男	20	8888888@qq.com
4	陈龙	男	21	7777777@qq.com

由于分层有相互引用关系:分别是:Model实体层不引用任何层,但其他每层都需要引用Model层,UI表示层要引用BLL业务逻辑层,BLL层引用DAL层,UI层不直接引用DAL层,UI层是可以引用DAL层,只是不建议引用,引用的前提是没有BLL层.有Web层,一般个人习惯性从Model层写起,接着写DAL层,再写BLL层,最后写UI层,(也可以倒着写)

了解了UI的需求后;进入我们分层中:其他界面省略实现功能在代码中体现,主要功能有两块录入数据(添加),和模糊查询,如没有实现该功能给出相应提示:

Model层:

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;namespace StuInfoManager.Model{    public class Student    {        private int stu_id;        //编号        public int Stu_id        {            get { return stu_id; }            set { stu_id = value; }        }        private string stu_name;        //学生姓名        public string Stu_name        {            get { return stu_name; }            set { stu_name = value; }        }        private string stu_sex;        //学生性别        public string Stu_sex        {            get { return stu_sex; }            set { stu_sex = value; }        }        private int stu_age;        //学生年龄        public int Stu_age        {            get { return stu_age; }            set { stu_age = value; }        }        private string stu_email;        //学生邮箱        public string Stu_email        {            get { return stu_email; }            set { stu_email = value; }        }    }}

DAL层:

引入了个SQLHelper类:

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SqlClient;using System.Data;using System.Configuration;namespace StuInfoManager.DAL{  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;      ///       /// 执行NonQuery命令      /// 
      ///       ///       /// 
           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;          }      }          ///       /// ExecuteScalar      /// 
      /// 第一个参数，SQLServer语句      /// 第二个参数，传递0个或者多个参数      /// 
           public static object ExecuteScalar(string cmdTxt, params SqlParameter[] parames)      {          return ExecuteScalar(cmdTxt, CommandType.Text, parames);      }      //可使用存储过程的ExecuteScalar      public static object ExecuteScalar(string cmdTxt, CommandType cmdtype, params SqlParameter[] parames)      {          if (string.IsNullOrEmpty(cmdTxt))          {              return null;          }          using (SqlConnection con = new SqlConnection(Constr))          {              using (SqlCommand cmd = new SqlCommand(cmdTxt, con))              {                  cmd.CommandType = cmdtype;                  if (parames != null)                  {                      cmd.Parameters.AddRange(parames);                  }                  con.Open();                return   cmd.ExecuteScalar();              }          }                }      //调用存储过程的DBHelper类（关于ExeceutScalar,包含事务,只能处理Int类型，返回错误号）      public static object ExecuteScalar(string cmdTxt, CommandType cmdtype,SqlTransaction sqltran, params SqlParameter[] parames)      {           if (string.IsNullOrEmpty(cmdTxt))          {              return 0;          }          using (SqlConnection con = new SqlConnection(Constr))          {              int sum = 0;              using (SqlCommand cmd = new SqlCommand(cmdTxt, con))              {                  cmd.CommandType=cmdtype;                  if (parames != null)                  {                      cmd.Parameters.AddRange(parames);                  }                  con.Open();                  sqltran = con.BeginTransaction();                  try                  {                      cmd.Transaction = sqltran;                      sum=Convert.ToInt32( cmd.ExecuteScalar());                      sqltran.Commit();                  }                  catch (SqlException ex)                  {                      sqltran.Rollback();                  }                  return sum;              }          }      }    }}

StudentDAL类:

using StuInfoManager.Model;using System;using System.Collections.Generic;using System.Data;using System.Data.SqlClient;using System.Linq;using System.Text;using System.Threading.Tasks;namespace StuInfoManager.DAL{    public class StudentDAL    {        //读取所有学生        public DataTable SelectStudent()        {            string str = "Data Source=.;Initial Catalog=StudentDB;uid=sa";            SqlConnection con = new SqlConnection(str);            string sql = "select * from Student";//查询Student            SqlDataAdapter da = new SqlDataAdapter(sql, con);            DataSet ds = new DataSet();            try            {                da.Fill(ds, "stuInfo");            }                       catch (SqlException e)            {                throw new Exception("数据连接异常!");            }            catch (Exception)            {                throw new Exception("数据转换异常!");            }            return ds.Tables["stuInfo"];        }        public bool AddStudent(Student stu)//添加学生信息        {            bool flag = false;//用@XXX,占位,实现添加            string sql = "insert into Student values(@name,@sex,@age,@email)";            SqlParameter[] para = {                                     new SqlParameter("@name",stu.Stu_name),                                     new SqlParameter("@sex",stu.Stu_sex),                                     new SqlParameter("@age",stu.Stu_age),                                     new SqlParameter("@email",stu.Stu_email)                                                                     };            int count = SQLHelper.ExecuteNonQuery(sql, CommandType.Text, para);            if (count > 0)            {                flag = true;            }            return flag;        }        public DataTable InquiryStudent(Student name)//模糊查询        {                              string sql = "select * from student where stu_name like '%'+@name+'%'";//按姓名查询            SqlParameter para = new SqlParameter("@name",name.Stu_name);            DataTable dt = SQLHelper.ExecuteDataTable(sql,para);            return dt;                             }            }}

BLL层:

StudentBLL类:

using StuInfoManager.DAL;using StuInfoManager.Model;using System;using System.Collections.Generic;using System.Data;using System.Linq;using System.Text;using System.Threading.Tasks;namespace StuInfoManager.BLL{    public class StudentBLL    {        StudentDAL studentdal = new StudentDAL();//BLL层引用DAL层        //读取所有学生        public DataTable SelectStudent()        {            return studentdal.SelectStudent();        }        public bool AddStudent(Student stu)//添加学生信息        {            return studentdal.AddStudent(stu);        }        public DataTable InquiryStudent(Student name)//按姓名查询        {            return studentdal.InquiryStudent(name);        }    }}

UI层:

(补充内容App.config)

App.config是XML文件,

App.config中要用到法二,在SQLHelper类中改如下代码,也要选择法二,实现功能一样

//微软提供了一定的方案,读取App.config中对应节点的内容        //法一:        public static string Constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;        //法二;        //public static string Constr = ConfigurationManager.AppSettings["constr"].ToString();        //用静态的方法调用的时候不用创建SQLHelper的实例        //Execetenonquery        // public static string Constr = "server=HAPPYPIG\\SQLMODEL;database=shooltest;uid=sa;pwd=6375196;";      //  public static string Constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

UI层各个功能块的代码如下:

using StuInfoManager.BLL;using StuInfoManager.Model;using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Data.SqlClient;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;namespace 学员信息录入{    public partial class StuInfoManager : Form    {        public StuInfoManager()        {            InitializeComponent();        }        StudentBLL studentbll = new StudentBLL();//表示层UI引用业务逻辑层        private void button1_Click(object sender, EventArgs e)        {                       //判断录入信息是否为空            if (txtName.Text.Trim()==""||txtAge.Text.Trim()==""||txtEmail.Text.Trim() == "")            {                MessageBox.Show("录入信息不能为空!");            }            else            {                Student student = new Student();                student.Stu_name = txtName.Text;                student.Stu_age = Convert.ToInt32(txtAge.Text);                student.Stu_sex = cmbSex.Text;                student.Stu_email = txtEmail.Text;                bool result = studentbll.AddStudent(student);                if (result)                {                    MessageBox.Show("学员信息录入成功！", "操作提示", MessageBoxButtons.OK);                }                else                {                    MessageBox.Show("学员信息录入失败！", "操作提示", MessageBoxButtons.OK);                }            }        }        private void StuInfoManager_Load(object sender, EventArgs e)//主窗体        {            cmbSex.SelectedIndex = 0;//下拉框的绑定            StudentBLL studentbll = new StudentBLL();//表示层UI引用业务逻辑层BLL                       try            {                DataTable dt = studentbll.SelectStudent();//获取学生的方法                dgvList.DataSource = dt;//绑定数据            }            catch (Exception ex)            {                throw new Exception(ex.Message);            }        }        private void butInquiry_Click(object sender, EventArgs e)//查询        {            string name = txtNames.Text;            Student stu = new Student();            stu.Stu_name = name;            DataTable result = studentbll.InquiryStudent(stu);            dgvList.DataSource = result;            #region 法二(没有用到分层)实现功能一样            ////1.1  连接字符串            //string str = "data source=.;initial catalog=StudentDB;uid=sa;";            ////1.2 创建连接对象                //SqlConnection con = new SqlConnection(str);            //SqlCommand cmd = con.CreateCommand();            //cmd.CommandText = "select * from student where stu_name like '%'+@name+'%'";            //cmd.CommandType = CommandType.Text;            //SqlParameter para = new SqlParameter("@name", txtNames.Text);            //cmd.Parameters.Add(para);            //SqlDataAdapter da = new SqlDataAdapter();            //da.SelectCommand = cmd;            //DataSet ds = new DataSet();            //da.Fill(ds, "Info");            //dgvList.DataSource = ds.Tables["Info"];                       #endregion        }    }}

上述代码属于个人所写,如有转载,需经本人同意,谢谢,

转载于:https://www.cnblogs.com/WuXuanKun/p/5516282.html

你可能感兴趣的文章