Retrieving SharePoint List Data using CAML

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.