Listview Paging in C#

• Download LsvPaging-noexe.zip - 73.2 KB
• Download LsvPaging.zip - 153.9 KB

Introduction

In Microsoft Visual C#, windows Forms application Project, there is a control for loading data’s and values like a spreadsheet that called as “Listview”.

Most of the peoples like to split the data’s into pages. Because more data’s in single page is very difficult to read. So they like to separate the data’s into several pages.

Background

The Listiview Paging Applications are already developed. But the Logic to implement feature are very difficult in existing code. So I find the new alternate way to implement this code.

The following logic program is developed by me. And It clearly gives an idea for the programmer to how to implement the listview paging.  

Code for Getting Sample Listiview Index Generation for Listiview Paging Feature:  

using System;
namespace sample
{
    class test
    {
        static void Main(String[] args)
        {
            int TotalRec, NRPP;
            int TotalPages = 0;
            String p;
            System.Console.WriteLine("Enter Total Records");
            TotalRec = Convert.ToInt32(System.Console.ReadLine());
            //Getting value to set number records are shown in the page. 
            System.Console.WriteLine("Enter No. of Records Per Page");
            NRPP = Convert.ToInt32(System.Console.ReadLine());
            //calculating Total Pages
            TotalPages = TotalRec / NRPP;
            if (TotalRec % NRPP > 0)
                TotalPages++;
            System.Console.WriteLine("The Total Pages:{0}", TotalPages);
            //Creating Page Indexes
            int i, j;
            for (int k = 1; k <= TotalPages; k++)
            {
                System.Console.WriteLine("\n\n Page No:{0} \n", k);
                i = (k - 1) * NRPP;
                int start = i;
                int end = 0;
                j = k * NRPP;
                for (; i < j; i++)
                {
                    if (i >= TotalRec)
                    {
                        break;
                    }
                    System.Console.WriteLine("Record Index:{0}", i);
                    end = i;
                }
                System.Console.WriteLine("Records Showing From {0} to {1}", (start + 1), (end + 1));
                System.Console.WriteLine("*****End of Page***** \n");
                p = System.Console.ReadLine();
            }
        }
    }
}  

Using the code 

Form Design:  

I have used 4 command buttons as a page navigator. And I put one numeric up and down control to change how many records should shown on the listview.  I Used two panels for holding navigator buttons and  holding numerical up and down control. 

Imported Namespace: 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb; 

 I have used only one form to implement this feature.

Public Variable Class: 

public class LsvPageGlobVar
    {
        public static string ConStr;
        public static DataTable sqlDataTable = new DataTable();
        public static int TotalRec; //Variable for getting Total Records of the Table
        public static int NRPP; //Variable for Setting the Number of Recrods per listiview page
        public static int Page; //List View Page for Navigate or move
        public static int TotalPages; //Varibale for Counting Total Pages.
        public static int RecStart; //Variable for Getting Every Page Starting Record Index
        public static int RecEnd; //Variable for Getting Every Page End Record Index
    }  

These variables are holding shared values of form.

Public Method Class: 

I have created the following public class to create static methods to access the function in any other class.  

public class LsvPageFunc 

Data Base Connection Method: 

The database  is using to connect the desired database by OleDbConnection. 

public static bool DbConnection()
        {
            bool functionReturnValue = false;
            try
            {
                 LsvPageGlobVar.ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=../../Database.mdb";
                //LsvPageGlobVar.ConStr ="Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=|DataDirectory|\data.mdb";      
                
                OleDbConnection sqlCon = new OleDbConnection();
                sqlCon.ConnectionString = LsvPageGlobVar.ConStr;
                sqlCon.Open();
                functionReturnValue = true;
                sqlCon.Close();
            }
            catch (Exception ex)
            {
                functionReturnValue = false;
                MessageBox.Show("Error : " + ex.ToString());
            }
            return functionReturnValue;
        } 

Query Execution Method: 

The following code is used to execute the SQL Queries. The method that returns the data table. 

public static DataTable ExecSQLQry(string SQLQuery)
       {
           try
           {
               OleDbConnection sqlCon = new OleDbConnection(LsvPageGlobVar.ConStr);
               OleDbDataAdapter sqlDA = new OleDbDataAdapter(SQLQuery, sqlCon);
               OleDbCommandBuilder sqlCB = new OleDbCommandBuilder(sqlDA);
               LsvPageGlobVar.sqlDataTable.Reset();
               sqlDA.Fill(LsvPageGlobVar.sqlDataTable);
           }
           catch (Exception ex)
           {
               MessageBox.Show("Error : " + ex.ToString());
           }
           return LsvPageGlobVar.sqlDataTable;
       }

For example. We can execute the query. 

Just like,

DataTable dtTest = new DataTable();  
dtTest= ExecSQLQry("SELECT * FROM tbl_name"); 

List View Data Loader Method: 

The following code is main method to load the paging in Listview Control. 

public static void FillLsvData(DataTable sqlData, ListView lvList, int imageID)
        {
            //Load the table data in the listview
            int i = 0;
            int j = 0;
            int m = 0;
            int xsize;
            

            lvList.Clear();
            // for Adding Column Names from the datatable

            LsvPageGlobVar.TotalRec = sqlData.Rows.Count;

            //try
            //{
                LsvPageGlobVar.TotalPages = LsvPageGlobVar.TotalRec / LsvPageGlobVar.NRPP;

                if (LsvPageGlobVar.TotalRec % LsvPageGlobVar.NRPP > 0)
                {
                    LsvPageGlobVar.TotalPages++;
                }
            //}

            //catch(DivideByZeroException e)
            //{
            //    MessageBox.Show("Error : " + e.ToString());
            //}
            

            for (i = 0; i <= sqlData.Columns.Count - 1; i++)
            {
                lvList.Columns.Add(sqlData.Columns[i].ColumnName);
            }

            //for adding records to the listview from datatable
            int l, k;
            
            l = (LsvPageGlobVar.Page - 1) * LsvPageGlobVar.NRPP;
            k = ((LsvPageGlobVar.Page) * LsvPageGlobVar.NRPP);
            
            LsvPageGlobVar.RecStart = l + 1;
            if (k > LsvPageGlobVar.TotalRec)
            {
                LsvPageGlobVar.RecEnd = LsvPageGlobVar.TotalRec;
            }
            else
            {
                LsvPageGlobVar.RecEnd = k;
            }

            for (; l < k; l++)
            {
                if (l >= LsvPageGlobVar.TotalRec)
                {
                    break;
                }

                lvList.Items.Add(sqlData.Rows[l][0].ToString(), imageID);
               
                for (j = 1; j <= sqlData.Columns.Count - 1; j++)
                {
                    if (!System.Convert.IsDBNull(sqlData.Rows[l][j]))
                    {
                        lvList.Items[m].SubItems.Add(sqlData.Rows[l][j].ToString());
                        
                    }
                    else
                    {
                        lvList.Items[m].SubItems.Add("");
                        
                    } 
                }
                m++;
            }


            //for rearrange the column size
            for (i = 0; i <= sqlData.Columns.Count - 1; i++)
            {
                xsize = lvList.Width / sqlData.Columns.Count - 8;

                if (xsize > 1450)
                {
                    lvList.Columns[i].Width = xsize;
                    lvList.Columns[i].AutoResize(ColumnHeaderAutoResizeStyle.ColumnContent);
                }

                else
                {
                    lvList.Columns[i].Width = 2000;
                    lvList.Columns[i].AutoResize(ColumnHeaderAutoResizeStyle.HeaderSize);
                }



            }
        } 

From the above the following section, is used to calculate how many pages are required for the total records.

LsvPageGlobVar.TotalPages = LsvPageGlobVar.TotalRec / LsvPageGlobVar.NRPP;
                if (LsvPageGlobVar.TotalRec % LsvPageGlobVar.NRPP > 0)
                {
                    LsvPageGlobVar.TotalPages++; 

We easily get how many records in the datatable, by using sqlData.Rows.Count.

 And then main think is we need create a starting record index and ending record index. This is done by the following two statements.

l = (LsvPageGlobVar.Page - 1) * LsvPageGlobVar.NRPP;
k = ((LsvPageGlobVar.Page) * LsvPageGlobVar.NRPP);   

We can use this method like following example:

LsvPageFunc.FillLsvData(LsvPageFunc.ExecSQLQry("Select * from tbl_Employee"),lsvData,0); 

This section is completed about the public methods. Now we are going to see the form events. 

Form Events: 

 Form Loading Event: 

private void frmLsvPage_Load(object sender, EventArgs e)
       {
           lsvData.Width = this.Width - 30;
           lsvData.Height = pnlNavigate.Top-100;
           pnlNRPP.Left = this.Width - pnlNRPP.Width-30;
           LsvPageGlobVar.NRPP = Convert.ToInt32(nudNRPP.Value);
           
           LsvPageFunc.DbConnection();
           LsvPageGlobVar.Page = 1;
           LsvPageFunc.FillLsvData(LsvPageFunc.ExecSQLQry("Select * from tbl_Employee"),lsvData,0);
           lblInfo.Text="Record Shown: " + LsvPageGlobVar.RecStart + " to "+LsvPageGlobVar.RecEnd+ "                      Page "+LsvPageGlobVar.Page+" of " + LsvPageGlobVar.TotalPages;
       } 

The Variable LsvPageGlobVar.Page = 1; is initiating with first page to show the records. LsvPageGlobVar.NRPP is the variable using to set how many of records are shown in the listview page. 

Form Resize Event: 

The form resize event is using control the form layout in runtime.

private void frmLsvPage_Resize(object sender, EventArgs e)
       {
           lsvData.Width = this.Width - 30;
           lsvData.Height = pnlNavigate.Top - 100;
           pnlNRPP.Left = this.Width - pnlNRPP.Width - 30;
       } 

Navigation Button Events:

The following codes are using to navigate the listview pages.

Move Next Button Event: 

private void btnNext_Click(object sender, EventArgs e)
       {
           if (LsvPageGlobVar.Page < LsvPageGlobVar.TotalPages)
           {
               LsvPageGlobVar.Page++;
           }
           LsvPageFunc.FillLsvData(LsvPageFunc.ExecSQLQry("Select * from tbl_Employee"), lsvData, 0);
           lblInfo.Text = "Record Shown: " + LsvPageGlobVar.RecStart + " to " + LsvPageGlobVar.RecEnd + "                      Page " + LsvPageGlobVar.Page + " of " + LsvPageGlobVar.TotalPages;
       } 

The lblInfo is the label control that show the current record number and current page number.

Move Previous Button Event: 

private void btnPrev_Click(object sender, EventArgs e)
       {
           if (LsvPageGlobVar.Page > 1)
           {
               LsvPageGlobVar.Page--;
           }
           LsvPageFunc.FillLsvData(LsvPageFunc.ExecSQLQry("Select * from tbl_Employee"), lsvData, 0);
           lblInfo.Text = "Record Shown: " + LsvPageGlobVar.RecStart + " to " + LsvPageGlobVar.RecEnd + "                      Page " + LsvPageGlobVar.Page + " of " + LsvPageGlobVar.TotalPages;
       }  

Move First Button Event: 

private void btnFirst_Click(object sender, EventArgs e)
       {
           LsvPageGlobVar.Page = 1;
           LsvPageFunc.FillLsvData(LsvPageFunc.ExecSQLQry("Select * from tbl_Employee"), lsvData, 0);
           lblInfo.Text = "Record Shown: " + LsvPageGlobVar.RecStart + " to " + LsvPageGlobVar.RecEnd + "                      Page " + LsvPageGlobVar.Page + " of " + LsvPageGlobVar.TotalPages;
       } 

Move Last Button Event: 

private void btnLast_Click(object sender, EventArgs e)
       {
           LsvPageGlobVar.Page = LsvPageGlobVar.TotalPages;
           LsvPageFunc.FillLsvData(LsvPageFunc.ExecSQLQry("Select * from tbl_Employee"), lsvData, 0);
           lblInfo.Text = "Record Shown: " + LsvPageGlobVar.RecStart + " to " + LsvPageGlobVar.RecEnd + "                      Page " + LsvPageGlobVar.Page + " of " + LsvPageGlobVar.TotalPages;
       } 

Numeric up and Down Control:(optional) 

This is optional event. Because the control that produce the 0 value. The control values is moved to the total pages calculation. It is used as a divider. If the value is 0, the system that produce an error. So I put this event to handle the Zero Division Error. 

And the Main think I produce this code to change the on time record showing. 

private void nudNRPP_ValueChanged(object sender, EventArgs e)
      {
          if (nudNRPP.Value != 0)
          {
              LsvPageGlobVar.NRPP = Convert.ToInt32(nudNRPP.Value);
          }
          else
          {
              nudNRPP.Value = 1;
          }
          LsvPageFunc.FillLsvData(LsvPageFunc.ExecSQLQry("Select * from tbl_Employee"), lsvData, 0);
          lblInfo.Text = "Record Shown: " + LsvPageGlobVar.RecStart + " to " + LsvPageGlobVar.RecEnd + "                      Page " + LsvPageGlobVar.Page + " of " + LsvPageGlobVar.TotalPages;

      }

Output Screen: 

Here the different state of the application.