CSwinform程序分页

CSwinform程序分页

///

/// 通用数据分页类 ///

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 DataPagerDBgoif exists(select * from sysobjects where name='usp_DataPager')drop procedure usp_DataPagergocreate 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(); } } }

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。