使用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时它的处理效率比较低。
(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);
}
当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 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 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);
//得到总的数据条数
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);//为了显示时间差判断效率的高低。
}
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);//为了显示时间差判断效率的高低。
}
转载于:https://blog.51cto.com/zhjjzhjj/403238