当前位置：首页 > 编程语言 > CSwinform程序分页

CSwinform程序分页

2021-01-13 23:13

阅读：500

标签：cte   page   final   btn   eve   exec   mamicode   add   rds   

技术图片

 

 

/// 
    /// 通用数据分页类
    /// 
    public class SqlDataPager
    {
        #region 一般属性

        /// 
        /// 每页显示的条数
        /// 
        public int PageSize { get; set; }
        /// 
        /// 需要显示的字段（以逗号分隔 ）
        /// 
        public string FiledName { get; set; }
        /// 
        /// 表的名称
        /// 
        public string TableName { get; set; }
        /// 
        /// 查询条件
        /// 
        public string Condition { get; set; }
        /// 
        /// 表的主键或唯一键
        /// 
        public string PrimaryKey { get; set; }
        /// 
        /// 当前页码
        /// 
        public int CurrentPage { get; set; }
        /// 
        /// 排序条件
        /// 
        public string Sort { get; set; }

        #endregion

        #region 只读属性

        /// 
        /// 记录的总数【不能直接赋值】
        /// 
        //  public int RecordCount { get; set; }//此中手设计不安全
        private int recordCount;
        public int RecordCount//设置只读属性，外面不能直接赋值
        {
            get { return recordCount; }
        }
        /// 
        /// 总页数
        /// 
        public int TotalPages
        {
            get
            {
                if (recordCount != 0)//如果查询记录总数不为0
                {
                    if (recordCount % PageSize != 0)
                    {
                        return recordCount / PageSize + 1;
                    }
                    else
                    {
                        return recordCount / PageSize;
                    }
                }
                else
                {
                    this.CurrentPage = 1;//如果查询没有数据，则当前页码需要复位
                    return 0;
                }
            }
        }
        #endregion

        //分页查询方法所用的SQL语句
        private string GetPagedSQL()
        {
            //计算需要过滤的总数
            string filterCount = (PageSize * (CurrentPage - 1)).ToString();
            //组合SQL语句
            string sql = "select  Top {0} {1} from {2}  where {3}  and {4} not in  ";
            sql += "(select Top {5}  {6} from {7} where {8} order by {9} ) order by {10};";
            sql += "select count(*) from {11} where {12}";
            sql = string.Format(sql, PageSize, FiledName, TableName, Condition,
                PrimaryKey, filterCount, PrimaryKey, TableName, Condition, Sort, Sort, TableName, Condition);
            return sql;
        }
        /// 
        /// 执行分页查询，返回DataTable
        /// 
        /// 
        public DataTable GetPagedData()
        {
            //【1】执行查询，返回分页后的结果集
            DataSet ds = SQLHelper.GetDataSet(GetPagedSQL());
            //【2】获取满足记录的总数
            this.recordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            return ds.Tables[0];
        }
    }

　　

 public partial class FrmDataPager : Form
    {
        private SqlDataPager objPager = null;
        public FrmDataPager()
        {
            InitializeComponent();
            this.dtpBirthday.Text = "1988-1-1";

            //初始化数据分页对象
            objPager = new SqlDataPager()
            {
                PrimaryKey = "StudentId",//表的主键
                TableName = "Students",
                FiledName = "StudentId,StudentName,Gender,Birthday,PhoneNumber",
                CurrentPage = 1,
                Sort = "StudentId ASC"//排序条件
            };

            //设置默认的显示条数
            this.cboRecordList.SelectedIndex = 1;
            this.dgvStudentList.AutoGenerateColumns = false;

            //禁用相关按钮
            this.btnFirst.Enabled = false;
            this.btnNext.Enabled = false;
            this.btnPre.Enabled = false;
            this.btnLast.Enabled = false;
            this.btnToPage.Enabled = false;
        }
        //执行查询的公共方法
        private void Query()
        {
            //开启所有按钮
            this.btnFirst.Enabled = true;
            this.btnNext.Enabled = true;
            this.btnPre.Enabled = true;
            this.btnLast.Enabled = true;
            this.btnToPage.Enabled = true;

            //【1】设置分页查询的条件
            objPager.Condition = string.Format("Birthday>‘{0}‘", this.dtpBirthday.Text);
            //【2】设置每页显示的条数
            objPager.PageSize = Convert.ToInt32(this.cboRecordList.Text.Trim());
            //【3】执行查询
            this.dgvStudentList.DataSource = objPager.GetPagedData();
            //【4】显示记录总数、显示总页数、显示当前页码
            this.lblRecordsCount.Text =objPager.RecordCount.ToString ();
            this.lblPageCount.Text = objPager.TotalPages.ToString();
            if (this.lblPageCount.Text == "0")
            {
                this.lblCurrentPage.Text = "0";
            }
            else
            {
                this.lblCurrentPage.Text = objPager.CurrentPage.ToString();
            }

            //禁用按钮的情况
            if (this.lblPageCount.Text == "0" || this.lblPageCount.Text == "1")
            {
                this.btnFirst.Enabled = false;
                this.btnNext.Enabled = false;
                this.btnPre.Enabled = false;
                this.btnLast.Enabled = false;
                this.btnToPage.Enabled = false;
            }
            else
            {
                this.btnToPage.Enabled = true;
            }
        }


        //提交查询
        private void btnQuery_Click(object sender, EventArgs e)
        {
            objPager.CurrentPage = 1;//每次执行查询都要设置为第1页
            Query();
            this.btnPre.Enabled = false;
            this.btnFirst.Enabled = false;
        }
        //第1页
        private void btnFirst_Click(object sender, EventArgs e)
        {
            objPager.CurrentPage = 1;
            Query();
            this.btnPre.Enabled = false;
            this.btnFirst.Enabled = false;
           // btnQuery_Click(null, null);//可以直接调用上面的事件
        }
        //下一页
        private void btnNext_Click(object sender, EventArgs e)
        {
            objPager.CurrentPage += 1;
            Query();
            //当执行到最后一页的时候应该禁用最后一页和下一页的按钮
            if (objPager.CurrentPage == objPager.TotalPages)
            {
                this.btnNext.Enabled = false;
                this.btnLast.Enabled = false;
            }
        }
        //上一页
        private void btnPre_Click(object sender, EventArgs e)
        {
            objPager.CurrentPage -= 1;
            Query();
            if (objPager.CurrentPage == 1)
            {
                this.btnPre.Enabled = false;
                this.btnFirst.Enabled = false;
            }
        }
        //最后一页
        private void btnLast_Click(object sender, EventArgs e)
        {
            objPager.CurrentPage = objPager.TotalPages;
            Query();
            this.btnNext.Enabled = false;
            this.btnLast.Enabled = false;
        }
        //跳转到
        private void btnToPage_Click(object sender, EventArgs e)
        {
            if (this.txtToPage.Text.Trim().Length == 0)
            {
                MessageBox.Show("请输入要跳转的页码！","信息提示");
                this.txtToPage.Focus();
                return;
            }
            //使用正则表达式验证必须为大于0的正整数...
            int toPage = Convert.ToInt32(this.txtToPage.Text.Trim());
            if (toPage > objPager.TotalPages)
            {
                MessageBox.Show("跳转的页数不能大于数据总页数！","信息提示");
                this.txtToPage.Focus();
                this.txtToPage.SelectAll();
                return;
            }
            //开始跳转页数
            objPager.CurrentPage = toPage;
            Query();
            if (objPager.CurrentPage == 1)
            {
                this.btnPre.Enabled = false;
                this.btnFirst.Enabled = false;
            }
            else if (objPager.CurrentPage == objPager.TotalPages)
            {
                this.btnNext.Enabled = false;
                this.btnLast.Enabled = false;
            }
        }
        //关闭窗口
        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}

　　带参数的分装：

 public class SqlDataPager
    {
        public SqlDataPager() { }
        /// 
        /// 每页显示的条数
        /// 
        public int PageSize { get; set; }
        /// 
        /// 总页数
        ///       
        public int TotalPages
        {
            get
            {
                if (RecordsCount != 0)
                {
                    if (RecordsCount % PageSize != 0)
                        return RecordsCount / PageSize + 1;
                    else
                        return RecordsCount / PageSize;
                }
                else
                {
                    this.CurrentPageIndex = 1;//设置默认页
                    return 0;
                }
            }
        }
        /// 
        /// 记录总数
        /// 
        public int RecordsCount { get; set; }
        /// 
        /// 当前页的页码
        /// 
        public int CurrentPageIndex { get; set; }
        /// 
        /// 获取分页的SQL语句
        /// 
        private string GetPagedSQL()
        {           
            //组合SQL语句
            string sql =
                     "Select Top  (@PageSize) StudentId,StudentName,Gender,Birthday,PhoneNumber from Students " +
                      "where  Birthday>@Birthday and StudentId not in" +
                     " (Select Top  (@filterCount) StudentId from Students where  Birthday>@Birthday order by StudentId ASC)" +
                     "order by StudentId ASC;" +
                     "select count(*) from Students where  Birthday>@Birthday";          
            return sql;
        }
        /// 
        /// 执行分页查询，返回DataTable
        /// 
        /// 
        public DataTable GetPagedData(string birthday)
        {    
            //封装查询需要的参数
            SqlParameter[] param = new SqlParameter[]
            {
                  new SqlParameter("@PageSize",this.PageSize),
                  new SqlParameter("@filterCount",(PageSize * (CurrentPageIndex - 1))),
                  new SqlParameter("@Birthday",birthday)
            };
            //执行查询
            DataSet ds = SQLHelper.GetDataSet(this.GetPagedSQL(), param);
            //获取满足条件的记录总数
            this.RecordsCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            //返回数据列表
            return ds.Tables[0];
        }
    }

　　

 private static string connString = "Server=.;DataBase=DataPagerDB;Uid=sa;Pwd=password01!";

        /// 
        /// 执行Insert、Update、Delete操作
        /// 
        /// 
        /// 
        public static int Update(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        /// 
        /// 执行返回单一结果的查询
        /// 
        /// 
        /// 
        public static object GetSingleResult(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        /// 
        /// 返回只读数据集的查询
        /// 
        /// 
        /// 
        public static SqlDataReader GetReader(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                conn.Close();
                throw ex;
            }
        }
        /// 
        /// 返回DataSet数据集
        /// 
        /// 
        /// 
        public static DataSet GetDataSet(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            //创建数据适配器（数据传输的载体）
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            //创建一个数据集对象（内存数据库）
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                da.Fill(ds);//调用适配器的Fill方法，将数据从数据库端，传送到客户端的数据集对象中
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        public static DataSet GetDataSet(string sql,SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddRange(param);           
            SqlDataAdapter da = new SqlDataAdapter(cmd);            
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

　　存储过程的分页：

use DataPagerDB
go
if exists(select * from sysobjects where name=‘usp_DataPager‘)
drop procedure usp_DataPager
go
create procedure usp_DataPager  
 @PageSize int,--每页显示多少条
 @FilterCount int, --过滤的条数 
 @Birthday varchar(20) --查询条件参数
as 
	Select Top  (@PageSize) StudentId,StudentName,Gender,Birthday,PhoneNumber from Students 
	where  Birthday>@Birthday and StudentId not in
	 (Select Top  (@FilterCount) StudentId from Students where  Birthday>@Birthday order by StudentId ASC)
	order by StudentId ASC
	--查询满足记录条数 
	select  count(*) from Students where  Birthday>@Birthday 
go

　　

 /// 
    /// 通用数据分页类
    /// 
    public class SqlDataPager
    {
        public SqlDataPager() { }
        /// 
        /// 每页显示的条数
        /// 
        public int PageSize { get; set; }
        /// 
        /// 总页数
        ///       
        public int TotalPages
        {
            get
            {
                if (RecordsCount != 0)
                {
                    if (RecordsCount % PageSize != 0)
                        return RecordsCount / PageSize + 1;
                    else
                        return RecordsCount / PageSize;
                }
                else
                {
                    this.CurrentPageIndex = 1;//设置默认页
                    return 0;
                }
            }
        }
        /// 
        /// 记录总数
        /// 
        public int RecordsCount { get; set; }
        /// 
        /// 当前页的页码
        /// 
        public int CurrentPageIndex { get; set; }
      
        /// 
        /// 执行分页查询，返回DataTable
        /// 
        /// 
        public DataTable GetPagedData(string birthday)
        {    
            //封装查询需要的参数
            SqlParameter[] param = new SqlParameter[]
            {
                  new SqlParameter("@PageSize",this.PageSize),
                  new SqlParameter("@filterCount",(PageSize * (CurrentPageIndex - 1))),
                  new SqlParameter("@Birthday",birthday)
            };
            //执行查询
            DataSet ds = SQLHelper.GetDataSet("usp_DataPager", param);
            //获取满足条件的记录总数
            this.RecordsCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            //返回数据列表
            return ds.Tables[0];
        }
    }

　　

     private static string connString = "Server=.;DataBase=DataPagerDB;Uid=sa;Pwd=password01!";

        /// 
        /// 执行Insert、Update、Delete操作
        /// 
        /// 
        /// 
        public static int Update(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        /// 
        /// 执行返回单一结果的查询
        /// 
        /// 
        /// 
        public static object GetSingleResult(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        /// 
        /// 返回只读数据集的查询
        /// 
        /// 
        /// 
        public static SqlDataReader GetReader(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                conn.Close();
                throw ex;
            }
        }
        /// 
        /// 返回DataSet数据集
        /// 
        /// 
        /// 
        public static DataSet GetDataSet(string sql)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            //创建数据适配器（数据传输的载体）
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            //创建一个数据集对象（内存数据库）
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                da.Fill(ds);//调用适配器的Fill方法，将数据从数据库端，传送到客户端的数据集对象中
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        public static DataSet GetDataSet(string procName,SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(procName, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(param);           
            SqlDataAdapter da = new SqlDataAdapter(cmd);            
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                da.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
    }

　　

CSwinform程序分页

标签：cte   page   final   btn   eve   exec   mamicode   add   rds   

原文地址：https://www.cnblogs.com/sunliyuan/p/12274271.html

上一篇：LeetCode 21. 合并两个有序链表(C#实现)——链表,递归,迭代

下一篇：DataGridView禁用行标题中的三角号

文章来自：搜素材网的编程语言模块，转载请注明文章出处。
文章标题：CSwinform程序分页
文章链接：http://soscw.com/index.php/essay/41519.html

评论

亲，登录后才可以留言！

热门文章

推荐文章

最新文章

置顶文章

关于我们 | 版权声明 | 常见问题 | 素材投稿 | 联系我们 | 网站地图 |
搜素材网素材除本站原创外均由用户分享，若发现权利被侵害，请联系及时联系我们，我们会在第一时间进行处理。
特别说明：本站所有资源除本站原创外仅供学习与参考，请勿用于商业用途,如有侵犯您的版权请联系客服服务QQ：点击这里给我发消息
Copyright © 2024 soscw.com 搜素材网素材网版权所有 蜀ICP备18015633号-1