使用sql语句实现分页

时间：2010-10-10 来源：张静静

使用sql语句实现分页：
（1）select top 3 * from dbo.Users where id not in(
select top(0*3) id from dbo.Users order by id desc
) order by id desc
用not in时它的处理效率比较低。（2）
当page页面大于1时使用。
select top 3 * from users where id<(
select top 1 id from (select top(1*3) id from users order by id desc)
as t order by id )order by id desc
当page页面等于0时使用。
select top 3 * from users order by id desc
我们一般都是用这种方法。
（3）select top 3 * from (select rowid = row_number()over(order by id desc),* from users) as t
where rowid>3*3
下面是个具体的应用：
这个是在dal层的应用：

//根据sql语句实现分页
public List<Data.Users> Select(IProvider provider,int page,int count)
       {
           string sql = "";
           if (page > 0)
           {
               sql = "select top " + count + " * from dbo.Admin where id<(select top 1 id from (select top(" + page * count + ") id from dbo.Admin order by id desc)as t order by id ) order by id desc";
           }
           else
           {
               sql = "select top " + count + " * from dbo.Admin order by id desc";
           }
           using (IDataReader dr = helper.ExecuteReader(provider, sql, null))
           {
               return helper.ConvertToList<Data.Users>(dr);
           }        }
//获得数据库中的记录数
       public int SelectCount(IProvider provider)
       {
           string sql = "select count(*) from Admin";
           return (int)helper.ExecuteScalar(provider,sql,null);        }
下面是在业务层的具体调用实例：
//选择分页的条数和得到分页
public static string Select(int page,int count)
      {
          string str = "";
          List<Data.Users> dus = null;

              using (IProvider provider = ManageFactory.Factory.GetProvider)
              {
                  DAL.OpUser ou = new Base.DAL.OpUser();
                  dus = ou.Select(provider,page,count);

              }


          foreach (Data.Users du in dus)
          {
              str +="编号："+du.Id +"名称："+ du.Username + "<br/>";
          }
          return str;       }
//得到总的数据条数
      public static int SelectCount()
      {
          int i = 0;
          using (IProvider provider = ManageFactory.Factory.GetProvider)
          {
              DAL.OpUser ou = new Base.DAL.OpUser();
              i = ou.SelectCount(provider);           }
          return i;
      }
//获取页面显示的字符串
      public static string PageString(int page,int count)
      {
          int size = SelectCount();
          int pagecount = size % count == 0 ? size / count : (size / count) + 1;
          StringBuilder sbu = new StringBuilder();
          sbu.AppendFormat("总共{0}页", pagecount);
          for (int i = 0; i < pagecount; i++)
          {
              sbu.AppendFormat("<a href='?page={0}'>", i+1);
              sbu.AppendFormat("第{0}页</a>", i + 1);
          }
          return sbu.ToString();
      }
最后就是在页面上的具体调用了：
    protected string str = "";//在aspx页面<%=str>
    protected string pagestr;//在aspx页面<%=pagestr>
    int page = 0;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Request.QueryString["page"] != null)
        {
            page = int.Parse(Request.QueryString["page"]);
        }
//得到在页面上显示的页面字符串
        pagestr = Base.BLL.UserBorrow.PageString(page, 4);
        DateTime date1 = DateTime.Now;
//得到每页显示的具体内容
        str = Base.BLL.UserBorrow.Select(page - 1, 4);
        TimeSpan ts = DateTime.Now - date1;
        Response.Write(ts.Milliseconds);//为了显示时间差判断效率的高低。

    }