Csharp:ListView paging and groups from Any Data Sources

 

    /// <summary>
    /// ListView 分頁，分組
    /// http://www.codeproject.com/Tips/535722/Listview-Paging-in-Csharp
    /// http://www.c-sharpcorner.com/uploadfile/853a6a/grouping-listview-items-dynamically/
    /// </summary>
    public partial class LsvPagingForm : Form
    {


        /// <summary>
        /// 設定分組
        /// </summary>
        /// <param name="item"></param>
        private void GroupItem(ListViewItem item)
        {
            // This flag will tell us if proper group already exists
            bool group_exists = false;

            // Check each group if it fits to the item
            foreach (ListViewGroup group in this.lsvData.Groups)
            {
                // Compare group's header to selected subitem's text
                if (group.Header == item.SubItems[this.groupBox.SelectedIndex].Text)
                {
                    // Add item to the group.
                    // Alternative is: group.Items.Add(item);
                    item.Group = group;
                    group_exists = true;
                    //group.Items.Count;
                    break;
                }
            }

            // Create new group if no proper group was found
            if (!group_exists)
            {
                // Create group and specify its header by
                // getting selected subitem's text
                ListViewGroup group = new ListViewGroup(item.SubItems[this.groupBox.SelectedIndex].Text);
                // We need to add the group to the ListView first
                this.lsvData.Groups.Add(group);
                item.Group = group;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        public LsvPagingForm()
        {
            InitializeComponent();

            //根據選定的字段分組
            // Add an event handler to the SelectedIndexChanged event,
            // which occurs when different type of grouping is selected
            this.groupBox.SelectedIndexChanged += (object o, EventArgs e) =>
            {
                // Clear group collection
                this.lsvData.Groups.Clear();

                // Loop through all existing items to group them properly
                foreach (ListViewItem item in this.lsvData.Items)
                {
                    GroupItem(item);
                }
            };
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void LsvPagingForm_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;
            
            //獲取各字段名稱
            for(int i=0;i<lsvData.Columns.Count;i++)
            {
               this.groupBox.Items.Add( lsvData.Columns[i].Text);
            }
            this.groupBox.SelectedIndex = 2;
            
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        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; 
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        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;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        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;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        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;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        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;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void LsvPagingForm_FormClosing(object sender, FormClosingEventArgs e)
        {
            //MessageBox.Show("Thanks & Regards \n By Anand. G, \n geovindu@live.in \n This is Redirect to My Facebook Page", "List View With Paging", MessageBoxButtons.OK, MessageBoxIcon.Information);
            //System.Diagnostics.Process.Start("http://www.dusystem.com/");
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void LsvPagingForm_Resize(object sender, EventArgs e)
        {
            lsvData.Width = this.Width - 30;
            lsvData.Height = pnlNavigate.Top - 100;
            pnlNRPP.Left = this.Width - pnlNRPP.Width - 30;
        }
        /// <summary>
        /// 獲取選定行的值
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void lsvData_ItemActivate(object sender, EventArgs e)
        {
            int k=0;
            //if (lsvData.Items[1].Selected == true)
            //{
            //    k = lsvData.SelectedItems.Count;

            //}
            ListView.SelectedListViewItemCollection breakfast =this.lsvData.SelectedItems;

            string price = "";
            foreach (ListViewItem item in breakfast)
            {
                for (int i = 0; i < item.SubItems.Count; i++)
                {
                    price += item.SubItems[i].Text + ","; //選定行第i列的值
                }
            }
            MessageBox.Show(price);

            if (lsvData.Items.Count > 0)
            {
                //lsvData.Items[0].Selected = true;
                MessageBox.Show(this.lsvData.SelectedItems[0].SubItems[0].Text + "," + this.lsvData.SelectedItems[0].SubItems[1].Text);
                Clipboard.SetDataObject(this.lsvData.SelectedItems[0].SubItems[1].Text);
                textBox1.Paste();
            }
        }

    }

    #region 

    /// <summary>
    /// 
    /// </summary>
     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
    }
    /// <summary>
    /// 
    /// </summary>
    public class LsvPageFunc
    {
        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;
        }

        //Function to execute all queires
        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;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlData"></param>
        /// <param name="lvList"></param>
        /// <param name="imageID"></param>
        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);
                }



            }
        }
    }
    #endregion