CurrentIndex=4
PageSize=10
Dim da As OleDbDataAdapter = New OleDbDataAdapter(sql, conn)
Dim ds As DataSet = New DataSet()
da.Fill(ds, (CurrentIndex - 1) * PageSize, PageSize, "TmpTable")

declare @iPageSize int =20
declare @iCurrentPage int =2

declare @sSql varchar(4000)=
'Select Top ' + convert(varchar,@iPageSize) + ' * from tblDiary where DiaryID 
not in
(
    select top ' + convert(varchar,@iPageSize  * @iCurrentPage) + ' DiaryID from tblDiary order by DiaryID asc
) order by DiaryID asc'

--PRINT @sSql

exec(@sSql)

------解决方案--------------------
C# code

public string GetSelectSectionSql(string select, string from, string where, string orderBy, int startIndex, int sectionSize)
        {
            return String.Format("select * from (select {0}, Row_Number() over (Order by {1}) as Row_Number from {2} where {3}) TempTable where Row_Number > {4} and Row_Number <= {5}", select, orderBy, from, where, startIndex, startIndex + sectionSize);
        }

------解决方案--------------------
用一条sql就可以了.
PageIndex，PageSize 这个是参数。


SQL code

int Start = ((PageIndex - 1) * PageSize) + 1;
            int End = (PageIndex) * PageSize;
            string sql = "select * from (select *,ROW_NUMBER() OVER(order by " + id + ") as rownumber  from table ) as tmp where rownumber between CAST(" + Start + " as varchar(10)) and CAST(" + End + " as varchar(10))