Home > ASP.Net, Linq > Gridview Paging with Linq

Gridview Paging with Linq


Hi

try this example for Gridview Paging using Linq to SQL

Step1 :- Open MS VS2008 and create a new website

Step2 :- In solution Explorer right cleck and select add new item and add LINQ to SQL classes and name it to “Northwind.dbml”

Step3 :- Open Northwind.dbml and Add Employees Table in Northwind Database and the Save

Step4 :- Right click on solution Explorer and add new class and name it “EmployeeDAL.cs”


using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;

/// <summary>
/// Summary description for EmployeeDAL
/// </summary>
public class EmployeeDAL
{
 public EmployeeDAL()
 {
  //
  // TODO: Add constructor logic here
  //
 }

    public int GetRowsCount()
    {
        NorthwindDataContext db = new NorthwindDataContext();
        try
        {
            return db.Employees.Count();
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }

    public List<Employee> SelectAll()
    {
        NorthwindDataContext db = new NorthwindDataContext();
        try
        {
            return db.Employees.ToList<Employee>();
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }

    public List<Employee> GetDataPaging(int startRows, int maxRows)
    {
        NorthwindDataContext db = new NorthwindDataContext();
        try
        {
            return db.Employees.Skip(startRows).Take(maxRows).ToList<Employee>();
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }

    public System.Web.UI.WebControls.ObjectDataSource GetData()
    {
        try
        {
            ObjectDataSource obds = new ObjectDataSource();
            obds.TypeName = "EmployeeDAL";
            obds.SelectMethod = "GetDataPaging";
            obds.MaximumRowsParameterName = "maxRows";
            obds.StartRowIndexParameterName = "startRows";
            obds.SelectCountMethod = "GetRowsCount";
            obds.EnablePaging = true;
            return obds;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }
}

Step5 :- In Default.aspx page add new Gridview control

    <div>
        <asp:GridView ID="GridView1" runat="server" AllowPaging="True" PageSize="5"
            AutoGenerateColumns="False" CellPadding="4" DataKeyNames="EmployeeID"
            ForeColor="#333333" GridLines="None"
            onpageindexchanging="GridView1_PageIndexChanging">
            <RowStyle BackColor="#EFF3FB" />
            <Columns>
                <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID"
                    InsertVisible="False" ReadOnly="True" SortExpression="EmployeeID" />
                <asp:BoundField DataField="FirstName" HeaderText="FirstName"
                    SortExpression="FirstName" />
                <asp:BoundField DataField="LastName" HeaderText="LastName"
                    SortExpression="LastName" />
            </Columns>
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <EditRowStyle BackColor="#2461BF" />
            <AlternatingRowStyle BackColor="White" />
        </asp:GridView>
    </div>

Step6 :- in code behind of Default.aspx.cs add the following code


using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public partial class Default5 : System.Web.UI.Page
{
    EmployeeDAL emp = new EmployeeDAL();

    public void FillGrid()
    {
        GridView1.DataSource = emp.GetData();
        GridView1.DataBind();
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            FillGrid();
        }
    }

    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        FillGrid();
    }
}

the run your website

Hope this helps

Good Luck.

Advertisements
Categories: ASP.Net, Linq Tags: ,
  1. Bruno
    June 20, 2010 at 4:42 am

    Great job!!!! The best way to do this gridview paging using linq to sql I saw.

  2. yasserzaid
    June 20, 2010 at 3:55 pm

    @Bruno :- Thank you for your comment hope my post helps you

  3. Indy
    September 16, 2010 at 6:22 am

    Thanks for the clear example.

  4. yasserzaid
    September 16, 2010 at 6:11 pm

    @Indy :- you are welcome hope my post helps you … Good Luck

  5. Eddie
    February 16, 2011 at 4:27 pm

    This is great post on paging with linq to sql. One question though, how would this work if passing a parameter like in the where clause? Thanks…

    • Lin
      February 24, 2011 at 7:20 am

      I aslo have the same question. I need to pass a parameter.How would i go about that. Thanks

  6. Denis Storti
    February 28, 2012 at 8:29 pm

    It’s beautiful! hahahaha thank you

  7. Denis Storti
    February 28, 2012 at 8:51 pm

    Parameter to where clause:
    Put it before returning obds in Step 4 (you can use also a SelectParameter):

    ControlParameter param = new ControlParameter("newParameter", "YOURCONTROL", "ITSPROPERTY");
                    obds.SelectParameters["newParameter"] = param;
                    pnlSelecionarProjetos.Controls.Add(obds);   //must place it within a container, like a panel
    

    So add the parameter to the GetRowsCount and GetDataPaging methods (before startRows and maxRows params). For example:

    public List<ProjetoSelecionado> GetDataPaging(string newParameter, int startRows, int maxRows)

    Now you can use your parameter in your LINQ query like:

    where (string.IsNullOrEmpty(novoParametro)) || p.t_carta_convite.t_safra.num_ano.ToString() == novoParametro

    That’s it. Let me know if any doubts arise.

  8. suganthi
    September 27, 2012 at 10:54 am

    I need coding for custom paging.It should be First previous 1 2 3 4 5 6 7 8 9 10 then ….( … for mention lot of record there) next and last.It should be increased in10 10 records

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: