WinFrom06

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

namespace Student.BLL
{
    public class StudentBLL
    {
        StudentDAL studentsDal = new StudentDAL();
        /// <summary>
        /// 保存数据
        /// </summary>
        /// <param name="student"></param>
        /// <returns></returns>
        public int SaveStudents(StudentModal student)
        {
            SqlParameter[] paras = null;
            if (student.Sid == Guid.Empty)
            {
                paras = new SqlParameter[6];
                paras[0] = new SqlParameter("@sname", student.Sname);
                paras[1] = new SqlParameter("@sage", student.Sage);
                paras[2] = new SqlParameter("@saddress", student.Saddress);
                paras[3] = new SqlParameter("@ssex", student.Ssex);
                paras[4] = new SqlParameter("@gid", student.Gid);
                paras[5] = new SqlParameter("@sid", student.Sid);
                return studentsDal.AddStudents(paras);

            }
            paras = new SqlParameter[5];
            paras[0] = new SqlParameter("@sname", student.Sname);
            paras[1] = new SqlParameter("@sage", student.Sage);
            paras[2] = new SqlParameter("@saddress", student.Saddress);
            paras[3] = new SqlParameter("@ssex", student.Ssex);
            paras[4] = new SqlParameter("@gid", student.Gid);
            return studentsDal.EditStudents(paras);
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="student"></param>
        /// <returns></returns>
        public int RemoveStudents(StudentModal student)
        {
            SqlParameter[] paras = new SqlParameter[1];
            paras[0] = new SqlParameter("@sid", student.Sid);
            return studentsDal.RemoveStudents(paras);
        }
        /// <summary>
        /// 查找数据并且分页
        /// </summary>
        /// <returns></returns>
        public DataTable FindStudentsAllBySnamePage(StudentModal student, int page, int rows)
        {
            SqlParameter[] paras = null;
            if (student != null)
            {
                paras = new SqlParameter[1];
                paras[0] = new SqlParameter("@sname", "%"+student.Sname+"%");
            }
            return studentsDal.FindStudentsAllBySnamePage(paras, page, rows);
        }

        /// <summary>
        /// 得到最大页码数
        /// </summary>
        /// <param name="student"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public int FindStudentsMaxPage(StudentModal student, int rows)
        {
            SqlParameter[] paras = null;
            if (student != null)
            {
                paras = new SqlParameter[1];
                paras[0] = new SqlParameter("@sname", "%"+student.Sname+"%");
            }
            return studentsDal.FindStudentsMaxPage(paras, rows);
        }
    }
}

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Student.Modal;
using System.Data.SqlClient;

namespace Student.DAL
{
    /// <summary>
    /// 学生操作数据库的类
    /// </summary>
    public class StudentDAL
    {
        //当前类中的全局变量
        DBHeple db = new DBHeple();
        /// <summary>
        /// 增加
        /// </summary>
        /// <param name="paras"></param>
        /// <returns></returns>

        public int AddStudents(SqlParameter[] paras)
        {
            return db.ExecuteNonQuery("insert  into  t_students values(NEWID(),@sname,@sage,@saddress,@ssex,@gid)", paras);
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="paras"></param>
        /// <returns></returns>
        public int EditStudents(SqlParameter[] paras)
        {
            return db.ExecuteNonQuery("update t_students  set sname=@sname,sage=@sage,saddress=@saddress,ssex=@ssex,gid=@gid where sid=@sid", paras);
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="paras"></param>
        /// <returns></returns>
        public int RemoveStudents(SqlParameter[] paras)
        {
            return db.ExecuteNonQuery("delete from t_students where sid=@sid", paras);
        }
        /// <summary>
        /// 查找数据并且分页
        /// </summary>
        /// <param name="paras"></param>
        /// <returns></returns>
        public DataTable FindStudentsAllBySnamePage(SqlParameter[] paras,int page,int rows)
        {
            StringBuilder sb = new StringBuilder();
            int start = ((page - 1) * rows) + 1;
            int end = page * rows;
            sb.Append(" select   * from ( ");
            sb.Append(" select * ,ROW_NUMBER() over(order by sname desc) as 'rid' from ");
            sb.Append(" t_students ");
            if (paras != null) {
                sb.Append(" where  sname  like @sname");
            }
            sb.Append(" )  temp where  rid between "+start);
            sb.Append(" and " + end);
            return db.ExecuteDataTable(sb.ToString(), paras);
        }
        /// <summary>
        /// 得到最大页码数
        /// </summary>
        /// <param name="paras"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public int FindStudentsMaxPage(SqlParameter[] paras,int rows) {
            string sql = "select  count(sid) from t_students";
            if (paras != null)
            {
                sql += " where sname like @sname";
            }
            int maxRows =int.Parse(db.ExecuteScalar(sql, paras).ToString());
            return maxRows % rows == 0 ? maxRows / rows : (maxRows / rows) + 1;
        }
    }
}

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

namespace Student.Modal
{
    public class StudentModal
    {
        private Guid sid;
        private string sname;
        private int sage;
        private string saddress;
        private string ssex;
        private Guid gid;
        public StudentModal()
        {

        }
        public StudentModal(string sname, int sage, string saddress, string ssex, Guid gid)
        {
            this.sname = sname;
            this.sage = sage;
            this.saddress = saddress;
            this.ssex = ssex;
            this.gid = gid;
        }

        public Guid Sid
        {
            get
            {
                return sid;
            }

            set
            {
                sid = value;
            }
        }

        public string Sname
        {
            get
            {
                return sname;
            }

            set
            {
                sname = value;
            }
        }

        public int Sage
        {
            get
            {
                return sage;
            }

            set
            {
                sage = value;
            }
        }

        public string Saddress
        {
            get
            {
                return saddress;
            }

            set
            {
                saddress = value;
            }
        }

        public string Ssex
        {
            get
            {
                return ssex;
            }

            set
            {
                ssex = value;
            }
        }

        public Guid Gid
        {
            get
            {
                return gid;
            }

            set
            {
                gid = value;
            }
        }
    }
}

using Student.BLL;
using Student.Modal;
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 Winfrom06
{
    public partial class Frm_StudentsTable : Form
    {
        StudentBLL studentBll = new StudentBLL();
        int page = 1;
        int rows = 10;
        int maxpage = 0;
        public Frm_StudentsTable()
        {
            InitializeComponent();
        }

        private void Frm_StudentsTable_Load(object sender, EventArgs e)
        {
            DataGridViewDataBind(1, 10);
            cmb_Rows.SelectedIndex = 0;
            
        }

        private void btn_Add_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < 200; i++)
            {
                StudentModal sm = new StudentModal("增加的学生" + i, i, "增加学生的地址" + i, i % 2 == 0 ? "男" : "女", Guid.Parse("d2a8df58-9e58-43a9-8ad6-9549a8ef5c7b"));
                studentBll.SaveStudents(sm);
            }
        }

        private void btn_Query_Click(object sender, EventArgs e)
        {
            DataGridViewDataBind(1, 10);
        }

        private void btn_Remove_Click(object sender, EventArgs e)
        {
           
        }


        #region  自定义方法
        /// <summary>
        /// 数据绑定并且分页
        /// </summary>
        /// <param name="page">当前第几页</param>
        /// <param name="rows">每页显示多少行</param>
        public void DataGridViewDataBind(int page, int rows)
        {
            StudentModal studetModal = null;
            if (txt_Query.Text.Trim().Length > 0)
            {
                studetModal = new StudentModal();
                studetModal.Sname = txt_Query.Text.Trim();
            }
            dgv_Students.DataSource = studentBll.FindStudentsAllBySnamePage(studetModal, page, rows);
            maxpage = studentBll.FindStudentsMaxPage(studetModal, rows);
            ShowPageMessAge();
        }

        /// <summary>
        /// 显示分页信息
        /// </summary>
        public void ShowPageMessAge() {
            lab_ShowPage.Text = page + "/" + maxpage;
        }
        #endregion

        private void btn_FirstPage_Click(object sender, EventArgs e)
        {
            page = 1;
            rows = 10;
            DataGridViewDataBind(page, rows);
        }

        private void btn_UpPage_Click(object sender, EventArgs e)
        {
            page = page > 1 ? page - 1 : 1;
            DataGridViewDataBind(page, rows);
        }

        private void btn_NextPage_Click(object sender, EventArgs e)
        {
            page = page == maxpage ? maxpage : page + 1;
            DataGridViewDataBind(page, rows);
        }

        private void btn_LastPage_Click(object sender, EventArgs e)
        {
            page = maxpage;
            DataGridViewDataBind(page, rows);
        }

        private void cmb_Rows_SelectedIndexChanged(object sender, EventArgs e)
        {
            rows = int.Parse(cmb_Rows.Text.Trim());
            page = 1;
            DataGridViewDataBind(page, rows);
        }

        
    }
}

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值