宇文辑录

有什么不好的地方欢迎指点，主要运用sql2005的ROW_NUMBER()
分页其实很简单，只要把页参数传递到以下SQL语句中即可。
where number between cast('" & PageSize & "' as int) *(cast('" & CurrentPage & "' as int)-1)+1 and cast('" & PageSize & "' as int)*cast('" & CurrentPage & "' as int)")
如果不懂vb.net可以到这个网页把代码转换为c#  http://www.developerfusion.com/tools/convert/vb-to-csharp/
前台用 gridview repeater datalist 都是一样的。


前台
[code]
            <asp:ScriptManager ID="ScriptManager1" runat="server">
                </asp:ScriptManager>
                <asp:UpdatePanel ID="UpdatePanel1" runat="server">
                
                     <ContentTemplate>
            <asp:repeater id="Repeater1" runat="server">
                <HeaderTemplate>
                    <table id="tb01" width="260%" border="0" cellspacing="1" cellpadding="2" bgcolor="#dddddd">
                        <tbody>
                            <tr style="background-color:#ffffff">
                                <td class="trTitle"  style="width:80px;background-image:url(images/trbg02.gif);background-color:#ffffff;height:23px;" >編號</td>
                                <td class="trTitle"  style="width:80px;background-image:url(images/trbg02.gif);background-color:#ffffff;height:23px;">姓名</td>
                                <td class="trTitle"   style="width:150px;background-image:url(images/trbg02.gif);background-color:#ffffff;height:23px;">名稱</td>
                                
                            </tr>
                </HeaderTemplate>
                <ItemTemplate>
                    <tr style="background-color:#ffffff">
                        <td height="20px"  align="left">
                            <a style="cursor:hand" href="<%= strLinkManitCrsCat %>&recid=<%# Container.DataItem("rec_id") %>&mode=1"  title="編輯/刪除"><strong><asp:label　 Font-Names="Tahoma" id="lblClassNum" Width="80px"　Runat="server" Text='<%# DataBinder.Eval(Container.DataItem,"emp_id") %>'>
                            </asp:label></strong></a>
                        </td>
                        <td align="left">
                            <asp:label id="Label4" Width="80px" Runat="server" Text='<%# DataBinder.Eval(Container.DataItem,"emp_chn_nam") %>'>
                            </asp:label>
                        </td>
                        <td align="left">
                            <asp:label id="lblCrsCatDesc" Width="150px" Runat="server" Text='<%# DataBinder.Eval(Container.DataItem,"crs_num") %>'>
                            </asp:label>
                        </td>
                        
                    </tr>
                </ItemTemplate>
                <AlternatingItemTemplate>
                    <tr bgcolor="#f9f9f9">
                        <td height="20px"  align="left">
                            <a style="cursor:hand" href="<%= strLinkManitCrsCat %>&recid=<%# Container.DataItem("rec_id") %>&mode=1" title="編輯/刪除"><strong><asp:label Font-Names="Tahoma" id="lblClassNum" Width="80px" Runat="server" Text='<%# DataBinder.Eval(Container.DataItem,"emp_id") %>'>
                            </asp:label></strong></a>
                        </td>
                        <td align="left">
                            <asp:label id="Label4" Width="80px" Runat="server" Text='<%# DataBinder.Eval(Container.DataItem,"emp_chn_nam") %>'>
                            </asp:label>
                        </td>
                    <td align="left">
                            <asp:label id="lblCrsCatDesc" Width="150px" Runat="server" Text='<%# DataBinder.Eval(Container.DataItem,"crs_num") %>'>
                            </asp:label>
                        </td>
                        <td align="left">
                            <asp:label id="Label8" Width="60px" Runat="server" Text='<%# DataBinder.Eval(Container.DataItem,"cls_num") %>'>
                            </asp:label>
                        </td>
                        
                        
                        
                    </tr>
                </AlternatingItemTemplate>
                <FooterTemplate>
                    <tr bgcolor="#f2f2f2">
                        <td height="8" colspan="22" align="center"></td>
                    </tr>
                    </tbody></table>
                </FooterTemplate>
            </asp:repeater>
            <div style="PADDING-TOP:10px" align="center">
                


            &nbsp; 共<asp:label id="LPageCount" ForeColor="#ff0000" Runat="server"></asp:label>頁
            <asp:label id="LTotalCount" ForeColor="#ff0000" Runat="server"></asp:label>條記錄
                    <asp:linkbutton id="Fistpage" Runat="server" CommandName="0">首頁</asp:linkbutton>
                    <asp:linkbutton id="Prevpage" Runat="server" CommandName="prev">上一頁</asp:linkbutton>
                    <asp:linkbutton id="Nextpage" Runat="server" CommandName="next">下一頁</asp:linkbutton>
                    <asp:linkbutton id="Lastpage" Runat="server" CommandName="last">尾頁</asp:linkbutton>當前第
                    <asp:label id="LCurrentPage" ForeColor="#ff0000" Runat="server"></asp:label>頁
           &nbsp; 轉到第
           <asp:textbox id="gotoPage" Width="30px" Runat="server" AutoPostBack="True" MaxLength="5" ></asp:textbox>頁
           <asp:Label style=" POSITION: absolute" id="msgbox" runat="server" ForeColor="Red" BorderColor="Red"></asp:Label>
           </div>
          
           </ContentTemplate>                
</asp:UpdatePanel>
            <input type="hidden" id="sortfield" runat="server" name="sortfield"/> <input type="hidden" id="sortstring" runat="server" name="sortstring"/>&nbsp;
        </form>
    </body>
</html>


[/code]
VB.NET code
[code]Imports System.Data

Partial Class TrainRecNavigation
    Inherits System.Web.UI.Page
    Public strLinkManitCrsCat As String
    Public strlinkQueryCrsCat As String
    Public strlink2 As String
    Public strClassNum As String = ""
    Public strCourseNum As String = ""
    Dim strfac As String = ""
    Dim strdept As String = ""
    Dim strApproDate As String = ""
    Dim strApproDate2 As String = ""
    Dim strApproStatus As String = ""
    Dim strTutorID As String = ""
    Dim strpage As String = ""
    Dim coursenum As Integer, classnum As Integer
    Dim PageCount As Integer, RecCount As Integer, CurrentPage As Integer, Current As Integer, Pages As Integer, JumpPage As Integer
    Dim PageSize As Integer = 15
    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If Session("UserID") = "" Then
            Response.Write("<script>alert('您尚未登錄或登錄超時,請重新登錄！');parent.location.href='login.aspx';</script>>")
        End If
        strClassNum = Server.UrlDecode(Request.QueryString("strClassNum")) & ""
        strCourseNum = Server.UrlDecode(Request.QueryString("strCourseNum")) & ""
        strfac = Server.UrlDecode(Request.QueryString("strfac")) & ""
        strdept = Server.UrlDecode(Request.QueryString("strdept")) & ""
        strApproDate = Server.UrlDecode(Request.QueryString("strApproDate")) & ""
        strApproDate2 = Server.UrlDecode(Request.QueryString("strApproDate2")) & ""
        strApproStatus = Server.UrlDecode(Request.QueryString("strApproStatus")) & ""
        strTutorID = Server.UrlDecode(Request.QueryString("strTutorID")) & ""
        strpage = Request.QueryString("page") & ""
        If Not Page.IsPostBack Then
            '-----------------------------------------
            RecCount = Calc()
            '計算總記錄
            PageCount = RecCount / PageSize + OverPage()
            '計算總頁數
            ViewState("PageCounts") = RecCount / PageSize - ModPage()
            If strpage <> "" Then
                '設置當前頁為返回頁
                ViewState("PageIndex") = CInt(strpage)
            Else
                '設置當前頁為1
                ViewState("PageIndex") = 1
                Session("CurPage") = 1
            End If

            ViewState("JumpPages") = PageCount
            LPageCount.Text = PageCount.ToString()
            LTotalCount.Text = RecCount.ToString()

            If RecCount <= PageSize Then
                gotoPage.Enabled = False
            Else
                gotoPage.Enabled = True
            End If
            'msgbox.Text = RecCount
            '----------------------------------------
            Call CreatePagedDataSource("")
        End If
        strLinkManitCrsCat = GotoMaintPage()
        strlinkQueryCrsCat = GotoQueryPage()
        strlink2 = GotoMaintPage2()
    End Sub
#Region "計算總行數"
    Public Function OverPage() As Integer
        '算余
        Dim pages As Integer = 0
        If RecCount Mod PageSize <> 0 Then
            pages = 1
        Else
            pages = 0
        End If
        Return pages
    End Function
    Public Function ModPage() As Integer
        '算余
        Dim pages As Integer = 0
        If RecCount Mod PageSize = 0 AndAlso RecCount <> 0 Then
            pages = 1
        Else
            pages = 0
        End If
        Return pages
    End Function
    Public Function Calc() As Integer
        '計算記錄總數
        Dim clsDBProcess As New dbprocess.OledbProcess
        Dim dsResult As New DataSet
        'Dim a5 As String = Server.UrlDecode(Request.QueryString("a5"))
        Dim ass As New StringBuilder("Select count(cls_num) as co from  TRAIN_REC a  where 1=1 ")
        If strClassNum.Trim.Length > 0 Then
            ass.Append(" And a.cls_num like '%" & strClassNum & "%'")
        End If
        If strCourseNum.Trim.Length > 0 Then
            ass.Append(" And a.crs_num like '%" & strCourseNum & "%'")
        End If
        If strfac.Trim.Length > 0 Then
            ass.Append(" And a.fac_num like '%" & strfac & "%'")
        End If
        If strdept.Trim.Length > 0 Then
            ass.Append(" And a.dept_num like '%" & strdept & "%'")
        End If
        If strApproStatus.Trim.Length > 0 Then
            ass.Append(" And a.appro_status like '%" & strApproStatus & "%'")
        End If
        If strTutorID.Trim.Length > 0 Then
            ass.Append(" And a.emp_id like '%" & strTutorID & "%'")
        End If
        If strApproDate.Trim.Length > 0 And strApproDate2.Trim.Length = 0 Then
            ass.Append(" And a.appro_date like '%" & strApproDate & "%' ")
        End If
        If strApproDate.Trim.Length > 0 And strApproDate2.Trim.Length > 0 Then
            ass.Append(" And a.appro_date between '" & strApproDate & "' and '" & strApproDate2 & "' ")
        End If
        Dim bss As String = ass.ToString()
        Dim RecordCount As Integer = 0

        dsResult = clsDBProcess.GetDataSet(Session("Server"), Session("Database"), bss)
        RecordCount = Int32.Parse(dsResult.Tables(0).Rows(0)(0))
        Return RecordCount
        msgbox.Text = RecordCount
    End Function
#End Region
#Region "翻頁"
    '下一頁
    Protected Sub Nextpage_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Nextpage.Click
        CurrentPage = CInt(ViewState("PageIndex"))
        Pages = CInt(ViewState("PageCounts"))
        CurrentPage = CurrentPage + 1
        ViewState("PageIndex") = CurrentPage
        Session("CurPage") = CurrentPage
        CreatePagedDataSource("")
    End Sub
    '上一頁
    Protected Sub Prevpage_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Prevpage.Click
        CurrentPage = CInt(ViewState("PageIndex"))
        Pages = CInt(ViewState("PageCounts"))
        CurrentPage -= 1
        ViewState("PageIndex") = CurrentPage
        Session("CurPage") = CurrentPage
        CreatePagedDataSource("")
    End Sub
    '最后一頁
    Protected Sub Lastpage_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Lastpage.Click
        CurrentPage = CInt(ViewState("PageIndex"))
        Pages = CInt(ViewState("PageCounts"))
        CurrentPage = Pages
        ViewState("PageIndex") = CurrentPage
        Session("CurPage") = CurrentPage
        CreatePagedDataSource("")
    End Sub
    '第一頁
    Protected Sub Fistpage_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Fistpage.Click
        CurrentPage = CInt(ViewState("PageIndex"))
        Pages = CInt(ViewState("PageCounts"))
        CurrentPage = 1
        ViewState("PageIndex") = CurrentPage
        Session("CurPage") = CurrentPage
        CreatePagedDataSource("")
    End Sub
    
    '轉到第幾頁
    Protected Sub gotoPage_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gotoPage.TextChanged
        Dim asd As String = Me.gotoPage.Text.Trim().ToString()
        JumpPage = CInt(ViewState("JumpPages"))
        If asd = "" Then
            Alert("超出范围")
            Return
        End If
        If Int32.Parse(gotoPage.Text) > JumpPage OrElse Int32.Parse(gotoPage.Text) <= 0 OrElse asd = "" Then
            Alert("超出范围")
            Return
        Else
            Dim InputPage As Integer = Int32.Parse(gotoPage.Text.ToString())
            ViewState("PageIndex") = InputPage
            Session("CurPage") = InputPage
            CreatePagedDataSource("")
        End If
    End Sub
#End Region
    
  
[/code]

[code]Public Sub CreatePagedDataSource(ByVal strStringValue As String)
        Dim intPage As Integer, strMid As String
        Dim strSQL As String = ""
        Dim dsResult As New DataSet
        Dim clsDBProcess As New dbprocess.OledbProcess
        Dim strMidName As String
        Dim strGetDataFlag As String

        CurrentPage = CInt(ViewState("PageIndex"))
        Pages = CInt(ViewState("PageCounts"))
        If CurrentPage > 1 Then
            Fistpage.Enabled = True
            Prevpage.Enabled = True
        Else
            Fistpage.Enabled = False
            Prevpage.Enabled = False
        End If
        If CurrentPage = Pages + 1 Then
            Nextpage.Enabled = False
            Lastpage.Enabled = False
        Else
            Nextpage.Enabled = True
            Lastpage.Enabled = True
        End If
        Try
            strGetDataFlag = Request.QueryString("getdata") & ""
            strMid = Request.QueryString("mid") & ""
            strMidName = GetMenuName(Session("Server"), Session("Database"), strMid)
            tb1.Rows(0).Cells(0).InnerHtml = "&nbsp;<b>" & strMidName & "</b>"

            If strGetDataFlag = "1" Then
                'rec_id,crs_num,cls_num,emp_id,fac_num,dept_num,grade_id,title_id,atn_status,lev_status,taken_hr,exam_result,finasst_per,finasst_amt,finasst_apr_date,finasst_act_date,remark,appro_user_id,appro_date,appro_status,crt_datetime,crt_user,chg_datetime,chg_user
                'rec_id,crs_num,emp_id,emp_id,fac_num,dept_num,grade_id,title_id,atn_status,lev_status,taken_hr,exam_result,finasst_per,finasst_amt,finasst_apr_date,finasst_act_date,remark,appro_user_id,appro_date,appro_status,crt_datetime,crt_user,chg_datetime,chg_user

                Dim strSQL2 As New StringBuilder("select * from (select a.rec_id,a.cls_num,a.emp_id,a.taken_hr,a.exam_result,a.finasst_per,a.finasst_amt,a.finasst_apr_date,a.finasst_act_date,a.crt_datetime,a.chg_datetime,CONVERT(varchar(100),a.appro_date, 23) as appro_date,ROW_NUMBER() OVER(ORDER BY a.rec_id) AS number, ")
                strSQL2.Append("b.dept_nam as dept_num,c.fac_num as fac_num,d.crs_nam as crs_num,e.user_nam as chg_user,f.user_nam as crt_user,g.code_value as grade_id,h.title_nam as title_id,i.code_value as atn_status,j.code_value as appro_status,k.emp_chn_nam,m.cls_start_date,m.cls_end_date,m.cls_start_time,m.cls_end_time,m.center_venue  from TRAIN_REC a ")
                strSQL2.Append("left join dept b on  a.dept_num=b.dept_num and a.fac_num=b.fac_num left join facility c on a.fac_num=c.fac_num   left join course d on a.crs_num=d.crs_num  left join user_id e on a.chg_user=e.user_id left join user_id f on a.crt_user=f.user_id ")
                strSQL2.Append(" left join CodeTable g on a.grade_id=g.code_id and g.table_id='Grade' left join title h on a.title_id=h.title_id and h.fac_num=a.fac_num left join CodeTable i on a.atn_status=i.code_id and i.table_id='CrsStatus' ")
                strSQL2.Append(" left join CodeTable j on a.appro_status=j.code_id and j.table_id='AppStatus' left join employee k on a.emp_id=k.emp_id  ")
                strSQL2.Append("left join (select r1.cls_num,r1.cls_start_date,r1.cls_end_date,r1.cls_start_time,r1.cls_end_time,r2.center_venue  from classes r1 left join train_center r2 on r1.center_id=r2.center_id ) m on a.cls_num=m.cls_num )T1 where number between cast('" & PageSize & "' as int) *(cast('" & CurrentPage & "' as int)-1)+1 and cast('" & PageSize & "' as int)*cast('" & CurrentPage & "' as int)")
                If strClassNum.Trim.Length > 0 Then
                    strSQL2.Append(" And a.cls_num like '%" & strClassNum & "%'")
                End If
                If strCourseNum.Trim.Length > 0 Then
                    strSQL2.Append(" And a.crs_num like '%" & strCourseNum & "%'")
                End If
                If strfac.Trim.Length > 0 Then
                    strSQL2.Append(" And a.fac_num like '%" & strfac & "%'")
                End If
                If strdept.Trim.Length > 0 Then
                    strSQL2.Append(" And a.dept_num like '%" & strdept & "%'")
                End If
                If strApproStatus.Trim.Length > 0 Then
                    strSQL2.Append(" And a.appro_status like '%" & strApproStatus & "%'")
                End If
                If strTutorID.Trim.Length > 0 Then
                    strSQL2.Append(" And a.emp_id like '%" & strTutorID & "%'")
                End If
                If strApproDate.Trim.Length > 0 And strApproDate2.Trim.Length = 0 Then
                    strSQL2.Append(" And a.appro_date like '%" & strApproDate & "%' ")
                End If
                If strApproDate.Trim.Length > 0 And strApproDate2.Trim.Length > 0 Then
                    strSQL2.Append(" And a.appro_date between '" & strApproDate & "' and '" & strApproDate2 & "' ")
                End If
                ' strSQL &= " order by a.cls_num desc"
                dsResult = clsDBProcess.GetDataSet(Session("Server"), Session("Database"), strSQL2.ToString)
                Repeater1.DataSource = dsResult
                Repeater1.DataBind()
                LCurrentPage.Text = CurrentPage.ToString()
                'msgbox.Text = Pages.ToString()
            End If
        Catch ex As Exception
            msgbox.Text = (ex.ToString)
        Finally
            dsResult = Nothing
            clsDBProcess = Nothing
        End Try
    End Sub
    
End Class

[/code]

其实核心的东西就是这一条语句 ，翻页时只要把参数传递进去就行了。

SQL code
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ProductID ASC) RowNum, * FROM Production.Product ) OrderData
WHERE RowNum BETWEEN @iRowCount*(@iPageNo-1)+1 and @iRowCount*@iPageNo