Bind DataGridView with custom paging using C# (.Net)

on

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

namespace Paging_inDataGridView
{
    public partial class Form1 : Form
    {
        SqlConnection connString;
        private SqlCommand sqlCmd1;
        private SqlCommand sqlCmd2;
        private SqlDataAdapter da;
        DataTable dt;

        private int currPageSize = 5;
        private int CurrcurrPageIndex = 1;
        private int totalcurrPageCount = 0;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            connString = new SqlConnection("SERVER=local;Initial Catalog=Test; Integrated Security=SSPI");
            sqlCmd1 = new SqlCommand("SELECT * FROM employeeMaster ORDER BY EmpId", connString);
            dt = new DataTable();
            da = new SqlDataAdapter(sqlCmd1);
            da.Fill(dt);
            dGV.DataSource = dt;
            // Get total count of the currPages;
            this.TotalPageCount();
            this.dGV.ReadOnly = true;
            // Load the first currPage of data;
            this.dGV.DataSource = GetCurrentRecords(1);
        }

        private void TotalPageCount()
        {
            int rowCount = dt.Rows.Count;
            this.totalcurrPageCount = rowCount / currPageSize;
            if (rowCount % currPageSize > 0)
            {
                this.totalcurrPageCount += 1;
            }
        }

        private DataTable GetCurrentRecords(int currPage)
        {
            DataTable dt = new DataTable();

            if (currPage == 1)
            {
                sqlCmd2 = new SqlCommand("SELECT TOP " + currPageSize + " * FROM employeeMaster ORDER BY EmpId", connString);
            }
            else
            {
                int PreviouscurrPageLimit = (currPage - 1) * currPageSize;

                sqlCmd2 = new SqlCommand("SELECT TOP " + currPageSize +
                    " * FROM employeeMaster " +
                    "WHERE EmpId NOT IN " +
                "(SELECT TOP " + PreviouscurrPageLimit + " EmpId from employeeMaster ORDER BY EmpId) ", connString); // +
            }
            try
            {
                this.da.SelectCommand = sqlCmd2;
                this.da.Fill(dt);
            }
            catch (Exception ex)
            {
                if (connString.State == ConnectionState.Open)
                {
                    connString.Close();
                }
            }
            return dt;
        }

        private void btnFirstPAge_Click(object sender, EventArgs e)
        {
            this.CurrcurrPageIndex = 1;
            this.dGV.DataSource = GetCurrentRecords(this.CurrcurrPageIndex);
        }

        private void btnNxtPage_Click(object sender, EventArgs e)
        {
            if (this.CurrcurrPageIndex < this.totalcurrPageCount)
            {
                this.CurrcurrPageIndex++;
                this.dGV.DataSource = GetCurrentRecords(this.CurrcurrPageIndex);
            }
        }

        private void btnPrevPage_Click(object sender, EventArgs e)
        {
            if (this.CurrcurrPageIndex > 1)
            {
                this.CurrcurrPageIndex--;
                this.dGV.DataSource = GetCurrentRecords(this.CurrcurrPageIndex);
            }
        }

        private void btnLastPage_Click(object sender, EventArgs e)
        {
            this.CurrcurrPageIndex = totalcurrPageCount;
            this.dGV.DataSource = GetCurrentRecords(this.CurrcurrPageIndex);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}

Sorry Guys I am not able to put on the screen, It’s very simple you just take a DataGridView with the same name used by me and button too. I don’t think it is very complex.

Comments

Post a Comment