由于客户对速度和性能上的要求越来越变态,而数据量一天天的庞大,因此本人产生了数据的查询和分页完全由客户端回调来实现。想法看上去复杂，实现起来也不难。废话不多说，看程序吧。
一、存储过程
包头：
create or replace package h_querypack is
-- author : evorul
-- created : 2007-3-29
-- purpose : 查询机构表
-- public type declarations
type mycursor is ref cursor;
procedure querylog (ret_cursor out mycursor,errorcode out int,p_logid int,p_starttime date,p_endtime date,p_operator varchar2 ,p_orderfield varchar2 ,
p_desc int,p_pagesize int,p_pageindex int,p_recordcount out int);
end h_querypack;
包体：
create or replace package body h_querypack is
-- author : evorul
-- created : 2007-3-29
-- purpose : 查询
-- 查询公司,分页用
procedure querylog (ret_cursor out mycursor,errorcode out int,p_logid int,p_starttime date,p_endtime date,p_operator varchar2 ,p_orderfield varchar2 ,
p_desc int,p_pagesize int,p_pageindex int,p_recordcount out int)
as
v_sql varchar2(3000);
v_sqlcount varchar2(3000);
v_orderfield varchar2(100);
v_order varchar2(5); --顺序
v_count int;
v_heirownum int;
v_lowrownum int;
begin
errorcode:=0;
v_sql:='select * from log where 1=1 ';
if(p_logid <> 0)then
v_sql := v_sql || ' and id = ' || to_char(p_logid);
end if;
if p_operator is not null then then
v_sql := v_sql || 'and operator like ''%' || rtrim(ltrim(p_operator))||'%''';
end if;
v_sql := v_sql ||' and (to_char(time,''yyyymmdd'') between ''' || to_char(p_starttime, 'yyyymmdd') ||''' and ''' || to_char(p_endtime, 'yyyymmdd') ||''')';
----取记录总数
v_sqlcount := 'select count(*) from (' || v_sql || ')';
execute immediate v_sqlcount into v_count;
p_recordcount := v_count;
--排序字段
if p_orderfield is not null then
v_orderfield:=p_orderfield;
else
v_orderfield:='id';
end if;
--是否降序
if p_desc <>0 then
v_order:=' asc';
else
v_order:=' desc';
end if;
v_sql:=v_sql || 'order by '|| v_orderfield || v_order;
----执行分页查询
v_heirownum := p_pageindex * p_pagesize;
v_lowrownum := v_heirownum - p_pagesize + 1;
v_sql := 'select * from (
select a.*, rownum rn from ('|| v_sql ||') a where rownum <= '|| to_char(v_heirownum) || ') b where rn >= ' || to_char(v_lowrownum) ;
open ret_cursor for v_sql;
exception
when no_data_found then
errorcode:=9999;
when others then
errorcode:=9999;
end querylog;
end h_querypack;
二、程序
dataaccess.cs
using system;
using system.data;
using system.data.oracleclient;
using system.collections;
using system.collections.specialized;
/**//// <summary>
///数据层 author: evorul date:2007-03-29
/// </summary>
public class dataaccess
...{
/**//// <summary>
/// 返回数据库连接字符串
/// </summary>
public static string databaseconnectionstring
...{
get
...{
namevaluecollection configsettings = (namevaluecollection)system.configuration.configurationmanager.getsection("appsettings");
return configsettings["connectionstring"];
}
}
/**//// <summary>
/// 返回每一页显示的纪录数
/// </summary>
public static int rowsperpage
...{
get
...{
namevaluecollection configsettings = (namevaluecollection)system.configuration.configurationmanager.getsection("appsettings");
return convert.toint32(configsettings["rowsperpage"]);
}
}
/**//// <summary>
/// 获取特定日志集合
/// </summary>
/// <param name="typeid">日志类型</param>
/// <param name="userid">操作人</param>
/// <param name="strorderfield">排序字段</param>
/// <param name="intasc">是否升序 0-降序,1-升</param>
/// <param name="pageindex">页码</param>
/// <param name="rowcount">页行数</param>
/// <param name="recordsum">符合条件的总记录数</param>
/// <returns></returns>
public static arraylist querylog(string stroperator,datetime dtstarttime,datetime dtendtime, string strorderfield,
int intasc, int pageindex, int rowcount, out int recordsum)
...{
// 返回集合
arraylist myarraylist = new arraylist();
// 创建连接
oracleconnection myconnection = new oracleconnection(databaseconnectionstring);
try
...{
// 打开连接
myconnection.open();
}
catch (exception ex)
...{
throw (ex);
}
try
...{
// 创建存储过程
oraclecommand mycommand = new oraclecommand("h_querypack.querylog", myconnection);
mycommand.commandtype = commandtype.storedprocedure;
oracledatareader dr;
// ============================== 参数定义 ==============================
// 返回值
mycommand.parameters.add("ret_cursor", oracletype.cursor);
mycommand.parameters["ret_cursor"].direction = parameterdirection.output;
oracleparameter ret = mycommand.parameters.add("errorcode", oracletype.int32);
ret.direction = parameterdirection.output;
oracleparameter retcountsum = mycommand.parameters.addwithvalue("p_recordcount", oracletype.int32);
retcountsum.direction = parameterdirection.output;
// 编号
mycommand.parameters.addwithvalue("p_logid", oracletype.int32).value = 0;
// 用户编号
mycommand.parameters.addwithvalue("p_operator", oracletype.varchar).value = stroperator;
// 时间下限
mycommand.parameters.addwithvalue("p_starttime", oracletype.datetime).value = dtstarttime;
// 时间上限
mycommand.parameters.addwithvalue("p_endtime", oracletype.datetime).value =dtendtime;
// 排序字段
mycommand.parameters.addwithvalue("p_orderfield", oracletype.varchar).value = strorderfield;
// 怎么排序
mycommand.parameters.addwithvalue("p_desc", oracletype.int32).value = intasc;
// 每页行数
mycommand.parameters.addwithvalue("p_pagesize", oracletype.int32).value = rowcount;
//页码
mycommand.parameters.addwithvalue("p_pageindex", oracletype.int32).value = pageindex;
// ============================ 参数定义完毕 ============================
// 执行存储过程
dr = mycommand.executereader();
// 执行未成功
if (convert.toint32(ret.value) != 0)
throw new exception("执行存储过程出错!");
// 总记录数
recordsum = convert.toint32(retcountsum.value);
while (dr.read())
...{
// 创建新日志
log log = new log();
//操作业务类型
if (dr["operationtype"] != dbnull.value)
...{
log.operationtype = convert.tostring(dr["operationtype"]);
}
// 时间
if (dr["time"] != dbnull.value)
log.time = convert.todatetime(dr["time"]);
// 用户
if (dr["operator"] != dbnull.value)
...{
log.operator = convert.tostring(dr["operator"]);
}
// 信息
if (dr["info"] != dbnull.value)
log.info = convert.tostring(dr["info"]);
// 加入返回集合
myarraylist.add(log);
}
dr.close();
return myarraylist;
}
catch (exception ex)
...{
throw (ex);
}
finally
...{
myconnection.close();
}
}
}
datalogic.cs
using system;
using system.data;
using system.configuration;
using system.collections;
/**//// <summary>
/// 业务逻辑层 author: evorul date:2007-03-29
/// </summary>
public class datalogic
...{
public datalogic()
...{
}
public static int recordsum = 0;
/**//// <summary>
/// 查询日志
/// </summary>
/// <param name="stroperator">操作人</param>
/// <param name="dtstartime">时间范围下限</param>
/// <param name="dtendtime">时间上限</param>
/// <param name="pageid">页码</param>
/// <returns></returns>
public static ienumerable getlogdata(string stroperator,datetime dtstartime,datetime dtendtime, string pageid)
...{
return log.getlist(stroperator,dtstartime,dtendtime,"time",1, convert.toint32(pageid),dataaccess.rowsperpage,out recordsum);
}
}
log.cs
using system;
using system.data;
using system.collections;
/**//// <summary>
/// 日志类
/// </summary>
public class log
...{
// ============================== 成员 ==============================
protected string operationtype;
/**//// <summary>
/// 时间
/// </summary>
protected datetime time = new datetime();
/**//// <summary>
/// 用户
/// </summary>
protected string m_operator;
/**//// <summary>
/// 信息
/// </summary>
protected string info = "";
// ============================== 属性 ==============================
public string operationtype
...{
get ...{ return operationtype; }
set ...{ operationtype = value; }
}
/**//// <summary>
/// 时间
/// </summary>
public datetime time
...{
get ...{ return time; }
set ...{ time = value; }
}
/**//// <summary>
/// 用户
/// </summary>
public string operator
...{
get ...{ return m_operator; }
set ...{ m_operator = value; }
}
/**//// <summary>
/// 信息
/// </summary>
public string info
...{
get ...{ return info; }
set ...{ info = value; }
}
// ============================== 方法 ==============================
/**//// <summary>
/// 创建空日志实例
/// </summary>
public log()
...{
}
/**//// <summary>
/// 新增日志
/// </summary>
public void add()
...{
try
...{
// 暂不支持该方法
throw new exception("新增日志");
}
catch (exception ex)
...{
throw (ex);
}
}
/**//// <summary>
/// 修改日志（不支持）
/// </summary>
public void modify()
...{
// 暂不支持该方法
throw new exception("修改日志");
}
/**//// <summary>
/// 删除日志
/// </summary>
public void del()
...{
// 暂不支持该方法
throw new exception("修改日志");
}
/**//// <summary>
/// 获取特定的日志集
/// </summary>
/// <param name="stroperator">操作人</param>
/// <param name="dtstarttime">开始时间</param>
/// <param name="dtendtime">结束时间</param>
/// <param name="strorderfield">排序字段</param>
/// <param name="intasc">0-降序,1-升序</param>
/// <param name="pageindex">页码</param>
/// <param name="rowcount">页行数</param>
/// <param name="recordsum">总记录数</param>
/// <returns></returns>
public static arraylist getlist(string stroperator,datetime dtstarttime,datetime dtendtime,string strorderfield,int intasc, int pageindex, int rowcount, out int recordsum)
...{
return dataaccess.querylog(stroperator,dtstarttime,dtendtime,strorderfield, intasc, pageindex, rowcount, out recordsum);
}
}
前台页 default.aspx
<%[email protected] page language="c#" autoeventwireup="true" codefile="default.aspx.cs" inherits="_default" %>
<!doctype html public "-//w3c//dtd xhtml 1.0 transitional//en" "http://www.w3.org/tr/xhtml1/dtd/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>客户端回调</title>
<style type="text/css">...
body {...}{
font-size: 12px;
color: #525252;
}
td {...}{
font-size: 12px;
color: #525252;
}
th {...}{
font-size: 12px
}
a:link {...}{
color: #000000; text-decoration: none
}
a:visited {...}{
color: #525252; text-decoration: none
}
a:hover {...}{
color: #0095a7; text-decoration: underline
}
td.alt_1 {...}{
border-top: 1px solid #d6d6d6;
border-right: 1px solid #d6d6d6;
font-size:12px;
color: #4f6b72;
}
td.alt_2 {...}{
border-top: 1px solid #d6d6d6;
border-right: 1px solid #d6d6d6;
}
td.alt_3 {...}{
border-left: 1px solid #d6d6d6;
border-bottom: 1px solid #d6d6d6;
}
td.alt_4 {...}{
border-left: 1px solid #d6d6d6;
border-right: 1px solid #d6d6d6;
}
</style>
<script type="text/javascript">...
//author: evorul date:2007-03-25
var pageindex=1;
function queryserver(objoperator,objstarttime,objendtime,intindex,boolreset)
...{
context = gridspan;
context.innerhtml = "<img src='http://www.VeVb.com/htmldata/images/pie.gif' />数据加载中...";
arg = "servermethodquery|" + objoperator.value.replace(/$/g,"")+"$"+ objstarttime.value.replace(/$/g,"")+"$"+ objendtime.value.replace(/$/g,"") +"$"+ intindex.tostring().replace(/$/g,"")+"$"+ boolreset.tostring().replace(/$/g,"");
<%= clientscript.getcallbackeventreference(this, "arg", "receiveserverdata", "context")%>;
}
function receiveserverdata(result, context)
...{
context.innerhtml = (result.split('$'))[0];
var t1=document.getelementbyid("recordsum");
var t2=document.getelementbyid("pagesum");
var t3=document.getelementbyid("currentpage");
var t5=document.getelementbyid("linkup");
var t6=document.getelementbyid("linkdown");
var t7=document.getelementbyid("downlistindex");
t1.innerhtml = (result.split('$'))[1];
t2.innerhtml = (result.split('$'))[2];
t3.innerhtml = (result.split('$'))[3];
pageindex=eval((result.split('$'))[3]);
if(pageindex>1)
...{
t5.innerhtml="<a href="#" +(pageindex-1).tostring()+",'false')">上一页</a>";
}
else
t5.innerhtml = "上一页";
if(pageindex< eval((result.split('$'))[2]))
...{
t6.innerhtml="<a href="#" +(pageindex+1).tostring()+",'false')">下一页</a>";
}
else
t6.innerhtml="下一页";
if(result.split('$').length>4)
...{
var t4=document.getelementbyid("spanindex");
t4.innerhtml = (result.split('$'))[4];
}
t7.value=pageindex;
}
function functionpageload()
...{
if(document.readystate!="complete") return;
context = gridspan;
arg = "servermethodquery|" +"$"+"1753-1-1"+ "$"+"9999-12-31"+ "$"+ "1"+"$"+ "true";
<%= clientscript.getcallbackeventreference(this, "arg", "receiveserverdata", "context")%>;
//页面加载完后执行的代码
}
//页面加载状态改变时执行的方法
document.onreadystatechange=functionpageload;
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<table cellpadding="0" cellspacing="0" border="0" id="table1">
<tr>
<td align="center" valign="top">
<br />
<table cellpadding="0" cellspacing="0" border="0" width="95%">
<tr>
<td><img src="http://www.VeVb.com/htmldata/images/round-1.gif" width="13" height="30" alt="" /></td>
<td class="alt_1">
日志查询</td>
</tr>
<tr>
<td class="alt_4" valign="top" colspan="2" align="center">
<table cellpadding="0" cellspacing="0" border="0" width="95%">
<tr>
<td align="right">
操作人员：</td>
<td align="left" >
<asp:textbox id="txtoperator" runat="server" cssclass="border" readonly="false"
width="90px"></asp:textbox></td>
<td align="right">
操作时间：</td>
<td align="left">
<asp:textbox id="txtstarttime" runat="server" cssclass="border"
readonly="false" width="90px"></asp:textbox>-<asp:textbox
id="txtendtime" runat="server" cssclass="border"
readonly="false" width="90px"></asp:textbox></td>
</tr>
<tr>
<td colspan="4" ></td>
</tr>
<tr>
<td colspan="4" >
<asp:button id="submit" runat="server" cssclass="btn2" text=" 确定 " height="20px" width="50px" /></td>
</tr>
</table>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<table cellpadding="0" cellspacing="0" border="0" width="100%">
<tr>
<td class="alt_3"> </td>
<td></td>
</tr>
</table>
</td>
</tr>
</table>
<span id="gridspan">
<asp:gridview id="logs" runat="server" autogeneratecolumns="false"
gridlines="horizontal" pagesize="15"
width="95%" backcolor="white" bordercolor="#336666" borderstyle="double" borderwidth="3px" cellpadding="4">
<rowstyle forecolor="#333333" height="24px" backcolor="white" />
<selectedrowstyle bordercolor="red" backcolor="#339966" font-bold="true" forecolor="white" />
<headerstyle backcolor="#336666" forecolor="white" height="30px" font-bold="true" />
<alternatingrowstyle borderwidth="1px" />
<columns>
<asp:templatefield headertext="用户">
<itemtemplate>
<asp:hyperlink id="hyperlink1" runat="server" navigateurl="" text='<%# eval("userinfo.name") %>'></asp:hyperlink>
</itemtemplate>
</asp:templatefield>
<asp:templatefield headertext="类型">
<itemtemplate>
<asp:hyperlink id="hyperlink2" runat="server" navigateurl="" text='<%# eval("type.name") %>'></asp:hyperlink>
</itemtemplate>
</asp:templatefield>
<asp:templatefield headertext="时间">
<itemtemplate>
<asp:hyperlink id="hyperlink4" runat="server" navigateurl="" text='<%# eval("time") %>'></asp:hyperlink>
</itemtemplate>
</asp:templatefield>
<asp:templatefield headertext="备注">
<itemtemplate>
<asp:hyperlink id="hyperlink5" runat="server" navigateurl="" text='<%# eval("info") %>'></asp:hyperlink>
</itemtemplate>
</asp:templatefield>
</columns>
<footerstyle backcolor="white" forecolor="#333333" />
<pagerstyle backcolor="#336666" forecolor="white" horizontalalign="center" />
</asp:gridview>
</span>
<table border="0" cellpadding="0" cellspacing="0" bordercolorlight="#000000" bordercolordark="#ffffff">
<tr>
<td align="center" nowrap >
共有<asp:label id="recordsum" runat="server" />条<asp:label id="pagesum" runat="server" />页结果
当前显示为第<asp:label id="currentpage" runat="server" />页 15条/页
<asp:label id="linkup" runat="server" >上一页</asp:label>
<asp:label id="linkdown" runat="server" >下一页</asp:label>
跳转至第<span id="spanindex"><asp:dropdownlist id="downlistindex" runat="server" width="50px">
</asp:dropdownlist></span>页
</td>
</tr>
</table>
<asp:objectdatasource id="datasourcelog" runat="server" typename="datalogic" selectmethod="getlogdata">
<selectparameters>
<asp:controlparameter controlid="txtoperator" defaultvalue=" " name="stroperator" propertyname="text"
type="string" />
<asp:controlparameter controlid="txtstarttime" defaultvalue="1753-1-1" name="dtstartime"
propertyname="text" type="datetime" />
<asp:controlparameter controlid="txtendtime" defaultvalue="9999-12-31" name="dtendtime"
propertyname="text" type="datetime" />
<asp:controlparameter name="pageid" controlid="downlistindex" defaultvalue="1" propertyname="selectedvalue" type="string" />
</selectparameters>
</asp:objectdatasource>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
default.aspx.cs
using system;
using system.data;
using system.configuration;
using system.web;
using system.io;
using system.text;
using system.web.security;
using system.web.ui;
using system.web.ui.webcontrols;
using system.web.ui.htmlcontrols;
using system.globalization;
public partial class _default : system.web.ui.page,icallbackeventhandler
...{
protected void page_load(object sender, eventargs e)
...{
this.submit.attributes.add("onclick", "queryserver(txtoperator,txtstarttime,txtendtime,1,"true");return false;");
this.downlistindex.attributes.add("onchange", "queryserver(txtoperator,txtstarttime,txtendtime,this.value,"false");return false;");
}
回调分页#region 回调分页
private string serverreturn;
public string getcallbackresult()
...{
string[] parts = serverreturn.split('|');
//根据传递的方法名进行调用，并传递相应的参数，目前只支持一个参数
return (string)gettype().getmethod(parts[0]).invoke(this, new object[] ...{ parts[1] });
}
public void raisecallbackevent(string eventargument)
...{
serverreturn = eventargument;
}
/**//// <summary>
/// 根据从客户端传来的值，对gridview的内容进行更新，并将更新后的gridview的html返回
/// </summary>
/// <param name="arg"></param>
/// <returns></returns>
public string servermethodquery(string arg)
...{
logs.datasourceid = "datasourcelog";
string[] arrayarg = arg.split('$');
this.txtoperator.text = arrayarg[0];
this.txtstarttime.text= arrayarg[1];
this.txtendtime.text = arrayarg[2];
intialpageselect();
this.downlistindex.selectedvalue = arrayarg[3];
logs.databind();
//传入客户端字符串,并用"$"分割
stringbuilder strhtml = new stringbuilder();
strhtml.append(rendercontrol(logs));
strhtml.append("$");
strhtml.append(datalogic.recordsum.tostring());
strhtml.append("$");
strhtml.append(convert.tostring(datalogic.recordsum / dataaccess.rowsperpage + 1));
strhtml.append("$");
strhtml.append(arrayarg[3]);
if (arrayarg[4] == "true")
...{
strhtml.append("$");
intialpageselect();
strhtml.append(rendercontrol(downlistindex));
}
return strhtml.tostring();
}
private string rendercontrol(control control)
...{
stringwriter writer1 = new stringwriter(cultureinfo.invariantculture);
htmltextwriter writer2 = new htmltextwriter(writer1);
control.rendercontrol(writer2);
writer2.flush();
writer2.close();
return writer1.tostring();
}
/**//// <summary>
/// 初始化页下拉单
/// </summary>
private void intialpageselect()
...{
downlistindex.items.clear();
for (int i = 0; i < (datalogic.recordsum / dataaccess.rowsperpage + 1); i++)
...{
this.downlistindex.items.add(convert.tostring(i + 1));
}
}
#endregion
}
如有错误，欢迎指正!
新闻热点
疑难解答
