Question

asp.net+oracle 存储过程分页,最好有完整的案例。

可是要求要用存储过程。

Answer 1

在ASP.NET下，不用存储过程也能让Oracle快速分页

是看了其他网友发的用了存储过程的，之后就把存储过程提取出来，弄成一个简单的SQL

请高手多多指教

/// <summary>
/// 分页获取数据列表
/// </summary>
/// <param name="PageSize">每页显示多少行</param>
/// <param name="PageIndex">要转到第PageIndex页</param>
/// <param name="strWhere"></param>
/// <returns></returns>
public DataSet GetList(int PageSize, int PageIndex, string strWhere)
{
int v_startRecord = (PageIndex - 1) * PageSize + 1;//计算开始
int v_endRecord = PageIndex * PageSize;//计算结束
StringBuilder sb = new StringBuilder();
sb.Append("SELECT * FROM (SELECT A.*, rownum r FROM (SELECT * FROM jdc WHERE 1=1 ");//记得要换表名
sb.Append(strWhere);
sb.Append(" ORDER BY jdcbh desc) A WHERE rownum<=");//还有排序的字段
sb.Append(v_endRecord);
sb.Append(") B WHERE r>=");
sb.Append(v_startRecord);
return DbHelperOra.Query(sb.ToString());
}

/// <summary>
/// 获取分页的总记录数
/// </summary>
/// <param name="strWhere"></param>
/// <returns></returns>
public int getRowCount(string strWhere)
{
string sql = string.Format(@"select count(*) as cnt from Jdc where 1=1 {0} ", strWhere);
return int.Parse(DbHelperOra.Query(sql).Tables[0].Rows[0]["cnt"].ToString());
}

Morning.Web.BLL.Jdc bll = new Morning.Web.BLL.Jdc();//这个是放在Page_Load上面，全局的

/// <summary>
/// GridView1 数据绑定
/// </summary>
protected void databind()
{
string sql = "";
if (!IsPostBack)
{
object obj = Session["CheckDataList"];
if (obj != null)
{
sql = obj.ToString();
//Session["CheckDataList"] = null;
}
}

int currentPage;
int startPage;
int PageSize = 10;
string page = Request.QueryString["page"];

if (page != null)
{
currentPage = Convert.ToInt32(page);
firstPage.HRef = Request.CurrentExecutionFilePath + "?page=1";
prepage.HRef = Request.CurrentExecutionFilePath + "?page=" + page;
}
else
{
currentPage = 1;
firstPage.HRef = "";
firstPage.Attributes.Add("style", "color:#999;");

}

DataSet ds = bll.GetList(PageSize, currentPage, sql);
int RowCount = bll.getRowCount(sql);
int PageCount = 1;
if (RowCount % PageSize == 0)
{
PageCount = RowCount / PageSize;
}
else
{
PageCount = RowCount / PageSize + 1;
}

if (currentPage == 1)
{
prepage.HRef = "";
prepage.Attributes.Add("style", "color:#999;");
}

if (currentPage + 1 > PageCount)
{
nextpage.HRef = "";
nextpage.Attributes.Add("style", "color:#999;");
}
else
{
nextpage.HRef = Request.CurrentExecutionFilePath + "?page=" + Convert.ToString((currentPage + 1));
}

if (currentPage != PageCount)
{
lastpage.HRef = Request.CurrentExecutionFilePath + "?page=" + PageCount;
}

currentPagelbl.Text = currentPage.ToString();
pageCountlbl.Text = PageCount.ToString();
itemNum.Text = RowCount.ToString();

if (currentPage % 10 == 0)
{
startPage = currentPage - 9;
}
else
{
startPage = currentPage - currentPage % 10 + 1;
}

if (startPage > 10)
{
Label l1 = new Label();
l1.Text = "<span title='前十页'><a href='" + Request.CurrentExecutionFilePath + "?page=" + Convert.ToString((startPage - 1)) + "'>...</a></span>";
prePh.Controls.Add(l1);
}

for (int i = startPage; i < startPage + 10; i++)
{
if (i > PageCount) break;
if (i == currentPage)
{
Label l2 = new Label();
l2.Text = " [ " + i + " ] ";
l2.Attributes.Add("style", "color:#999;");
PlaceHolder1.Controls.Add(l2);
}
else
{
HtmlAnchor a1 = new HtmlAnchor();
a1.InnerText = " [ " + i + " ] ";
a1.HRef = Request.CurrentExecutionFilePath + "?page=" + i;
PlaceHolder1.Controls.Add(a1);

}
}

if (PageCount >= startPage + 10)
{
Label l3 = new Label();
l3.Text = "<span title='下十页'><a href='" + Request.CurrentExecutionFilePath + "?page=" + Convert.ToString((startPage + 10)) + "'>...</a></span>";
PlaceHolder0.Controls.Add(l3);
}

GridView1.DataSource = ds;
GridView1.DataBind();
}

前台ASPX

<div class="pagestyle">
第 <asp:Label ID="currentPagelbl" runat="server" Text="" ForeColor="red"></asp:Label>
/
<asp:Label ID="pageCountlbl" runat="server" Text="" ForeColor="red"></asp:Label> 页, 共有 <asp:Label ID="itemNum" runat="server" Text="" ForeColor="red" Font-Bold="true"></asp:Label> 条记录     <a
id="firstPage" runat="server" title="第一页">|<</a><a id="prepage" runat="server" title="前一页"><</a><asp:PlaceHolder runat="server" ID="prePh"></asp:PlaceHolder>
<asp:PlaceHolder runat="server" ID="PlaceHolder1"></asp:PlaceHolder>
<asp:PlaceHolder runat="server" ID="PlaceHolder0"></asp:PlaceHolder>
<a id="nextpage" runat="server" title="下一页">></a><a id="lastpage" runat="server" title="最后一页">>|</a></div>

Answer 2

两者没有必然的联系，我这边只说思路了。实现方式很多种
1）定义一个存储过程，传递参数包括“当前页面”“每页显示条数”

2）在存储过程中根据表名或者sql语句去写对应的分页select语句，并把结果返回