ASP.NET针对Sybase数据库使用分页技术
ASP.NET针对Sybase数据库使用分页技术
在编写一个通用数据库操作接口的分页查询时，发现MS SQL Server、Oracle、MySql、Access等数据库都有分页查询语句，只有Sybase没有，如果用AseDataAdapter.Fill(dataSet, startRowIndex, maximumRows, "item")方法实现，实际上还是将数据库的所有记录返回的，这样在数据量很大(超过10万条记录)时，速度将无法忍受，经过互联网搜索，发现Sybase 15后加入了Top子句，经测试后发现Top不能使用在子句中，效果与set rowcount一样不能实现分页，如果有哪位大虾知道其语法请予以赐教，搜索中还找到一个分页查询的存储过程，代码如下：
create procedure test_p @ipage int, @num int as /* @ipage 页码, @num 每页的记录数 */
begin
declare @maxpages int, @rcount int /* @maxpages 最大页码 */
if @ipage>=100
select @maxpages=ceiling(count(*)/@num) from test
else
select @maxpages=100000
if @ipage<=@maxpages/2
begin
select @rcount=@ipage*@num
set rowcount @rcount
select id=identity(12),name,descs,ddd into #temptable1 from test order by id
select * from #temptable1 where id>=(@ipage-1)*@num and id<= @ipage*@num
end
else
begin
select @rcount=(@maxpages-@ipage+1)*@num
set rowcount @rcount
select id=identity(12),name,descs,ddd into #temptable2 from test order by id desc
select id,name, ddd,descs from #temptable2 where id>=(@maxpages-@ipage)*@num and id<= (@maxpages-@ipage+1)*@num order by id desc
end
end
后有好心人更改为一个通用版本，代码如下：
create procedure splitpage @qry varchar(16384),@ipage int, @num int as /*@qry SQL语句, @ipage 页数, @num 每页记录条数 */
begin
declare @maxpages int
declare @rcount int
declare @execsql varchar(16384)
if @ipage>=100
select @maxpages=ceiling(count(*)/@num) from test
else
select @maxpages=100000
if @ipage<=@maxpages/2
begin
select @rcount=@ipage*@num
set rowcount @rcount
set @execsql = stuff(@qry,charindex('select',@qry),6,'select sybid=identity(12),')
set @execsql = stuff(@execsql, charindex('from',@execsql),4,'into #temptable1 from')
set @execsql = @execsql || ' select * from #temptable1 where sybid>' || convert(varchar,(@ipage-1)*@num) || ' and sybid <= ' || convert(varchar,@ipage*@num)
execute (@execsql)
end
else
begin
select @rcount=(@maxpages-@ipage+1)*@num
set rowcount @rcount
set @execsql = stuff(@qry,charindex('select',@qry),6,'select sybid=identity(12),')
set @execsql = stuff(@execsql, charindex('from',@execsql),4,'into #temptable1 from')
set @execsql = @execsql || ' order by sybid desc'
set @execsql = @execsql || ' select * from #temptable1 where sybid > ' || convert(varchar,(@maxpages-@ipage)*@num) || ' and sybid <= ' || convert(varchar,(@maxpages-@ipage+1)*@num)
execute (@execsql)
end
end
使用中发现几个问题：
Ø SQL语句中不能有排序子句；
Ø 查询表中不能用Identity；
Ø 分页计算存在问题。
我对该存储过程进行了修改，代码如下：
/****************************************************************/
/* 对于大于maxrow条的数据表，排序参数不要为空， */
/* 在查询后一半页数时，排序参数使用倒序 */
/* 对于有自增量字段的数据表，自增量字段名称参数不能为空， */
/* 此时排序参数无效，排序按自增量字段进行排序 */
/****************************************************************/
--drop proc splitpage
create procedure splitpage
@fieldclause varchar(8042), --字段
@tableclause varchar(100), --表名
@whereclause varchar(8042), --条件
@sortclause varchar(180), --排序
@identityname varchar(20), --自增量字段名称
@ipage int, --需要查询页数
@num int, --每页条数
@totalrow int, --该查询语句结果的总记录数
@maxrow int --数据表总行数超过此参数后，在查询后一半页数时，排序参数使用倒序
as
begin
declare @maxpages int --总页数
declare @rcount int --查询的记录数
declare @bottomstart int --从后面查询标志,0-从上查询;1-从下查询
declare @execsql varchar(16384) --SQL语句
declare @remainder int --计算数据表总条数的余数变量
select @rcount=@ipage*@num
select @remainder=@totalrow % @num
select @maxpages=ceiling(@totalrow/@num)+1
if @totalrow>@maxrow
begin
if @ipage>@maxpages/2
begin
select @bottomstart = 1
end
else
begin
select @bottomstart = 0
end
end
else
begin
select @bottomstart = 0
end
if @identityname=''
begin
select @execsql = 'select sybid=identity(12),' || @fieldclause || ' into #moy_temptable from ' || @tableclause
end
else
begin
select @execsql = 'select 100000000000 sybid,' || @fieldclause || ' into #moy_temptable from ' || @tableclause
select @bottomstart = 0
end
if @bottomstart = 0
begin
set rowcount @rcount
end
else
begin
select @rcount=(@maxpages-@ipage+1)*@num
set rowcount @rcount
end
if @whereclause<>''
begin
select @execsql = @execsql || ' where ' || @whereclause
end
if @sortclause<>''
begin
if @identityname=''
begin
select @execsql = @execsql || ' order by ' || @sortclause
end
else
begin
select @execsql = @execsql || ' order by ' || @identityname
end
end
if @identityname<>''
begin
select @execsql = @execsql || ' update #moy_temptable set sybid=(select count(1) from #moy_temptable b where a.' || @identityname || '>=b.' || @identityname || ') from #moy_temptable a'
end
if @bottomstart = 0
begin
select @execsql = @execsql || ' select ' || @fieldclause ||' from #moy_temptable where sybid>' || convert(varchar,(@ipage-1)*@num) || ' and sybid <= ' || convert(varchar,@ipage*@num)
end
else
begin
select @execsql = @execsql || ' select ' || @fieldclause ||' from #moy_temptable where sybid <= ' || convert(varchar,(@maxpages-@ipage)*@num+@remainder) || ' and sybid > ' || convert(varchar,(@maxpages-(@ipage+1))*@num+@remainder)
end
execute (@execsql)
set rowcount 0
end
下面是ASP.NET（C#）对该存储过程的调用，代码如下：
private DataTable GetDataTable(string fieldClause, string tableClause, string whereClause,
string sortClause, string identityName, int pageIndex, int pageNum, int totalRowCount)
{
connection.Open();
try
{
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "splitpage";
AseParameterCollection parameters = (AseParameterCollection)command.Parameters;
parameters.Add("@fieldclause", AseDbType.VarChar, 8042).Value = fieldClause;
parameters.Add("@tableclause", AseDbType.VarChar, 100).Value = tableClause;
parameters.Add("@whereclause", AseDbType.VarChar, 8042).Value = whereClause;
parameters.Add("@sortclause", AseDbType.VarChar, 180).Value = sortClause;
parameters.Add("@identityname", AseDbType.VarChar, 20).Value = identityName;
parameters.Add("@ipage", AseDbType.Integer).Value = pageIndex;
parameters.Add("@num", AseDbType.Integer).Value = pageNum;
parameters.Add("@totalrow", AseDbType.Integer).Value = totalRowCount;
parameters.Add("@maxrow", AseDbType.Integer).Value = MaxRowInversionSortNum;
AseDataAdapter dataAdapter = new AseDataAdapter((AseCommand)command);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet, "item");
DataTable dataTable = dataSet.Tables["item"];
return dataTable;
}
finally
{
connection.Close();
}
}
private List<T> GetInfos<T>(DataTable dataTable, bool isPageOverHalf) where T:IBaseClass,new ()
{
List<T> result = new List<T>();
T data=new T();
if (isPageOverHalf)
{
for (int num = dataTable.Rows.Count - 1; num >= 0; num--)
{
data = (T)data.GetInfo(dataTable.Rows[num]);
result.Add(data);
}
}
else
{
foreach (DataRow dataRow in dataTable.Rows)
{
data = (T)data.GetInfo(dataRow);
result.Add(data);
}
}
return result;
}
值得注意的是在从下查询时，应该从后向前添加到记录集中，通过下面的函数获取页码及从下查询标志的信息：
private void GetPageInfo(int totalRowCount, int startRowIndex, int maximumRows, out bool isPageOverHalf, out int pageIndex)
{
pageIndex = (startRowIndex + maximumRows) / maximumRows;
if (totalRowCount <= MaxRowInversionSortNum)
{
isPageOverHalf = false;
return;
}
decimal dTotalRowCount = (decimal)totalRowCount;
decimal dPageNum = (decimal)maximumRows;
decimal pageCount = Math.Ceiling(dTotalRowCount / dPageNum);
decimal pageHalf = Math.Ceiling(pageCount / (decimal)2);
if (pageIndex > pageHalf)
{
isPageOverHalf = true;
}
else
{
isPageOverHalf = false;
}
}
所需表的建表脚本：
alter table test_detail
drop constraint FK_TEST_DET_REFERENCE_TEST_MAI
go
if exists (select 1
from sysobjects
where id = object_id('test_detail')
and type = 'U')
drop table test_detail
go
if exists (select 1
from sysobjects
where id = object_id('test_main')
and type = 'U')
drop table test_main
go
/*==============================================================*/
/* Table: test_detail */
/*==============================================================*/
create table test_detail (
detail_id integer identity,
main_id integer null,
detail_name char(10) not null,
constraint PK_TEST_DETAIL primary key (detail_id)
)
go
/*==============================================================*/
/* Table: test_main */
/*==============================================================*/
create table test_main (
main_id integer not null,
main_name char(10) not null,
constraint PK_TEST_MAIN primary key (main_id)
)
go
alter table test_detail
add constraint FK_TEST_DET_REFERENCE_TEST_MAI foreign key (main_id)
references test_main (main_id)
go
整个源代码见附件。
源代码
