搜索+分页

 

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();
        }
    }
}