asp.net 分页类库，支持sql和oracle存储过程

最新推荐文章于 2025-06-23 12:43:33 发布

suncheng_hong

最新推荐文章于 2025-06-23 12:43:33 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： asp.net/c# 文章标签： sql 存储 oracle asp.net dataset string

本文链接：https://blog.youkuaiyun.com/suncheng_hong/article/details/6293864

asp.net/c# 专栏收录该内容

6 篇文章

订阅专栏

本文介绍了一种使用Oracle数据库进行分页查询的方法，包括通过SQL语句和存储过程两种方式实现分页功能。提供了详细的代码示例，展示了如何构建分页查询逻辑及页面导航。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

DbUtil，用来操作数据库，和调用存储过程：

namespace DBsys { public class DbUtil { public OracleConnection conn; public DbUtil() { conn = new OracleConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]); } /* * 执行增删改操作 * 参数 sql语句 * * */ public void updateData(string sql) { try { openConn(); } catch (Exception e) { throw new Exception("在打开连接时出现连接级别的错误！"); } OracleCommand cmd = new OracleCommand(sql, conn); try { cmd.ExecuteNonQuery(); } catch (Exception e) { // throw new TourException("在对锁定的行执行该命令期间发生了异常"); throw e; } try { closeConn(); } catch (Exception e) { throw e; } } public DataSet getDataSet(string sql) { try { openConn(); } catch (Exception e) { throw e; } //OracleCommand cmd = new OracleCommand(sql,conn); //OracleDataReader oda = cmd.ExecuteReader(); //while (oda.Read()) //{ // oda. //} OracleDataAdapter sda = new OracleDataAdapter(sql, conn); DataSet ds = new DataSet(); try { sda.Fill(ds); } catch (Exception e) { throw e; } try { closeConn(); } catch (Exception e) { throw e; } return ds; } public int totalRec(string sql) { OracleCommand ocmd = new OracleCommand(); OracleParameter op = new OracleParameter("sql_str", OracleType.VarChar); OracleParameter op1 = new OracleParameter(); op1.OracleType = OracleType.Number; op1.Direction = ParameterDirection.ReturnValue; op.Value = sql; ocmd.Parameters.Add(op); ocmd.Parameters.Add(op1); ocmd.Connection = conn; ocmd.CommandText = "f_page_totalRec"; ocmd.CommandType = CommandType.StoredProcedure; openConn(); ocmd.ExecuteNonQuery(); closeConn(); return int.Parse(op1.Value.ToString()); } public DataSet pageRs(string sql, string pk, string px, int n1, int n2) { openConn(); OracleParameter p_mycs = new OracleParameter("mycs", OracleType.Cursor); OracleParameter p_pk = new OracleParameter("pk", OracleType.VarChar); OracleParameter p_px = new OracleParameter("px", OracleType.VarChar); OracleParameter p_n1 = new OracleParameter("n1", OracleType.Number); OracleParameter p_n2 = new OracleParameter("n2", OracleType.Number); OracleParameter p_sql_t = new OracleParameter("sql_t", OracleType.VarChar); p_mycs.Direction = ParameterDirection.Output; p_pk.Direction = ParameterDirection.Input; p_px.Direction = ParameterDirection.Input; p_n1.Direction = ParameterDirection.Input; p_n2.Direction = ParameterDirection.Input; p_sql_t.Direction = ParameterDirection.Input; p_pk.Value = pk; p_px.Value = px; p_n1.Value = n1; p_n2.Value = n2; p_sql_t.Value = sql; OracleCommand ocmd = new OracleCommand("pk_page.p_wt", conn); ocmd.CommandType = CommandType.StoredProcedure; ocmd.Parameters.Add(p_mycs); ocmd.Parameters.Add(p_pk); ocmd.Parameters.Add(p_px); ocmd.Parameters.Add(p_n1); ocmd.Parameters.Add(p_n2); ocmd.Parameters.Add(p_sql_t); OracleDataAdapter da = new OracleDataAdapter(ocmd); DataSet ds = new DataSet(); da.Fill(ds); closeConn(); return ds; } /* * 关闭连接 * */ public void closeConn() { try { if (conn.State != ConnectionState.Closed) { conn.Close(); } } catch (Exception e) { throw e; } } private void openConn() { try { if (conn.State != ConnectionState.Open) { conn.Open(); } } catch (InvalidOperationException ee) { throw ee; } catch (Exception eee) { throw eee; } } } }

PageUtilAbs，分页的骨架类：

namespace PageSys { public abstract class PageUtilAbs { protected string pageUrl;//当前页面相对路径 protected int totalPage;//总页数 protected int prePageIndex;//上一页 protected int nextPageIndex;//下一页 protected int currPageIndex;//当前页 protected int totalRec;//总记录数 protected int pageSize;//每页几条记录 protected string sql; //sql protected string pk; //主键 protected string desc;//排序 public abstract int computeTotalRec(); public abstract DataSet pageDataSet(); public void build(int currPageIndex, int pageSize, string pageUrl, string sql, string pk, string desc){ this.currPageIndex = currPageIndex; this.pageSize = pageSize; this.pageUrl = pageUrl; this.sql = sql; this.pk = pk; this.desc = desc; init(); } private void init() { //总页数 totalRec = computeTotalRec(); totalPage = totalRec / pageSize; if (totalRec % pageSize > 0) totalPage = totalPage + 1; } public string createPageFooter() { StringBuilder str = new StringBuilder(); this.prePageIndex = currPageIndex - 1; this.nextPageIndex = currPageIndex + 1; if (currPageIndex > 1) { str.Append( "<a href="" + this.pageUrl + "&page=1&pageSize=" + this.pageSize + "" mce_href="" + this.pageUrl + "&page=1&pageSize=" + this.pageSize + "">首页</a> "); } else { str.Append("首页 "); } if (currPageIndex > 1) { str.Append( "<a href="" + this.pageUrl + "&page=" + this.prePageIndex + "&pageSize=" + this.pageSize + "" mce_href="" + this.pageUrl + "&page=" + this.prePageIndex + "&pageSize=" + this.pageSize + "">上页</a> "); } else { str.Append("上页 "); } str.Append(" 当前" + this.currPageIndex + "页 "); if (currPageIndex < totalPage) { str.Append( "<a href="" + this.pageUrl + "&page=" + this.nextPageIndex + "&pageSize=" + this.pageSize + "" mce_href="" + this.pageUrl + "&page=" + this.nextPageIndex + "&pageSize=" + this.pageSize + "">下页</a> "); } else { str.Append("下页"); } if (totalPage > 1 && currPageIndex != totalPage) { str.Append( "<a href="" + this.pageUrl + "&page=" + this.totalPage + "&pageSize=" + this.pageSize + "" mce_href="" + this.pageUrl + "&page=" + this.totalPage + "&pageSize=" + this.pageSize + "">末页</a> "); } else { str.Append("末页"); } str.Append(" 共" + totalRec + "条记录"); str.Append(" 每页<SELECT size=1 name=pagesize onchange=/"window.location.href=this.value/">"); if (pageSize == 3) { str.Append("<OPTION value='" + this.pageUrl + "&pageSize=3' selected>3</OPTION>"); } else { str.Append("<OPTION value='" + this.pageUrl + "&pageSize=3'>3</OPTION>"); } if (pageSize == 10) { str.Append("<OPTION value='" + this.pageUrl + "&pageSize=10' selected>10</OPTION>"); } else { str.Append("<OPTION value='" + this.pageUrl + "&pageSize=10'>10</OPTION>"); } if (pageSize == 20) { str.Append("<OPTION value='" + this.pageUrl + "&pageSize=20' selected>20</OPTION>"); } else { str.Append("<OPTION value='" + this.pageUrl + "&pageSize=20'>20</OPTION>"); } if (pageSize == 50) { str.Append("<OPTION value='" + this.pageUrl + "&pageSize=50' selected>50</OPTION>"); } else { str.Append("<OPTION value='" + this.pageUrl + "&pageSize=50'>50</OPTION>"); } if (pageSize == 100) { str.Append("<OPTION value='" + this.pageUrl + "&pageSize=100' selected>100</OPTION>"); } else { str.Append("<OPTION value='" + this.pageUrl + "&pageSize=100'>100</OPTION>"); } str.Append("</SELECT>"); str.Append("条分" + totalPage + "页显示转到"); str.Append("<SELECT size=1 name=Pagelist onchange=/"window.location.href=this.value/">"); for (int i = 1; i < totalPage + 1; i++) { if (i == currPageIndex) { str.Append("<OPTION value='" + this.pageUrl + "&page=" + i + "&pageSize=" + this.pageSize + "'"+" selected>" + i + "</OPTION>"); } else { str.Append("<OPTION value='" + this.pageUrl + "&page=" + i + "&pageSize=" + this.pageSize + "'>" + i + "</OPTION>"); } } str.Append("</SELECT>页"); return str.ToString(); } } }

SQL分页类：

namespace PageSys { public class PageUtil : PageUtilAbs { private DbUtil du = new DbUtil(); public PageUtil(int currPageIndex, int pageSize, string pageUrl, string sql, string pk, string desc) { base.build(currPageIndex, pageSize, pageUrl, sql, pk, desc); } public override int computeTotalRec() { StringBuilder sb = new StringBuilder(); sb.Append("select count(*) c from"); sb.Append("("); sb.Append(this.sql); sb.Append(")count_table"); DataSet ds = du.getDataSet(sb.ToString()); return Convert.ToInt32(ds.Tables[0].Rows[0]["c"].ToString()); } public override DataSet pageDataSet() { int n1 = (Convert.ToInt32(currPageIndex) - 1) * Convert.ToInt32(pageSize) + 1; int n2 = n1 + Convert.ToInt32(pageSize) - 1; StringBuilder sb = new StringBuilder(); sb.Append("select tb1.* from"); sb.Append("("); sb.Append("select tb.*,row_number() over(order by " + this.pk + " " + this.desc + ")rn from"); sb.Append("("); sb.Append(this.sql); sb.Append(")tb"); sb.Append(")tb1"); sb.Append(" where tb1.rn<=" + n2); sb.Append(" and tb1.rn>=" + n1); return du.getDataSet(sb.ToString()); } } }

存储过程分页类：

namespace PageSys { public class PageUtilProc : PageUtilAbs { private DbUtil du = new DbUtil(); public PageUtilProc(int currPageIndex, int pageSize, string pageUrl, string sql, string pk, string desc) { base.build(currPageIndex, pageSize, pageUrl, sql, pk, desc); } public override int computeTotalRec() { int rec = du.totalRec(this.sql); return rec; } public override DataSet pageDataSet() { int n1 = (Convert.ToInt32(currPageIndex) - 1) * Convert.ToInt32(pageSize) + 1; int n2 = n1 + Convert.ToInt32(pageSize) - 1; DataSet ds = du.pageRs(this.sql, this.pk, this.desc, n1, n2); return ds; } } }

查询总数的oracle函数：

create or replace function f_page_totalRec( sql_str in varchar2 ) return integer as num integer; sql_n varchar2(1000); begin num := 0; sql_n := 'select count(1) from('||sql_str||')count_table'; execute IMMEDIATE sql_n into num; return num; end;

存储过程包头：

create or replace package pk_page is type mytype is ref cursor; procedure p_wt( mycs out mytype, pk in varchar2, px in varchar2, n1 in number, n2 in number, sql_t in varchar2 ); end;

存储过程包体：

CREATE OR REPLACE package BODY pk_page as procedure p_wt( mycs out mytype, pk in varchar2, px in varchar2, n1 in number, n2 in number, sql_t in varchar2 ) as sqltemp varchar2(2000); begin sqltemp:=''; sqltemp:=sqltemp||'select tb1.* from'; sqltemp:=sqltemp||'('; sqltemp:=sqltemp||'select tb.*,row_number() over(order by '|| pk || ' '|| px ||')rn from'; sqltemp:=sqltemp||'('; sqltemp:=sqltemp||sql_t; sqltemp:=sqltemp||')tb'; sqltemp:=sqltemp||')tb1'; sqltemp:=sqltemp||' where tb1.rn>='|| n1; sqltemp:=sqltemp||' and tb1.rn<='|| n2; open mycs for sqltemp; end p_wt; end pk_page;

调用代码：

string sid = Request.QueryString["sid"]; string timestart = Request.QueryString["timestart"]; string timeend = Request.QueryString["timeend"]; string n1 = Request.QueryString["n1"]; string n2 = Request.QueryString["n2"]; string currPageIndex = Request.QueryString["page"]; string pageSize = Request.QueryString["pageSize"]; currPageIndex = "".Equals(currPageIndex) || currPageIndex == null ? "1" : currPageIndex; pageSize = "".Equals(pageSize) || pageSize == null ? "20" : pageSize; string url = "temp_data_stat_if.aspx?sid=" + sid + "×tart=" + timestart + "&timeend=" + timeend + "&n1=" + n1 + "&n2=" + n2; StringBuilder sb = new StringBuilder(); sb.Append(" select t.id,tt.bh,tt.name cgqName,t.cgqdate,t.createdat,ttt.name bdzName,tttt.name cityName"); sb.Append(" from comm_cgqdate t "); sb.Append(" join sys_cgq tt on t.bh = tt.bh "); sb.Append(" and tt.bdzid = "+sid+" "); sb.Append(" and t.createdat>=to_date('"+timestart+"','yyyy-mm-dd hh24:mi') "); sb.Append(" and t.createdat<=to_date('"+timeend+"','yyyy-mm-dd hh24:mi') "); sb.Append(" and to_number(t.cgqdate)>="+n1+" "); sb.Append(" and to_number(t.cgqdate)<="+n2+" "); sb.Append(" join sys_bdz ttt on tt.bdzid = ttt.id "); sb.Append(" join sys_shi tttt on ttt.shiid = tttt.id "); PageUtil pu = new PageUtil(int.Parse(currPageIndex), int.Parse(pageSize), url, sb.ToString(), "createdat", "asc"); Repeater1.DataSource = pu.pageDataSet(); Repeater1.DataBind(); Label1.Text = pu.createPageFooter();