Paging DataGridView in C# using LINQ

• Download PagingWithCSharpLINQ.zip - 1,017 B

Introduction

This article explains how you can make a DataGridview with paging capability in C# for Windows Forms (not in ASP.NET because there is a DataGridView with paging property in ASP.NET). It is so simple to do this so I decided to share my expereince with those who want to know!

Background

My idea was created when I wanted to show many records in a DataGridView that did not cause reduced speed of loading the form and data, so in this example at first I loaded 5 by 5 records in a DataGridView for best performance.

Using the code

We shall do the following:

1- Create a Windows application form in Visual Studio 2010 (or 2008) with language C#.

2. In Form1, we shall drag and drop three component from toolbox(two buttons and one datagridview)

Then change the properties of the buttons as follows:

• Name:btnNext Text:Next
• Name:btnPrev Text:Prev

3. The next step is create a server-base .mdf database by right click on project in Solution Explorer and select Add--->New item and then choose server-based database:

Now right click in server-based database in Solution Explorer and click open after that in server explorer right click on the database and select "New table" and create a table with three columns called "Username", "Password", "id". Then save the table. After that right click on Solution Explorer and select Add->New item->LINQ to SQL classes and press OK.

Then drag the table called users (this table created by server-based database SQL data access .mdf in visual studio 2010):

In the form class we shall define the following variables:

public static int nTotalRow  //total rows for next button
public static int pTotalRow  //total rows for previos button
public static int nSkkipedRows  //the steps for skkiping rows in next button
public static int pSkkipedRows    //the steps for skkiping rows in previous button
public static int Total             //total rows of table

Finally using LINQ we can use the "skip" and "take" methods for showing data in a DataGridView. In the form load event, we write the following:

private void Form1_Load(object sender, EventArgs e)
{
    btnPrev.Enabled = false;

    DataClasses1DataContext d = new DataClasses1DataContext();
    var Rowss = from q in d.UsersTables
    select new { q.Username,q.Password };
    nTotalRow = Rowss.Count();
    pTotalRow = Rowss.Count();
    Total = Rowss.Count();

    if (nTotalRow <= 5)
    {
        var nr = (from qq in d.UsersTables
        select new {qq.Username,qq.Password});
        dataGridView1.DataSource = nr;
    }
    else if (nTotalRow > 5)
    {
        nSkippedRows = 5;

        var nr = (from qq in d.UsersTables

        select new { qq.Username,qq.Password}).Take(nSkippedRows);
        dataGridView1.DataSource = nr;
        //calculate the remaining rows for next button
        nTotalRow= nTotalRow - nSkippedRows;}
        rivate void Form1_Load(object sender, EventArgs e)
        {
            btnPrev.Enabled = false;

            DataClasses1DataContext d = new DataClasses1DataContext();
            var Rowss = from q in d.UsersTables
            select new { q.Username,q.Password };
            nTotalRow = Rowss.Count();
            pTotalRow = Rowss.Count();
            Total = Rowss.Count();

            if (nTotalRow <= 5)
            {
                var nr = (from qq in d.UsersTables
                select new {qq.Username,qq.Password});
                dataGridView1.DataSource = nr;
            }
            else if (nTotalRow > 5)
            {
                nSkippedRows = 5;

                var nr = (from qq in d.UsersTables

                select new { qq.Username,qq.Password}).Take(nSkippedRows);
                dataGridView1.DataSource = nr;
                //calculate the remaining rows for next button
                nTotalRow= nTotalRow - nSkippedRows;
            }

In the click event of two buttons we write the following:

private void btnPrev_Click(object sender, EventArgs e)
{
    DataClasses1DataContext d = new DataClasses1DataContext();
    if ( ((Total- pTotalRow)-5 ==0) || Total- pTotalRow==0)
    {
        var Rowss = (from q in d.UsersTables
                     orderby q.id
                     select new { q.Username,q.Password}).Take(5);
        dataGridView1.DataSource = Rowss;
        nTotalRow += 5;
        nSkippedRows-=5;

        if (nSkippedRows == 0)
        {
            nSkippedRows = 5;
        }

        //calculate total rows for previous button
        pTotalRow += 5;
        btnPrev.Enabled = false;
        btnNext.Enabled = true;  }
        else if (Total - pTotalRow > 5)
        {
            var Rowss = (from q in d.UsersTables
                         orderby q.id
                         select new { q.Username, q.Password }).Skip(Total - pTotalRow - 5).Take(5);
            dataGridView1.DataSource = Rowss;
            btnNext.Enabled = true;
            pTotalRow += 5;
            nTotalRow = nTotalRow + 5;
            nSkippedRows -= 5;
        }

private void btnNext_Click(object sender, EventArgs e)
{
    DataClasses1DataContext d = new DataClasses1DataContext();
    if (nTotalRow <= 5)
    {

        var Rowss = (from q in d.UsersTables
             orderby q.id
             select new { q.Username,q.Password }).Skip(nSkippedRows).Take(nTotalRow);
        dataGridView1.DataSource = Rowss;
        pTotalRow -= 5;
        nSkippedRows += 5;
        nTotalRow -= 5;
        btnNext.Enabled = false;
        btnPrev.Enabled = true;
    }
    else if (nTotalRow > 5)
    {
        var Rowss = (from q in d.UsersTables
                             orderby q.id
                             select new { q.Username,q.Password}).Skip(nSkippedRows).Take(5);
        dataGridView1.DataSource = Rowss;
        pTotalRow -= 5;
        nTotalRow = nTotalRow - 5;
        nSkippedRows += 5;
        btnPrev.Enabled = true;
    }

You can download the project and see how exactly it works.

in deed we have a table calles "UsersTable" with following records(17 records):

when we run the application we will see like this at the first five records(this datagridview show records 5 by 5):

now when click the "Next" button we will see this records:

if we press "Next" button more times we see the last page like this:

in this case the "Next" disabled and we can revers by "Prev" button.

Points of Interest

I am really interested in using LINQ, it is so easy and a very complete technology.