a_list.DataSource = sClass.Data_DataTable("Select top 20 ID,A_Name,A_Dingdan,A_Price_dingdan,(A_Price_dingdan+A_Price_Express+A_Price_Shouxu)-(A_Price_Ticket+A_Price_moeny),A_zffs_text,A_Class,A_Time,A_Qr_Time from A_Dingdan where ID not in (select top " & p * 20 & " ID from A_Dingdan where (A_Dingdan like '%" + t + "%' or A_user like '%" + t + "%' or A_Name like '%" + t + "%') order by id desc) and (A_Dingdan like '%" + t + "%' or A_user like '%" + t + "%' or A_Name like '%" + t + "%') order by id desc").Tables(0)用sql语句来分页如何P是页码,20是每页数量。 我这是随便写了内部用来玩玩的,安全性、性能各方面大家勿在意。
/// <summary> /// 获取当前页的[图书]信息 /// </summary> /// <param name="pageIndex">第N页</param> /// <param name="pageSize">每页显示的记录数</param> /// <returns></returns> public static List<Book> GetAllBooks(int pageIndex, int pageSize) { //======= 方法1(sql语句) ========== //当前页第一条记录对应的行号 int start = (pageIndex - 1) * pageSize + 1; //当前页最后一条记录对应的行号 int end = start + pageSize - 1; string sql = "select * from " + "(select row_number() over(order by id) as n,* from Books) N_Books " + "where n between @start and @end"; List<Book> list = new List<Book>(); try { SqlParameter[] par = new SqlParameter[] { new SqlParameter("@start",start), new SqlParameter("@end",end) }; //DataTable table = DBHelper.GetDataTable(sql, par); DataSet ds = DBHelper.GetDataSet(CommandType.Text,sql, par); foreach (DataRow row in ds.Tables[0].Rows) { Book book = new Book(); book.Id = (int)row["Id"]; book.Title = (string)row["Title"]; book.Author = (string)row["Author"]; book.PublishDate = (DateTime)row["PublishDate"]; book.ISBN = (string)row["ISBN"]; book.WordsCount = (int)row["WordsCount"]; book.UnitPrice = (decimal)row["UnitPrice"]; book.ContentDescription = (string)row["ContentDescription"]; book.AurhorDescription = (string)row["AurhorDescription"]; book.EditorComment = (string)row["EditorComment"]; book.TOC = (string)row["TOC"]; book.Clicks = (int)row["Clicks"]; //获取[出版社]信息 book.Publisher = PublisherService.GetPublisherById((int)row["PublisherId"]); //FK //获取[图书类别]信息 book.Category = CategoryService.GetCategoryById((int)row["CategoryId"]); //FK list.Add(book); } return list; } catch (Exception e) { //Console.WriteLine(e.Message); return null; }
根据pageIndex pageSise rowCount 。 自己拼接分页字符串 html 。
不用可以啊 就手写呗 1: 得到总行数 select count(*) from tb 2: select top(索引*每页显示数量) from tb where id not in (索引-1)*每页显示数 3:总页面数 总行数/每页显示数 基本上 就是这些了 直接用存储过程写 最简单了
CREATE proc dbo.procPaging2 @tablename varchar(50), @column varchar(500), @primarykey varchar(20), @pagesize int, @pageindex int, @where varchar(2000), @order varchar(1000), @datacount int output, @pagecount int output, @pageindex_p int output as declare @sql nvarchar(4000) declare @sqlwhere varchar(2000) declare @sqlwhere2 varchar(2000) declare @sqlorder varchar(1000) if(@where <> '') begin set @sqlwhere = 'where ' + @where + ' ' end else begin set @sqlwhere = ' ' end set @sql = 'set @datacount=(select count(*) from ' + @tablename + ' ' + @sqlwhere + ')' exec sp_executesql @sql,N'@datacount int out',@datacount out if(@datacount % @pagesize <> 0 ) begin set @pagecount = @datacount / @pagesize + 1 end else begin set @pagecount = @datacount / @pagesize end if(@pageindex > @pagecount) begin if(@pagecount = 0) begin set @pageindex_p = 1 end else begin set @pageindex_p = @pagecount end end else if(@pageindex < 1) begin set @pageindex_p = 1 end else begin set @pageindex_p = @pageindex end if(@where <> '') begin set @sqlwhere = 'AND ' + @where set @sqlwhere2 = @where end else begin set @sqlwhere = '' set @sqlwhere2 = '' end if(@order <> '') begin set @sqlorder = ' order by ' + @order end else begin set @sqlorder = '' end SET @sql = 'SELECT TOP ' + ltrim(str(@pagesize)) + ' ' + @column + ' FROM ' + @tablename + ' ' + 'WHERE [ID] < (SELECT MIN([ID]) FROM (SELECT TOP ' + ltrim(str(@pagesize*(@pageindex_p-1))) + ' [ID] FROM ' + @tablename + ' WHERE ' + + @sqlwhere2 + @sqlorder + ') AS t) ' + @sqlwhere + @sqlorder print @sql print @pageindex_p exec (@sql)GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
我这是随便写了内部用来玩玩的,安全性、性能各方面大家勿在意。
前台<% foreach %>
/// 获取当前页的[图书]信息
/// </summary>
/// <param name="pageIndex">第N页</param>
/// <param name="pageSize">每页显示的记录数</param>
/// <returns></returns>
public static List<Book> GetAllBooks(int pageIndex, int pageSize)
{
//======= 方法1(sql语句) ========== //当前页第一条记录对应的行号
int start = (pageIndex - 1) * pageSize + 1;
//当前页最后一条记录对应的行号
int end = start + pageSize - 1; string sql = "select * from " +
"(select row_number() over(order by id) as n,* from Books) N_Books " +
"where n between @start and @end"; List<Book> list = new List<Book>(); try
{
SqlParameter[] par = new SqlParameter[]
{
new SqlParameter("@start",start),
new SqlParameter("@end",end)
}; //DataTable table = DBHelper.GetDataTable(sql, par);
DataSet ds = DBHelper.GetDataSet(CommandType.Text,sql, par); foreach (DataRow row in ds.Tables[0].Rows)
{
Book book = new Book(); book.Id = (int)row["Id"];
book.Title = (string)row["Title"];
book.Author = (string)row["Author"];
book.PublishDate = (DateTime)row["PublishDate"];
book.ISBN = (string)row["ISBN"];
book.WordsCount = (int)row["WordsCount"];
book.UnitPrice = (decimal)row["UnitPrice"];
book.ContentDescription = (string)row["ContentDescription"];
book.AurhorDescription = (string)row["AurhorDescription"];
book.EditorComment = (string)row["EditorComment"];
book.TOC = (string)row["TOC"];
book.Clicks = (int)row["Clicks"];
//获取[出版社]信息
book.Publisher = PublisherService.GetPublisherById((int)row["PublisherId"]); //FK
//获取[图书类别]信息
book.Category = CategoryService.GetCategoryById((int)row["CategoryId"]); //FK list.Add(book);
} return list;
}
catch (Exception e)
{
//Console.WriteLine(e.Message);
return null;
}
自己拼接分页字符串 html 。
2: select top(索引*每页显示数量) from tb where id not in (索引-1)*每页显示数 3:总页面数 总行数/每页显示数
基本上 就是这些了
直接用存储过程写 最简单了
@tablename varchar(50),
@column varchar(500),
@primarykey varchar(20),
@pagesize int,
@pageindex int,
@where varchar(2000),
@order varchar(1000),
@datacount int output,
@pagecount int output,
@pageindex_p int output
as
declare @sql nvarchar(4000)
declare @sqlwhere varchar(2000)
declare @sqlwhere2 varchar(2000)
declare @sqlorder varchar(1000)
if(@where <> '')
begin
set @sqlwhere = 'where ' + @where + ' '
end
else
begin
set @sqlwhere = ' '
end
set @sql = 'set @datacount=(select count(*) from ' + @tablename + ' ' + @sqlwhere + ')'
exec sp_executesql @sql,N'@datacount int out',@datacount out
if(@datacount % @pagesize <> 0 )
begin
set @pagecount = @datacount / @pagesize + 1
end
else
begin
set @pagecount = @datacount / @pagesize
end
if(@pageindex > @pagecount)
begin
if(@pagecount = 0)
begin
set @pageindex_p = 1
end
else
begin
set @pageindex_p = @pagecount
end
end
else if(@pageindex < 1)
begin
set @pageindex_p = 1
end
else
begin
set @pageindex_p = @pageindex
end
if(@where <> '')
begin
set @sqlwhere = 'AND ' + @where
set @sqlwhere2 = @where
end
else
begin
set @sqlwhere = ''
set @sqlwhere2 = ''
end
if(@order <> '')
begin
set @sqlorder = ' order by ' + @order
end
else
begin
set @sqlorder = ''
end
SET @sql = 'SELECT TOP ' + ltrim(str(@pagesize)) + ' ' + @column + ' FROM ' + @tablename + ' '
+ 'WHERE [ID] < (SELECT MIN([ID]) FROM (SELECT TOP ' + ltrim(str(@pagesize*(@pageindex_p-1))) + ' [ID] FROM ' + @tablename + ' WHERE ' + + @sqlwhere2 + @sqlorder + ') AS t) ' + @sqlwhere + @sqlorder
print @sql
print @pageindex_p
exec (@sql)GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO