C# DataGridView Pagination Tutorial

In this tutorial, we'll see how to use paginated DataGridView control and simplify the development. Pagination is highly recommended when loading a huge data set from the database.

Sample source code used in the demo.

string conStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;


private void populate()
{
using (SqlConnection con = new SqlConnection(conStr))
{
string sql = "SELECT * FROM Products";
SqlDataAdapter sda = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
sda.Fill(ds, "Products");
dataGridView1.DataSource = ds.Tables["Products"];
}
}


dataGridViewPaging1.RequestQueryData += DataGridViewPaging_RequestQueryData;
dataGridViewPaging1.Initialize(count());



private int count()
{
using (SqlConnection con = new SqlConnection(conStr))
{
con.Open();
using (var command = con.CreateCommand())
{
command.CommandText = "SELECT COUNT(*) FROM Products";
var reader = command.ExecuteScalar();
return Convert.ToInt32(reader);
}
}
}


private void DataGridViewPaging_RequestQueryData(object sender, RequestQueryDataEventArgs e)
{
using (SqlConnection con = new SqlConnection(conStr))
{
con.Open();
using (var command = con.CreateCommand())
{
command.CommandText = "SELECT * FROM Products ORDER BY Id OFFSET " + e.PageOffset + "ROWS FETCH NEXT " + e.MaxRecords + " ROWS ONLY";
dataGridViewPaging1.DataSource = command.ExecuteReader();
}
}

}