分页原理以及代码分析

刺骨的言语ヽ痛彻心扉 2022-09-26 03:25 162阅读 0赞

</pre>良心制作  本人学习中整理的<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;//分页总数的计算