Paging in LINQ


Are you serching article about how to Paging in LINQ with code snippet

This code is to retrieve data from database table and binding it to grid and paging in LINQ.



Dim db As New TestDataContext

Dim tcs = From p In db.tblTests _
where p.site="dotnetspider" _
Skip 20 Take 10

GridView1.DataSource = tcs
GridView1.DataBind()



Comments

Author: Phagu Mahato09 Feb 2014 Member Level: Gold   Points : 10

You can try to use this sample code for Paging in LINQ


using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Linq;
using System.Collections.Generic;


namespace pinCodeObjects
{

{


private static readonly object ReadLock = new object();


private static List m_pinCodeInstance;
public static List pinCodeInstance
{
get
{

if (m_pinCodeInstance == null)
{
lock (ReadLock)
{
if (m_pinCodeInstance == null)
{
m_pinCodeInstance = LoadData();
}
}

}
return m_pinCodeInstance;
}
}


public static IEnumerable GetpinCodes(int ResultsPerPage, int PageNumber)
{

//number of records to skip
int skip = (PageNumber - 1) * ResultsPerPage;

//number of results per page.
int take = ResultsPerPage;

//execute Linq query for result set
IEnumerable result = (from pincodes in pinCodeInstance select pincodes).Skip(skip).Take(ResultsPerPage);

//return result
return result;
}


public static int SelectCount()
{
return (from pincodes in pinCodeInstance select pincodes).Count();
}


private static List LoadData()
{


List pinlist = new List();


System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["ProgrammersJournal"].ConnectionString;

conn.Open();

try
{


SqlCommand cmd = new SqlCommand("pj_getallpincodes", conn);


using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{


pinlist.Add(new pinCode(reader.GetSqlString(0).ToString(), reader.GetSqlString(1).ToString(), reader.GetSqlString(2).ToString(), Convert.ToDecimal(reader.GetSqlString(3).ToString()), Convert.ToDecimal(reader.GetSqlString(4).ToString()), Convert.ToInt32(reader.GetSqlString(5).ToString()), Convert.ToInt16(reader.GetSqlString(6).ToString())));
}
}
}

catch (Exception ex)
{


throw new Exception(ex.Message);
}

finally
{


if (conn.State != ConnectionState.Closed)
{
conn.Close();
}

//instance is populated
//force garbage collection
GC.Collect();
GC.WaitForPendingFinalizers();

}

//return new instance of pin code collection
return pinlist;
}

private pinCodeCollection()
{

}

}


public class pinCode
{


private string m_pin;
public string pin
{
get { return m_pin; }
}


public string City
{
get { return m_City; }
}


private string m_State;
public string State
{
get { return m_State; }
}


private decimal m_Latitude;
public decimal Latitude
{
get { return m_Latitude; }
}


private decimal m_Longitude;
public decimal Longitude
{
get { return m_Longitude; }
}


private int m_TimeZone;
public int TimeZone
{
get { return m_TimeZone; }
}


private short m_Dst;
public short Dst
{
get { return m_Dst; }
}


public pinCode()
{

}

public pinCode(string pin, string city, string state, decimal latitude, decimal longitude, int timeZone, short dst)
{

this.m_pin = pin;
this.m_City = city;
this.m_State = state;
this.m_Latitude = latitude;
this.m_Longitude = longitude;
this.m_TimeZone = timeZone;
this.m_Dst = dst;

}

}
}



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: