Dotnetdreamer's Weblog

SharePoint, Silverlight and Azure

Retrieving SharePoint List Data using CAML

Posted by Ramprasad Navaneethakrishnan on February 9, 2009

Lets see how to retrieve data from a SharePoint list and display it in a Web Part.

Scenario:

We have a SharePoint custom list with the name ‘MySPList’ which has the following columns.Title, AssignedTo and Status. We want to get all the data from the list for which AssignedTo has a value of ‘Ramprasad’.

CAML Query:

For the above scenario, the CAML query should look like the following..

<Where><Eq><FieldRef Name=’AssignedTo‘/><Value Type=’Text‘>Ramprasad</Value></Eq></Where>

Note here that in SharePoint list, ‘AssignedTo’ column is of type ‘Single Line of  Text’. The corresponding type in CAML is ‘TEXT’ which is mentioned in the CAML query.

Query Execution:

SPList mylist = web.Lists[“MySPList”];
SPQuery query = new SPQuery();
query.Query = “<Where><Eq><FieldRef Name=’AssignedTo’/><Value Type=’Text’>Ramprasad</Value></Eq></Where>”;
SPListItemCollection items = mylist.GetItems(query);

This statement execute the CAML query and stores the results as SPListItemsCollection object.

Loop through the results:

Now loop though the SPListItemsCollection object and get the needed column data .

foreach (SPListItem item in items)
{
DataRow _row = _table.NewRow();
_row[0]  = SPEncode.HtmlEncode(item[“Title”].ToString());
_row[1] = SPEncode.HtmlEncode(item[“AssignedTo”].ToString());
_row[2] =  SPEncode.HtmlEncode(item[“Status”].ToString());
_table.Rows.Add(_row);
}

Step by Step:

1. Open Visual Studio 2008. File -> New -> Project.

2. Select WebPart project template from Sharepoint project type.

3. Copy paste the following code

using System;
using System.Data;
using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Serialization;

using Microsoft.SharePoint;
using Microsoft.SharePoint.Utilities;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;

namespace SharePointListDataRetrival
{
[Guid(“7594e58f-eceb-4fce-84b9-e5ce7424aa67”)]
public class SharePointListDataRetrivalWebPart : System.Web.UI.WebControls.WebParts.WebPart
{
DataSet _listData;
public SharePointListDataRetrivalWebPart()
{
}

protected override void CreateChildControls()
{
base.CreateChildControls();
DataGrid dgSpListData = new DataGrid();
dgSpListData.DataSource = GetListData();
dgSpListData.DataBind();
this.Controls.Add(dgSpListData);
}

private DataSet GetListData()
{
_listData = new DataSet();
DataTable _table = new DataTable();
DataColumn dc = new DataColumn(“Title”);
_table.Columns.Add(dc);
dc = new DataColumn(“AssignedTo”);
_table.Columns.Add(dc);
dc = new DataColumn(“Status”);
_table.Columns.Add(dc);
using (SPWeb web = SPContext.Current.Site.RootWeb)
{

SPList mylist = web.Lists[“MySPList”];
SPQuery query = new SPQuery();
query.Query = “<Where><Eq><FieldRef Name=’AssignedTo’/><Value Type=’Text’>Ramprasad</Value></Eq></Where>”;
SPListItemCollection items = mylist.GetItems(query);
foreach (SPListItem item in items)
{
DataRow _row = _table.NewRow();
_row[0]  = SPEncode.HtmlEncode(item[“Title”].ToString());
_row[1] = SPEncode.HtmlEncode(item[“AssignedTo”].ToString());
_row[2] =  SPEncode.HtmlEncode(item[“Status”].ToString());
_table.Rows.Add(_row);
}
}
_listData.Tables.Add(_table);
return _listData;
}
}
}

4.  Project Properties -> Debug -> Under ‘Start Action’ section,Select ‘Start browser with url’ -> Give the name of your sharepoint site. Eg : http://vm:1002

5. Build the project.

6. Deploy the solution

7. Open SharePoint site. Site Settings -> Edit Page -> Add New WebPart -> Locate your webpart by the name – >Select Ok.

Now, you will be now able to see the SharePoint list data in your webpart.

Let me know your comments. Thanks.

Advertisements

32 Responses to “Retrieving SharePoint List Data using CAML”

  1. Hi

    Nice article. Have a look at my series of blog entries regarding performance considerations when using the SharePoint objectd model to access SharePoint Lists. It will help you understand the internals of the SharePoint API and will allow you to avoid common problems that would lead to performance issues under load or with production like data.

    Read more at dynaTrace Blog about SharePoint Performance

  2. […] Retrieving SharePoint List Data using CAML […]

  3. Maya said

    Hello and thanks for the post.
    I have tried the sample code, I’m getting lots of error
    the erroe that I get is : Unexpected character ‘”’
    what am I missing??

  4. Ramprasad Navaneethakrishnan said

    Hi Maya,
    Thanks for your feedback.

    Can you give me more details on the error you got? It’s a compilation error or a runtime exception? On executing which line you get this exception?

  5. Bhushan Gawale said

    Hi Nice Post,
    I got help from this..

    Thanks to Ramprasad

  6. Sam said

    Hi Ramprasad,

    It was an excellent article. I am across a situation where in I need to get the data from SharePoint List on a remote accessible server into my C# application running on a different machine (other than SharePoint machine). Is it possible or is there any way I can achieve this.

  7. Ramprasad Navaneethakrishnan said

    Hi Sam,

    It is ofcourse possible. We can achive it my means of consuming sharepoint web services. Lists.asmx webservice provides methods to retrive the list items. The web method name is GetListItems. I’ll posting an article on this topic shortly. Keep a tab on this blog post!

    Thanks for your comments.

  8. Santhosh said

    Hi Ramprasad,

    Nice Post. Thank you. I am new to CAML queries. My requirement is to fetch the list items based on two field values.
    query.Query = “Ramprasad”;

    I have to check one more field ‘Status’ . The Status is only Active , I have to fetch the item from the list.

    Could you please help me to do it?

    Thnaks

  9. Ramprasad Navaneethakrishnan said

    Hi Santhosh,
    Thanks for your comments.
    You can check multiple column values by using the CAML tags / depending on your requirement. For example, to get the list items whose Title column value is ‘Ramprasad’ and whose Status column value is ‘Active’, the following query can be used..

    Where
    And
    Eq
    FieldRef Name=”Title”
    Value Type=”Text” Ramprasad Value
    Eq
    Eq
    FieldRef Name=”Status”
    Value Type=”Text” Active Value
    Eq
    And
    Where

    Please add < and > before and after the CAML tags 🙂

    You can find all CAML syntax here.
    http://www.a2zdotnet.com/View.aspx?id=90

    Hope this helps.

  10. Ramprasad Navaneethakrishnan said

    As I said, the answer to your question is utilizing sharepoint web services. How to use sharepoint web services to retrieve list data? You can find a post on this here..
    http://dotnetdreamer.com/2009/06/04/moss-web-services-accessing-sharepoint-list-data/

  11. fadwa said

    Many thanks for your comment

    please, always provide us information like this

  12. Giovanni said

    Great Post, thank you so much for sharing.

    I’m getting an error that says: The type of namespace name ‘Dataset’ could not be found.

    I would love to get this working as it will help me with a web part I’m developing.

    Many thanks again for posting such a valuable resource for us that are new to sharepoint development.

  13. Giovanni said

    I figured out the error and was able to build the solution, but it does not work. I get an error that says: An unexpected error has occurred. It also includes a link to the Web Part maintenance page.

  14. Ramprasad Navaneethakrishnan said

    Can you share the code here so that I can figure out what went wrong?

  15. sharepointtech said

    I have little different senario. i have custom list for name and telephone no. now i want to search from that list. want to give search box in which user type name and got the result.

    any idea abt that

  16. Amarnath said

    Hi Ramprasad,

    thnaks for the nice post. i have a requirement like this:

    I have list with name “MyList” i have to fetch data from this list with query start_data somedate or type = sometype

    here start_data, type are list colums and somedate are taken as input from users.

    i have ben working for this from past 2 months can you please help on this?

  17. Amarnath said

    requirement is retrieving of data from list within specified date range start and end dates. this dates are taken as input from users. can i achieve this?

  18. Rahul said

    Hello Ramprasad
    i saw your code..actually i am trying to do the same thing only difference is that i don’t want to use the query to fetch the data. I want to fetch the data from the list which i had already created in sharepoint site and want to display that data in a datagrid.Can you tell me what all changes i can make in the existing code.

  19. SC said

    Hello Ram Prasad,

    Is it possible to query another list in a CAML query???I mean is nested queries possible??

  20. MOSS techie said

    Is it possible to use the SPEncode.HtmlEncode(item[“Middle Name”].ToString()to get non-required fields??
    it gives an exception of
    “Object reference not set to an instance of an object” since that row does not have data for this column

  21. Lambros Vasiliou said

    @MOSS TEchie:
    The exception is caused by .ToString(). What happenned here is that your item[“Middle Name”] column contained no value. To get around this issue:
    string middleName=item[“Middle Name”]==null ? string.Empty : SPEncode.HtmlEncode(item[“Middle Name”].ToString();

    Also notice that the result SPListItemCollection supports .GetDataTable() to convert your results to a data table.

  22. sara said

    Nice !!!!!!!!

    Try this too,
    SharePoint Lists and more

  23. SharePoint said

    Thanks dude for this Nice post.
    I have one problem this is somewhat how related to your post. I am working on an intranet portal having a aspx page called visitingCard.aspx and that is sharepoint list’s default page comes when clicking new button having columns username, Department, Designation, email, phone no etc.
    This form is user specific I mean as any user log in by its account (User name and password) and click on this page(visitingCard.aspx) all the fields in the page should be filled automatically related to that user.
    And this data is coming from active directory.

    Plz help me to do this.

  24. venkat said

    Hi Ram,

    Nice Article.

    When I try to build ,I’m getting an error that says: The type of namespace name ‘Dataset’ could not be found. Can you tell me the issue here ?

    And the GUID you mentioned should be MySPList GUID ? Right

  25. Ashok said

    Hi Ramprasad,

    Thanks for the nice post. Everything worked, and I was quite impressed that I didn’t have to load this webpart into the webpart gallery – it was already available to add onto any page.

    However, for some reason, the webpart displays all the list data. It is not using the query where we just want AssignedTo = Ramprasad. (I created dummy data to test this).

    Any ideas why all data is being returned?

    Thank you,

    Ashok

  26. Lavina said

    Hey could you let me know if I want to retrieve data from more than 1 list. For example, I have two lists Customer and Order and I want to retrieve data from both these lists programmatically, can I use CAML query for it? or some other way?

  27. […] http://dotnetdreamer.com/2009/02/09/retrieving-sharepoint-list-data-using-caml/ Categories: Sharepoint Programming Tags: CAML، programming، برنامه نويسي Comments (0) Trackbacks (0) Leave a comment Trackback […]

  28. Prithivi said

    hi,
    Very useful post thanks..

    But getting error.Pls help me

    [ArgumentException: Value does not fall within the expected range.]
    Microsoft.SharePoint.SPListCollection.GetListByName(String strListName, Boolean bThrowException) +230
    Microsoft.SharePoint.SPListCollection.get_Item(String strListName) +32
    SharePointListDataRetrival.WebPart1.GetListData() +384
    SharePointListDataRetrival.WebPart1.CreateChildControls() +61
    System.Web.UI.Control.EnsureChildControls() +87
    System.Web.UI.Control.PreRenderRecursiveInternal() +50
    System.Web.UI.WebControls.WebParts.WebPart.PreRenderRecursiveInternal() +62
    System.Web.UI.Control.PreRenderRecursiveInternal() +170
    System.Web.UI.Control.PreRenderRecursiveInternal() +170
    System.Web.UI.Control.PreRenderRecursiveInternal() +170
    System.Web.UI.Control.PreRenderRecursiveInternal() +170
    System.Web.UI.Control.PreRenderRecursiveInternal() +170
    System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041

    Its succesfully deployed
    after i went to MySPList and try to add web part.
    My web part also there..
    but i selected and gave ok..inthis situation am getting bug..
    pls help me out..

    Thanks in advance

  29. Prithiv said

    [ArgumentException: Value does not fall within the expected range.]
    Microsoft.SharePoint.SPListCollection.GetListByName(String strListName, Boolean bThrowException) +230
    Microsoft.SharePoint.SPListCollection.get_Item(String strListName) +32
    SharePointListDataRetrival.WebPart1.GetListData() +384
    SharePointListDataRetrival.WebPart1.CreateChildControls() +61
    System.Web.UI.Control.EnsureChildControls() +87
    System.Web.UI.Control.PreRenderRecursiveInternal() +50
    System.Web.UI.WebControls.WebParts.WebPart.PreRenderRecursiveInternal() +62
    System.Web.UI.Control.PreRenderRecursiveInternal() +170
    System.Web.UI.Control.PreRenderRecursiveInternal() +170
    System.Web.UI.Control.PreRenderRecursiveInternal() +170
    System.Web.UI.Control.PreRenderRecursiveInternal() +170
    System.Web.UI.Control.PreRenderRecursiveInternal() +170
    System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041

    Error pls help me

  30. Sasank said

    Hi Ramprasad,

    Firstly superb tutorial.. Tnk u so much for posting..

    After creating the Webpart as specified by you , i am getting an error “Access Denied” while deploying the solution. Please suggest how to go ahead.

    I am new to sharepoint

    Thanks
    Sasank

  31. Sasank said

    Hi Ramprasad,

    Thanks for the nice post.

    But When am deploying the solution as you mentioned, am getting an Error “Access Denied”. Please tell me how to fix it asap.

    Thanks
    Sasank.K

  32. jhansi said

    Hi Ramprasad,

    Thank you very much for the nice post. I am facing the query problem for a long time.
    I would like to use switch case for retrieving data from sharepoint document library.
    Let say i have three fields first name, middle name and last name in document librabry. I am trying to get the following cases.
    1. Only first name
    2. only middle name
    3. only last name
    4. Full name- first name, middle name and last name.

    But I am not able to use switch case in sharepoint designer, can you please help me in this and suggest me how to proceed further.

    Thanks in advance
    jhansi

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: