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

数据库

数据表名	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;
      /// <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;
              }
          }
      }
    }
}

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文件,

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <!---添加一个特定的节点-->
  <!--//法一:-->
  <connectionStrings>
    <add name="constr" connectionString="Data Source=.;Initial catalog=MySchool;uid=sa"/>
  </connectionStrings>
  <!--//法二;
  <appSettings>
      <add key="constr" value ="Data Source=.;Initial catalog=MySchool;uid=sa"/>
      </appSettings>
  -->  
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup> 
</configuration>

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
        }
    }
}

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