Hello,
Tankete, I just have try what you are asking for a couple of days ago :
The principe is simple : returning only a needed page from 50th to 60th records consists in querying this way :
SELECT * TOP 60 ... WHERE NOT IN SELECT TOP 50 * WHERE [joining primarykeys]
I have taken advices from Dino Esposito's MSDN articles, and have created a PageQuery function thats returns such a SQL statement, based on a normal SQL statement, primaryKey information, currentpageindex and pagesize.
This sample of code uses a custompaged enabled datagrid called dg, with a access database that contains a "HugeTable" table with a "ID" primary key.
Do not forget to enable custompaging to your datagrid
Code:
Private _Cx As OleDbConnection
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
_Cx = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Inetpub\wwwroot\labo\app\db\paged_datagrid.mdb;Mode=Share Deny None;")
If Not IsPostBack Then
dg.DataBind()
DataList1.DataBind()
End If
End Sub
'
Private Sub dg_DataBinding(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dg.DataBinding
Dim sqlstr As String = PageQuery("SELECT * FROM HugeTable ORDER BY ID", "ID", dg.CurrentPageIndex, dg.PageSize)
_Cx.Open()
dg.VirtualItemCount = New OleDbCommand("SELECT COUNT(*) FROM HugeTable", _Cx).ExecuteScalar
Dim dr As OleDbDataReader = New OleDbCommand(sqlstr, _Cx).ExecuteReader(CommandBehavior.CloseConnection)
dg.DataSource = dr
End Sub
'
'
Private Function PageQuery(ByVal commandText As String, _
ByVal primaryKey As String, _
ByVal currentPageIndex As Integer, _
ByVal pageSize As Integer) As String
Dim SubQuery1_TopCount As Integer = IIf(currentPageIndex = 0, _
pageSize, _
currentPageIndex * pageSize + pageSize)
Dim SubQuery1 As String = Replace(commandText, _
"SELECT ", _
String.Format("SELECT TOP {0} ", SubQuery1_TopCount))
If currentPageIndex = 0 Then Return SubQuery1
SubQuery1 = String.Format("({0}) AS T1", SubQuery1)
Dim SubQuery2_TopCount As Integer = currentPageIndex * pageSize
Dim SubQuery2 As String = Replace(commandText, _
"SELECT ", _
String.Format("SELECT TOP {0} ", SubQuery2_TopCount))
SubQuery2 = String.Format("({0}) AS T2", SubQuery2)
Dim MainQuery As String = String.Format("SELECT t1.* FROM {0} LEFT JOIN {1} ON T1.{2}= T2.{2} WHERE T2.{2} IS NULL", _
SubQuery1, _
SubQuery2, _
primaryKey)
Return MainQuery
End Function
'
Private Sub dg_PageIndexChanged(ByVal source As System.Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles dg.PageIndexChanged
dg.CurrentPageIndex = e.NewPageIndex
dg.DataBind()
End Sub
Hope it helps. For SQL server, the built query could be better implemented using subqueries instead of inner join, which is much more faster in access.
Laurent.