DataGridView, ADO.NET, Binding, and Paging on the Client

Question

I'm working on a WinForm application that has several queries that bring back approx 20000 records and then fills a DataTable, and binds that DataTable to a DataGridView.

I want to allow the users to page through the grid 500 records at a time. What is the best way to do this? I want to do the paging on the client side. I see that the System.Data.DataView has a filter and sort, but nothing to help with paging.

UPDATE: I starting thinking about a few approaches to do something custom. Can I get to the unqiue "row count id" that DataTable/DataRow use internally? and do something with that to count out the number of records I need?

Answer 1

Here is an example from VB-Tips:

http://www.vb-tips.com/dbPages.aspx?ID=5dbe894a-a7e6-434c-bd84-73494c71063f

Imports System.Data.SqlClient
Imports System.Text
Imports System.ComponentModel

Public Class Form1

    Dim da As SqlDataAdapter
    Dim conn As SqlConnection
    Dim ds As New DataSet

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim strConn As String
        Dim cmd As SqlCommand
        Dim sbCmd As New StringBuilder


        strConn = String.Format("Server = {0};", Environment.MachineName)
        strConn &= "Database = NorthWind; Integrated Security = SSPI;"
        conn = New SqlConnection(strConn)
        cmd = New SqlCommand("Select count(ProductName) From Products", conn)
        Try
            da = New SqlDataAdapter("Select * from Products", conn)

            conn.Open()

            With nuPage
                .Maximum = Math.Ceiling(cmd.ExecuteScalar / 10)
                .Minimum = 1
                .Increment = 1
                .Value = 1
            End With

            conn.Close()

            da.Fill(ds, 0, 10, "Products")
            ds.Tables("Products").DefaultView.AllowNew = False
            DataGridView1.DataSource = ds.Tables("Products")
            For Each col As Object In DataGridView1.Columns
                If TypeOf col Is DataGridViewCheckBoxColumn Then
                    DirectCast(col, DataGridViewCheckBoxColumn).Visible = False
                ElseIf TypeOf col Is DataGridViewTextBoxColumn Then
                    Dim tbc As DataGridViewTextBoxColumn = CType(col, DataGridViewTextBoxColumn)
                    If tbc.Name = "ProductName" Then
                        tbc.Width = 275
                        tbc.HeaderText = "Product Name"
                    ElseIf tbc.Name = "UnitPrice" Then
                        tbc.Width = 75
                        tbc.HeaderText = "Price"
                        tbc.DefaultCellStyle.Format = "c"
                        tbc.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
                    Else
                        tbc.Visible = False
                    End If
                End If
            Next
        Catch ex As Exception
            Trace.WriteLine(ex.ToString)
        End Try

    End Sub

    Private Sub nuPage_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles nuPage.ValueChanged
        Dim intStart As Integer = (nuPage.Value - 1) * 10
        ds.Clear()
        da.Fill(ds, intStart, 10, "Products")

    End Sub
End Class

Answer 2

My first attempts at paging involved the built in pager. However, I've quickly migrated to custom elements that trigger the page binding.

Normally (at least in ASP), you would bind the DataTable to the DataGridView and then do a

DataGridView.ActivePageIndex=x; DataGridView.Databind();

However, since we're talking about 20.000 records, it's best that you do an initial lookup of the number of records found, and then just bring sets of 500 into the client.

*EDIT - after briefly checking, there are no paging options in windows forms. My best guess is that you will need to do the lookup/custom paging.