实现分页效果
存储过程:
create or replace package JT_P_page is
type type_cur is ref cursor; --定义游标变量用于返回记录集
procedure Pagination (Pindex in number, --要显示的页数索引,从0开始
Psql in varchar2, --产生分页数据的查询语句
Psize in number, --每页显示记录数
Pcount out number, --返回的分页数
Prowcount out number, --返回的记录数
v_cur out type_cur --返回分页数据的游标
);
end JT_P_page;主体:
create or replace package body JT_P_page is
procedure Pagination(Pindex in number, --要显示的页数索引,从0开始
Psql in varchar2, --产生分页数据的查询语句
Psize in number, --每页显示记录数
Pcount out number, --返回的分页数
Prowcount out number, --返回的记录数
v_cur out type_cur --返回分页数据的游标
) AS
v_sql VARCHAR2(1000);
v_Pbegin number;
v_Pend number;
begin
v_sql := 'select count(*) from (' || Psql || ')';
execute immediate v_sql into Prowcount; --计算记录总数
Pcount := ceil(Prowcount / Psize); --计算分页总数
--显示任意页内容
v_Pend := Pindex * Psize + Psize;
v_Pbegin := v_Pend - Psize + 1;
v_sql := 'select * from (' || Psql || ') where rn between ' || v_Pbegin || ' and ' || v_Pend;
open v_cur for v_sql;
end Pagination;
end JT_P_page;c#后台代码:
#region 获取分页列表
/// <summary>
/// 获取分页列表
/// </summary>
/// <param name="CurrentPage">当前页码</param>
/// <param name="parameterTotalCount">总记录</param>
/// <param name="parameterTotalPage">总页数</param>
/// <returns></returns>
public static DataTable GetCarBrandPage(string strWhere, int CurrentPage,int pageSize, out int parameterTotalCount, out int parameterTotalPage)
{
DBConnection db = new DBConnection();
strSqls.Remove(0, strSqls.Length);
strSqls.Append(" * ");
pagination.Column = strSqls.ToString();
pagination.Table = " OPERATIONS ";
pagination.Where = strWhere;
pagination.OrderColumn = "ops_date";
pagination.OrderType = "DESC";
pagination.PageSize = pageSize;
pagination.CurrentPage = CurrentPage;
return db.GetOnePageTable(pagination, out parameterTotalCount, out parameterTotalPage);
}
#endregion
GetOnePageTable函数:
#region 分页通用函数
/// <summary>
/// 分页通用函数
/// </summary>
/// <param name="parameterPagination">分页对象</param>
/// <param name="parameterTotalCount">返回总记录数</param>
/// <param name="parameterTotalPage">返回总页数</param>
/// <returns>返回数据表</returns>
public DataTable GetOnePageTable(Pagination parameterPagination, out int parameterTotalCount, out int parameterTotalPage)
{
int totalCount, totalPage;
OleDbParameter[] parms = new OleDbParameter[9];
parms[0] = paraInstance(":Pindex", OleDbType.Integer, 4, null, ParameterDirection.Input);
parms[1] = paraInstance(":Psql", OleDbType.VarChar, 500, null, ParameterDirection.Input);
parms[2] = paraInstance(":Psize", OleDbType.Integer, 4, null, ParameterDirection.Input);
parms[3] = paraInstance(":Pcount", OleDbType.Integer, 4, null, ParameterDirection.Output);
parms[4] = paraInstance(":Prowcount", OleDbType.Integer, 4, null, ParameterDirection.Output);
parms[5] = paraInstance(":v_cur", OleDbType.type_cur, 4, null, ParameterDirection.Output);
DataTable dt = GetRecordForShowPage("JT_P_page.Pagination", CommandType.StoredProcedure, out totalCount, out totalPage, parms);
parameterTotalCount = totalCount;
parameterTotalPage = totalPage;
return dt;
}
#endregionGetRecordForShowPage函数
#region 带有out参数的分页执行方法
/// <summary>
/// 存储过程分页专用函数,返回一个数据表
/// </summary>
/// <param name="sql">sql语句或存储过程</param>
/// <param name="cmdType">语句类型,sql语句或存储过程</param>
/// <param name="totalCount">总记录</param>
/// <param name="totalPage">总页数</param>
/// <param name="parameters">参数列表</param>
/// <returns></returns>
public DataTable GetRecordForShowPage(string sql, CommandType cmdType, out int totalCount, out int totalPage, params OleDbParameter[] parameters)
{
DataTable dt = new DataTable(); init();
OleDbDataAdapter sda = new OleDbDataAdapter(sql, conn); object a = null, b = null;
sda.SelectCommand.CommandType = cmdType;
sda.SelectCommand.CommandTimeout = 1800;
if (parameters != null)
{
foreach (OleDbParameter p in parameters)
sda.SelectCommand.Parameters.Add(p);
}
try
{
sda.Fill(dt);
}
catch (Exception e)
{
throw e;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
a = sda.SelectCommand.Parameters[0].Value;
b = sda.SelectCommand.Parameters[1].Value;
if (a != null && b != null && a.ToString() != "" && b.ToString() != "")
{
totalCount = int.Parse(sda.SelectCommand.Parameters[0].Value.ToString());
totalPage = int.Parse(sda.SelectCommand.Parameters[1].Value.ToString());
}
else
{
totalCount = 0;
totalPage = 0;
}
sda.SelectCommand.Parameters.Clear();
sda.Dispose();
} return dt;
}
#endregion
不知道为什么,总是查询不出数据。
存储过程:
create or replace package JT_P_page is
type type_cur is ref cursor; --定义游标变量用于返回记录集
procedure Pagination (Pindex in number, --要显示的页数索引,从0开始
Psql in varchar2, --产生分页数据的查询语句
Psize in number, --每页显示记录数
Pcount out number, --返回的分页数
Prowcount out number, --返回的记录数
v_cur out type_cur --返回分页数据的游标
);
end JT_P_page;主体:
create or replace package body JT_P_page is
procedure Pagination(Pindex in number, --要显示的页数索引,从0开始
Psql in varchar2, --产生分页数据的查询语句
Psize in number, --每页显示记录数
Pcount out number, --返回的分页数
Prowcount out number, --返回的记录数
v_cur out type_cur --返回分页数据的游标
) AS
v_sql VARCHAR2(1000);
v_Pbegin number;
v_Pend number;
begin
v_sql := 'select count(*) from (' || Psql || ')';
execute immediate v_sql into Prowcount; --计算记录总数
Pcount := ceil(Prowcount / Psize); --计算分页总数
--显示任意页内容
v_Pend := Pindex * Psize + Psize;
v_Pbegin := v_Pend - Psize + 1;
v_sql := 'select * from (' || Psql || ') where rn between ' || v_Pbegin || ' and ' || v_Pend;
open v_cur for v_sql;
end Pagination;
end JT_P_page;c#后台代码:
#region 获取分页列表
/// <summary>
/// 获取分页列表
/// </summary>
/// <param name="CurrentPage">当前页码</param>
/// <param name="parameterTotalCount">总记录</param>
/// <param name="parameterTotalPage">总页数</param>
/// <returns></returns>
public static DataTable GetCarBrandPage(string strWhere, int CurrentPage,int pageSize, out int parameterTotalCount, out int parameterTotalPage)
{
DBConnection db = new DBConnection();
strSqls.Remove(0, strSqls.Length);
strSqls.Append(" * ");
pagination.Column = strSqls.ToString();
pagination.Table = " OPERATIONS ";
pagination.Where = strWhere;
pagination.OrderColumn = "ops_date";
pagination.OrderType = "DESC";
pagination.PageSize = pageSize;
pagination.CurrentPage = CurrentPage;
return db.GetOnePageTable(pagination, out parameterTotalCount, out parameterTotalPage);
}
#endregion
GetOnePageTable函数:
#region 分页通用函数
/// <summary>
/// 分页通用函数
/// </summary>
/// <param name="parameterPagination">分页对象</param>
/// <param name="parameterTotalCount">返回总记录数</param>
/// <param name="parameterTotalPage">返回总页数</param>
/// <returns>返回数据表</returns>
public DataTable GetOnePageTable(Pagination parameterPagination, out int parameterTotalCount, out int parameterTotalPage)
{
int totalCount, totalPage;
OleDbParameter[] parms = new OleDbParameter[9];
parms[0] = paraInstance(":Pindex", OleDbType.Integer, 4, null, ParameterDirection.Input);
parms[1] = paraInstance(":Psql", OleDbType.VarChar, 500, null, ParameterDirection.Input);
parms[2] = paraInstance(":Psize", OleDbType.Integer, 4, null, ParameterDirection.Input);
parms[3] = paraInstance(":Pcount", OleDbType.Integer, 4, null, ParameterDirection.Output);
parms[4] = paraInstance(":Prowcount", OleDbType.Integer, 4, null, ParameterDirection.Output);
parms[5] = paraInstance(":v_cur", OleDbType.type_cur, 4, null, ParameterDirection.Output);
DataTable dt = GetRecordForShowPage("JT_P_page.Pagination", CommandType.StoredProcedure, out totalCount, out totalPage, parms);
parameterTotalCount = totalCount;
parameterTotalPage = totalPage;
return dt;
}
#endregionGetRecordForShowPage函数
#region 带有out参数的分页执行方法
/// <summary>
/// 存储过程分页专用函数,返回一个数据表
/// </summary>
/// <param name="sql">sql语句或存储过程</param>
/// <param name="cmdType">语句类型,sql语句或存储过程</param>
/// <param name="totalCount">总记录</param>
/// <param name="totalPage">总页数</param>
/// <param name="parameters">参数列表</param>
/// <returns></returns>
public DataTable GetRecordForShowPage(string sql, CommandType cmdType, out int totalCount, out int totalPage, params OleDbParameter[] parameters)
{
DataTable dt = new DataTable(); init();
OleDbDataAdapter sda = new OleDbDataAdapter(sql, conn); object a = null, b = null;
sda.SelectCommand.CommandType = cmdType;
sda.SelectCommand.CommandTimeout = 1800;
if (parameters != null)
{
foreach (OleDbParameter p in parameters)
sda.SelectCommand.Parameters.Add(p);
}
try
{
sda.Fill(dt);
}
catch (Exception e)
{
throw e;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
a = sda.SelectCommand.Parameters[0].Value;
b = sda.SelectCommand.Parameters[1].Value;
if (a != null && b != null && a.ToString() != "" && b.ToString() != "")
{
totalCount = int.Parse(sda.SelectCommand.Parameters[0].Value.ToString());
totalPage = int.Parse(sda.SelectCommand.Parameters[1].Value.ToString());
}
else
{
totalCount = 0;
totalPage = 0;
}
sda.SelectCommand.Parameters.Clear();
sda.Dispose();
} return dt;
}
#endregion
不知道为什么,总是查询不出数据。
解决方案 »
- 在线求助
- 将二维数组添加到一个表绑定到Gridview
- 静态生成的问题
- 关于Access:ASP可将视图当存储过程用,ASP.NET怎么写法?
- 如何在ASP.net下实现像QQ或迅雷一样的从下面浮上个提醒消息框?
- 在代码中用什么方法能清除treeview生成的所有节点???
- 在asp.net中MailMessage和SmtpMail类发送邮件的问题
- sql表如何把表结构及描述导入WORD中?(在线等)
- WebForm,DataGrid指定数据集dataSet,居然dataGrid无法显示,大家知道怎么回事啊?
- 将中文参数编码的问题
- 设计时的特殊字体如何导入到服务器
- asp.net mvc中上传图片立即显示怎么弄啊
参数名: value 在网上也没查到