namespace 使用存储过程分页{ public partial class Demo2 : System.Web.UI.Page { int pagesize = 3; protected void Page_Load(object sender, EventArgs e) { if(!IsPostBack) { ViewState["pageindex"] = 1; count(); DataLoad(); } } private void count() { string constr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString; SqlConnection conn = new SqlConnection(constr); conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "SELECT COUNT(*) FROM T_News WHERE NewsTitle LIKE @newskey OR NewsContent LIKE @newskey"; cmd.Parameters.AddWithValue("@newskey","%"+txtkey.Text+"%"); int total=Convert.ToInt32(cmd.ExecuteScalar());//一共有多少页 if (total % pagesize == 0) { ViewState["pagelastindex"] = total / pagesize; } else { ViewState["pagelastindex"] = total / pagesize + 1; } } private void DataLoad() { string constr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString; SqlConnection conn = new SqlConnection(constr); conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText="SELECT TOP (@pagesize) * FROM T_News WHERE (NewsTitle LIKE @newskey OR NewsContent LIKE @newskey)AND Id NOT IN(SELECT TOP((@pageindex-1)*@pagesize)Id FROM T_News WHERE NewsTitle LIKE @newskey OR NewsContent LIKE @newskey ORDER BY Id)ORDER BY Id"; cmd.Parameters.AddWithValue("@pageindex",Convert.ToInt32(ViewState["pageindex"])); cmd.Parameters.AddWithValue("@pagesize",pagesize); cmd.Parameters.AddWithValue("@newskey","%"+txtkey.Text+"%"); SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); adapter.Fill(dt);
cmd.Dispose(); conn.Dispose(); StringBuilder sb = new StringBuilder(); sb.Append("<table border=2>"); sb.Append("<tr><td>新闻标题</td><td>新闻内容</td><td>创建时间</td></tr>"); for (int i = 0; i < dt.Rows.Count; i++) { sb.Append("<tr>"); sb.Append("<td>"+dt.Rows[i]["NewsTitle"].ToString()+"</td>"); sb.Append("<td>"+dt.Rows[i]["NewsContent"].ToString()+"</td>"); sb.Append("<td>"+dt.Rows[i]["CreateTime"].ToString()+"</td>"); sb.Append("</tr>"); } sb.Append("</table>"); divResult.InnerHtml = sb.ToString(); } //查询 protected void btnQuery_Click(object sender, EventArgs e) { ViewState["pageindex"]=1; count(); DataLoad(); } //第一页 protected void btnFirst_Click(object sender, EventArgs e) { ViewState["pageindex"] = 1; DataLoad(); } //上一页 protected void btnSecond_Click(object sender, EventArgs e) { int pageindex = Convert.ToInt32(ViewState["pageindex"]); if(pageindex>1) { pageindex--; ViewState["pageindex"] = pageindex; DataLoad(); } } //下一页 protected void btnNext_Click(object sender, EventArgs e) { int pageindex=Convert.ToInt32(ViewState["pageindex"]); if (pageindex < Convert.ToInt32(ViewState["pagelastindex"])) { pageindex++; ViewState["pageindex"] = pageindex; DataLoad(); } } //最后一页 protected void btnLast_Click(object sender, EventArgs e) { ViewState["pageindex"] = ViewState["pagelastindex"]; DataLoad(); } }}
转载于:https://www.cnblogs.com/qzc900809/archive/2013/03/13/2958191.html
相关资源：实现PHP搜索加分页