1.ASPnetPage详解分页，2.以及sql写分页

 

2.sql 分页

   注意：首先要得到pageSize,pageIndex

   select * from

      (

           select top pageSize * from

               (

                  select top pageSize*pageIndex from Test order by id desc

               )

            as A order by id desc

      )

   Test

 select top  20*4的方式在SQL中取前80条记录,你会失败,因为TOP子句有一个限制,就是TOP num中的num不能是一个计算值或表达式而必须是一个既定的常量-------------------------------------------------------------------------------

  declare @count int

 set @count =2*10

   select * from

      (

           select top 2* from

               (

                  select top @count from Test order by id desc

               )

            as A order by id desc

      )

   Test

 

 

https://files.cnblogs.com/tinachang021/aspnentpager.rar

2.AspnetPage控件使用分页

   1。在sql语句中没有进行分页的情况

        注意使用这个控件的时候，一定要个  this.AspNetPager1.RecordCount 赋值哦，不然这个控件将不显示。

         RecordCount 是总记录数，（数据库里检索出来的所有记录数）。。。 

         StartIndex=this.AspNetPager1.PageSize * (this.AspNetPager1.CurrentPageIndex - 1)

         EnedIndex=this.AspNetPager1.PageSize

          详情看源码：

              

 1    private DataTable  LoadLocation()
 2    {
 3        int totalCount = 0;
 4        DataSet ds = new DataSet();
 5        int pageIndex = Util.ConvertTo<int>(Request.QueryString["page"], 0);
 6        if (pageIndex > 0)
 7        {
 8            AspNetPager1.CurrentPageIndex = pageIndex;
 9        }
        ds = BLLMemberLocation.GetMemberLocation(this.AspNetPager1.PageSize * (this.AspNetPager1.CurrentPageIndex - 1), this.AspNetPager1.PageSize, out totalCount);
        this.AspNetPager1.RecordCount = totalCount;

        if (totalCount <= this.AspNetPager1.PageSize)
        {
            this.AspNetPager1.Visible = false;
        }
        else
        {

            this.AspNetPager1.Visible = true;
        }

        if (ds != null)
        {

            this.repMyInfo.DataSource = ds;
            this.repMyInfo.DataBind();
        }
        return null;
      
    }

 public static DataSet GetMemberLocation(int pageindex, int pagesize, out int totalCount)
    {
        DataSet ds = new DataSet();
        using (SqlConnection myConnection = new SqlConnection(connString))
        {
            SqlCommand sqlcmd = new SqlCommand("app_GetMemLocation", myConnection);
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.Parameters.Add("@MemberID", SqlDbType.UniqueIdentifier);
            sqlcmd.Parameters["@MemberID"].Value = userID;
            ds = Util.GetPagedRecords(myConnection, sqlcmd, pageindex, pagesize, out totalCount);
            myConnection.Close();
        }
       
        return ds;
    }
 public static DataSet GetPagedRecords(SqlConnection myConnection, SqlCommand sqlcmd, int pageIndex, int pageSize, out int totalRecords)
    {
        int StartRecord = pageIndex;
        int MaxRecords = pageSize;

        if (pageIndex < 0)
            throw new ArgumentException("PageIndex cannot be negative");
        if (pageSize < 1)
            throw new ArgumentException("PageSize must be positive");

        long lBound = (long)pageIndex * pageSize;
        long uBound = lBound + pageSize - 1;

        if (uBound > System.Int32.MaxValue)
        {
            throw new ArgumentException("PageIndex too big");
        }
        myConnection.Open();
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
        using (SqlDataAdapter adapter = new SqlDataAdapter(sqlcmd))
        {
            adapter.Fill(dt);
            totalRecords = dt.Rows.Count;
            dt.Clear();
            adapter.Fill(ds, StartRecord, MaxRecords, "tbName");
       }

        myConnection.Close();
        return ds;
    }

 

这里还要注意哦。。。adapter.Fill(ds.startrecord,maxrecord,"tbName")//它也是取的是当前页的数据

    例如：startRecord=0,maxrecord=10

   那它取的是0－10这10条数据

                 10－20

                  20－30

                 .。。。。

 

同时还要注意：AspNetPager控件它有url参数的哦：

         http://localhost:4849/AppointmentMaker/UserProfile/MemberLocation.aspx?page=2

           每次点击分页控件它将会把参数显示到url上:即page就是当前页

 

 

   2.在sql语句中进行分页的情况