Follow Us

Skip Navigation LinksHome > Articles > Sharepoint > Accessing Data From Sharepoint List

Accessing Data From Sharepoint List

Explains different methods to access data from sharepoint lists

By Project 9X   On   Thursday, 09 October 2008

Page Views : 49422   |   Technologies : Sharepoint

Rating : Rated :
0
| More..

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

Keywords :
Tags :
Rate This Article :

Comments :

# 1 Annonymous Wrote on 10/13/2008


Thanks. great composition !!



# 2 Annonymous Wrote on 10/24/2008


Great artical



# 3 Annonymous Wrote on 03/19/2009


Thanks Gr88 Woerk..Very useful stuff. 1 question..How can we update the list(like adding new row to data table) from data table ? Thanks .



# 4 Annonymous Wrote on 04/21/2009


".How can we update the list(like adding new row to data table) from data table ? Thanks " -->What do you mean by updating list from data table?! To update specific list item use for each loop then if item found - change column value. dont forget item.update(); at end



# 5 Annonymous Wrote on 07/09/2009


Great work. Found out the hard way that getDataTable() does not return all columns. Example: FileRef=>URL Path in the PublishingImages library displays in gridview bound to spdatasource, but not when bound to GetDataTable() result. Initially thought it might be a content type/hidden fields issue, but it does display some system columns not listed under content type. Sort of limits its usefullness if you do not know what columns will/will not be returned.



# 6 Annonymous Wrote on 07/30/2009


This is Excellent article.




blog comments powered by Disqus
User Login
Username :
Password :
Register Login

Forgot Password


Related Articles