ASP实现分页查询

定义一个页面要显示的条数

int pagesize = 3;

#region 定义一个方法方便调用 
private void fun()
        {
            string str = @"data source=PC-LENOVE\SQLEXPRESS;initial catalog=数据库名字;user=sa;password=****";
            using (SqlConnection conn = new SqlConnection(str))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = "SELECT TOP(@pagesize) * FROM T_News WHERE Id NOT IN(SELECT TOP ((@pageindex-1)*@pagesize) Id FROM T_News ORDER BY Id) ORDER BY Id";
                    cmd.Parameters.Add("@pagesize", pagesize);
                    cmd.Parameters.Add("@pageindex",Convert.ToInt32(ViewState["pageindex"]));
                    SqlDataAdapter ad = new SqlDataAdapter(cmd);
                    DataTable table=new DataTable();
                    ad.Fill(table);
                    StringBuilder sb = new StringBuilder();
                    sb.Append("<table>");
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        sb.Append("<tr>");
                        sb.Append("<td>" + table.Rows[i]["NewsTitle"].ToString() + "</td><td>" + table.Rows[i]["NewsContent"].ToString() + "</td>");
                        sb.Append("</tr>");
                    }
                    max = table.Rows.Count;
                    sb.Append("</table>");
                    re.InnerHtml = sb.ToString();
                }
            }
        }
#endregion

上边的代码是查询和输出的。上面出现的ViewState来充当页面数值

初始给ViewState一个值：

ViewState["pageindex"] = 1;

首页的实现：

protected void Button3_Click(object sender, EventArgs e)
        {
            ViewState["pageindex"] = 1;
            fun();
        }

末页的实现需要的总页数值：

 private void count()
        {
            string str = @"data source=PC-LENOVE\SQLEXPRESS;initial catalog=数据库名字;user=sa;password=****";
            SqlConnection conn = new SqlConnection(str);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            conn.Open();
            cmd.CommandText = "select count(*) from T_News";
            int a = Convert.ToInt32(cmd.ExecuteScalar());
            if (a % pagesize == 0)
            {
                ViewState["lastpage"] = a / pagesize;
            }
            else
            {
                ViewState["lastpage"] = a / pagesize + 1;
            }
            //ViewState["lastpage"]是最大的页数
            cmd.Dispose();
            conn.Dispose();
        }

末页：

 protected void Button2_Click(object sender, EventArgs e)
        {
            ViewState["pageindex"] = ViewState["lastpage"];
            fun();
        }

下一页的实现：

 protected void LinkButton2_Click(object sender, EventArgs e)
        {
            int a = Convert.ToInt32(ViewState["pageindex"]);
            if (a<Convert.ToInt32(ViewState["lastpage"]))//约束页面数值，使它不超过最大页数值
            {
            a++;
            ViewState["pageindex"] = a;
            fun();
            }
        }

上一页的实现：

 protected void LinkButton1_Click(object sender, EventArgs e)
        {
            int a = Convert.ToInt32(ViewState["pageindex"]);
            if (a>1)//页数值必须大于0
            {
                a--;
                ViewState["pageindex"] = a;
                fun();
                #region 笨方法
                /*
                string str = @"data source=PC-LENOVE\SQLEXPRESS;initial catalog=数据库名字;user=sa;password=****";
                using (SqlConnection conn = new SqlConnection(str))
                {
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        conn.Open();
                        cmd.CommandText = "SELECT TOP(@pagesize) * FROM T_News WHERE Id NOT IN(SELECT TOP ((@pageindex-1)*@pagesize) Id FROM T_News ORDER BY Id) ORDER BY Id";
                        cmd.Parameters.Add("@pagesize", pagesize);
                        cmd.Parameters.Add("@pageindex", a);
                        a--;
                        ViewState["pageindex"] = a;
                        SqlDataAdapter ad = new SqlDataAdapter(cmd);
                        DataTable table = new DataTable();
                        ad.Fill(table);
                        StringBuilder sb = new StringBuilder();
                        sb.Append("<table>");
                        for (int i = 0; i < table.Rows.Count; i++)
                        {
                            sb.Append("<tr>");
                            sb.Append("<td>" + table.Rows[i]["NewsTitle"].ToString() + "</td><td>" + table.Rows[i]["NewsContent"].ToString() + "</td>");
                            sb.Append("</tr>");
                        }
                        max = table.Rows.Count;
                        sb.Append("</table>");
                        re.InnerHtml = sb.ToString();
                        a--;
                    }
                }
                 */
                #endregion
            }
            
        }

页面加载代码如下：

protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)//回发事件时不执行
            {
                ViewState["pageindex"] = 1;
                count();
                fun();
              
            }
        }