Retrieving large number of Items from sharepoint 2007 list

If you have to reterive a large number of Items and also need a better
performance then you should use one of the methods below :

1. Using SPQuery

2. Using PortalSiteMapProvider Class

Lets see the examples for both the methods :

Our Query - Query to get all the Items in a list where Category is "Sp2007"

SPQuery -

// Get SiteColl
SPSite curSite = new
SPSite("http://myPortal");

//Get Web Application
SPWeb curWeb = curSite.OpenWeb();

// Create a SPQuery Object
SPQuery curQry = new SPQuery();

// Write the query
curQry.Query = "<Where><Eq><FieldRef
Name='Category'/>
<Value Type='Text'>SP2007
</Value></Eq></Where>";

// Set the Row Limit
curQry.RowLimit = 100;

//Get the List
SPList curList = curWeb.Lists(new Guid("myListGUID"));

//Get the Items using Query
SPListItemCollection curItems = curList.GetItems(curQry);


// Enumerate the resulting items

foreach (SPListItem curItem in curItems)
{

string ResultItemTitle =
curItem["Title"].ToString();

}


PortalSiteMapProvider class -

The class includes a method called GetCachedListItemsByQuery that retrieves data from a list based on an SPQuery object that is provided as a parameter to the method call.
The method then looks in its cache to see if the items already exist. If they do, the method returns the cached results, and if not, it queries the list, stores the results in cache and returns them from the method call.


// Get Current Web
SPWeb curWeb = SPControl.GetContextWeb(HttpContext.Current);

//Create the Query
SPQuery curQry = new SPQuery();
curQry.Query = "<Where><Eq><FieldRef Name=\'Category\'/><Value Type=\'Text\'>SP2007</Value></Eq></Where>";


// Get Portal Map Provider

PortalSiteMapProvider ps = PortalSiteMapProvider.WebSiteMapProvider;

PortalWebSiteMapNode pNode = TryCast (ps.FindSiteMapNode (curWeb.ServerRelativeUrl), PortalWebSiteMapNode);

// Get the items
pItems = ps.GetCachedListItemsByQuery(pNode, "myListName_NotID", curQry, curWeb);

// Enumerate all resulting Items
foreach (PortalListItemSiteMapNode curItem in pItems)
{
string ResultItemTitle = curItem["Title"].ToString();
}