SharePoint 2010 SPQuery Paging

For better performance you can do paging in SharePoint without returning all items. But There you can only arrows prev and next page, you cannot use numbering page.

This sample code of paging:

<asp:DropDownList ID="DropDownListSortColumns" runat="server"

onselectedindexchanged="DropDownListSortColumns_SelectedIndexChanged" AutoPostBack=true>

<asp:ListItem>IDasp:ListItem>

<asp:ListItem>Titleasp:ListItem>

<asp:ListItem>Createdasp:ListItem>

<asp:ListItem>Modifiedasp:ListItem>

asp:DropDownList>

<asp:DropDownList ID="DropDownListSortOrder" runat="server"

onselectedindexchanged="DropDownListSortOrder_SelectedIndexChanged" AutoPostBack=true>

<asp:ListItem Value="True">Ascendingasp:ListItem>

<asp:ListItem Value="False">Descendingasp:ListItem>

asp:DropDownList>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns=true class="style1">

asp:GridView>

<table style="float:right; width:100px">

<tr>

<td>

<asp:LinkButton ID="LinkButtonPrevious" runat="server"

onclick="LinkButtonPrevious_Click"><<asp:LinkButton>

td>

<td>

<asp:Label ID="LabelPaging" runat="server" Text="Label">asp:Label>td>

<td>

<asp:LinkButton ID="LinkButtonNext" runat="server"

onclick="LinkButtonNext_Click">>>asp:LinkButton>

td>

tr>

table>

Step Two:

Now we need to handle the data load events , sort column change events and the paging buttons events . For that we need to write event handlers

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

LoadData(1);

}

}

private void LoadData(int currentPage)

{

ViewState["CurrentPage"] = currentPage;

FillData(ViewState["Next"] as string, DropDownListSortColumns.SelectedValue,Convert.ToBoolean( DropDownListSortOrder.SelectedItem.Value));

}

private void FillData(string pagingInfo, string sortColumn, bool sortAscending)

{

int currentPage = Convert.ToInt32(ViewState["CurrentPage"]);

uint rowCount = 5;

string columnValue;

string nextPageString = "Paged=TRUE&p_ID={0}&p_" + sortColumn + "={1}";

string PreviousPageString = "Paged=TRUE&PagedPrev=TRUE&p_ID={0}&p_" + sortColumn + "={1}";

SPListItemCollection collection;

//first make a call to fetch the desired result set

//here is the actual call to the dal function

collection = DAL.GetTestItems(sortColumn, sortAscending, pagingInfo, rowCount);

DataTable objDataTable = collection.GetDataTable();

GridView1.DataSource = objDataTable;

GridView1.DataBind();

//now we need to identify if this is a call from next or first

if (null != collection.ListItemCollectionPosition)

{

if (collection.Fields[sortColumn].Type == SPFieldType.DateTime)

{

columnValue = SPEncode.UrlEncode( Convert.ToDateTime(collection[collection.Count - 1][sortColumn]).ToUniversalTime().ToString("yyyyMMdd HH:mm:ss"));

}

else

{

columnValue = SPEncode.UrlEncode( Convert.ToString(collection[collection.Count - 1][sortColumn]));

}

nextPageString = string.Format(nextPageString, collection[collection.Count - 1].ID, columnValue);

}

else

{

nextPageString = string.Empty;

}

if (currentPage > 1)

{

if (collection.Fields[sortColumn].Type == SPFieldType.DateTime)

{

columnValue = SPEncode.UrlEncode(Convert.ToDateTime(collection[0][sortColumn]).ToUniversalTime().ToString("yyyyMMdd HH:mm:ss"));

}

else

{

columnValue =SPEncode.UrlEncode( Convert.ToString(collection[0][sortColumn]));

}

PreviousPageString = string.Format(PreviousPageString, collection[0].ID, columnValue);

}

else

{

PreviousPageString = string.Empty;

}

if (string.IsNullOrEmpty(nextPageString))

{

LinkButtonNext.Visible = false;

}

else

{

LinkButtonNext.Visible = true;

}

if (string.IsNullOrEmpty(PreviousPageString))

{

LinkButtonPrevious.Visible = false;

}

else

{

LinkButtonPrevious.Visible = true;

}

ViewState["Previous"] = PreviousPageString;

ViewState["Next"] = nextPageString;

LabelPaging.Text = ((currentPage - 1) * rowCount) + 1 + " - " + currentPage * rowCount;

}

protected void LinkButtonPrevious_Click(object sender, EventArgs e)

{

LoadData(Convert.ToInt32(ViewState["CurrentPage"]) - 1);

}

protected void LinkButtonNext_Click(object sender, EventArgs e)

{

LoadData(Convert.ToInt32(ViewState["CurrentPage"]) + 1);

}

protected void DropDownListSortColumns_SelectedIndexChanged(object sender, EventArgs e)

{

ViewState.Remove("Previous");

ViewState.Remove("Next");

LoadData(1);

}

protected void DropDownListSortOrder_SelectedIndexChanged(object sender, EventArgs e)

{

ViewState.Remove("Previous");

ViewState.Remove("Next");

LoadData(1);

}

Step 3

Now the last step is to add the DAL class for this solution to complete

public class DAL

{

public static SPListItemCollection GetTestItems(string sortBy, bool sortAssending, string pagingInfo, uint rowLimit)

{

SPWeb objWeb = SPContext.Current.Web;

SPListItemCollection collection;

SPQuery objQuery = new SPQuery();

objQuery.RowLimit = rowLimit;

objQuery.Query = "";

objQuery.ViewFields = "";

if (!string.IsNullOrEmpty(pagingInfo))

{

SPListItemCollectionPosition position = new SPListItemCollectionPosition(pagingInfo);

objQuery.ListItemCollectionPosition = position;

}

collection = objWeb.Lists["Test"].GetItems(objQuery);

return collection;

}

}