asp.net 分頁sql語句(結(jié)合aspnetpager)

更新時間：2009年01月30日 18:44:59 作者：

一直用的是存儲過程分頁，小項目一般不寫存儲過程，就需要直接寫分頁sql語句。

數(shù)據(jù)庫操作類：

/// <summary> 
/// 取得總數(shù) 
/// </summary> 
/// <returns></returns> 
public string getTotal() 
{ 
StringBuilder sb = new StringBuilder(); 
sb.Append("select count(*) total from Test"); 
DataTable dt = DBHelper.ExecuteDt(sb.ToString()); 
return dt.Rows[0][0].ToString(); 
} 
/// <summary> 
/// 根據(jù)當前頁碼，每頁條數(shù)，取得相應數(shù)據(jù)。 
/// </summary> 
/// <param name="pageNum">每頁顯示條數(shù)</param> 
/// <param name="currentPage">當前頁碼</param> 
/// <returns></returns> 
public DataTable getPagesData(int pageNum, int currentPage) 
{ 
StringBuilder sb = new StringBuilder(); 
sb.Append("select top " + pageNum + " * from Test where "); 
sb.Append("ID not in (select top " + pageNum * currentPage + " ID from Test)"); 
return DBHelper.ExecuteDt(sb.ToString()); 
} 

前臺：

復制代碼代碼如下:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="sqlPager_Default" %> 
<%@ Register Assembly="AspNetPager" Namespace="Wuqi.Webdiyer" TagPrefix="webdiyer" %> 

<!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> 
</head> 
<body> 
<form id="form1" runat="server"> 
<div> 
<asp:gridview ID="gvSql" runat="server"> 
</asp:gridview> 
</div> 
<div> 
<webdiyer:aspnetpager ID="AspNetPager1" runat="server" OnPageChanged="AspNetPager1_PageChanged" PageSize="3"> 
</webdiyer:aspnetpager> 
</div> 
</form> 
</body> 
</html>

后臺：

復制代碼代碼如下:

using System; 
using System.Data; 
using System.Configuration; 
using System.Collections; 
using System.Web; 
using System.Web.Security; 
using System.Web.UI; 
using System.Web.UI.WebControls; 
using System.Web.UI.WebControls.WebParts; 
using System.Web.UI.HtmlControls; 

public partial class sqlPager_Default : System.Web.UI.Page 
{ 
BLL.Test test = new BLL.Test(); 
protected void Page_Load(object sender, EventArgs e) 
{ 
if (!IsPostBack) 
{ 
AspNetPager1.RecordCount = Convert.ToInt32(test.getTotal());//此屬性保存總記錄數(shù).. 
Bind(); 
} 
} 
private void Bind() 
{ 
this.gvSql.DataSource = test.getPagesData(Convert.ToInt32(AspNetPager1.PageSize), AspNetPager1.CurrentPageIndex - 1); 
this.gvSql.DataBind(); 
} 
protected void AspNetPager1_PageChanged(object sender, EventArgs e) 
{ 
Bind(); 
} 
}