Wednesday, November 6, 2013

DataGridview Paging using BindingSource and BindingNavigator in C#

Here's a simple way to integrate paging in Datagridview using BindingNavigator and BindingSource. The original source can be found here: How can we do paging in datagridview in winform
I made some modifications to simulate loading of thousands of records from a database.
Main Form Class:
public partial class FBinding : Form  
{  
 public static int totalRecords { get; set; }  
 public const int pageSize = 10;  
 private List<string> sourceData = new List<string>();  
 private DataTable dtSource = new DataTable();        
 public FBinding()  
 {  
   InitializeComponent();  
   bindingNav.BindingSource = bindingWebsite;  
   bindingWebsite.CurrentChanged += new EventHandler(bindingWebsite_CurrentChanged);  
   SetSource();  
   bindingWebsite.DataSource = new PageOffsetList();  
 }  
 void bindingWebsite_CurrentChanged(object sender, EventArgs e)  
 {  
   // The desired page has changed, so fetch the page of records using the "Current" offset   
   int offset = (int)bindingWebsite.Current;  
   var records = new List<Record>();  
   for (int i = offset; i < offset + pageSize && i < totalRecords; i++)  
   {  
  try  
  {  
    records.Add(new Record() { Website = sourceData[i].ToString() });  
  }  
  catch (Exception ex)  
  {  
  }  
   }  
   dgWebsites.DataSource = records;  
 }  
 private void SetSource()  
 {  
   string sql = string.Format("select trim(website), url from tblwebsite order by trim(website) asc;");  
   MySqlConnection conn = null;  
   try  
   {  
  string connection = ConfigurationManager.AppSettings["AdminWebsite"].ToString();  
  conn = new MySqlConnection(connection);  
  conn.Open();  
  MySqlCommand cmd = new MySqlCommand(sql, conn);  
  MySqlDataAdapter da = new MySqlDataAdapter(cmd);  
  da.Fill(dtSource);  
  if (dtSource.Rows.Count > 0)  
  {  
    //totalRecords = dtSource.Rows.Count;  
    foreach (DataRow item in dtSource.Rows)  
    {  
   if (!string.IsNullOrEmpty(item[0].ToString()) && !string.IsNullOrEmpty(item[1].ToString()))  
   {  
     sourceData.Add(item[0].ToString() + ", " + item[1].ToString());  
   }  
    }  
  }  
  totalRecords = sourceData.Count;  
  conn.Close();  
   }  
   catch (Exception e)  
   {  
  conn.Close();  
  conn.Dispose();  
   }  
 }  
 private void FBinding_Load(object sender, EventArgs e)  
 { }  
}  
Here's the classes used in Databinding:
public class Record   
{   
    public string Website { get; set; }   
}  
Paging class:
public class PageOffsetList : IListSource   
   {   
    public bool ContainsListCollection    
    {    
     get;    
     protected set;   
    }   
    private int TotalRecords = 0;   
    private int pageSize = 0;   
    public PageOffsetList(int pageSize, int total)   
    {   
     this.TotalRecords = total;   
     this.pageSize = pageSize;   
    }   
    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 = offset + pageSize)   
     {   
      pageOffsets.Add(offset);   
     }   
     return pageOffsets;   
    }   
   }   
Here's the result of the program:
I also segregated the classes in my application to simplify the approach.
Source code available for download at Github

5 comments:

  1. Replies
    1. You have to be specific about the error or why it doesn't work on your end.

      Delete
  2. Can you give me the source code. I am having hard time try to understand from stackoverflow

    ReplyDelete
    Replies
    1. I've forgotten where I saved the sample project for this tutorial since we don't use MySQL in our projects as of this time. The solution presented in StackOverflow is straightforward and easy to follow. All you need to do is replace the source with actual data from your database. I'll try to upload a sample project for this @ github once I have free time.

      Delete
  3. I have updated the article to include the source code which is available for download at Github. :-)

    ReplyDelete