Community discussion forum
Custom ASP.NET Datagrid Paging With Exact Count
-
This thread is for discussions of Custom ASP.NET Datagrid Paging With Exact Count.
-
Advertisement
Simply the fastest line-level profiler for .NET ever
“The low overhead means it has minimal impact on the execution of my program”
Mark Everest, Development Team Leader, Renault F1 Team Ltd.Try out the new ANTS Profiler 4 for yourself. Download your 14-day trial now
-
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.
-
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. -
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. -
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.
-
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
Post a reply
Quick links
Recent activity
- qwe qwe replied to we search the company in I...
- Konstantin Kovshenin replied to Can't seem to submit a blog
- Barry Roberts replied to connecting to a database
- MOHAMMED SEIDU replied to How to Export Datagridview ...
- MOHAMMED SEIDU replied to How to Export Datagridview ...
- Alexis Méndez replied to how to select item to datag...
Enter your message below
Sign in or Join us (it's free).