load large dataset in vb.net

Question

I have a gridview, which is linked to database table. there are 2,050 total records in database table. I want to display 25 records per page.

Below are output of important variables:

• 2050 totalRecords (total records in table)
• 26 pageSize (one page has 26 records)
• pageIndex(current Page Number selected)
• 79 Totalpages = (Math.Ceiling(totalRecords / pageSize)

Issue: Gridview GridView1_PageIndexChanging is showing only 2 pages. This is incorrect bc according to variables aboce, we know it should be 79 Pages in total.

Cause of Issue: <asp:GridView ... PageSize="25"> gridview show 25 records per page. Than on back-end I set the global variable Dim pageSize As Integer = 26 (for testing i inc 25 to 26). Sql Query is loading 26 records from database and putting in Gridview. 1st page has 25 records and 2nd page has 1 record. which equal to 26 records

*** note if I change to Dim pageSize As Integer = 25, than pagging will be 0 on gridview becuase gridview pagging is seem to be based on sql query 1st result and not total count from table.

What I want: I want to display all 79 pages. not just 2 pages.

Code:

FrontEnd: I have a gridview with pagesize = 25

<asp:GridView ... PageSize="25" AllowPaging="true" PageSize="25" OnPageIndexChanging="GridView1_PageIndexChanging">

Global Variables:

Dim currentPageIndex As Integer = 0  ' Stores the current page index (starts from 0).
Dim pageSize As Integer = 26      ' Defines the number of records per page (set to 26).
Dim sortColumn As String = ""     ' Holds the name of the currently sorted column (initially empty).
Dim sortDirection As SortDirection = sortDirection.Ascending ' Tracks the current sorting direction (ascending by default).

Main Method:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If Not Page.IsPostBack Then
        BindGridData()
    End If
End Sub

Gridview Pagging function:

Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
    GridView1.PageIndex = e.NewPageIndex
    BindGridData(pageIndex:=e.NewPageIndex)
End Sub

Load data from database and link it to gridview:

Protected Sub BindGridData(Optional ByVal pageIndex As Integer = 0,
                          Optional ByVal sortColumn As String = "",
                          Optional ByVal sortDirection As String = "ASC")
    Dim sqlConn1 As New OdbcConnection(myConnectionString1)
    Try
        sqlConn1.Open()

        Dim queryString As String = "SELECT * FROM MyTable "

        ' Add ORDER BY clause for sorting - Use the correct SortDirection enumeration value for ASC or DESC
        If Not String.IsNullOrEmpty(sortColumn) Then
            queryString &= " ORDER BY " & sortColumn & " " & sortDirection.ToString()
        End If
        ' Add paging logic using LIMIT and OFFSET clauses
        queryString &= " OFFSET " & (pageIndex * pageSize) & " ROWS FETCH NEXT " & pageSize & " ROWS ONLY "

        Dim adapter As OdbcDataAdapter = New OdbcDataAdapter(queryString, sqlConn1)
        Dim dt As DataTable = New DataTable()
        adapter.Fill(dt)
        Dim totalRecords As Integer = GetTotalRecordCount() ' total records count from database

        ' Bind data to GridView
        If totalRecords > 0 Then
            GridView1.DataSource = dt
            GridView1.DataBind()

            CountL.Text = dt.Rows.Count & " Records " & totalRecords & " =GetTotalRecordCount() " & (pageIndex + 1) & " of " & (Math.Ceiling(totalRecords / pageSize)) & " Pages"
        Else
            CountL.Text = "No records found."
        End If

        sqlConn1.Close()
    Catch ex As Exception
        ' Handle exception
    End Try
End Sub

GetTotalRecordCount method:

Private Function GetTotalRecordCount() As Integer
        Dim sqlConn2 As New OdbcConnection(myConnectionString1)
        Try
            sqlConn2.Open()

            Dim countQuery As String = "SELECT COUNT(*) FROM MyTable "
            Dim cmd As OdbcCommand = New OdbcCommand(countQuery, sqlConn2)
            Dim totalRecords As Integer = CInt(cmd.ExecuteScalar())

            Return totalRecords
        Finally
            sqlConn2.Close()
        End Try
    End Function

Answer 1

Ok, we not resolved if you REALLY want to have a pager with 78 values?

I suppose that is possible, but I never seen such a pager with that many choices.

As noted, in your posted code, you get a RowCount, but NEVER supply that RowCount to the pager. And the built in data pager does not have that option. As noted, the built in data pager works against the FULL data set.

And as noted, I been around computers for 30 years, and NEVER seen a data pager with 78 options!

However, it most certainly is possible, and this means we have to roll our own pager.

So, we need a control that can have from 1 to "N" choices. Best choice for that is a RadioButton list.

So, my grid rows are quite large, so I going with 12 per page.

With 12 per row, then for a database of 2000 rows, then I have about 2000/12 = 166 buttons. However, 79 or 160? It is too many!!!

So, say this markup:

        <div style="width: 50%">
            <asp:GridView ID="GVHotels" runat="server" AutoGenerateColumns="False"
                DataKeyNames="ID" CssClass="table table-hover">
                <Columns>
                    <asp:BoundField DataField="FirstName" HeaderText="FirstName" />
                    <asp:BoundField DataField="LastName" HeaderText="LastName" />
                    <asp:BoundField DataField="City" HeaderText="City" />
                    <asp:BoundField DataField="HotelName" HeaderText="Hotel" />
                    <asp:BoundField DataField="Description" HeaderText="Description" />
                    <asp:TemplateField HeaderText="Edit"
                        ItemStyle-HorizontalAlign="Center" ItemStyle-Width="130px">
                        <ItemTemplate>
                            <asp:Button ID="cmdEdit" runat="server" Text="Select"
                                OnClick="cmdEdit_Click" />
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
        </div>

        <br />

        <div style="width: 100%">

            <asp:RadioButtonList ID="RadioPager" runat="server"
                AppendDataBoundItems="True"
                RepeatDirection="Horizontal"
                DataTextField="Value"
                DataValueField="Key" CssClass="rMyChoice"
                AutoPostBack="true"
                OnSelectedIndexChanged="RadioPager_SelectedIndexChanged"
                RepeatColumns="30">
            </asp:RadioButtonList>

        </div>
    </div>

And our code to load, and manage the paging?

Well, one nice feature of using a RadioButton list, is it will "remember" the current button, and that becomes our current page setting.

Hence this code:

Const PageSize As Integer = 12
Dim NumPages As Integer = 0


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Not IsPostBack Then
        FirstDataLoad()
        ViewState("NumPages") = NumPages
    Else
        NumPages = ViewState("NumPages")
    End If

End Sub

Sub FirstDataLoad()

    Dim NumPages As Integer = 0
    Dim NumPagesD As Decimal = GetRowCount() / PageSize

    NumPages = Int(NumPagesD)        ' decimal to integer
    If NumPagesD > NumPages Then
        NumPages += 1  ' add 1 if some rows on last page
    End If

    Dim butListSource As New Dictionary(Of Integer, Integer)
    For i = 1 To NumPages
        butListSource.Add(i, i)   'value and display are same (but do NOT have to be!)
    Next

    RadioPager.DataSource = butListSource
    RadioPager.DataBind()
    RadioPager.SelectedIndex = 0    ' list is zero based, but we use PageIndex starting at 1


    Dim strSQL As String =
        "SELECT * FROM tblHotels2 ORDER BY ID"

    GVHotels.DataSource = GetData(strSQL)
    GVHotels.DataBind()


End Sub

Public Function GetRowCount() As Integer

    Dim strSQL = "SELECT COUNT(*) FROM tblHotels2"
    GetRowCount = MyRst(strSQL).Rows(0)(0)


End Function

Public Function GetData(strSQL As String) As DataTable

    ' Assume that RadioButton index (zero based page number 
    ' is ALWAYS set BEFORE calling this routine

    Dim PageNumber = RadioPager.SelectedIndex + 1

    Dim strSQL2 As String = strSQL &
        " OFFSET ((@PageNumber - 1) * @RowsPerPage) ROWS
        FETCH NEXT @RowsPerPage ROWS ONLY;"

    Dim cmdSQL As New SqlCommand(strSQL2)
    cmdSQL.Parameters.Add("@PageNumber", SqlDbType.Int).Value = PageNumber
    cmdSQL.Parameters.Add("@RowsPerPage", SqlDbType.Int).Value = PageSize

    Return MyRstP(cmdSQL)


End Function

So, not a lot of code, but MORE important, is the code is readable.

And the RadioButton "index changed" event works much like a combo box, and that code is this:

Protected Sub RadioPager_SelectedIndexChanged(sender As Object, e As EventArgs)

    Dim strSQL As String =
        "SELECT * FROM tblHotels2 ORDER BY ID"

    GVHotels.DataSource = GetData(strSQL)
    GVHotels.DataBind()


End Sub

Note VERY close how we persist the number of pages. Remember, global form values do NOT persist between post-backs (they go out of scope). The server does NOT keep a copy of the code behind in memory after a post back. After all, the user might close their laptop lid, or might now be shopping on Amazon.

So, web-based software is what we call "state less". The page, the code variables start from scratch on each new post back. This is just how web sites work. So, I persisted the row count by shoving it into ViewState.

So note the NumPages is a constant, so that will persist. However, the number of Rows I declare at the page (class) level WILL NOT persist between postbacks. So, on first page load, I get a row count, and then shove it into ViewState. On each additional page post back, then I restore that value from ViewState back into the variable, and then the rest of the page code can enjoy that value (keep in mind that page load event fires each and every time, not just on first page load. So, your "real" first page load code goes inside of the If Not IsPostBack code stub.

However, the RadioButton control does “persist” it’s current setting, which is our current page number. So, text box, or most asp.net controls do have what we called “built in” view state, so we don’t have to write code to hold and keep and persist the current page number, since the RadioButton index of the current selected button achieves that.

The result when I run the above page is this:

So, it not clear if you looking to have 78 buttons, or that you want a pager that has paging on the pages!

Such a setup is more common, and it looks like this:

So, we not only move to next/previous page, and we not only can select a page, but you ALSO can page on pages, and thus you moving ahead the number of page buttons you display, not just one page of data ahead.