REST CRUD (Insert,Update,Delete,Get) Operations Using SharePoint Hosted App - Office 365

SharePoint adds the ability for you to remotely interact with SharePoint sites by using REST. Now, you can interact directly with SharePoint objects by using any technology that supports standard REST capabilities.

You can perform basic create, read, update, and delete (CRUD) operations by using the Representational State Transfer (REST) interface provided by SharePoint.

In this article we will do CRUD operations using REST. For understanding purposes I am going to divide this article into two parts. The first two points I will discuss in this article; the  remaining four points I will discuss in another article.

1. Create List in Office 365 SharePoint Site
2. Creation of Project using SharePoint Hosted App
3. Html code for User Interface
4. Write Business logic for Insert, Update, Delete, Get data functionalities.
5. Deploy the Project
6. Test the application.

Create List in Office 365 SharePoint Site

• Login to Office 365 SharePoint and Create List in site.
• In this case, I have created the list name Employee and created three columns EmpName (Default Title column name changed to EmpName), Salary and Address.
  
   

Creation of Project using SharePoint Hosted App

Find the below steps to create the project.

• Select SharePoint Add- in template
• Give the Proper Name for the project. In this case I have given project name spRESTCrudOperations.
  
  

• Give the SharePoint Site URL and select SharePoint Hosted App option.
  
  

• Give Office 365 credentials and click on finish button
  
  
  
  

• Once you click on Finish button, project will be created.
  
  

• In solution explorer, you can find SharePoint hosted app structure. Contains Default pages, Scripts, AppManifest file
  
  

• AppManifest file looks like this.
  
  
  
• App.js file looks like this under Scripts
  
  

• Default.aspx looks like this under Pages.
  
  

Code:

In this article, we will discuss from the third point onward, as we have already discussed  the first two points in Part One.

1. Create a List in Office 365 SharePoint Site
2. Creation of Project using SharePoint Hosted App
3. HTML code for User Interface
4. Write Business logic for Insert, Update, Delete, Get data functionalities under App.js file.
5. Deploy the Project
6. Test the application.

HTML Code for User Interface

In default.aspx page, we will do some changes as mentioned below.

• Add the below script tags after jQuery script tag.
  
  1. <script type="text/javascript" src="_layouts/15/sp.runtime.js"></script>  
  2. <script type="text/javascript" src="_layouts/15/sp.js"></script>  
  

• Write the below HTML code for User Interface under ContentPlaceHolderID.
  
  1. <table class="centerTable">    
  2.      <tr>    
  3.          <td>    
  4.              <table>    
  5.     
  6.                  <tr>    
  7.                      <td><span style="color: red; font: bold;"></span>ID </td>    
  8.                      <td>    
  9.                          <input type="text" id="empID" class="csValue" size="40" />    
  10.                      </td>    
  11.                  </tr>  
  12.                  <tr>    
  13.                      <td><span style="color: red; font: bold;"></span>EmployeeName </td>    
  14.                      <td>    
  15.                          <input type="text" id="empName" class="csValue" size="40" />    
  16.                      </td>    
  17.                  </tr>    
  18.                  <tr>    
  19.                      <td><span style="color: red; font: bold;"></span>Salary </td>    
  20.                      <td>    
  21.                          <input type="text" id="empSalary" class="csValue" size="40" />    
  22.                      </td>    
  23.                  </tr>    
  24.                  <tr>    
  25.                      <td><span style="color: red; font: bold;"></span>Address </td>    
  26.                      <td>    
  27.                           
  28.                          <textarea name="Text1" cols="40" rows="5" id="empAddress" class="csValue"></textarea>    
  29.                      </td>    
  30.                  </tr>    
  31.     
  32.     
  33.              </table>    
  34.                 
  35.          </td>    
  36.     
  37.      </tr>    
  38.  </table>    
  39.  <table>    
  40.      <tr>    
  41.     
  42.          <td>    
  43.              <input type="button" value="Clear" id="btnClear" style="background-color: #4CAF50; border: none; color: white; padding: 7px 15px; text-align: center; text-decoration: none; display: inline-block; font-size: 14px; margin: 4px 2px; cursor: pointer;" />    
  44.          </td>    
  45.     
  46.     
  47.          <td>    
  48.              <input type="button" value="Submit" id="btnCreate" style="background-color: #4CAF50; border: none; color: white; padding: 7px 15px; text-align: center; text-decoration: none; display: inline-block; font-size: 14px; margin: 4px 2px; cursor: pointer;" />    
  49.          </td>    
  50.   
  51.           <td>    
  52.              <input type="button" value="Update" id="btnUpdate" style="background-color: #4CAF50; border: none; color: white; padding: 7px 15px; text-align: center; text-decoration: none; display: inline-block; font-size: 14px; margin: 4px 2px; cursor: pointer;" />    
  53.          </td>   
  54.            <td>    
  55.              <input type="button" value="GetData" id="btnGet" style="background-color: #4CAF50; border: none; color: white; padding: 7px 15px; text-align: center; text-decoration: none; display: inline-block; font-size: 14px; margin: 4px 2px; cursor: pointer;" />    
  56.          </td>   
  57.   
  58.           <td>    
  59.              <input type="button" value="Delete" id="btnDelete" style="background-color: #4CAF50; border: none; color: white; padding: 7px 15px; text-align: center; text-decoration: none; display: inline-block; font-size: 14px; margin: 4px 2px; cursor: pointer;" />    
  60.          </td>   
  61.   
  62.            
  63.      </tr>    
  64.     
  65.     
  66.     
  67.  </table>    
  

Write Business logic for Insert, Update, Delete, Get data functionalities under App.js file.
We are writing some methods for insert, update, delete, and get data and clear data operations.

• createEmployee();
• UpdateEmployee();
• GetEmployeeDetails();
• ClearData();
• GetEmployeeDetailsByID(); 

• createEmployee() method looks like this.
  
  1. function createEmployee() {  
  2.   
  3.      $.ajax({  
  4.          url: _spPageContextInfo.siteAbsoluteUrl + "/_api/web/lists/getbytitle('" + listName + "')/items",  
  5.          type: "POST",  
  6.          contentType: "application/json;odata=verbose",  
  7.          data: JSON.stringify  
  8.  ({  
  9.      __metadata:  
  10.      {  
  11.          type: "SP.Data.EmployeeListItem"  
  12.      },  
  13.      Title: $("#empName").val(),  
  14.      Salary: $("#empSalary").val(),  
  15.      Address: $("#empAddress").val()  
  16.  }),  
  17.          headers: {  
  18.              "Accept": "application/json;odata=verbose", // return data format  
  19.              "X-RequestDigest": $("#__REQUESTDIGEST").val()  
  20.          },  
  21.          success: function (data, status, xhr) {  
  22.              $("#tblEmployees").empty();  
  23.              GetEmployeeDetails();  
  24.              alert("Successfully Submitted");  
  25.          },  
  26.          error: function (xhr, status, error) {  
  27.              alert(JSON.stringify(error));  
  28.          }  
  29.      });  
  30.  }   

• UpdateEmployee() method looks like below.
  
  1. function UpdateEmployee() {  
  2.   
  3.         var id = $("#empID").val();  
  4.         $.ajax({  
  5.             url: _spPageContextInfo.siteAbsoluteUrl + "/_api/web/lists/getbytitle('" + listName + "')/items('" + id + "')", // list item ID    
  6.             type: "POST",  
  7.             data: JSON.stringify  
  8.             ({  
  9.                 __metadata:  
  10.                 {  
  11.                     type: "SP.Data.EmployeeListItem"  
  12.                 },  
  13.                 Title: $("#empName").val(),  
  14.                 Salary: $("#empSalary").val(),  
  15.                 Address: $("#empAddress").val()  
  16.   
  17.             }),  
  18.             headers:  
  19.             {  
  20.                 "Accept": "application/json;odata=verbose",  
  21.                 "Content-Type": "application/json;odata=verbose",  
  22.                 "X-RequestDigest": $("#__REQUESTDIGEST").val(),  
  23.                 "IF-MATCH": "*",  
  24.                 "X-HTTP-Method": "MERGE"  
  25.             },  
  26.             success: function (data, status, xhr) {  
  27.                 $("#tblEmployees").empty();  
  28.                 GetEmployeeDetails();  
  29.                 alert("Date Updated Successfully");  
  30.             },  
  31.             error: function (xhr, status, error) {  
  32.                 alert(JSON.stringify(error));  
  33.             }  
  34.         });  
  35.     }   

• GetEmployeeDetails() Method looks like below.
  
  1. function GetEmployeeDetails() {  
  2.         $.ajax({  
  3.             url: _spPageContextInfo.siteAbsoluteUrl + "/_api/web/lists/getbytitle('" + listName + "')/items?$select=Title,Salary,Address",  
  4.             type: "GET",  
  5.             headers: { "Accept": "application/json;odata=verbose" }, // return data format  
  6.             success: function (data) {  
  7.                 //console.log(data.d.results);  
  8.                  
  9.                 for (var i = 0; i < data.d.results.length; i++) {  
  10.                     var item = data.d.results[i];  
  11.                     $("#tblEmployees").append(item.Title + "\t" + item.Salary + "\t" + item.Address + "<br/>");  
  12.                 }  
  13.             },  
  14.             error: function (error) {  
  15.                 alert(JSON.stringify(error));  
  16.             }  
  17.         });  
  18.   
  19.   
  20.   
  21.     }   

• GetEmployeeDetailsByID() method looks like this.
  
  1. function GetEmployeeDetailsByID() {  
  2.         var idValue = $("#empID").val();  
  3.   
  4.   
  5.         $.ajax({  
  6.             url: _spPageContextInfo.siteAbsoluteUrl + "/_api/web/lists/getbytitle('" + listName + "')/items('" + idValue + "')",  
  7.             type: "GET",  
  8.             headers: { "Accept": "application/json;odata=verbose" }, // return data format  
  9.             success: function (data) {  
  10.                 $("#empName").val(data.d.Title);  
  11.                 $("#empSalary").val(data.d.Salary);  
  12.                 $("#empAddress").val(data.d.Address);  
  13.                 $("#tblEmployees").empty();  
  14.                 GetEmployeeDetails();  
  15.             },  
  16.             error: function (error) {  
  17.                 alert(JSON.stringify(error));  
  18.             }  
  19.         });  
  20.     }   

Finally, the App.js code looks like below. 

1. 'use strict';  
2. var hostWebUrl;  
3. var appWebUrl;  
4. var listName = "Employee";  
5. ExecuteOrDelayUntilScriptLoaded(initializePage, "sp.js");  
6.   
7. function initializePage() {  
8.     var context = SP.ClientContext.get_current();  
9.     var user = context.get_web().get_currentUser();  
10.   
11.     // This code runs when the DOM is ready and creates a context object which is needed to use the SharePoint object model  
12.     $(document).ready(function () {  
13.   
14.   
15.   
16.         GetEmployeeDetails();  
17.   
18.         $("#btnCreate").on('click', function () {  
19.             createEmployee();  
20.             ClearData();  
21.   
22.   
23.         });  
24.         $("#btnUpdate").on('click', function () {  
25.             UpdateEmployee();  
26.             ClearData();  
27.   
28.   
29.         });  
30.   
31.         $("#btnClear").on('click', function () {  
32.   
33.             ClearData();  
34.   
35.         });  
36.   
37.         $("#btnGet").on('click', function () {  
38.             $('#empName').val("");  
39.             $("#empSalary").val("");  
40.             $("#tblAddress").val("");  
41.             $("#tblEmployees").empty();  
42.             GetEmployeeDetailsByID();  
43.   
44.         });  
45.   
46.         $("#btnDelete").on('click', function () {  
47.             deleteEmployee();  
48.             ClearData();  
49.   
50.   
51.   
52.         });  
53.   
54.   
55.     });  
56.   
57.   
58.   
59.   
60.     function deleteEmployee() {  
61.         var id = $("#empID").val();  
62.   
63.         $.ajax  
64.         ({  
65.             url: _spPageContextInfo.siteAbsoluteUrl + "/_api/web/lists/getbytitle('" + listName + "')/items('" + id + "')",  
66.             type: "POST",  
67.             headers:  
68.              {  
69.                  "Accept": "application/json;odata=verbose",  
70.                  "Content-Type": "application/json;odata=verbose",  
71.                  "X-RequestDigest": $("#__REQUESTDIGEST").val(),  
72.                  "IF-MATCH": "*",  
73.                  "X-HTTP-Method": "DELETE"  
74.              },  
75.             success: function (data, status, xhr) {  
76.                 $("#tblEmployees").empty();  
77.                 GetEmployeeDetails();  
78.   
79.                 alert("Successfully record deleted");  
80.             },  
81.             error: function (xhr, status, error) {  
82.                 alert(JSON.stringify(error));  
83.             }  
84.         });  
85.     }  
86.   
87.   
88.     function ClearData() {  
89.   
90.         $("#empID").val("");  
91.         $('#empName').val("");  
92.         $("#empSalary").val("");  
93.         $("#empAddress").val("");  
94.   
95.     }  
96.     function GetEmployeeDetailsByID() {  
97.         var idValue = $("#empID").val();  
98.   
99.   
100.         $.ajax({  
101.             url: _spPageContextInfo.siteAbsoluteUrl + "/_api/web/lists/getbytitle('" + listName + "')/items('" + idValue + "')",  
102.             type: "GET",  
103.             headers: { "Accept": "application/json;odata=verbose" }, // return data format  
104.             success: function (data) {  
105.                 $("#empName").val(data.d.Title);  
106.                 $("#empSalary").val(data.d.Salary);  
107.                 $("#empAddress").val(data.d.Address);  
108.                 $("#tblEmployees").empty();  
109.                 GetEmployeeDetails();  
110.             },  
111.             error: function (error) {  
112.                 alert(JSON.stringify(error));  
113.             }  
114.         });  
115.     }  
116.   
117.   
118.   
119.     function UpdateEmployee() {  
120.   
121.         var id = $("#empID").val();  
122.         $.ajax({  
123.             url: _spPageContextInfo.siteAbsoluteUrl + "/_api/web/lists/getbytitle('" + listName + "')/items('" + id + "')", // list item ID    
124.             type: "POST",  
125.             data: JSON.stringify  
126.             ({  
127.                 __metadata:  
128.                 {  
129.                     type: "SP.Data.EmployeeListItem"  
130.                 },  
131.                 Title: $("#empName").val(),  
132.                 Salary: $("#empSalary").val(),  
133.                 Address: $("#empAddress").val()  
134.   
135.             }),  
136.             headers:  
137.             {  
138.                 "Accept": "application/json;odata=verbose",  
139.                 "Content-Type": "application/json;odata=verbose",  
140.                 "X-RequestDigest": $("#__REQUESTDIGEST").val(),  
141.                 "IF-MATCH": "*",  
142.                 "X-HTTP-Method": "MERGE"  
143.             },  
144.             success: function (data, status, xhr) {  
145.                 $("#tblEmployees").empty();  
146.                 GetEmployeeDetails();  
147.                 alert("Date Updated Successfully");  
148.             },  
149.             error: function (xhr, status, error) {  
150.                 alert(JSON.stringify(error));  
151.             }  
152.         });  
153.     }  
154.   
155.     function createEmployee() {  
156.   
157.         $.ajax({  
158.             url: _spPageContextInfo.siteAbsoluteUrl + "/_api/web/lists/getbytitle('" + listName + "')/items",  
159.             type: "POST",  
160.             contentType: "application/json;odata=verbose",  
161.             data: JSON.stringify  
162.     ({  
163.         __metadata:  
164.         {  
165.             type: "SP.Data.EmployeeListItem"  
166.         },  
167.         Title: $("#empName").val(),  
168.         Salary: $("#empSalary").val(),  
169.         Address: $("#empAddress").val()  
170.     }),  
171.             headers: {  
172.                 "Accept": "application/json;odata=verbose", // return data format  
173.                 "X-RequestDigest": $("#__REQUESTDIGEST").val()  
174.             },  
175.             success: function (data, status, xhr) {  
176.                 $("#tblEmployees").empty();  
177.                 GetEmployeeDetails();  
178.                 alert("Successfully Submitted");  
179.             },  
180.             error: function (xhr, status, error) {  
181.                 alert(JSON.stringify(error));  
182.             }  
183.         });  
184.     }  
185.     function GetEmployeeDetails() {  
186.   
187.   
188.   
189.         $.ajax({  
190.             url: _spPageContextInfo.siteAbsoluteUrl + "/_api/web/lists/getbytitle('" + listName + "')/items?$select=ID,Title,Salary,Address",  
191.             type: "GET",  
192.             headers: { "Accept": "application/json;odata=verbose" }, // return data format  
193.             success: function (data) {  
194.                 //console.log(data.d.results);  
195.   
196.   
197.                 var table = $("#tblEmployees");  
198.                 var html = "<thead><tr><th>ID</<th><th>Name</th><th>Salary</th><th>Address</th></tr></thead>";  
199.   
200.   
201.   
202.                 for (var i = 0; i < data.d.results.length; i++) {  
203.                     var item = data.d.results[i];  
204.                     //$("#tblEmployees").append(item.Title + "\t" + item.Salary + "\t" + item.Address + "<br/>");  
205.   
206.   
207.                     html += "<tr><td>" + item.ID + "</td><td>" + item.Title + "</td><td>" + item.Salary + "</td><td>" + item.Address + "</td></tr>";  
208.   
209.   
210.                 }  
211.                 table.html(html);  
212.             },  
213.             error: function (error) {  
214.                 alert(JSON.stringify(error));  
215.             }  
216.         });  
217.   
218.   
219.   
220.     }  
221.   
222.   
223.     function manageQueryStringParameter(paramToRetrieve) {  
224.         var params =  
225.         document.URL.split("?")[1].split("&");  
226.         var strParams = "";  
227.         for (var i = 0; i < params.length; i = i + 1) {  
228.             var singleParam = params[i].split("=");  
229.             if (singleParam[0] == paramToRetrieve) {  
230.                 return singleParam[1];  
231.             }  
232.         }  
233.     }  
234.   
235.     // This function prepares, loads, and then executes a SharePoint query to get the current users information  
236.     function getUserName() {  
237.         context.load(user);  
238.         context.executeQueryAsync(onGetUserNameSuccess, onGetUserNameFail);  
239.     }  
240.   
241.     // This function is executed if the above call is successful  
242.     // It replaces the contents of the 'message' element with the user name  
243.     function onGetUserNameSuccess() {  
244.         $('#message').text('Hello ' + user.get_title());  
245.     }  
246.   
247.     // This function is executed if the above call fails  
248.     function onGetUserNameFail(sender, args) {  
249.         alert('Failed to get user name. Error:' + args.get_message());  
250.     }  
251. }   

Deploy the Project

Right click on the solution and select the "Deploy" option.

• Once you deploy it, the system will ask for Ofice 365 credentials. Enter the credentials.
• Trust the app like in the below screen.
  
   

• You can see the User Interface page like below.
  
   

Test the application

Here, we will test for "Submit" button.

• Enter Employee Name, Salary, and Address values and click on the "Submit" button. Leave the ID text box empty for it is auto-generated.
  
  

• Once you click on Submit button, the data will be displayed in the below table.
  
  
  
• To test Get Data, enter the ID value in text box. Example - I entered 15 in ID text box and clicked on "Get Data" button. The list Item data will be displayed in the respective text boxes.
  
   

• To test Clear functionality, click on "Clear" button; the data will be cleared for textboxes.
  
  

• To test "Delete" functionality, enter the ID value and click on Delete button. Example- I have entered ID 15 in text box and clicked on Delete button. List Item ID 15 record will be removed from the list.