- 论坛徽章:
- 0
|
转:默默ASP.NET
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
复制代码 |
|