Article Subtitles:
This article explains following topics.
- SharePoint List and database objects
- SharePoint List and Object Model
- How can I get data from SharePoint list and display in SharePoint web part
- SharePoint list and Gridview control
Overview
In previous article SharePoint Web part Using Web User Control we saw how to develop web part using user control and in CAML Query in SharePoint we explore different use of CAML query in SharePoint.
Now, let’s see how to get data from SharePoint list using different methods. This article does not involve list item add, update, delete or things like that. (Wait for next one!!)
In all of these methods you can return datatable and assign to gridview. Finally wrap gridview in user control, deploy web part and u r all set!!
Option 1: Using SPQuery
Query Class: SPQuery
Method: GetItems (Query)
Returns: SPListItemcollection
Before we start check this once.
Typical CAML Query structure (also refer this)
<Query>
<ViewFields>
<FieldRef Name='EmployeeID' />
<FieldRef Name='LastName' />
<FieldRef Name='Phone' />
<FieldRef Name='EmailAddress' />
</ViewFields>
<Where>
<Or>
<BeginsWith>
<FieldRef ID='66cb931d-ff9c-47b2-8e13-d3554c1798af' /> //******
<Value Type='Text'>A</Value>
</BeginsWith>
<BeginsWith>
<FieldRef ID='Title' />
<Value Type='Text'>B</Value>
</BeginsWith>
</Or>
</Where>
<OrderBy>
<FieldRef ID='EmployeeID' Ascending='False' />
</OrderBy>
</Query>
Important
****** = you can give GUID of column if you know.
ViewFields cannot handle the <FieldRef ID='[guid]' /> construct.
If you use this query with a SPQuery object, you have to:
- remove the <Query></Query> tags
- Assign the ViewFields node to the ViewFields property of the SPQuery object.
- Assign the Where and Order By node to the Query property of the SPQuery object.
Code Block:
using (SPWeb web = SPContext.Current.Site.RootWeb)
{
SPList mylist = web.Lists["Tasks"];
SPQuery query = new SPQuery();
query.Query = "<Where><Eq><FieldRef Name='Status'/>" +
"<Value Type='Choice'>Completed</Value></Eq></Where>";
//type is important
SPListItemCollection items = mylist.GetItems(query);
foreach (SPListItem item in items)
{
Response.Write(SPEncode.HtmlEncode(item["Title"].ToString()) + "<BR>");
}
}
- You can also add row one-by-one to the data table as follows. Finally you can return datatable.
Public DataTable GetNamesFromList()
{
DataTable dt_name = new DataTable("nameList");
dt_name.Columns.Add("name");
using (SPWeb web = SPContext.Current.Site.RootWeb)
{
SPList mylist = web.Lists["Tasks"];
SPQuery query = new SPQuery();
query.Query = "<Where><Eq><FieldRef Name='Title'/>" +
"<Value Type='Text'>Completed</Value></Eq></Where>";
//type is important
SPListItemCollection items = mylist.GetItems(query);
foreach (SPListItem item in items)
{
DataRow row = dt_name_link.NewRow();
row["name"] = item["Title"].ToString();
dt_name.Rows.Add(row);
// Response.Write(SPEncode.HtmlEncode(item["Title"].ToString()) + "<BR>");
}
}
Return dt_name;
}
Option 2: Using SPSiteDataQuery
Query Class: SPSiteDataQuery
Method: GetSiteData (Query)
Returns: DataTable
Overview:
web.GetSiteData(qry):
Performs a query for list items across multiple lists, which can be located in multiple Web sites in the same Web site collection
qry.Webs:
Gets or sets the inner XML that specifies which Web sites to include in the query as specified by the Scope attribute on the Webs tag in the query. By default, the query considers the current Web site, that is, the Web site from which the GetSiteData method was invoked.
i.e. qry.Webs = "<webs scope="SiteCollection">";
qry. RowLimit:
Gets or sets a limit for the number of items returned in the query per page.
qry.ViewFields:
Gets or sets the fields that are returned in the query.
qry.Lists:
Gets or sets the inner XML that specifies which lists to include in the query.
i.e.: qry.Lists = "<Lists ServerTemplate='100' />";
ServerTemplate -- Limits the query to lists of the specified server template. i.e. <Lists ServerTemplate="104" />
BaseType -- Limits the query to lists of the specified base type. Example: <Lists BaseType="1" />
0 Generic list
1 Document library
3 Discussion forum
4 Vote or Survey
5 Issues list
qry.Query:
Gets or sets the inner XML that defines the query.
Code Block:
public static DataTable GetNames()
{
// retrieve the reference to the site where the Web part is on
SPWeb web = SPContext.Current.Web;
// retrieve all the different types of beers via a CAML query
SPSiteDataQuery qry = new SPSiteDataQuery();
qry.Query = "<OrderBy><FieldRef Name='Type' /></OrderBy>";
qry.ViewFields = "<FieldRef Name='Type' />";
qry.Lists = "<Lists ServerTemplate='100' />";
qry.Webs = "<Web Scope='Recursive' />";
// -- execute the query
DataTable dt = web.GetSiteData(qry);
return dt;
}
Option 3: Using GetDataTable
Query Class: none J
Method: GetDataTable()
Returns: DataTable
spList.Items.GetDataTable();
returns all list items as a DataTable Object.
Code Block:
public DataTable GetListItems()
{
SPSite spSite = new SPSite("http://larMOSS/properties/Lists/Property%20List/");
SPWeb spWeb = spSite.OpenWeb();
SPList spList = spWeb.Lists["Property List"];
DataTable dt = spList.Items.GetDataTable();
DataTable newdt;
if (dt != null)
{
DataView dv = new DataView(dt);
dv.RowFilter = "Status = 'Active'"; //condition like where clause
string[] st = { "Title", "Owner", "Status" };
newdt = dv.ToTable(False, st);
}
dt = null;
spList = null;
spWeb = null;
spSite = null;
return newdt;
}
Thanks