查询数据后,将数据分页显示
一、前台页面
Title 查询
顺序号 ID 标题 时间
二、后台代码
protected void Page_Load(object sender, EventArgs e) { string keywords = Request.QueryString["keywords"]; AspNetPager1.RecordCount = new DAL.title().GetCount(keywords); } //查询按钮 protected void btnSearch_Click(object sender, EventArgs e) { Response.Redirect("querypage.aspx?keywords=" + txtKeywords.Text); } //显示全部 protected void btn_all_Click(object sender, EventArgs e) { Response.Redirect("querypage.aspx"); } //清空protected void btn_empty_Click(object sender, EventArgs e) { txtKeywords.Text = ""; this.RepList1.Controls.Clear();//清空当前内容 RepList1.DataSource = string.Empty; RepList1.DataBind(); AspNetPager1.RecordCount = 0; } protected void AspNetPager1_PageChanged(object src, EventArgs e) { string keywords = Request.QueryString["keywords"]; txtKeywords.Text = keywords; RepList1.DataSource = new DAL.title().SelectbyPage(keywords,AspNetPager1.StartRecordIndex.ToString(), AspNetPager1.EndRecordIndex.ToString()); RepList1.DataBind(); }
三、DAL代码
//得到条件查询后的记录行数 public int GetCount(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append("select count(*) from title where"); strSql.Append(CombSqlTxt(strWhere)); return Convert.ToInt32(new SqlHelper().ExecuteScalar(strSql.ToString(), CommandType.Text)); } // 组合SQL查询语句========================== protected string CombSqlTxt(string _keywords) { StringBuilder strTemp = new StringBuilder(); if (!string.IsNullOrEmpty(_keywords)) { strTemp.Append(" title like '%" + _keywords + "%'"); } else { strTemp.Append(" 1=1"); } return strTemp.ToString(); } //带有查询条件的分页 public DataTable SelectbyPage(string strWhere, string startIndex, string endIndex) //当前页的首条页码参数starIndex和最后页码参数endIndex { StringBuilder strSql = new StringBuilder(); strSql.Append("with temptbl as ( SELECT ROW_NUMBER() OVER (ORDER BY id desc)AS Row, * from title where "); strSql.Append(CombSqlTxt(strWhere)); //添加条件语句 strSql.Append(") SELECT * FROM temptbl where Row between @startIndex and @endIndex"); SqlParameter[] sqlParameters = { new SqlParameter("@startIndex", startIndex), new SqlParameter("@endIndex",endIndex)}; DataTable dt = new SqlHelper().ExecuteQuery(strSql.ToString(), sqlParameters, CommandType.Text); return dt; }
四、效果图