</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;//分页总数的计算