百万级数据查询分页

主要运用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 都是一样的。

 

前台

HTML 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
>

VB.NET 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


    

   


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

其实核心的东西就是这一条语句 ，翻页时只要把参数传递进去就行了。
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