1. Introduction
2. Accessing our Data
3. The HTML Portion
4. The Source Code

Comments

1. Dimitrios Markatos
   08 Jul 2005 at 17:18

   Hi, thanks, I appreciate your compliments.
   

   
   I have written another older article that I think should help. It's entitled- Drilldown Datagrid Searching with ASP.NET.
   

   
   This article demonstrates how to do a standard search, display the results in a datagrid, then further filter and search the datagrid itself and drilldown into the datagrid results themselves.
   

   
   So to merge the two, work off of the aforementioned article and incorporate the custom paging. All this should be no problem, and plenty to get you up and running.
   

   
   Also, for scalability purposes, read another article of mine here on DF - .NET Data Caching . Between the two you'll be able to provide some great functionality and performance.
   

   
   I hope this helps!
   

   
   - Jimmy Markatos

2. Jaime Cruz
   08 Jul 2005 at 15:56

   I loved your article on custom paging but would very much like to know how to apply it when you filter your initial data set to retreive specific records. I have a grid with 813 records sorted by user name. I have a search box in which a user can enter a name in order to filter the original dataset. If the search result in only 4 pages of records how can I page through that new set of records? Thanks in advance for any help and for the great article.

3. mcslayton
   26 Oct 2003 at 20:00

   Check this site...
   

   
   I too was on the same quest and I've created a best practices reference site that covers many enterprise topics necessary to create large scale enterprise sites.

4. Grumly
   17 Oct 2003 at 05:38

   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\pageddatagrid.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.
   

5. tankete
   16 Oct 2003 at 14:58

   I was hoping that you would address how to do paging with exact counts while NOT returning the entire dataset from the database, just the current page worth of data. Can you provide any information on how you would accomplish that? The current approach that I can think of does not perform as well as desired.