C# – Create pagination for asynchronous loading of data into datagrid view

When loading data into a datagird view you will learn that sometimes there is too much data in the query that you need to paginate the results to make your desktop application load much faster. You can load data asynchronously using pagination to limit the results and only request more data using a button to paginate to the other rows. Please see previous post on how to load data into a datagrid view from multiple tables. Once complete the code below will paginate the results.

Step 1: Create a new view in SQL
To create a new view in Microsoft SQL first log into SQL database manager with your user credentials and double click on your database to expand the menu items. Select Views and then right click and select New View from the menu items. This will open a new pane. Here you need to add the tables from your database and build the relations between your tables. Once complete click save and name the view.

Step 2: Add View to Data Grid Component
In Visual Studio 2015 IDE, create a new windows form then select new data grid view component from toolbox menu and add it to your form. Click the arrow on the top right hand corner and from the drop down menu select Add Project Data Source. This will open the import wizard. Select the view you created in Step 1 and click finish to add the view data to your data grid. You will be able to edit the column headers later on.

Step 3: Add PagedList NuGet Package
In Visual Studio 2015 IDE, Go to Tools->NuGet Package Manager -> Manage NuGet Packages for Solution and Search for PagedList v1.17.0 by Troy Goode. This library allows us to programmatically develop pagination that loads data asynchronously so that your application loads faster. Please see final coding step below:

Create New View in SQL 2008
Create new view in Microsoft SQL 2008
Add Project Data Source Visual Studio IDe
Add View to your projects data source
Add Paged List to C Project
Add Paged List Package to project using NuGet Package Manager.

Final Step: Add Pagination Code to your Form
You will need to add two buttons to your form: a btnNext and a btnPrevious as well as the asynchronous function call to load the data:

 

Add to top:

using PagedList;

int pageNumber = 1;
IPagedList<YOURVIEWOBJECT> list;

 

public async Task<IPagedList<YOURVIEWOBJECT>> GetPagedListAsync(string order, int pageNumber = 1, int pageSize = 40)
{
return await Task.Factory.StartNew(() =>
{
using (dbEntities db = new dbEntities())
{
if (order == "Ascending")
{
//YOUR DATA QUERY

return db.YOURVIEWOBJECT.Where(f => f.zone > 0).OrderBy(p => p.id).ToPagedList(pageNumber, pageSize);
}
else
{
return db.YOURVIEWOBJECT.Where(f => f.zone > 0).OrderByDescending(p => p.id).ToPagedList(pageNumber, pageSize);


}
}

});
}

 

 

private async void btnNext_Click(object sender, EventArgs e)
{

ListSortDirection direction;
var order = dataGridView1.Columns[0].HeaderCell.SortGlyphDirection.ToString();

if (list.HasNextPage)
{

list = await GetPagedListAsync(order, ++pageNumber, 40);

btnFirst.Enabled = list.HasPreviousPage;
btnNext.Enabled = list.HasNextPage;
vYOURVIEWBindingSource.DataSource = list.ToList();
lblPage.Text = string.Format("Page {0}/{1}", pageNumber, list.PageCount); //Page Number Label
}
}

private async void btnFirst_Click(object sender, EventArgs e)
{

ListSortDirection direction;
var order = dataGridView1.Columns[0].HeaderCell.SortGlyphDirection.ToString();

if (list.HasPreviousPage)
{

list = await GetPagedListAsync(order, --pageNumber, 40);

btnFirst.Enabled = list.HasPreviousPage;
btnNext.Enabled = list.HasNextPage;
vYOURVIEWBindingSource.DataSource = list.ToList();
lblPage.Text = string.Format("Page {0}/{1}", pageNumber, list.PageCount); //Page Number Label

}
}

Leave a Reply


How to code your own sFTP File Uploader Windows application using C# and SSH

Total Page Visits: 521 – Today Page Visits: 1

Total Page Visits: 521 - Today Page Visits: 1