分层 MySchool

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySchool.DAL;


namespace MySchool.BLL
{
   public class GradeBLL
    { 
       GradeDAL grade=new GradeDAL();
        //根据年纪名称获取年纪编号
       public int GetIdByName(string gradename)
       {
          return grade.GetIdByName(gradename);


       }
               //获取所有年纪信息
       public  DataTable FindAllGrade()
       {
           return grade.FindAllGrade();
       }
        //显示所有学生信息
       public DataTable chuxun()
       {
           return grade.chuxun();
       }
         //年级查询
       public DataTable Chaxun1(string name)
       {
          return grade.Chaxun1(name);
       }
        //姓名查询
       public DataTable chaxun2(string name)
       {
           return grade.chaxun2(name);
       }
    }
}



using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySchool.DAL;
using MySchool.Model;

namespace MySchool.BLL
{
    public class StudentBLL
    {
        private StudentDAL dal = new StudentDAL();

        public bool IsLongin(string username, string pwd)
        {
            //登陆

            return dal.IsLongin(username, pwd);

        }

        //添加学生
        public bool AddStudent(string LoginPwd, string StudentName, string Gender, int GradeId, string Phone,
            string Address, DateTime Birthday, string Email)
        {
            return dal.AddStudent(LoginPwd, StudentName, Gender, GradeId, Phone, Address, Birthday, Email);
        }

        //删除
        public int shanchu(string shu)
        {
            return dal.shanchu(shu);
        }

        //查询
        public DataTable chuxnun3(string name)
        {
            return dal.chuxnun3(name);
        }

        //修改密码
        public bool xiugaimima(Student stu)
        {
            return dal.xiugaimima(stu);
        }

        //带参
        public DataTable get(string name, int gid, out int i)
        {         
            return dal.get(name,gid, out i);
        }
    }
}
    

using SIM.DAL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MySchool.DAL
{
    public class GradeDAL
    {
        //根据年纪名称获取年纪编号
        public int GetIdByNameAgo(string gradename)
        {
            string str = SqlHelper.Constr;
            SqlConnection con = new SqlConnection(str);
            string sql = "select gradeid from grade where gradename='" + gradename + "'";
            SqlCommand cmd = new SqlCommand(sql, con);
            con.Open();
            int gradeid = Convert.ToInt32(cmd.ExecuteScalar());
            con.Close();
            return gradeid;
        }

        public int GetIdByName(string gradename)
        {
            
            string sql = "select gradeid from grade where gradename='" + gradename + "'";
          
            int gradeid = Convert.ToInt32(SqlHelper.ExecuteScalar(sql));
          
            return gradeid;
        }

        //获取所有年纪信息
        public  DataTable FindAllGradeAgo()
        {
            string str = SqlHelper.Constr;
            SqlConnection con = new SqlConnection(str);
            string sql = "select gradeid,gradename from grade";
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            da.Fill(ds, "GradeInfo");
            return ds.Tables["GradeInfo"];
        }

        public  DataTable FindAllGrade()
        {
          
            string sql = "select gradeid,gradename from grade";
           return SqlHelper.ExecuteDataTable(sql);
            

        }

        //显示所有学生信息
        public DataTable chuxun()
        {
            string str = SqlHelper.Constr;
            SqlConnection con = new SqlConnection(str);
            string sql = @"select [StudentNo],[LoginPwd],[StudentName],[Gender],Grade.[GradeId],[GradeName],phone
                         from student,grade
                         where student.GradeId=Grade.GradeId";
            SqlDataAdapter sa = new SqlDataAdapter(sql, con);
            DataSet ds = new DataSet();
            sa.Fill(ds, "info");
           
            return ds.Tables["info"];
        }

        public DataTable chuxun1()
        {
           
            string sql = @"select [StudentNo],[LoginPwd],[StudentName],[Gender],Grade.[GradeId],[GradeName],phone
                         from student,grade
                         where student.GradeId=Grade.GradeId";
           return SqlHelper.ExecuteDataTable(sql);

        }
        //年级查询
        public DataTable Chaxun1Ago(string name)
        {
            string str = SqlHelper.Constr;
            SqlConnection con = new SqlConnection(str);
            con.Open();
            string sql = "select studentno,studentname,gender,birthday from student,grade where student.gradeid=grade.gradeid and grade.gradeid in (select gradeid from grade where gradename='" + name + "')";
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sql, con);
            da.Fill(ds, "Studentlist");
            return ds.Tables["Studentlist"];
        }

        public DataTable Chaxun1(string name)
        {
       
            string sql = "select studentno,studentname,gender,birthday from student,grade where student.gradeid=grade.gradeid and grade.gradeid in (select gradeid from grade where gradename='" + name + "')";
            return SqlHelper.ExecuteDataTable(sql);
        }

        //姓名查询
        public DataTable chaxun2(string name)
        {
            string str = SqlHelper.Constr;
            SqlConnection con=new SqlConnection(str);
            con.Open();
            string sql = @"select  *  from Student,grade  where student.gradeid=grade.gradeid
                            and studentName like'%" + name + "%'";

            DataSet ds=new DataSet();
            SqlDataAdapter da=new SqlDataAdapter(sql,con);
            da.Fill(ds, "info");
            return ds.Tables["info"];

        }

        public DataTable chaxun2Ago(string name)
        {
           
            string sql = @"select  *  from Student,grade  where student.gradeid=grade.gradeid
                            and studentName like'%" + name + "%'";
            return SqlHelper.ExecuteDataTable(sql);
        }
    
    }
 

    
}


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

using System.Configuration;
namespace SIM.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;
              }
          }
      }
    }
}


using SIM.DAL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using  MySchool.Model;
namespace MySchool.DAL
{
    //数据访问层
    public class StudentDAL
{

    
        //添加学生
        public bool AddStudentAgo(string LoginPwd,string StudentName,string Gender,int GradeId, string   Phone, string   Address, DateTime   Birthday, string   Email )
        {
            bool flage = false;
            string str = SqlHelper.Constr;
            SqlConnection con = new SqlConnection(str);
            string sql = "insert into [dbo].[Student](LoginPwd, StudentName, Gender, GradeId, Phone, Address, Birthday, Email)values('" + LoginPwd + "','" + StudentName + "','" + Gender + "','" + GradeId + "','" + Phone + "','" + Address + "','" + DateTime.Now.ToShortDateString() + "','" + Email + "') ";
            SqlCommand cmd=new SqlCommand(sql,con);
            con.Open();
            int count = cmd.ExecuteNonQuery();
            con.Close();
            if (count>0)
            {
                flage = true;
            }
            return flage;
        }

        public bool AddStudent(string LoginPwd, string StudentName, string Gender, int GradeId, string Phone,
            string Address, DateTime Birthday, string Email)
        {
            bool flage = false;
          
            string sql =
                "insert into [dbo].[Student](LoginPwd, StudentName, Gender, GradeId, Phone, Address, Birthday, Email)values('" +
                LoginPwd + "','" + StudentName + "','" + Gender + "','" + GradeId + "','" + Phone + "','" + Address +
                "','" + DateTime.Now.ToShortDateString() + "','" + Email + "') ";
        
            int count = SqlHelper.ExecuteNonQuery(sql);
            
            if (count > 0)
            {
                flage = true;
            }
            return flage;


        }




       
        public bool IsLonginAgo(string username, string pwd)
        {
            bool flage = false;
      
            string str = SqlHelper.Constr;
            SqlConnection con = new SqlConnection(str);
            string sql = "select count(1) from student where studentname='"+username+"'and loginpwd='"+pwd+"'";
            SqlCommand com  = new SqlCommand(sql,con);
            con.Open();
            int i = Convert.ToInt32(com.ExecuteScalar());
            if (i>0)
            {
                
                flage = true;
            }
            con.Close();
            return flage;
        }
        //登陆方法
        public bool IsLongin(string username, string pwd)
        {
            bool flage = false;
          
            string sql = "select count(1) from student where studentname='" + username + "'and loginpwd='" + pwd + "'";
         
            int i = Convert.ToInt32(SqlHelper.ExecuteScalar(sql));
            if (i > 0)
            {
                flage = true;
            }        
            return flage;
        }

    //修改密码
        public bool xiugaimima(Student stu)
        {

            //string str = SqlHelper.Constr;
            //SqlConnection con=new SqlConnection(str);

            string sql = "update student set loginpwd='" + stu.LoginPwd+ "'where studentname='" + stu.StudentName + "'";
            //SqlCommand cmd=new SqlCommand(sql,con);
            //con.Open();
            int i = SqlHelper.ExecuteNonQuery(sql);
            if (i > 0)
            {
                return true;
            }
            else
            {

                return false;
            }
        }

             //删除
            public int shanchu(string shu)
            {
                //bool flag = false;
                //string str = SqlHelper.Constr;
                //SqlConnection con=new SqlConnection(str);
                string sql = "delete from student where studentno='"+shu+"'";
                //con.Open();
                //SqlCommand com=new SqlCommand(sql,con);
                
                //con.Close();
                int i = SqlHelper.ExecuteNonQuery(sql);
                return i;

            }    
      
        
        
        
        public DataTable chuxnun3(string name)
         
{
            String str = SqlHelper.Constr;
            SqlConnection con=new SqlConnection(str);
            string sql = "select * from student where studentname=@name";
            SqlParameter para=new SqlParameter("@name",name);
            SqlCommand cmd=new SqlCommand(sql,con);
            cmd.Parameters.Add(para);
            SqlDataAdapter da=new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds=new DataSet();
            da.Fill(ds,"stuinfo]") ;
            return ds.Tables["stuinfo"];




}
        //带参
        public DataTable get(string name,int gid,out int i)
        {
            string str =SqlHelper.Constr;
            SqlConnection con=new SqlConnection(str);
            string sql = "usp_0139";
            SqlCommand cmd=new SqlCommand(sql,con);
            SqlParameter[] paras =
            {
                new SqlParameter("@gender", name),
                new SqlParameter("@gradeid", gid),
                new SqlParameter("@countnum", SqlDbType.Int),
                new SqlParameter("@myreturn",SqlDbType.Int), 
                
            };
            paras[2].Direction = ParameterDirection.Output;
            paras[3].Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.AddRange(paras);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da=new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds=new DataSet();
            da.Fill(ds,"info");
            i = Convert.ToInt32(paras[2].Value);
            int myreturn = Convert.ToInt32(paras[3].Value);
            Console.WriteLine(myreturn);
            Console.WriteLine(i);
            return ds.Tables["info"];


        }

}

}

    

    
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MySchool.Model
{
   public class Student
    {
       public Student()
       {

       }

       private int _studentno;
        private string _loginpwd;
        private string _studentname;
        private string _gender;
        private int _gradeid = 1;
        private string _phone;
        private string _address;
        private DateTime? _birthday;
        private string _email;
        private int? _mytt;
        /// <summary>
        /// 
        /// </summary>
        public int StudentNo
        {
            set { _studentno = value; }
            get { return _studentno; }
        }
        /// <summary>
        /// 
        /// </summary>
        public string LoginPwd
        {
            set { _loginpwd = value; }
            get { return _loginpwd; }
        }
        /// <summary>
        /// 
        /// </summary>
        public string StudentName
        {
            set { _studentname = value; }
            get { return _studentname; }
        }
        /// <summary>
        /// 
        /// </summary>
        public string Gender
        {
            set { _gender = value; }
            get { return _gender; }
        }
        /// <summary>
        /// 
        /// </summary>
        public int GradeId
        {
            set { _gradeid = value; }
            get { return _gradeid; }
        }
        /// <summary>
        /// 
        /// </summary>
        public string Phone
        {
            set { _phone = value; }
            get { return _phone; }
        }
        /// <summary>
        /// 
        /// </summary>
        public string Address
        {
            set { _address = value; }
            get { return _address; }
        }
        /// <summary>
        /// 
        /// </summary>
        public DateTime? Birthday
        {
            set { _birthday = value; }
            get { return _birthday; }
        }
        /// <summary>
        /// 
        /// </summary>
        public string Email
        {
            set { _email = value; }
            get { return _email; }
        }
        /// <summary>
        /// 
        /// </summary>
        public int? MyTT
        {
            set { _mytt = value; }
            get { return _mytt; }
        }
    }
}



using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace MySchool.UI
{
    static class Program
    {
        /// <summary>
        /// 应用程序的主入口点。
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1() );
        }
    }
}