In this article I will explain with an example, how to display records with Next Previous Buttons in DataGridView in Windows Forms (WinForms) Application using C# and VB.Net.
Limited set of records per Page will be displayed in DataGridView with the help of Stored Procedure.
When the Next Button is clicked, the next set of records will be fetched from Database and similarly when Previous Button is clicked, the previous set of records will be fetched from Database and displayed in Windows Forms DataGridView using C# and VB.Net.
 
 
Database
Here I am making use of Microsoft’s Northwind Database. The download and install instructions are provided in the following article.
Download and install Northwind Database
 
 
Pagination Stored Procedure
SQL Server 2005 and higher versions introduced a new ROW_NUMBER function that allows to add row numbers to the records that selected from a Table. Using the ROW_NUMBER function, the custom pagination logic can be implemented within the SQL Server Stored Procedure.
The Stored Procedure accepts PageIndex and PageSize as input parameters in order to fetch the records for the desired page index. In order to populate the Pager in front end, the total number of records in the table is needed which is fetched using the RecordCount Output parameter.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
CREATE PROCEDURE [dbo].[GetCustomersPageWise]
      @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY [CustomerID] ASC
      )AS RowNumber
            ,[CustomerID]
            ,[ContactName]
            ,[Country]
       INTO #Results
      FROM [Customers]
   
      SELECT @RecordCount = COUNT(*)
      FROM #Results
         
      SELECT [CustomerID]
            ,[ContactName]
            ,[Country] 
      FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
   
      DROP TABLE #Results
END
 
 
Form Controls
In the below Form, there’s a DataGridView and a Panel control.
Display records with Next Previous Buttons in Windows DataGridView using C# and VB.Net
 
 
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Data.SqlClient;
 
VB.Net
Imports System.Data
Imports System.Data.SqlClient
 
 
Implementing Paging in DataGridView
Inside the Form Load event, the BindGrid function is called which executes the Stored Procedure and it fetches the records from Customers table of the Northwind database using DataReader (SqlDataReader).
The SqlDataReader is then loaded into a DataTable and finally the DataTable is used to populate the DataGridView.
Note: For more details about populating the DataGridView control using DataReader, please refer my article, Populate (Bind) DataGridView using DataReader in Windows Forms (WinForms) Application using C# and VB.Net.
 
Initially the value of the PageSize is set to 5 and the PageIndex is set as 1. The value of the RecordCount Output parameter and PageIndex are passed to the PopulatePager method (discussed later).
C#
//Set the Page Size.
int PageSize = 5;
private void Form1_Load(object sender, EventArgs e)
{
    this.BindGrid(1);
}
 
private void BindGrid(int pageIndex)
{
    string constring = @"Data Source=.\SQL2005;Initial Catalog=Northwind;Integrated Security=true";
    using (SqlConnection con = new SqlConnection(constring))
    {
        using (SqlCommand cmd = new SqlCommand("GetCustomersPageWise", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
            cmd.Parameters.AddWithValue("@PageSize", PageSize);
            cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4);
            cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
            con.Open();
            DataTable dt = new DataTable();
            dt.Load(cmd.ExecuteReader());
            dataGridView1.DataSource = dt;
            con.Close();
            int recordCount = Convert.ToInt32(cmd.Parameters["@RecordCount"].Value);
            this.PopulatePager(recordCount, pageIndex);
        }
    }
}
 
VB.Net
'Set the Page Size.
Private PageSize As Integer = 5
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
    Me.BindGrid(1)
End Sub
 
Private Sub BindGrid(pageIndex As Integer)
    Dim constring As String = "Data Source=.\SQL2005;Initial Catalog=Northwind;Integrated Security=true"
    Using con As New SqlConnection(constring)
        Using cmd As New SqlCommand("GetCustomersPageWise", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
            cmd.Parameters.AddWithValue("@PageSize", PageSize)
            cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4)
            cmd.Parameters("@RecordCount").Direction = ParameterDirection.Output
            con.Open()
            Dim dt As New DataTable()
            dt.Load(cmd.ExecuteReader())
            dataGridView1.DataSource = dt
            con.Close()
            Dim recordCount As Integer = Convert.ToInt32(cmd.Parameters("@RecordCount").Value)
            Me.PopulatePager(recordCount, pageIndex)
        End Using
    End Using
End Sub
 
 
Populating the Pager
The PopulatePager method accepts the count of the total records present in the Table and the current PageIndex. Then some calculations are done to find the first and the last page of the pager and then a list of possible pages is generated by adding objects of the Page class to the Generic List collection.
Finally a loop is executed and for each Page present in the collection, its corresponding dynamic Button control is added to the Panel control.
Note: For more details about creating dynamic controls in Windows Applications, please refer my article, Add Dynamic Button controls Click event handlers in Windows Forms (WinForms) Application.
 
Each dynamic Button is assigned a click event handler, when the Button is clicked the value of its Name is passed as PageIndex parameter to the BindGrid function, which populates the DataGridView with the new set of records.
C#
private void PopulatePager(int recordCount, int currentPage)
{
    List<Page> pages = new List<Page>();
    int startIndex, endIndex;
    int pagerSpan = 5;
 
    //Calculate the Start and End Index of pages to be displayed.
    double dblPageCount = (double)((decimal)recordCount / Convert.ToDecimal(PageSize));
    int pageCount = (int)Math.Ceiling(dblPageCount);
    startIndex = currentPage > 1 && currentPage + pagerSpan - 1 < pagerSpan ? currentPage : 1;
    endIndex = pageCount > pagerSpan ? pagerSpan : pageCount;
    if (currentPage > pagerSpan % 2)
    {
        if (currentPage == 2)
        {
            endIndex = 5;
        }
        else
        {
            endIndex = currentPage + 2;
        }
    }
    else
    {
        endIndex = (pagerSpan - currentPage) + 1;
    }
 
    if (endIndex - (pagerSpan - 1) > startIndex)
    {
        startIndex = endIndex - (pagerSpan - 1);
    }
 
    if (endIndex > pageCount)
    {
        endIndex = pageCount;
        startIndex = ((endIndex - pagerSpan) + 1) > 0 ? (endIndex - pagerSpan) + 1 : 1;
    }
 
    //Add the First Page Button.
    if (currentPage > 1)
    {
        pages.Add(new Page { Text = "First", Value = "1" });
    }
 
    //Add the Previous Button.
    if (currentPage > 1)
    {
        pages.Add(new Page { Text = "<<", Value = (currentPage - 1).ToString() });
    }
 
    for (int i = startIndex; i <= endIndex; i++)
    {
        pages.Add(new Page { Text = i.ToString(), Value = i.ToString(), Selected = i == currentPage });
    }
 
    //Add the Next Button.
    if (currentPage < pageCount)
    {
        pages.Add(new Page { Text = ">>", Value = (currentPage + 1).ToString() });
    }
 
    //Add the Last Button.
    if (currentPage != pageCount)
    {
        pages.Add(new Page { Text = "Last", Value = pageCount.ToString() });
    }
 
    //Clear existing Pager Buttons.
    pnlPager.Controls.Clear();
 
    //Loop and add Buttons for Pager.
    int count = 0;
    foreach (Page page in pages)
    {
        Button btnPage = new Button();
        btnPage.Location = new System.Drawing.Point(38 * count, 5);
        btnPage.Size = new System.Drawing.Size(35, 20);
        btnPage.Name = page.Value;
        btnPage.Text = page.Text;
        btnPage.Enabled = !page.Selected;
        btnPage.Click += new System.EventHandler(this.Page_Click);
        pnlPager.Controls.Add(btnPage);
        count++;
    }
}
 
private void Page_Click(object sender, EventArgs e)
{
    Button btnPager = (sender as Button);
    this.BindGrid(int.Parse(btnPager.Name));
}
 
public class Page
{
    public string Text { get; set; }
    public string Value { get; set; }
    public bool Selected { get; set; }
}
 
VB.Net
Private Sub PopulatePager(recordCount As Integer, currentPage As Integer)
    Dim pages As New List(Of Page)()
    Dim startIndex As Integer, endIndex As Integer
    Dim pagerSpan As Integer = 5
 
    'Calculate the Start and End Index of pages to be displayed.
    Dim dblPageCount As Double = CDbl(CDec(recordCount) / Convert.ToDecimal(PageSize))
    Dim pageCount As Integer = CInt(Math.Ceiling(dblPageCount))
    startIndex = If(currentPage > 1 AndAlso currentPage + pagerSpan - 1 < pagerSpan, currentPage, 1)
    endIndex = If(pageCount > pagerSpan, pagerSpan, pageCount)
    If currentPage > pagerSpan Mod 2 Then
        If currentPage = 2 Then
            endIndex = 5
        Else
            endIndex = currentPage + 2
        End If
    Else
        endIndex = (pagerSpan - currentPage) + 1
    End If
 
    If endIndex - (pagerSpan - 1) > startIndex Then
        startIndex = endIndex - (pagerSpan - 1)
    End If
 
    If endIndex > pageCount Then
        endIndex = pageCount
        startIndex = If(((endIndex - pagerSpan) + 1) > 0, (endIndex - pagerSpan) + 1, 1)
    End If
 
    'Add the First Page Button.
    If currentPage > 1 Then
        pages.Add(New Page() With { _
          .Text = "First", _
          .Value = "1" _
        })
    End If
 
    'Add the Previous Button.
    If currentPage > 1 Then
        pages.Add(New Page() With { _
          .Text = "<<", _
          .Value = (currentPage - 1).ToString() _
       })
    End If
 
    For i As Integer = startIndex To endIndex
        pages.Add(New Page() With { _
          .Text = i.ToString(), _
          .Value = i.ToString(), _
          .Selected = i = currentPage _
        })
    Next
 
    'Add the Next Button.
    If currentPage < pageCount Then
        pages.Add(New Page() With { _
          .Text = ">>", _
          .Value = (currentPage + 1).ToString() _
        })
    End If
 
    'Add the Last Button.
    If currentPage <> pageCount Then
        pages.Add(New Page() With { _
          .Text = "Last", _
          .Value = pageCount.ToString() _
        })
    End If
 
    'Clear existing Pager Buttons.
    pnlPager.Controls.Clear()
 
    'Loop and add Buttons for Pager.
    Dim count As Integer = 0
    For Each page As Page In pages
        Dim btnPage As New Button()
        btnPage.Location = New System.Drawing.Point(38 * count, 5)
        btnPage.Size = New System.Drawing.Size(35, 20)
        btnPage.Name = page.Value
        btnPage.Text = page.Text
        btnPage.Enabled = Not page.Selected
        AddHandler btnPage.Click, AddressOf Page_Click
        pnlPager.Controls.Add(btnPage)
        count += 1
    Next
End Sub
 
Private Sub Page_Click(sender As Object, e As EventArgs)
    Dim btnPager As Button = TryCast(sender, Button)
    Me.BindGrid(Integer.Parse(btnPager.Name))
End Sub
 
Public Class Page
    Public Property Text() As String
    Public Property Value() As String
    Public Property Selected() As Boolean
End Class
 
 
Screenshot
Display records with Next Previous Buttons in Windows DataGridView using C# and VB.Net
 
 
Downloads
Download Code