分页原理以及代码分析

</pre><p><span style="font-size:18px;">良心制作  本人学习中整理的</span></p><p><pre name="code" class="csharp">//获取总条数
        public int GetAllCount() 
        {
           return SqlHelper.GetScaler(" select COUNT(1) from UserInfo");
        }

方法一：top分页法
select top 5 * from UserInfo
--怎么忽略前面已经查询了的数据 数据量小的时候效率还是比较高，但是数据量越大效率越低
select top 5 * from UserInfo where Id not in
(select top 15 Id from UserInfo)

/// <summary>
        /// 分页查询所有数据
        /// </summary>
        /// <returns></returns>
        public List<UserInfo> GetAll(string _name, string _UrEmail,int page,int i) 
        {
            string sql1 = "";
            List<UserInfo> list = new List<UserInfo>();
            string sql = "select top " + i + " * from UserInfo where 1=1";
            if (_name != null && _name != "")
            {
                sql1 += " and UrName like '%" + _name + "%' ";
            }
            if (_UrEmail != null && _UrEmail != "")
            {
                sql1 += " and UrEmail like '%" + _UrEmail + "%' ";
            }
            if (page  >0)
            {
                sql += sql1 + "and UrId not in(select top (("+page+"-1)* "+i+") UrId from UserInfo where 1=1" + sql1 + " order by UrId desc) order by UrId desc  ";
            }
            DataSet ds = DBHelrp.GetAll(sql);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                UserInfo ui = new UserInfo();
                ui.UrId = Convert.ToInt32(dr["UrId"]);
                ui.UrName = dr["UrName"].ToString();
                ui.UrPwd = dr["UrPwd"].ToString();
                ui.UrSex = dr["UrSex"].ToString();
                ui.UrEmail = dr["UrEmail"].ToString();
                ui.Urfile = dr["Urfile"].ToString();

                list.Add(ui);
            }
            return list;
        }

方法二：between and 分页法  不推荐
select * from UserInfo where Id between 1 and 5
但是很有局限性，guid字符串id不适用，id不连续不适用


方法三：:row_number分页法
思路：先查询出行号在用between end进行分页
select * from (
select ROW_NUMBER() over(order by id) as 'rownumber',* from UserInfo
) temp where rownumber  between 6 and 10
需要使用sql包裹
sql包裹，把一个把的查询结果用作另外一个查询

public DataTable GetUserByPage(int _page,int _rows,string _username,string _password) 
        {
            string wheresql = " where 1=1 ";
            if (!string.IsNullOrEmpty(_username))
            {
                wheresql += " and  UserName ='" + _username + "'";
            }
            if (!string.IsNullOrEmpty(_password))
            {
                wheresql += " and  Pwd ='" + _password + "'";
            }


            string sqls = @"select * from (
                            select ROW_NUMBER() over(order by id) as 'myrOW_NUMBER',* from UserInfo {2}
                            ) temp where myrOW_NUMBER between {0} and {1} ";


            return SqlHelper.GetDataTable(string.Format(sqls, (_page - 1) * _rows + 1, _page * _rows, wheresql));   
        }

方法四：: offset 分页法
有个缺陷2012才开始支持


方法五：使用存储过程分页


方法六：内存分页

 int totalpagetnum = (totalrecord + pagesize - 1) / pagesize;//分页总数的计算