C# – Create pagination for asynchronous loading of data into datagrid view
When loading data into a DataGrid 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:
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
}
}
2 Comments
Leave a Reply
Developing a C# WebApi on Domain-Driven Design (DDD) in .Net6 using Clean Architecture
Total Page Visits: 2575 – Today Page Visits: 2
WinForm is unlike ASP.NET (Web Application). ASP.NET is sending data through internet connection. There is a performance issue if you are sending large data to client’s web browser. But in WinForm development, the data is transmitting within the memory of computer. Hundred thousands of rows is sent and received almost instantly. You can load Hundred Thousands of rows at once at DataGridView. No problem. You just need to implement VirtualMode of DataGridView control. By enabling VirtualMode = true, DataGridView is not holding any actual data. The data is retrieved from other source such as DataTable, List object , etc. Therefore, the DataGridView will consume very little of memory. Here is some of the basic intro about VirtualMode. For more details, you may Google it. (Note: Code Tested)
This method is a custom c# solution to load data into DataGridView via a Virtual Private Network through the internet.