客服电话
APP下载
迪恩网络APP
随时随地掌握行业动态
官方微信
扫描二维码
关注迪恩网络微信公众号
|
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 整个源代码见附件。 |
请发表评论