Sharepoint Retrieve List Item Using CAML And SPQuery

Description:

This article will show you how to get items from sharepoint list or document library with greater performance.
This function is generic method which can be use in sharepoint implementation where you required to get list items.

Before you query, you should have below inputs:
1. Site Url
2. List Name
3. Caml query
4. No of max item which need retrieve.
5. folder name in list if required.

The Code:

The following function give shows how this can be done.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
/// <summary> 
/// This function get items from sharepoint lists.
/// It is generic function which can query to folder and pagination 
/// <param name="Uri">(Mandatory)Contains Site Url</param> 
/// <param name="queryXML">(Mandatory)Caml Query which can be generated by U2U builder
/// You may pass string.Empty in queryXML if you need to get all items</param> 
/// <param name="rowCount">(Mandatory)</param> 
/// <param name="folderName">(optional) folder namer to limit items</param> 
/// <returns></returns>
/// </summary>
private SPListItemCollection GetListItem(Uri siteUri, string listPath,  
                                 string queryXML, uint rowCount, string folderName)
{
	SPList list;
	using (SPSite site = new SPSite(siteUri.ToString().TrimEnd('/')))
	{
		using (SPWeb web = site.OpenWeb())
		{
			 /* listName = listPath; */
			list = web.GetList(listPath);
		}
	}
	/* //you can also write like this if you have current context
	 SPWeb web = SPContext.Current.Web;
	 listName = listPath;
	 list = web.GetList(listPath);
	 */
 
	SPQuery query = new SPQuery();
	query.Query = queryXML; // caml query which can be created 
                                // using U2U caml builder 
	query.RowLimit = rowCount; // Limit the result to increase performance
 
	// Limit the view of result to increase performance
        query.ViewFields = string.Concat( 
                           "<FieldRef Name='Title' />", 
                           "<FieldRef Name='ID' />", 
                           "<FieldRef Name='CustomColumn1' />", 
                           "<FieldRef Name='CustomColumn2' />"); 
 
	// If require to query on folder then pass folder name
	if (!String.IsNullOrEmpty(folderName))
	{
		SPFolder searchFolder = list.RootFolder.SubFolders[folderName];
		query.Folder = searchFolder;
		query.ViewAttributes = "Scope="Recursive"";
	}
	SPListItemCollection itemColl = list.GetItems(query);
	return itemColl;
}
 
///
<summary> /// Read each item from SPListItemCollection
/// </summary>
///
private void ReadListItems(SPListItemCollection itemColl)
{
	// read item from SPListItemCollection
	foreach (SPListItem item in itemColl)
	{
		Console.WriteLine(Convert.ToString(item["Title"]));
		Console.WriteLine(Convert.ToString(item["ID"]));
	}
}

If you wish to get all items of sharepoint list then you have to just pass empty string in queryXML. You may call like below:-

1
2
GetListItem(new Uri("http://sharepoint.infoyen.com"), 
             "/lists/testlist", string.Empty, 2000, string.Empty)
Coverage Almost this article also cover below queries:-

Accessing list items using the sharepoint object model
get list items from sharepoint list
upgrade performance caml query in large list sharepoint
increase performance caml query large lists sharepoint
get list items in sharepoint 2010 c#

Hope it helps!
Thanks!
Avinash

calendarFebruary 24, 2012 · cardInfoyen · comments7 Comments
tagTags: , , , , , , ,  · Posted in: CAML, List, MOSS, SharePoint

7 Responses

  1. Infoyen - June 21, 2012

    caml query to get all list items in sharepoint 2007 :-

    If you wish to get all items of sharepoint list then you have to just pass empty string in queryXML. You may call like below:-

    GetListItem("write siteurl", "/lists/testlist", "", 2000, "")
  2. bijesh - June 26, 2012

    i have a sharepoint libary with columns like name, company etc. I want user to search with criteria like name and company. The library have subfolders and search should traverse through all the subfolder and list the file in the library that matches the criteria supplied by the user.

    It would be of great help if i get help in this

  3. Infoyen - June 26, 2012

    1. Create caml query based on your required filter (may be like company = “infoyen” etc..). (to generate caml query use “u2u caml query builder”)
    2. Create view fields string. This strings tell that which kid of column you want to display (like name, company etc) after retrieving data.
    Put this view fields string in “GetListItem” function
    3. Define row limit. Like how many result can be expected.

    Now call to my function

    GetListItem(Uri siteUri, string listPath, string queryXML, uint rowCount, string folderName)

    Here parameters would be like this:-

    1. siteUri can be like this new Uri(“http://yourshrepointsiteurl”)
    2. listPath will be you target list for e.g “/lists/test”
    3. queryXML = your caml query
    4. rowCount = the max row count which you are expected in result
    5. folderName = folder name in your list. If you want parse all folder then just send empty.

    This will work awesome for you.

    Pros of this generic function:-

    1. You use listPath to create list object not list name. It increases performance.
    2. You use rowcount. It increases performance.
    3. You use caml which filter item but it does not get all document.
    4. You pass folder name to narrow your result.
    5. viewfields give less columns as per your requirement. If you don’t put this then it get lots of system columns. I think it’s more than 15 columns which are unwanted.
    So mentioning view fields also it increases performance.

    Hope it helps…

  4. bijesh - June 26, 2012

    Thank you very much infoyen. i am really new to this field. It would be a great help if you tell me where i can put this caml query. it should be put in a content query webpart or should it a ,net solution. The format i want to achieve is this.

    Search:-
    Name:-______ Company:-_________ Date Created:______

    SearchButton

    List:-
    Name Company Date Created
    ————————————————–
    ABC XYZ 20/05/2012
    CBE HYJ 21/06/2012

    etc. So the search panel at the top of the list can be a webpart which uses this caml query and diplays in the list below?….I have being banging my head for days on this….your guidance is greatly appreciated

  5. Infoyen - June 27, 2012

    Hi Bijesh,

    Your query is not related this post. This post says that how you can fetch data from sharepoint list with good performance.
    But i am happy to reply on your query. I have No issues. see below details:-

    Your query: You required a custom search page, where you have filter parameter screen and result screen.
    Approach: You may create 1 ASP.NET user control and load this user control on sharepoint page using web part.
    How to create user control, how to load in page using webpart, how to deploy: For all these please visit my below article:-
    http://sharepoint.infoyen.com/2012/04/11/create-and-deploy-user-control-in-sharepoint-using-feature/

    Steps:

    1. Create .net user control which will have 2 zone; 1 for filter parameter and 1 for result.
    2. Load this user control using webpart on sharepoint page.
    3. Select parameter and click On submit.
    4. On submit you create caml query based on selected parameter and send these to my function
    “GetListItem” which you find at below url: http://sharepoint.infoyen.com/2012/02/24/sharepoint-retrieve-list-item-using-caml-and-spquery/
    5. My function will return “SPListItemCollection”. You can loop this collection and fill into datatable or grid or whatever option which you take to display on result.
    6. You might require paging concept.
    For paging concept which get data from sharepoint list: please go to below article:-
    http://sharepoint.infoyen.com/2012/03/06/sharepoint-list-pagination-using-splistitemcollectionposition/

    It is high level idea for you. But truly; its big module not a small task.
    Let me know for any help.

    Regards,
    Avinash

  6. vishwas - November 11, 2014

    hey,

    I read this post and tried to implement the same but while implementing this in visual studio I am getting an error on the ” listName ” parameter in the code on line ” listName = listPath”

    can you help me out that what actually that listName means ?

  7. Infoyen - November 20, 2014

    My mistake.
    Delete this line “listName = listPath”.

    It should work.

Leave a Reply

Spam Protection: , required

myworldmaps infoyen