Click here to Skip to main content
12,762,516 members (36,775 online)
Click here to Skip to main content
Add your own
alternative version

Stats

189.4K views
1.3K downloads
17 bookmarked
Posted 10 May 2015

CRUD Operation to List Using SharePoint 2013 Rest API

, 1 Jan 2016 CPOL
Rate this:
Please Sign up or sign in to vote.
SharePoint 2013, REST API

Introduction

The goal of this document is to provide a complete idea about CRUD (create, read, update and delete) operation in a list of SharePoint 2013 using REST API. The target audiences must know OData (Open Data Protocol is an OASIS standard that defines the best practice for building and consuming RESTful APIs).

Initial Setup

For making things easy, please consider we have the following two lists (SpTutorial & SpTutorialParent) where we will perform CRUD operation.

SpTutorial

In this list, I have tried to cover all types of columns so that we can see how to get and set values to those columns.

https://www.codeproject.com/KB/sharepoint/990131/SP_tutorial.PNG

SpTutorialParent

It is used in SpTutorial as a lookup column so that we can see how to get and set value to a lookup column.

https://www.codeproject.com/KB/sharepoint/990131/sp_parent.PNG

Note: Please check the attachment to have PowerShell Script to create these lists.

Tools needed

All examples are given using jQuery.ajax but you can use any REST Client like Fiddler, Postman or Advanced REST Client and so on. These REST Clients have some problems. They do not work seamlessly with SharePoint. That's why I made my own SharePoint REST Client.

sp rest client

I believe you will be happy using this REST Client for testing/exploring REST API of SharePoint. Find the documentation of it over here.

REST in SharePoint 2013

REST service for list was first introduced in SharePoint 2010. It was under the end point /_vti_bin/listdata.svc, and it still works in SharePoint 2013. SharePoint 2013 introduces another endpoint /_api/web/lists, and which is much more powerful than in SharePoint 2010. The main advantage of REST in SharePoint 2013 is: we can access data by using any technology that supports REST web request and Open Data Protocol (OData) syntax. That means you can do everything just making HTTP requests to the dedicated endpoints. Available HTTP methods are GET, POST, PUT, MERGE, and PATCH. The supported data format is ATOM (XML based) or JSON

READ: HTTP GET method is used for any kinds of read operation.

CREATE: Any kind of create operation like list, list item, site and so on maps to the HTTP POST method. You have to specify the data in request body and that’s all. For non-required columns, if you do not specify the values, then they will be set to their default values. Another important thing is: you cannot set value to the read-only fields. If you do so, then you will get an exception.

UPDATE: For updating existing SharePoint 2013 objects, there are three HTTP methods like PUT, PATCH and MERGE available. The recommended methods are PATCH and MERGE. PUT requires the entire huge object to perform update operation. Let's say we have a list named EMPLOYEE and it has 100+ fields and we want to update EmployeeName field only. In this case, if we use PUT method, we must have to specify the value of others fields along with EmployeeName field. But PATCH and MERGE are very easy. We just have to specify the value of EmployeeName filed only.

DELETE: HTTP DELETE method is used to delete any objects in SharePoint 2013

For accessing SharePoint resources by using REST API, at first we have to find the appropriate endpoints. The following table demonstrates the endpoints associated with CRUD operation in a list.

URL endpoint

Description

Supported HTTP Method

/_api/Web/Lists

Retrieving all lists in a site and adding new lists

GET, POST

/_api/Web/Lists/GetByTitle('listname')

Getting a list details by its title and updating it as well. If anyone changes your list title, your code will break.

GET, POST

/_api/Web/Lists(guid'guid id of your list')

Same as above but changing list title will not affect the code.

GET, POST

/_api/Web/Lists/GetByTitle(' listname ')/Fields

Retrieving all fields associated with a list and add new fields

GET, POST

/_api/Web/Lists/GetByTitle('listname')/

Fields/GetByTitle('fieldname')

Getting details of a field, modifying and deleting it.

GET, PUT, PATCH, MERGE, DELETE

/_api/Web/Lists/GetByTitle('listname')

/Items

Retrieving all items in a list and adding new items

GET, POST

/_api/web/lists/GetByTitle('listname')

/GetItemById(itemId)

This endpoint can be used to get, update and delete a single item.

GET, PUT, PATCH, MERGE, DELETE

Hope you are done with creating the above lists as mentioned before. Now come to the real examples how we can use it in practical life. Accessing REST endpoints from client side, there are so many ways but the approach is almost same everywhere. In this article, jQuery($.ajax) will be used to access all endpoints.

GET Items From List

At first, create our method to get items from a particular list. If we use $.ajax, it should look like the following:

function getItems(url) {
    $.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + url,
        type: "GET",
        headers: {
            "accept": "application/json;odata=verbose",
        },
        success: function (data) {
            console.log(data.d.results);
        },
        error: function (error) {
            alert(JSON.stringify(error));
        }
    });
}

In the above code, _spPageContextInfo.webAbsoluteUrl may be new to you. Actually, it returns the current site url and it’s the preferred way rather typing it hard coded. Now it’s time to constructs some urls and call the above method.

Getting All Items from SpTutorial

If we go through the REST endpoints table again, the endpoint (constructed url) should look like the following:

var urlForAllItems = "/_api/Web/Lists/GetByTitle('SpTutorial')/Items";

Call the method getItems(urlForAllItems);

In the data.d.results, you will find fields internal names as object’s property. In the above example, we will get only the Id of Lookup and Person type column. But we need more information about these columns in our JSON results. To nail this, we have to learn some OData query string operators.

$select specifies which fields to return in JSON results.

$expand helps to retrieve information from Lookup columns.

Now if we re-write the urlForAllItems, it should look like the following:

var urlForAllItems = 
               "/_api/Web/Lists/GetByTitle('SpTutorial')/Items?"+
               "$select=ID,Title,SpMultiline,SpChoice,
               SpNumber,SpCurrency,SpDateTime,SpCheckBox,SpUrl,"+
                "SpPerson/Name,SpPerson/Title,SpLookup/Title, SpLookup/ID" +
                "&$expand=SpLookup,SpPerson";

To use $expand along with $select, you have to specify the column names in $select just what I did in the above like SpLookup/Title, SpLookup/ID.

$filter specifies which items to return. If I want to get the items where Title of SpTutorial equals to‘first tutorial’ and ID of SpTutorialParent equals to 1, the URL should look like the following:

var urlForFilteredItems = 
               "/_api/Web/Lists/GetByTitle('SpTutorial')/Items?"+
               "$select=ID,Title,SpMultiline,SpChoice,SpNumber,
               SpCurrency,SpDateTime,SpCheckBox,SpUrl,"+
               "SpPerson/Name,SpPerson/Title,SpLookup/Title,SpLookup/ID"+
               "&$expand=SpLookup,SpPerson&$filter=Title eq 
               'first tutorial' and SpLookup/ID eq 1";

You may notice that I have used a query operator like ‘eq’ in above URL. Now let’s see what are the other query operators available.

Numeric

String

Date Time functions

Lt (less than)

startsWith (if starts with some string value)

day()

Le (less than or equal)

substringof ( if contains any sub string)

month()

Gt (greater than)

 

year()

Ge (greater than or equal)

 

hour()

Eq (equal to)

Eq

minute()

Ne (not equal to)

Ne

second()

Note: Unfortunately, date time functions do not work with new style (URL) of SharePoint 2013. But there is a hope we can do it like SharePoint 2010 style.

var filterByMonth = "/_vti_bin/listdata.svc/SpTutorial?$filter=month(SpDateTime) eq 6";

$orderby is used to sort items. Multiples fields are allowed separate by comma. Ascending or descending order can be specified just by appending the asc or desc keyword to query.

var urlForOrderBy = "/_api/Web/Lists/GetByTitle('SpTutorial')/Items?" +
    "$select=ID,Title,SpMultiline,SpChoice,
    SpNumber,SpCurrency,SpDateTime,SpCheckBox,SpUrl," +
    "SpPerson/Name,SpPerson/Title,SpLookup/Title,SpLookup/ID" +
    "&$expand=SpLookup,SpPerson&$orderby=ID desc";

$top is used to apply paging in items.

var urlForPaging = "/_api/Web/Lists/GetByTitle
('SpTutorial')/Items?$top=2";

Adding New Item

In this case, our HTTP method will be POST. So write a method for it.

function addNewItem(url, data) {
    $.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + url,
        type: "POST",
        headers: {
            "accept": "application/json;odata=verbose",
            "X-RequestDigest": $("#__REQUESTDIGEST").val(),
            "content-Type": "application/json;odata=verbose"
        },
        data: JSON.stringify(data),
        success: function (data) {
            console.log(data);
        },
        error: function (error) {
            alert(JSON.stringify(error));
        }
    });
}

In header, you have to specify the value of X-RequestDigest. It’s a hidden field inside the page, you can get its value by the above mentioned way ($("#__REQUESTDIGEST").val()). But sometimes, it does not work. So the appropriate approach is to get it from /_api/contextinfo. For this, you have to send aHTTP POST request to this URL (_api/contextinfo) and it will return X-RequestDigest value (in the JSON result, its name should be FormDigestValue).

URL and request body will be like the following for adding new item in list.

var addNewItemUrl = "/_api/Web/Lists/GetByTitle('SpTutorial')/Items"; 
var data = {
    __metadata: { 'type': 'SP.Data.SpTutorialListItem' },
    Title: 'Some title',
    SpMultiline: 'Put here some multiline text. 
    You can add here some rich text also',
    SpChoice: 'Choice 3',
    SpNumber: 5,
    SpCurrency: 34,
    SpDateTime: new Date().toISOString(),
    SpCheckBox: true,
    SpUrl: {
        __metadata: { "type": "SP.FieldUrlValue" },
        Url: "http://test.com",
        Description: "Url Description"
    },
    SpPersonId: 3,
    SpLookupId: 2
};

Note: Properties of data are the internal names of the fields. We can get it from following URL by making a HTTP GET request.

var urlForFieldsInternalName = "/_api/Web/Lists/GetByTitle('SpTutorial')/
Fields?$select=Title,InternalName&$filter=ReadOnlyField eq false";

Type

Value

Single line of text

String

Multiple lines of text

Multiple lines can be added here also rich text

Choice

String but it must come from choices available in the list.

Number

Integer or double

Currency

Like number

Date and Time

String but it must be in ISOString format

Lookup

Integer and must be the ID of Lookup item

Yes/No

true or false

Person or Group

Integer and must be the ID of Person or Group

Hyperlink or Picture

Object that has three properties only like __metadata, Url, Description

For inserting multiple values to person or group column, we have to specify the ids of people or group.

var data = {
    __metadata: { "type": "SP.Data.TestListItem" },
    Title: "Some title",
    MultiplePersonId: { 'results': [11,22] } 
}

N.B.: This is an update based on user comment.

How to specify the value of __metadata for new list item? Actually, it looks like the following.

__metadata: {'type': 'SP.Data.' + 'Internal Name of the list' + 'ListItem'}

Or we can get it it from following URL by sending a GET request.

/_api/Web/Lists/getbytitle('List Name')/ListItemEntityTypeFullName

Updating Item

We can use the following method for updating an item.

function updateItem(url, oldItem, newItem) {
    $.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + url,
        type: "PATCH",
        headers: {
            "accept": "application/json;odata=verbose",
            "X-RequestDigest": $("#__REQUESTDIGEST").val(),
            "content-Type": "application/json;odata=verbose",
            "X-Http-Method": "PATCH",
            "If-Match": oldItem.__metadata.etag
        },
        data: JSON.stringify(newItem),
        success: function (data) {
            console.log(data);
        },
        error: function (error) {
            alert(JSON.stringify(error));
        }
    });
}

Then you can get your old item, modify it and construct the URL.

var updateItemUrl = "/_api/Web/Lists/GetByTitle('SpTutorial')/
getItemById('Id of old item')";

Something has been changed as compared to add new item. Let me explain one by one. Now HTTP method is PATCH and it is also specified in header ("X-Http-Method": "PATCH") and which is recommended I mentioned earlier.

etag means Entity Tag which is always returned during HTTP GET items. You have to specify etag value while making any update or delete request so that SharePoint can identify if the item has changed since it was requested. Following are the ways to specify etag.

  1. "If-Match": oldItem.__metadata.etag (If etag value does not match, service will return an exception)
  2. "If-Match": "*" (It is considered when force update or delete is needed)

Deleting Item

It’s very simple. Just the following method can do everything.

function deleteItem(url, oldItem) {
    $.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + url,
        type: "DELETE",
        headers: {
            "accept": "application/json;odata=verbose",
            "X-RequestDigest": $("#__REQUESTDIGEST").val(),
            "If-Match": oldItem.__metadata.etag
        },
        success: function (data) {
           
        },
        error: function (error) {
            alert(JSON.stringify(error));
        }
    });
}

URL is the same as updating item. It will not return anything when operation is successful. But if any error happens, then it will return an exception.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Atish Dipongkor
Software Developer BrainStation-23
Bangladesh Bangladesh
2016 Microsoft MVP

Currently, I am devoted to provide technical and development support to the SharePoint clients and also I am working on angularjs. I am experienced with C#, ASP.NET, SharePoint, SignalR, angularjs, MS SQL, Oracle 11g R2, Windows Phone, Firefox OS and so on. I have fallen in love with many technologies but never got married to any of them. I am evolving myself as full stack developer. I always like to share knowledge as much as to gather from you.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionHow to filter data using HyperLink url Pin
Member 1287935930-Nov-16 6:47
memberMember 1287935930-Nov-16 6:47 
PraiseSPCRUD.JS with Angular Pin
Jeff M Jones2-May-16 6:39
memberJeff M Jones2-May-16 6:39 
GeneralRe: SPCRUD.JS with Angular Pin
Atish Dipongkor9-May-16 1:08
professionalAtish Dipongkor9-May-16 1:08 
GeneralMy vote of 5 Pin
Fernando A. Gomez F.15-Feb-16 18:47
memberFernando A. Gomez F.15-Feb-16 18:47 
GeneralRe: My vote of 5 Pin
Atish Dipongkor16-Feb-16 7:39
professionalAtish Dipongkor16-Feb-16 7:39 
QuestionRest API Set ID, Password Shrapoint Aouth Parsing to php Pin
Ali Nalobi Harahap14-Oct-15 22:49
memberAli Nalobi Harahap14-Oct-15 22:49 
AnswerRe: Rest API Set ID, Password Shrapoint Aouth Parsing to php Pin
Atish Dipongkor14-Oct-15 23:25
professionalAtish Dipongkor14-Oct-15 23:25 
QuestionNice post Pin
crshnbrn13-Oct-15 6:18
membercrshnbrn13-Oct-15 6:18 
AnswerRe: Nice post Pin
Atish Dipongkor13-Oct-15 8:17
professionalAtish Dipongkor13-Oct-15 8:17 
GeneralRe: Nice post Pin
crshnbrn13-Oct-15 9:30
membercrshnbrn13-Oct-15 9:30 
GeneralRe: Nice post Pin
Atish Dipongkor13-Oct-15 20:50
professionalAtish Dipongkor13-Oct-15 20:50 
GeneralRe: Nice post Pin
crshnbrn2-Dec-15 8:53
membercrshnbrn2-Dec-15 8:53 
QuestionNice Article Super Pin
Member 109391221-Oct-15 2:16
memberMember 109391221-Oct-15 2:16 
AnswerRe: Nice Article Super Pin
Atish Dipongkor1-Oct-15 3:16
professionalAtish Dipongkor1-Oct-15 3:16 
QuestionNice Article !!! But I've questions. Pin
Aditya Mirza30-Aug-15 21:48
memberAditya Mirza30-Aug-15 21:48 
AnswerRe: Nice Article !!! But I've questions. Pin
Atish Dipongkor31-Aug-15 8:01
professionalAtish Dipongkor31-Aug-15 8:01 
QuestionMissing Code files? Pin
JimShelley14-Aug-15 7:51
memberJimShelley14-Aug-15 7:51 
AnswerRe: Missing Code files? Pin
Atish Dipongkor14-Aug-15 9:03
professionalAtish Dipongkor14-Aug-15 9:03 
GeneralRe: Missing Code files? Pin
JimShelley21-Aug-15 9:31
memberJimShelley21-Aug-15 9:31 
GeneralRe: Missing Code files? Pin
Atish Dipongkor22-Aug-15 9:45
professionalAtish Dipongkor22-Aug-15 9:45 
GeneralRe: Missing Code files? Pin
JimShelley25-Aug-15 5:40
memberJimShelley25-Aug-15 5:40 
QuestionGood Article . Pin
Neetin_18092-Jul-15 22:22
memberNeetin_18092-Jul-15 22:22 
AnswerRe: Good Article . Pin
Atish Dipongkor2-Jul-15 23:40
professionalAtish Dipongkor2-Jul-15 23:40 
QuestionHow to insert multiple values to person or group field Pin
BHANU PRAKASH BYSANI29-May-15 4:09
memberBHANU PRAKASH BYSANI29-May-15 4:09 
SuggestionRe: How to insert multiple values to person or group field Pin
Atish Dipongkor29-May-15 9:18
professionalAtish Dipongkor29-May-15 9:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170217.1 | Last Updated 2 Jan 2016
Article Copyright 2015 by Atish Dipongkor
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid