Tuesday, 29 May 2012

C# Programming : Paging Query Using LINQ

Using LINQ we can achive paging logic in your application much easier by exposing the Skip and Take methods. The Skip method enables you to skip a defined number of records in the resultset. The Take method enables you to specify the number of records to return from the resultset. By calling Skip and then Take, you can return a specific number of records from a specific location of the resultset.
You can use Skip and Take methods Take method in any LINQ Query either Simple query or Joining Query.

In this sample example We skip first 10 records and take next 10 records for second page , For first page skipp zero records like this ".skip(0)" and take 10 records. This will give first 10 records for first page.

Here are example.

C# Example :
    public class Books
        public string Title { get; set; }
        public string ISBN { get; set; }
        public DateTime ReleaseDate { get; set; }
        public int Pages { get; set; }
        public int PublisherId { get; set; }

    protected void Page_Load(object sender, EventArgs e)
        var bookslist = GetBooksList();
        var query = (from bl in bookslist select bl).Skip(10).Take(10);
        this.gvBooks.DataSource = query;
    public List<Books> GetBooksList()
        return new List<Books> {
                        new Books { Title="ASP.NET",ISBN="asp1",ReleaseDate= DateTime.Parse( "11/11/2010") ,Pages=200,PublisherId=1},
                        new Books { Title="C#.NET",ISBN="c#2",ReleaseDate= DateTime.Parse( "10/11/2010") ,Pages=500,PublisherId=1},
                        new Books { Title="VB.NET",ISBN="vb3",ReleaseDate= DateTime.Parse( "5/5/2009") ,Pages=400,PublisherId=1},
                        new Books { Title="SQL Server",ISBN="sql4",ReleaseDate= DateTime.Parse( "6/9/2010"),Pages=300,PublisherId=2 },
                        new Books { Title="JAVA",ISBN="java5",ReleaseDate= DateTime.Parse( "8/5/2011"),Pages=400,PublisherId=3 }

VB.net Example :
    Public Class Books
        Public Property Title() As String
        Public Property ISBN As String
        Public Property ReleaseDate As Date
        Public Property Pages As Integer
        Public Property PublisherId As Integer
    End Class

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim bookslist = GetBooksList()
        Dim query = (From bl In bookslist _
                    Select bl).Skip(10).Take(10)
        gvBooks.DataSource = Query
    End Sub
    Public Function GetBooksList() As List(Of Books)
        Dim lstBooks As New List(Of Books) From { _
                                New Books With {.Title = "ASP.NET", .ISBN = "asp1", .ReleaseDate = DateTime.Parse("11/11/2010"), .Pages = 200, .PublisherId = 1}, _
                                New Books With {.Title = "C#.NET", .ISBN = "c#2", .ReleaseDate = DateTime.Parse("10/11/2010"), .Pages = 500, .PublisherId = 1}, _
                                New Books With {.Title = "VB.NET", .ISBN = "vb3", .ReleaseDate = DateTime.Parse("5/5/2009"), .Pages = 400, .PublisherId = 1}, _
                                New Books With {.Title = "SQL Server", .ISBN = "sql4", .ReleaseDate = DateTime.Parse("6/9/2010"), .Pages = 300, .PublisherId = 2}, _
                                New Books With {.Title = "JAVA", .ISBN = "java5", .ReleaseDate = DateTime.Parse("8/5/2011"), .Pages = 400, .PublisherId = 3}}

        Return lstBooks
    End Function

No comments:

Post a Comment