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.
Andreas Grabner said
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
Links (4/2/2009) « Steve Pietrek - Everything SharePoint and Office said
[...] Retrieving SharePoint List Data using CAML [...]
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??
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?
Bhushan Gawale said
Hi Nice Post,
I got help from this..
Thanks to Ramprasad
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.
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.
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
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.
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/
fadwa said
Many thanks for your comment
please, always provide us information like this
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.
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.
Ramprasad Navaneethakrishnan said
Can you share the code here so that I can figure out what went wrong?
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
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?
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?
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.
SC said
Hello Ram Prasad,
Is it possible to query another list in a CAML query???I mean is nested queries possible??
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
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.
sara said
Nice !!!!!!!!
Try this too,
SharePoint Lists and more
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.
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
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