ASP.NET 2.0 分页技术之使用纯sql语句的双top分页篇

悟静 2013-02-02

展开全文

Repeater在前台使用比较灵活自由，但有一个问题就是Repeater不支持直接分页，这个很多人看起来就有点不想用了，但我想大家都知道GridView控件或DataGrid控件在启用自带分页的时候其实效率是非常低的，大的不说，一但到了百万级数据以后，就会感觉是多么的痛苦和无耐了，所以即使是用DataGrid(GridView)控件，高手们还是只会只使用一个界面而已，而分页肯定都是自己写的，这样一比较利弊就出来了，既然这样那就选择Repeater吧,本篇示例演示使用纯sql语句的双top分页法达到控件分页的功能.
--------------------------------------------------------------------------------

前台代码：
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www./TR/xhtml1/DTD/xhtml1-transitional.dtd">

后台事件代码：
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
//------导包
using System.Data.SqlClient;

public partial class Default3 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        int cpage;
        if (Request.QueryString["page"] != null)
            cpage = Convert.ToInt32(Request.QueryString["page"]);
        else
            cpage = 1;

        TimeSpan newtimespan = DateTime.Now.TimeOfDay;//运行时间开始
        int pagesize = 5;
        string sql = "";
        string sqlstr = "select count(*) from jobs";
        SqlConnection con = new SqlConnection("server=(local);database=pubs;uid=sa;pwd=sa;");
        SqlCommand cmd = new SqlCommand(sqlstr, con);
        con.Open();
        int totalput = Convert.ToInt32(cmd.ExecuteScalar());

        int maxpage = 1;
        if (totalput % pagesize == 0)
        {
            maxpage = totalput / pagesize;
        }
        else
        {
            maxpage = totalput / pagesize + 1;
        }
        if (maxpage == 0) { maxpage = 1; }
        if (cpage < 1) { cpage = 1; }
        else if (cpage > maxpage) { cpage = maxpage; }

        if (totalput != 0)
        {
            if (cpage == 1)
            {
                sql = "select top " + pagesize + " * from jobs order by job_id desc";
            }
            else
            {
                sql = "select top " + pagesize + " * from jobs where job_id not in(select top " + (cpage - 1) * pagesize + " job_id from jobs order by job_id desc) order by job_id desc";
            }
        }

        SqlDataAdapter adapter = new SqlDataAdapter(sql, con);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        adapter.Dispose();

        this.Label1.Text = "共有信息" + totalput.ToString() + "条当前是第" + cpage.ToString() + "/" + maxpage.ToString() + "页 ";
        if (cpage != 1)
        {
            this.HyperLink1.NavigateUrl = "Default3.aspx?page=1";
            this.HyperLink2.NavigateUrl = "Default3.aspx?page=" + Convert.ToString(cpage - 1);
        }
        if (cpage != maxpage)
        {
            this.HyperLink3.NavigateUrl = "Default3.aspx?page=" + Convert.ToString(cpage + 1);
            this.HyperLink4.NavigateUrl = "Default3.aspx?page=" + maxpage.ToString();
        }

        this.Repeater1.DataSource = ds.Tables[0].DefaultView;
        this.Repeater1.DataBind();
        //算出运行时间并附值给Label控件显示
        this.Label1.Text = DateTime.Now.TimeOfDay.Subtract(newtimespan).TotalMilliseconds.ToString();
    }
}

如上述代码有什么问题请留言或联系我!