介绍:
包括ASP.NET和ASP.NET AJAX + JSON两个DEMO,最低耗时35ms左右(AJAX+JSON)。
百万级数据库查询分页,使用Oracle 存储过程。
代码生成的案例:
Asp.net 2.0 + ,Oracle 10g +
开发环境:ASP.Net 2.0 - 4.5, Oracle 11g r2
项目开源发布,供下载学习。需要自己建一个TEST数据表,包括3个字段ID,COL1,COL2。
描述:
实现效果
包括ASP.NET和ASP.NET AJAX + JSON两个DEMO,最低耗时35ms左右(AJAX+JSON)。
Oracle Select 查询效率对比
select * from (select rownum ro,t.* from test t where rownum<=900015) where ro>900000; --0.546s Using
select * from test;--Memery Overflow after 505.21s
select * from (select ROW_NUMBER() OVER(ORDER BY id) ro,t.* from test t) where ro>900000 and ro<=900015; --3.791s
select * from test where id>900000 and id <=900015 ; --0.172s
前台AJAX请求使用jQuery,对后台返回的json进行处理。
测试视频:视频(Windows屏幕录像专家录屏)
调用方法:
<span style="font-size:12px;">protected void Page_Load(object sender, EventArgs e)
{
Paginator1.QueryTable = "test";//表名,必须
Paginator1.QuerySelect = "rownum 行号,id as 编号,col1 栏目1,col2 as 栏目2" ;
Paginator1.QuerySize = 15;
Paginator1.QueryConn = ConfigurationManager.ConnectionStrings["Paginator" ].ToString();
Paginator1.QueryWhere = "";
Paginator1.QueryOrder = "";
} </span>
Source Code Files
- Paginator.ascx #1 - 分页控件.
- Paginator.ascx.cs #2 - 分页控件后台源码.
- Paginator.ascx.designer.cs #3 - 不解释
- SQL_Test.sql #4 - SQL 备份
- style.css #5 - 样式表
- Default.aspx #6 - 无AJAX实例
- Default.aspx.cs #7 -
- Default.aspx.designer.cs #8 -
- DemoWithAjax.aspx #9 - 有AJAX的Demo
- DemoWithAjax.aspx.cs #10 -
- DemoWithAjax.aspx.designer.cs #11 -
- EDI_Paginator.sql #12 - 分页SQL
- 其他文件
More Information
存储过程源码:
<span style="font-size:12px;">/*
* 百万级分页存储过程
* Modified by Willin Wang
* Jul 23rd, 2012.
* http://www.willin.org
*/
create or replace procedure edi_paginator
(
parm_pageID in number, --页索引,从1开始
parm_pageSize in number, --每页大小
parm_tblName in varchar2, --表名
parm_strSelect in varchar2, --查询内容
parm_strWhere in varchar2, --where条件
parm_orderBy in varchar2, --排序条件
parm_ref_cur1 out SYS_REFCURSOR, --总记录数
parm_ref_cur2 out SYS_REFCURSOR --结果集
)
IS
v_sql1 VARCHAR2 (2000); --获取数据的sql语句
v_sql2 VARCHAR2 (4000);
v_pagecount NUMBER ; --该条件下记录页数
v_row_start NUMBER ; --开始记录
v_row_end NUMBER ; --结束记录
v_count NUMBER;
--分页获取数据
BEGIN
v_sql1 := 'select count(1) from ' || parm_tblName ;
IF parm_strWhere is not NULL OR parm_strWhere <> ''
THEN
v_sql1 := v_sql1 || ' where 1=1 ' || parm_strWhere;
END IF ;
EXECUTE IMMEDIATE v_sql1 INTO v_count;
OPEN parm_ref_cur1 for v_sql1;
--计算数据记录开始和结束
v_pageCount := v_count / parm_pageSize + 1 ;
v_row_start := (parm_pageID - 1 ) * parm_pageSize + 1;
v_row_end := parm_pageID * parm_pageSize ;
v_sql2 := 'select rownum ro, t.* from ' || parm_tblName || ' t';
v_sql2 := v_sql2 || ' where rownum<=' || v_row_end;
IF parm_strWhere is not NULL OR parm_strWhere <> ''
THEN
v_sql2 := v_sql2 || parm_strWhere ;
END IF ;
IF parm_orderBy is not null and parm_orderBy <> ''
THEN
v_sql2 := v_sql2 || ' order by ' || parm_orderBy;
END IF ;
v_sql2 := 'select '|| parm_strSelect||' from (' || v_sql2 || ') where ro >=' || v_row_start ;
--DBMS_OUTPUT.put_line (v_sql2);
OPEN parm_ref_cur2 FOR v_sql2;
END edi_paginator; </span>
核心jQuery代码:
<script type="text/javascript">
//<![CDATA[
if (window.jQuery) {
jQuery(document).ready( function (w) {
w( '.bread input').live('click' , function () {
w( '#<% =this.hdCMD.ClientID %>' ).val(w(this).attr( 'id'));
});
w( 'form').live('submit' , function () {
w.ajax({
url: w( this).attr('action' ),
type: 'POST',
data: w( this).serialize() + "&pjan=true&querypage=" + w('#<%=this.lblCurPage.ClientID %>').html(),
dataType: 'JSON',
success: function (result) {
//alert(result);
var data = result.Table1, meta = result.Table2, tmpHtml = "";
w.each(data, function (rowid, row) {
if (rowid % 2 == 0)
tmpHtml += "\<tr style='background-color:#EFF3FB;'>" ;
else
tmpHtml += "\<tr>";
w.each(row, function (colid, col) {
tmpHtml += "\<td>" + col + "\<\/td>" ;
});
tmpHtml += "\<\/tr>";
});
w( '#<% =this.PageList.ClientID %> tr:gt(0)' ).remove();
w( '#<% =this.PageList.ClientID %> tr:eq(0)' ).after(tmpHtml);
w( '#<% =this.lblCurPage.ClientID %>' ).html(meta[1].Meta);
w( '#<% =this.lblTimestamp.ClientID %>' ).html(meta[3].Meta);
}
});
return false ;
});
});
}
/*]]>*/
</script>