How can we do pagination in datagridview in winform

Question

I want to show 10 records per page in a datagridview on a window form and user must click next button to show next 10 records. Is it there some property in DataGridview or do i need to create a custom control.

What i need to do to achieve this.

Solution 1

Here's a simple working example, where a BindingNavigator GUI control uses a BindingSource object to identify page breaks, by setting its DataSource to a custom subclass of IListSource. (Thanks to this answer for the key idea.) When the user clicks the "next page" button, the BindingNavigator fires bindingSource1_CurrentChanged and your code can fetch the desired records. Instructions:

1. Create a Windows Forms application
2. Drag onto the form a BindingNavigator, a DataGridView, and a BindingSource
3. Replace Form1.cs with the following code:

using System;
using System.Collections.Generic;
using System.Windows.Forms;

namespace PagedDataGridView
{
    public partial class Form1 : Form
    {
        private const int totalRecords = 43;
        private const int pageSize = 10;

        public Form1()
        {
            InitializeComponent();
            dataGridView1.Columns.Add(new DataGridViewTextBoxColumn { DataPropertyName = "Index" });
            bindingNavigator1.BindingSource = bindingSource1;
            bindingSource1.CurrentChanged += new System.EventHandler(bindingSource1_CurrentChanged);
            bindingSource1.DataSource = new PageOffsetList();
        }

        private void bindingSource1_CurrentChanged(object sender, EventArgs e)
        {
            // The desired page has changed, so fetch the page of records using the "Current" offset 
            int offset = (int)bindingSource1.Current;
            var records = new List<Record>();
            for (int i = offset; i < offset + pageSize && i < totalRecords; i++)
                records.Add(new Record { Index = i });
            dataGridView1.DataSource = records;
        }

        class Record
        {
            public int Index { get; set; }
        }

        class PageOffsetList : System.ComponentModel.IListSource
        {
            public bool ContainsListCollection { get; protected set; }

            public System.Collections.IList GetList()
            {
                // Return a list of page offsets based on "totalRecords" and "pageSize"
                var pageOffsets = new List<int>();
                for (int offset = 0; offset < totalRecords; offset += pageSize)
                    pageOffsets.Add(offset);
                return pageOffsets;
            }
        }
    }
}

Solution 2

Here is my solution : It took me almost a year to find it and proud of this one

public class SuperGrid : DataGridView
    {
        public int PageSize
        {
            get
            {
                return _pageSize;
            }
            set
            {
                _pageSize = value;
            }
        }
        public int _pageSize = 10;
        BindingSource bs = new BindingSource();
        BindingList<DataTable> tables = new BindingList<DataTable>();
        public void SetPagedDataSource(DataTable dataTable, BindingNavigator bnav)
        {
            DataTable dt = null;
            int counter = 1;
            foreach (DataRow dr in dataTable.Rows)
            {
                if (counter == 1)
                {
                    dt = dataTable.Clone();
                    tables.Add(dt);
                }
                dt.Rows.Add(dr.ItemArray);
                if (PageSize < ++counter  )
                {
                    counter = 1;
                }
            }
            bnav.BindingSource = bs;
            bs.DataSource = tables;
            bs.PositionChanged += bs_PositionChanged;
            bs_PositionChanged(bs, EventArgs.Empty);
        }
        void bs_PositionChanged(object sender, EventArgs e)
        {
            this.DataSource = tables[bs.Position];
        }
    }

How to use it? Add above code to your project, drag the Supergrid and a bindingnavigator control to your win form .

 superGrid1.PageSize = 5;
 DataTable dt = DataProvider.ExecuteDt("select * from test order by col");
  superGrid1.SetPagedDataSource(dt, bindingNavigator1);

And you get a paged Datagridview with data binding without much hastle/

Solution 3

Implement Paging DataGridView in Windows Forms (WinForms) Application using C# and VB.Net, this is another solution : https://www.aspsnippets.com/Articles/Implement-Paging-DataGridView-in-Windows-Forms-WinForms-Application-using-C-and-VBNet.aspx

In this article It will be explained how to implement Paging DataGridView in Windows Forms (WinForms) Application using C# and VB.Net. DataGridView control in Windows Forms (WinForms) Application does not have paging capabilities and hence Custom Paging using Stored Procedure needs to be implemented. 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

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);
        }
    }
}

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; }
}

Solution 4

Another Approach for this problem:

public class PagedGrid : DataGridView
    {
        Paging pg;
        SQLQuery s;
        public void SetPagedDataSource(  SQLQuery s, BindingNavigator bnav)
        {
            this.s = s;
            int count = DataProvider.ExecuteCount(s.CountQuery);
            pg = new Paging(count, 5);
            bnav.BindingSource = pg.BindingSource;
            pg.BindingSource.PositionChanged += new EventHandler(bs_PositionChanged);
            //first page
            string q = s.GetPagingQuery(pg.GetStartRowNum(1), pg.GetEndRowNum(1), true);
            DataTable dt = DataProvider.ExecuteDt(q);
            DataSource = dt;
        }

        void bs_PositionChanged(object sender, EventArgs e)
        {
            int pos = ((BindingSource)sender).Position + 1;
            string q = s.GetPagingQuery(pg.GetStartRowNum(pos), pg.GetEndRowNum(pos), false);
            DataTable dt = DataProvider.ExecuteDt(q);
            DataSource = dt;
        }

        public void UpdateData()
        {
            DataTable dt = (DataTable)DataSource;
            using (SqlConnection con = new SqlConnection(DataProvider.conStr))
            {
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(s.CompleteQuery, con);
                SqlCommandBuilder cb = new SqlCommandBuilder(da);
                da.UpdateCommand = cb.GetUpdateCommand();
                da.InsertCommand = cb.GetInsertCommand();
                da.DeleteCommand = cb.GetDeleteCommand();
                da.Update(dt);
            }
            MessageBox.Show("The changes are committed to database!");
        }
    }


  /// <summary>
    /// Gives functionality of next page , etc for paging.
    /// </summary>
    public class Paging
    {
        public int _totalSize = 0;
        private int _pageSize = 0;

        public int TotalSize
        {
            get
            {
                return _totalSize;
            }
            set
            {
                if (value <= 0)
                {
                    throw new ArgumentException();
                }
                _totalSize = value;
            }
        }

        public int PageSize
        {
            get
            {
                return _pageSize;
            }
            set
            {
                if (value <= 0)
                {
                    throw new ArgumentException();
                }
                _pageSize = value;
            }
        }

        public Paging(int totalSize, int pageSize)
        {
            this.TotalSize = totalSize;
            this.PageSize = pageSize;
        }

        public int GetStartRowNum(int PageNum)
        {
            if (PageNum < 1)
            {
                throw new Exception("Page number starts at 1");
            }
            if (PageNum > GetPageCount())
            {
                throw new Exception("Page number starts at " + GetPageCount().ToString());
            }
            return 1 + ((PageNum - 1) * _pageSize);
        }

        public int GetEndRowNum(int PageNum)
        {
            if (PageNum < 1)
            {
                throw new Exception("Page number starts at 1");
            }
            if (PageNum > GetPageCount())
            {
                throw new Exception("Page number starts at " + GetPageCount().ToString());
            }
            return _pageSize + ((PageNum - 1) * _pageSize);
        }

        public int GetPageCount()
        {
            return (int)Math.Ceiling(TotalSize / (decimal)PageSize);
        }

        public bool IsFirstPage(int PageNum)
        {
            if (PageNum == 1)
            {
                return true;
            }
            return false;
        }

        public bool IsLastPage(int PageNum)
        {
            if (PageNum == GetPageCount())
            {
                return true;
            }
            return false;
        }
        private int _currentPage = 1;
        public int CurrentPage
        {
            get
            {
                return _currentPage;
            }
            set
            {
                _currentPage = value;
            }
        }
        public int NextPage
        {
            get
            {
                if (CurrentPage + 1 <= GetPageCount())
                {
                    _currentPage = _currentPage + 1;
                }
                return _currentPage;
            }
        }

        public int PreviousPage
        {
            get
            {
                if (_currentPage - 1 >= 1)
                {
                    _currentPage = _currentPage - 1;
                }
                return _currentPage;
            }
        }
        private BindingSource _bindingSource = null;
        public BindingSource BindingSource
        {
            get
            {
                if (_bindingSource == null)
                {
                    _bindingSource = new BindingSource();
                    List<int> test = new List<int>();
                    for (int i = 0; i < GetPageCount(); i++)
                    {
                        test.Add(i);
                    }
                    _bindingSource.DataSource = test;
                }
                return _bindingSource;
            }

        }
    }


    /// <summary>
    /// Query Helper of Paging
    /// </summary>
    public class SQLQuery
    {

        private string IDColumn = "";
        private string WherePart = " 1=1 ";
        private string FromPart = "";
        private string SelectPart = "";

        public SQLQuery(string SelectPart, string FromPart, string WherePart, string IDColumn)
        {
            this.IDColumn = IDColumn;
            this.WherePart = WherePart;
            this.FromPart = FromPart;
            this.SelectPart = SelectPart;

        }

        public string CompleteQuery
        {
            get
            {
                if (WherePart.Trim().Length > 0)
                {
                    return string.Format("Select {0} from {1} where {2} ", SelectPart, FromPart, WherePart);
                }
                else
                {
                    return string.Format("Select {0} from {1} ", SelectPart, FromPart);
                }
            }
        }

        public string CountQuery
        {
            get
            {
                if (WherePart.Trim().Length > 0)
                {
                    return string.Format("Select count(*) from {0} where {1} ", FromPart, WherePart);
                }
                else
                {
                    return string.Format("Select count(*) from {0} ", FromPart);

                }
            }
        }



        public string GetPagingQuery(int fromrow, int torow, bool isSerial)
        {
            fromrow--;
            if (isSerial)
            {
                return string.Format("{0} where {1} >= {2} and {1} <= {3}", CompleteQuery, IDColumn, fromrow, torow);
            }
            else
            {
                string select1 = "";
                string select2 = "";
                if (WherePart.Trim().Length > 0)
                {
                    select1 = string.Format("Select top {3} {0} from {1} where {2} ", SelectPart, FromPart, WherePart, torow.ToString());
                    select2 = string.Format("Select top {3} {0} from {1} where {2} ", SelectPart, FromPart, WherePart, fromrow.ToString());
                }
                else
                {
                    select1 = string.Format("Select top {2} {0} from {1} ", SelectPart, FromPart, torow.ToString());
                    select2 = string.Format("Select top {2} {0} from {1} ", SelectPart, FromPart, fromrow.ToString());
                }
                if (fromrow <= 1)
                {
                    return select1;
                }
                else
                {
                    return string.Format("{0} except {1} ", select1, select2);
                }

            }
        }


    }

using it:

 private void Form1_Load(object sender, EventArgs e)
        {
            SQLQuery s = new SQLQuery("*", "table", "", "id");
            pagedGrid1.SetPagedDataSource(s, bindingNavigator1);
        }

Note: The DataPrivier class is not included here , it is a simple class that returns datatable from any source.

Solution 5

Try this, this code is for OleDb, but also works for SqlServer connections. The dt (DataTable) object is filled with selected page rows, assuming that page starts with 1 (not 0)

public DataTable getData(string sql, int pgNo, int totalRows) 
{
DataTable dt = null;
        using (OleDbConnection conn = new OleDbConnection(connStr))
        {
            try
            {
                DataSet ds;
                conn.Open();
                ds = new DataSet();
                OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
                adapter.Fill(ds, (pgNo-1)*totalRows, totalRows, "Table");
                conn.Close();
                dt = ds.Tables[0];
            }
            catch (Exception ex)
            {if (conn != null) conn.Dispose();}
return dt;
}

View more solutions

Comments

[Mou]

this link may help programcall.com/9/dotnet/…

[Ankur Sharma]

@Rick Mohr : Hi! Thank you so much for the code. It is really helpful. Just one problem. I have code like: var transactionLogCount = TransactionLogManager.GetTransactionLogCount(); The above command gives total count. var transactionLogList = new List<ITransactionLog>(); transactionLogList.AddRange(TransactionLogManager.GetAllTran‌​sactionLog(int StartIndex, int EndIndex); now how to do paging when i need to add all the time in the for loop.

[Ramon Araujo]

Great! Just created a Custom Control to use it many times. A couple of things you should change. 1.- At the beginning of SetPagedDataSource you should initialize variables bs and tables: bs = new BindingSource(); tables = new BindingList<DataTable>(); 2.- Check if (bs.Position > 0) before the action inside bs_PositionChanged(). Congrats!

[Nwachukwu A. Nnaemeka]

how do you modify the columns, assuming you want to add columns like view, edit and delete

[Shantanu Gupta]

Seems like this solution would load entire data set at once. i.e. If there are 5mn records, all of them will be pulled into dataset. Correct?

[mw509]

Pretty much! But will only display what you define in the rowsPerPage