Post
Ask Question

Performing CRUD Operations On Azure SQL Database Using SharePoint Framework Web Part And ASP.NET Web API - Part Two

In part 1 of this article, we looked at how to set up SQL database in Azure, created a simple get operation in web API, and published the API to Azure. You can read part 1 here -

In this post, we will be further developing our web API to include Post, Update, and Delete methods, then call the API within an SPFX web part.

Agenda for this post is -
  • Update our Web API to include Post, Update and Delete methods
  • Creating SPFX web part
  • Consume the Web API and Implement CRUD operations in Spfx web part
  • Test the web part
Updating the API

If you have created a Web API solution as described in part 1, go ahead and copy the code below to update your solution, otherwise go back to part 1 to create a web API solution. Your final code should look like this.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Net;  
  5. using System.Net.Http;  
  6. using System.Web.Http;  
  7. using DemoAPI.Models;  
  8.   
  9. namespace DemoAPI.Controllers  
  10. {  
  11.     public class EmployeeController : ApiController  
  12.     {  
  13.         public IEnumerable<Employee> GetEmployees()  
  14.         {  
  15.             using (StaffDemoDBEntities entities = new StaffDemoDBEntities())  
  16.             {  
  17.                 return entities.Employees.ToList();  
  18.             }  
  19.         }  
  20.   
  21.         public HttpResponseMessage GetEmployeeById(int id)  
  22.         {  
  23.             try  
  24.             {  
  25.                 using (StaffDemoDBEntities entities = new StaffDemoDBEntities())  
  26.                 {  
  27.                    var entity= entities.Employees.FirstOrDefault(e => e.ID == id);  
  28.                     //entities.Employees.Where(e => e.ID == id).FirstOrDefault();  
  29.                     if (entity != null)  
  30.                     {  
  31.                         return Request.CreateResponse(HttpStatusCode.OK, entity);  
  32.                     }  
  33.                     else  
  34.                         return Request.CreateErrorResponse(HttpStatusCode.NotFound, "Employee with id " + id + "was not found");  
  35.   
  36.                 }  
  37.             }  
  38.             catch(Exception ex)  
  39.             {  
  40.                 return Request.CreateErrorResponse(HttpStatusCode.BadGateway, ex);  
  41.             }  
  42.         }  
  43.            
  44.         public HttpResponseMessage Post([FromBody] Employee employee)  
  45.         {  
  46.             try {  
  47.             using (StaffDemoDBEntities entities = new StaffDemoDBEntities())  
  48.             {  
  49.                 entities.Employees.Add(employee);  
  50.                 entities.SaveChanges();  
  51.                 var message = Request.CreateResponse(HttpStatusCode.Created, employee);  
  52.                     message.Headers.Location = new Uri(Request.RequestUri + "/" + employee.ID.ToString());  
  53.                 return message;  
  54.             }  
  55.             }  
  56.             catch (Exception ex)  
  57.             {  
  58.                 return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex);  
  59.                   
  60.             }  
  61.         }  
  62.         public HttpResponseMessage Put(int id, [FromBody] Employee employee)  
  63.         { try  
  64.             {  
  65.                 using (StaffDemoDBEntities entities = new StaffDemoDBEntities())  
  66.                 {  
  67.                     var entity = entities.Employees.FirstOrDefault(e => e.ID == id);  
  68.   
  69.                     if (entity != null)  
  70.                     {  
  71.                         entity.FirstName = employee.FirstName;  
  72.                         entity.LastName = employee.LastName;  
  73.                         entity.Gender = employee.Gender;  
  74.                         entity.Salary = employee.Salary;  
  75.                         entities.SaveChanges();  
  76.                         return Request.CreateResponse(HttpStatusCode.OK, entity);  
  77.                     }  
  78.                     else  
  79.                         return Request.CreateErrorResponse(HttpStatusCode.NotFound, "Employee with id " + id + "was not found");  
  80.                 }  
  81.             }  
  82.             catch(Exception ex)  
  83.             {  
  84.                 return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex);  
  85.             }  
  86.         }  
  87.   
  88.         public HttpResponseMessage Delete(int id)  
  89.         {  
  90.             try  
  91.             {  
  92.                 using (StaffDemoDBEntities entities = new StaffDemoDBEntities())  
  93.                 {  
  94.                     var entity = entities.Employees.FirstOrDefault(e => e.ID == id);  
  95.                     if (entity != null)  
  96.                     {  
  97.                         entities.Employees.Remove(entity);  
  98.                         entities.SaveChanges();  
  99.                         return Request.CreateResponse(HttpStatusCode.OK, entity);  
  100.                     }  
  101.                     else  
  102.                         return Request.CreateErrorResponse(HttpStatusCode.NotFound, "Employee object with id " + id + "was not found.");  
  103.                 }  
  104.             }  
  105.             catch (Exception ex)  
  106.             {  
  107.                 return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ex);  
  108.             }  
  109.         }  
  110.   
  111.   
  112.     }  
  113. }  
Right-click on your project and publish to Azure as described in part 1.

Creating SPFX web part

The first step is to set up your dev environment for SharePoint framework development. If you have not already done so, follow this link to Set up your SharePoint Framework development environment.

Now, that you have finished setting up your environment, follow the steps below to create an spfx web part.

Step 1

Open command line, CMD.

Step 2

Create a folder named spfxusing the md command md (i.e. md spfx).

Step 3

Navigate to the folder you just created using "cd spfx" and type yo @microsoft/sharepoint


Step 4

Follow the screenshot below to create a solution for your web part. Note that you can name your web part AzureWebAPIWebPart as shown below or anything you wish. Just ensure that when you are copying the code from here, you replace your web part name where necessary.


Step 5

If all is well, you will seen a congratulations screen that looks like below. If there is any issue, ensure you fix it before going on to the next step.



Step 6

You can run gulp serve to ensure everything is okay. This should launch your default browser. Click the plus sign and add your web part. If all is fine, you should see your web part with default look.

Now let's install jquery and Typings for jquery as we require jquery ajax to make calls to our web API.

Step 7

To add jquery to your project, on command line type,

npm jquery --save
npm @types/jquery --save

Step 8

Then type "code ." without the quote to open visual studio code editor.

Step 9

Click the drop down arrow next to src >Webparts>YourWebpartName

Step 10

Verify that jquery library was added successfully to your project by clicking the arrow next to nodes_modules > jquery > dist. Verify jquery.min.js is there.

Let's un-bundle the jquery library by externalizing it. This step is optional but good for page load performance.

Step 11

Open Config.json. YourWebpartName > Config > Config.json

Step 12

Modify it as shown below,
  1. "externals": {  
  2.     "jquery": {  
  3.       "path": "node_modules/jquery/dist/jquery.min.js",  
  4.       "globalName": "jQuery"  
  5.     }  

Step 13

Go back to src and open YourWebpartName.ts. Let's import the jquery and load css file from a SharePoint library in the YourWebpartName.ts.

Note

Normally you would want to put your css classes in the .scss file (i.e. YourWebpartName.scss) and import it into the YourWebpartName.ts file (this is actually done automatically during project creation). But I decided to put it in a SharePoint list because my css selectors are grouped and I could not reference them using "styles.selector". Let me explain this a bit further,

  1. <div class="${ styles.webApiDemoWebPart }">  
  2.         <div class="${ styles.container }">  
  3.           <div class="${ styles.row }">  
  4.             <div class="${ styles.column }">  
  5.               <span class="${ styles.title }">Welcome to SharePoint!</span>  
  6.               <p class="${ styles.subTitle }">Customize SharePoint experiences using Web Parts.</p>  
  7.               <p class="${ styles.description }">${escape(this.properties.description)}</p>  
  8.               <a href="https://aka.ms/spfx" class="${ styles.button }">  
  9.                 <span class="${ styles.label }">Learn more</span>  
  10.               </a>  
  11.             </div>  
  12.           </div>  
  13.         </div>  
  14.       </div>  

 "from the above code, class="${ styles.container }" means selector .container is being referenced from the stylesheet file (i.e. YourWebpartName.scss file). But when your css class selectors are grouped like,

  1. .container .row .column{  
  2.    width: 70%;  
  3. }  
then using styles.selector becomes difficult as you cannot have something like class="${styles.container .row .column}". This will not work. If you know how to achieve it, kindly share it in the comment section.

To load our css file from a list or an external location, we have to import SPComponentLoader into our project

Step 14

Add the code line below in the import section at the top of your code inside YourWebpartName.ts file

 

import { SPComponentLoader } from '@microsoft/sp-loader';

Step 15

We also need to import jquery into the file. Copy and paste the following line of code above the SPComponentLoader. No special reason for putting it above SPComponentLoader, it's just my practice.

import * as jquery from 'jquery';

Step 16

Copy and paste the css file below in notepad and save as styles.css
  1. div.blueTable {  
  2.   border: 1px solid #1C6EA4;  
  3.   background-color: #EEEEEE;  
  4.   width: 100%;  
  5.   text-align: left;  
  6.   border-collapse: collapse;  
  7. }  
  8. .divTable.blueTable .divTableCell, .divTable.blueTable .divTableHead {  
  9.   border: 1px solid #AAAAAA;  
  10.   padding: 3px 2px;  
  11. }  
  12. .divTable.blueTable .divTableBody .divTableCell {  
  13.   font-size: 13px;  
  14. }  
  15. .divTable.blueTable .divTableRow:nth-child(even) {  
  16.   background: #D0E4F5;  
  17. }  
  18. .divTable.blueTable .divTableHeading {  
  19.   background: #1C6EA4;  
  20.   background: -moz-linear-gradient(top, #5592bb 0%, #327cad 66%, #1C6EA4 100%);  
  21.   background: -webkit-linear-gradient(top, #5592bb 0%, #327cad 66%, #1C6EA4 100%);  
  22.   background: linear-gradient(to bottom, #5592bb 0%, #327cad 66%, #1C6EA4 100%);  
  23.   border-bottom: 2px solid #444444;  
  24. }  
  25. .divTable.blueTable .divTableHeading .divTableHead {  
  26.   font-size: 15px;  
  27.   font-weight: bold;  
  28.   color: #FFFFFF;  
  29.   border-left: 2px solid #D0E4F5;  
  30. }  
  31. .divTable.blueTable .divTableHeading .divTableHead:first-child {  
  32.   border-left: none;  
  33. }  
  34.   
  35. .blueTable .tableFootStyle {  
  36.   font-size: 14px;  
  37.   font-weight: bold;  
  38.   color: #FFFFFF;  
  39.   background: #D0E4F5;  
  40.   background: -moz-linear-gradient(top, #dcebf7 0%, #d4e6f6 66%, #D0E4F5 100%);  
  41.   background: -webkit-linear-gradient(top, #dcebf7 0%, #d4e6f6 66%, #D0E4F5 100%);  
  42.   background: linear-gradient(to bottom, #dcebf7 0%, #d4e6f6 66%, #D0E4F5 100%);  
  43.   border-top: 2px solid #444444;  
  44. }  
  45. .blueTable .tableFootStyle {  
  46.   font-size: 14px;  
  47. }  
  48. .blueTable .tableFootStyle .links {  
  49.      text-align: right;  
  50. }  
  51. .blueTable .tableFootStyle .links a{  
  52.   display: inline-block;  
  53.   background: #1C6EA4;  
  54.   color: #FFFFFF;  
  55.   padding: 2px 8px;  
  56.   border-radius: 5px;  
  57. }  
  58. .blueTable.outerTableFooter {  
  59.   border-top: none;  
  60. }  
  61. .blueTable.outerTableFooter .tableFootStyle {  
  62.   padding: 3px 5px;   
  63. }  
  64. /* DivTable.com */  
  65. .divTable{ display: table; }  
  66. .divTableRow { display: table-row; }  
  67. .divTableHeading { display: table-header-group;}  
  68. .divTableCell, .divTableHead { display: table-cell;}  
  69. .divTableHeading { display: table-header-group;}  
  70. .divTableFoot { display: table-footer-group;}  
  71. .divTableBody { display: table-row-group;}  
  72.   
  73.   
  74. /* Css styles for the form */  
  75.   
  76. * {  
  77.     box-sizing: border-box;  
  78. }  
  79.   
  80. input[type=text], select, textarea{  
  81.     width: 100%;  
  82.     padding: 12px;  
  83.     border: 1px solid #ccc;  
  84.     border-radius: 4px;  
  85.     box-sizing: border-box;  
  86.     resize: vertical;  
  87. }  
  88.   
  89. label {  
  90.     padding: 12px 12px 12px 0;  
  91.     display: inline-block;  
  92. }  
  93.   
  94. input[type=submit] {  
  95.     background-color: #4CAF50;  
  96.     color: white;  
  97.     padding: 12px 20px;  
  98.     border: none;  
  99.     border-radius: 4px;  
  100.     cursor: pointer;  
  101.     float: right;  
  102. }  
  103.   
  104. input[type=submit]:hover {  
  105.     background-color: #45a049;  
  106. }  
  107.   
  108. .container {  
  109.     border-radius: 5px;  
  110.     background-color: #f2f2f2;  
  111.     padding: 20px;  
  112. }  
  113.   
  114. .col-25 {  
  115.     float: left;  
  116.     width: 25%;  
  117.     margin-top: 6px;  
  118. }  
  119.   
  120. .col-75 {  
  121.     float: left;  
  122.     width: 75%;  
  123.     margin-top: 6px;  
  124. }  
  125.   
  126. /* Clear floats after the columns */  
  127. .row:after {  
  128.     content: "";  
  129.     display: table;  
  130.     clear: both;  
  131. }  
  132.   
  133. /* Responsive layout - when the screen is less than 600px wide, make the two columns stack on top of each other instead of next to each other */  
  134. @media (max-width: 600px) {  
  135.     .col-25, .col-75, input[type=submit] {  
  136.         width: 100%;  
  137.         margin-top: 0;  
  138.     }  
  139. }  

Step 17

Upload it to Site Asset library in your SharePoint site. Note you can use any library of your choice.

Step 18

Right-click on the styles.css inside the library and click Copy link. follow the instruction to copy the link. You can get the short link to the file by pasting the link in the browser and hit enter. This will give you something like,

https://yourspsite.sharepoint.com/sites/dev/SiteAssets/styles.css?slrid=65513e9e-e0b3-4000-c649-ece51bb97dbc.

Step 19

Copy https://yourspsite.sharepoint.com/sites/dev/SiteAssets/styles.css out of the link and go back to YourWebpartName.ts

Step 20

Add the following line of code just under SPComponentLoader, but above export interface WebpartProps

SPComponentLoader.loadCss('https://yourspsite.sharepoint.com/sites/dev/SiteAssets/styles.css')

Let's create our form and a table to display data from Azure database.

Step 21

Replace the code block in public render () with the code block below

  1. public render(): void {  
  2.     this.domElement.innerHTML = `   
  3.     <div class="container">  
  4.  <form>  
  5.     <div class="row">  
  6.     <h2 style="text-align:left" id="statusMode">  
  7.                     Add New Record  
  8.                 </h4>  
  9.       <div class="col-25">  
  10.         <label for="fname">First Name</label>  
  11.       </div>  
  12.       <div class="col-75">  
  13.         <input type="text" id="fname" name="firstname" placeholder="First Name..">  
  14.       </div>  
  15.     </div>  
  16.     <div class="row">  
  17.       <div class="col-25">  
  18.         <label for="lname">Last Name</label>  
  19.       </div>  
  20.       <div class="col-75">  
  21.         <input type="text" id="lname" name="lastname" placeholder="Last Name..">  
  22.       </div>  
  23.     </div>  
  24.     <div class="row">  
  25.       <div class="col-25">  
  26.         <label for="gender">Gender</label>  
  27.       </div>  
  28.       <div class="col-75">  
  29.       <input type="text" id="gender" name="gender" placeholder="Gender..">  
  30.       </div>  
  31.     </div>  
  32.     <div class="row">  
  33.       <div class="col-25">  
  34.         <label for="salary">Salary</label>  
  35.       </div>  
  36.       <div class="col-75">  
  37.       <input type="text" id="salary" name="gender" placeholder="Salary..">  
  38.       </div>  
  39.     </div>  
  40.     <!-- hidden controls -->  
  41.     <div style="display: none">  
  42.         <input id="recordId" />  
  43.     </div>  
  44.     <div class="row">  
  45.       <input type="submit" value="Submit" id="btnSubmit">  
  46.     </div>  
  47. </form>  
  48. </div>  
  49.   
  50. //Creates a table to display data from Azure database  
  51.     <div class="divTable blueTable">  
  52. <div class="divTableHeading">  
  53. <div class="divTableRow">  
  54. <div class="divTableHead">First Name</div>  
  55. <div class="divTableHead">Last Name</div>  
  56. <div class="divTableHead">Gender</div>  
  57. <div class="divTableHead">Salary</div>  
  58. </div>  
  59. </div>  
  60. <div class="divTableBody" id="fileGrid">  
  61.   
  62.   
  63. </div>  
  64. </div>  
  65. <div class="blueTable outerTableFooter"><div class="tableFootStyle"><div class="links"><a href="#">«</a> <a class="active" href="#">1</a> <a href="#">2</a> <a href="#">3</a> <a href="#">4</a> <a href="#">»</a></div></div></div>  
  66. `;  

This code block does two things. It creates a form to carry out Create and Update operations on our table and creates a table to display data from Azure database.

Implement CRUD Operations in SPFX

Step 1

Still in YourWebpartName.ts, update the code block inside Public render() as follows,

  1. public render(): void {  
  2.     this.domElement.innerHTML = `   
  3.     <div class="container">  
  4.  <form>  
  5.     <div class="row">  
  6.     <h2 style="text-align:left" id="statusMode">  
  7.                     Add New Record  
  8.                 </h4>  
  9.       <div class="col-25">  
  10.         <label for="fname">First Name</label>  
  11.       </div>  
  12.       <div class="col-75">  
  13.         <input type="text" id="fname" name="firstname" placeholder="First Name..">  
  14.       </div>  
  15.     </div>  
  16.     <div class="row">  
  17.       <div class="col-25">  
  18.         <label for="lname">Last Name</label>  
  19.       </div>  
  20.       <div class="col-75">  
  21.         <input type="text" id="lname" name="lastname" placeholder="Last Name..">  
  22.       </div>  
  23.     </div>  
  24.     <div class="row">  
  25.       <div class="col-25">  
  26.         <label for="country">Gender</label>  
  27.       </div>  
  28.       <div class="col-75">  
  29.       <input type="text" id="gender" name="gender" placeholder="Gender..">  
  30.       </div>  
  31.     </div>  
  32.     <div class="row">  
  33.       <div class="col-25">  
  34.         <label for="subject">Salary</label>  
  35.       </div>  
  36.       <div class="col-75">  
  37.       <input type="text" id="salary" name="gender" placeholder="Salary..">  
  38.       </div>  
  39.     </div>  
  40.     <!-- hidden controls -->  
  41.     <div style="display: none">  
  42.         <input id="recordId" />  
  43.     </div>  
  44.     <div class="row">  
  45.       <input type="submit" value="Submit" id="btnSubmit">  
  46.     </div>  
  47. </form>  
  48. </div>  
  49.   
  50.     <div class="divTable blueTable">  
  51. <div class="divTableHeading">  
  52. <div class="divTableRow">  
  53. <div class="divTableHead">First Name</div>  
  54. <div class="divTableHead">Last Name</div>  
  55. <div class="divTableHead">Gender</div>  
  56. <div class="divTableHead">Salary</div>  
  57. </div>  
  58. </div>  
  59. <div class="divTableBody" id="fileGrid">  
  60.   
  61.   
  62. </div>  
  63. </div>  
  64. <div class="blueTable outerTableFooter"><div class="tableFootStyle"><div class="links"><a href="#">«</a> <a class="active" href="#">1</a> <a href="#">2</a> <a href="#">3</a> <a href="#">4</a> <a href="#">»</a></div></div></div>  
  65.       
  66.     `;  
  67. $(document).ready(function(){  
  68.   PopulateData();  
  69.   $('#statusMode').html('Add New Record');  
  70.   $('#btnSubmit').click(function(e){  
  71.     if($('#statusMode').html()=="Add New Record")  
  72.     {  
  73.       alert('Add record function');  
  74.       AddNewRecord();  
  75.         
  76.     }  
  77.     else{  
  78.     UpdateRecord($('#recordId').val());  
  79.     }  
  80.   });  
  81. });  
  82.     function PopulateData()  
  83.     {  
  84.       jquery.ajax({  
  85.         url: "https://yourdemoapi.azurewebsites.net/api/employee",  
  86.         type:"GET",  
  87.         headers: {"Accept": "application/json; odata=verbose"},  
  88.         success: function(data){  
  89.           if(data){  
  90.             var len = data.length;  
  91.             var txt = "";  
  92.             if(len > 0){  
  93.                 for(var i=0;i<len;i++){  
  94.                       
  95.                 txt += '<div class="divTableRow" ><div class="divTableCell">'+data[i].FirstName+'</div><div class="divTableCell">'+data[i].LastName+'</div>' +  
  96.                                '<div class="divTableCell">'+data[i].Gender+'</div><div class="divTableCell">'+data[i].Salary+'</div><div class="divTableCell">'+"<a id='" + data[i].ID + "' href='#' class='EditFileLink'>Edit</a>"+'</div><div class="divTableCell">'+"<a id='" + data[i].ID + "' href='#' class='DeleteLink'>Delete</a>"+'</div></div>';  
  97.                 }  
  98.                 if(txt != ""){  
  99.                     $("#fileGrid").append(txt);  
  100.                 }  
  101.             }  
  102.         }  
  103.         },  
  104.   
  105.         error: function(jqXHR, textStatus, errorThrown){  
  106.           alert('error: ' + textStatus + ': ' + errorThrown);  
  107.       }  
  108.       });  
  109.     }  
  110.     $(document).on('click', '.EditFileLink', function (e) {  
  111.       e.preventDefault();  
  112.       var id = this.id;  
  113.       var requestUri = "https://yourdemoapi.azurewebsites.net/api/employee/"+id+"";  
  114.       $.ajax({  
  115.           url: requestUri,  
  116.           method: "GET",  
  117.           contentType: "application/json;odata=verbose",  
  118.           headers: { "accept": "application/json;odata=verbose" },  
  119.           success: function (data) {  
  120.               $('#fname').val(data.FirstName);  
  121.               $('#lname').val(data.LastName);  
  122.               $('#gender').val(data.Gender);  
  123.               $('#salary').val(data.Salary);  
  124.               $('#statusMode').html('Edit Record');  
  125.               $('#recordId').val(data.ID);  
  126.           }  
  127.       });  
  128.     });  
  129.     $(document).on('click', '.DeleteLink', function (e) {  
  130.       e.preventDefault();  
  131.       var id = this.id;  
  132.       var confirmDelete = confirm('Confirm deletion');  
  133.       if(confirmDelete){   
  134.       var requestUri = "https://yourdemoapi.azurewebsites.net/api/employee/"+id+"";  
  135.       $.ajax({  
  136.           url: requestUri,  
  137.           method: "DELETE",  
  138.           headers: { "accept": "application/json;odata=verbose" },  
  139.           success: function () {  
  140.               alert('Record deleted successfully');  
  141.           }  
  142.       });  
  143.       location.reload(true);  
  144.     }  
  145.     else{  
  146.   
  147.     }  
  148.     });  
  149.   
  150.     // Add new record  
  151.     function AddNewRecord() {  
  152.       var firstName = $("#fname").val();  
  153.       var lastName = $("#lname").val();  
  154.       var gender = $("#gender").val();  
  155.       var salary = $("#salary").val();  
  156.       var requestUri = "https://yourdemoapi.azurewebsites.net/api/employee";  
  157.       var requestHeaders = {  
  158.           "accept": "application/json;odata=verbose",  
  159.       }  
  160.       var requestData = {  
  161.           FirstName: firstName,  
  162.           LastName: lastName,  
  163.           Gender: gender,  
  164.           Salary: salary  
  165.       };  
  166.       var requestBody = JSON.stringify(requestData);  
  167.     
  168.       jquery.ajax({  
  169.           url: requestUri,  
  170.           method: "POST",  
  171.           contentType: "application/json;odata=verbose",  
  172.           headers: requestHeaders,  
  173.           data: requestBody,  
  174.           success: function ()  
  175.           {  
  176.             alert('Record successfully updated');  
  177.     
  178.           },  
  179.           error: function(jqXHR){  
  180.             alert('error: ' + jqXHR.responseText);  
  181.         }  
  182.       });  
  183.   }  
  184.     //update record  
  185. function UpdateRecord(id) {  
  186.     var firstName = $("#fname").val();  
  187.     var lastName = $("#lname").val();  
  188.     var gender = $("#gender").val();  
  189.     var salary = $("#salary").val();  
  190.     var requestUri = "https://yourdemoapi.azurewebsites.net/api/employee/"+id+"";  
  191.     var requestHeaders = {  
  192.         "accept": "application/json;odata=verbose",  
  193.     }  
  194.     var requestData = {  
  195.         FirstName: firstName,  
  196.         LastName: lastName,  
  197.         Gender: gender,   
  198.         Salary: salary  
  199.     };  
  200.     var requestBody = JSON.stringify(requestData);  
  201.   
  202.     jquery.ajax({  
  203.         url: requestUri,  
  204.         method: "PUT",  
  205.         contentType: "application/json;odata=verbose",  
  206.         headers: requestHeaders,  
  207.         data: requestBody,  
  208.         success: function ()  
  209.         {  
  210.           alert('Record successfully updated');  
  211.   
  212.         },  
  213.         error: function(jqXHR){  
  214.           alert('error: ' + jqXHR.responseText);  
  215.       }  
  216.     });  
  217. }  
  218.   }  
This code implements get, create, update and delete operations using PopulateData, AddNewRecord, UpdateRecord and DeleteLink functions respectively, to make calls to the API hosted on Azure.

Your complete code should look like this,
  1. import { Version } from '@microsoft/sp-core-library';  
  2. import {  
  3.   BaseClientSideWebPart,  
  4.   IPropertyPaneConfiguration,  
  5.   PropertyPaneTextField  
  6. } from '@microsoft/sp-webpart-base';  
  7. import { escape } from '@microsoft/sp-lodash-subset';  
  8. import * as jquery from 'jquery';  
  9. //import styles from './WebApiDemoWebPartWebPart.module.scss';  
  10. import * as strings from 'WebApiDemoWebPartWebPartStrings';  
  11. import { SPComponentLoader } from '@microsoft/sp-loader';  
  12.   
  13. SPComponentLoader.loadCss('https://yourspsite.sharepoint.com/sites/dev/SiteAssets/styles.css');  
  14.   
  15. export interface IWebApiDemoWebPartWebPartProps {  
  16.   description: string;  
  17. }  
  18.   
  19. export default class WebApiDemoWebPartWebPart extends BaseClientSideWebPart<IWebApiDemoWebPartWebPartProps> {  
  20.   
  21.   public render(): void {  
  22.     this.domElement.innerHTML = `   
  23.     <div class="container">  
  24.  <form>  
  25.     <div class="row">  
  26.     <h2 style="text-align:left" id="statusMode">  
  27.                     Add New Record  
  28.                 </h4>  
  29.       <div class="col-25">  
  30.         <label for="fname">First Name</label>  
  31.       </div>  
  32.       <div class="col-75">  
  33.         <input type="text" id="fname" name="firstname" placeholder="First Name..">  
  34.       </div>  
  35.     </div>  
  36.     <div class="row">  
  37.       <div class="col-25">  
  38.         <label for="lname">Last Name</label>  
  39.       </div>  
  40.       <div class="col-75">  
  41.         <input type="text" id="lname" name="lastname" placeholder="Last Name..">  
  42.       </div>  
  43.     </div>  
  44.     <div class="row">  
  45.       <div class="col-25">  
  46.         <label for="country">Gender</label>  
  47.       </div>  
  48.       <div class="col-75">  
  49.       <input type="text" id="gender" name="gender" placeholder="Gender..">  
  50.       </div>  
  51.     </div>  
  52.     <div class="row">  
  53.       <div class="col-25">  
  54.         <label for="subject">Salary</label>  
  55.       </div>  
  56.       <div class="col-75">  
  57.       <input type="text" id="salary" name="gender" placeholder="Salary..">  
  58.       </div>  
  59.     </div>  
  60.     <!-- hidden controls -->  
  61.     <div style="display: none">  
  62.         <input id="recordId" />  
  63.     </div>  
  64.     <div class="row">  
  65.       <input type="submit" value="Submit" id="btnSubmit">  
  66.     </div>  
  67. </form>  
  68. </div>  
  69.     <div class="divTable blueTable">  
  70. <div class="divTableHeading">  
  71. <div class="divTableRow">  
  72. <div class="divTableHead">First Name</div>  
  73. <div class="divTableHead">Last Name</div>  
  74. <div class="divTableHead">Gender</div>  
  75. <div class="divTableHead">Salary</div>  
  76. </div>  
  77. </div>  
  78. <div class="divTableBody" id="fileGrid">  
  79. </div>  
  80. </div>  
  81. <div class="blueTable outerTableFooter"><div class="tableFootStyle"><div class="links"><a href="#">«</a> <a class="active" href="#">1</a> <a href="#">2</a> <a href="#">3</a> <a href="#">4</a> <a href="#">»</a></div></div></div>  
  82.       
  83.     `;  
  84. $(document).ready(function(){  
  85.   PopulateData();  
  86.   $('#statusMode').html('Add New Record');  
  87.   $('#btnSubmit').click(function(e){  
  88.     if($('#statusMode').html()=="Add New Record")  
  89.     {  
  90.       alert('Add record function');  
  91.       AddNewRecord();  
  92.         
  93.     }  
  94.     else{  
  95.     UpdateRecord($('#recordId').val());  
  96.     }  
  97.   });  
  98. });  
  99.   
  100. //bind data to the table  
  101.     function PopulateData()  
  102.     {  
  103.       jquery.ajax({  
  104.         url: "https://yourdemoapi.azurewebsites.net/api/employee",  
  105.         type:"GET",  
  106.         headers: {"Accept": "application/json; odata=verbose"},  
  107.         success: function(data){  
  108.           if(data){  
  109.             var len = data.length;  
  110.             var txt = "";  
  111.             if(len > 0){  
  112.                 for(var i=0;i<len;i++){  
  113.                       
  114.                 txt += '<div class="divTableRow" ><div class="divTableCell">'+data[i].FirstName+'</div><div class="divTableCell">'+data[i].LastName+'</div>' +  
  115.                                '<div class="divTableCell">'+data[i].Gender+'</div><div class="divTableCell">'+data[i].Salary+'</div><div class="divTableCell">'+"<a id='" + data[i].ID + "' href='#' class='EditFileLink'>Edit</a>"+'</div><div class="divTableCell">'+"<a id='" + data[i].ID + "' href='#' class='DeleteLink'>Delete</a>"+'</div></div>';  
  116.                 }  
  117.                 if(txt != ""){  
  118.                     $("#fileGrid").append(txt);  
  119.                 }  
  120.             }  
  121.         }  
  122.         },  
  123.   
  124.         error: function(jqXHR, textStatus, errorThrown){  
  125.           alert('error: ' + textStatus + ': ' + errorThrown);  
  126.       }  
  127.       });  
  128.     }  
  129.   
  130.     //load and bind data to form in edit mode  
  131.     $(document).on('click', '.EditFileLink', function (e) {  
  132.       e.preventDefault();  
  133.       var id = this.id;  
  134.       var requestUri = "https://yourdemoapi.azurewebsites.net/api/employee/"+id+"";  
  135.       $.ajax({  
  136.           url: requestUri,  
  137.           method: "GET",  
  138.           contentType: "application/json;odata=verbose",  
  139.           headers: { "accept": "application/json;odata=verbose" },  
  140.           success: function (data) {  
  141.               $('#fname').val(data.FirstName);  
  142.               $('#lname').val(data.LastName);  
  143.               $('#gender').val(data.Gender);  
  144.               $('#salary').val(data.Salary);  
  145.               $('#statusMode').html('Edit Record');  
  146.               $('#recordId').val(data.ID);  
  147.           }  
  148.       });  
  149.     });  
  150.   
  151.     //delete record  
  152.     $(document).on('click', '.DeleteLink', function (e) {  
  153.       e.preventDefault();  
  154.       var id = this.id;  
  155.       var confirmDelete = confirm('Confirm deletion');  
  156.       if(confirmDelete){   
  157.       var requestUri = "https://yourdemoapi.azurewebsites.net/api/employee/"+id+"";  
  158.       $.ajax({  
  159.           url: requestUri,  
  160.           method: "DELETE",  
  161.           headers: { "accept": "application/json;odata=verbose" },  
  162.           success: function () {  
  163.               alert('Record deleted successfully');  
  164.           }  
  165.       });  
  166.       location.reload(true);  
  167.     }  
  168.     else{  
  169.   
  170.     }  
  171.     });  
  172.   
  173.     // Add new record  
  174.     function AddNewRecord() {  
  175.       var firstName = $("#fname").val();  
  176.       var lastName = $("#lname").val();  
  177.       var gender = $("#gender").val();  
  178.       var salary = $("#salary").val();  
  179.       var requestUri = "https://yourdemoapi.azurewebsites.net/api/employee";  
  180.       var requestHeaders = {  
  181.           "accept": "application/json;odata=verbose",  
  182.       }  
  183.       var requestData = {  
  184.           FirstName: firstName,  
  185.           LastName: lastName,  
  186.           Gender: gender,  
  187.           Salary: salary  
  188.       };  
  189.       var requestBody = JSON.stringify(requestData);  
  190.     
  191.       jquery.ajax({  
  192.           url: requestUri,  
  193.           method: "POST",  
  194.           contentType: "application/json;odata=verbose",  
  195.           headers: requestHeaders,  
  196.           data: requestBody,  
  197.           success: function ()  
  198.           {  
  199.             alert('Record successfully updated');  
  200.     
  201.           },  
  202.           error: function(jqXHR){  
  203.             alert('error: ' + jqXHR.responseText);  
  204.         }  
  205.       });  
  206.   }  
  207.     //update record  
  208. function UpdateRecord(id) {  
  209.     var firstName = $("#fname").val();  
  210.     var lastName = $("#lname").val();  
  211.     var gender = $("#gender").val();  
  212.     var salary = $("#salary").val();  
  213.     var requestUri = "https://yourdemoapi.azurewebsites.net/api/employee/"+id+"";  
  214.     var requestHeaders = {  
  215.         "accept": "application/json;odata=verbose",  
  216.     }  
  217.     var requestData = {  
  218.         FirstName: firstName,  
  219.         LastName: lastName,  
  220.         Gender: gender,   
  221.         Salary: salary  
  222.     };  
  223.     var requestBody = JSON.stringify(requestData);  
  224.   
  225.     jquery.ajax({  
  226.         url: requestUri,  
  227.         method: "PUT",  
  228.         contentType: "application/json;odata=verbose",  
  229.         headers: requestHeaders,  
  230.         data: requestBody,  
  231.         success: function ()  
  232.         {  
  233.           alert('Record successfully updated');  
  234.   
  235.         },  
  236.         error: function(jqXHR){  
  237.           alert('error: ' + jqXHR.responseText);  
  238.       }  
  239.     });  
  240. }  
  241.   }  
  242.   
  243.   
  244.   protected get dataVersion(): Version {  
  245.     return Version.parse('1.0');  
  246.   }  
  247.   
  248.   protected getPropertyPaneConfiguration(): IPropertyPaneConfiguration {  
  249.     return {  
  250.       pages: [  
  251.         {  
  252.           header: {  
  253.             description: strings.PropertyPaneDescription  
  254.           },  
  255.           groups: [  
  256.             {  
  257.               groupName: strings.BasicGroupName,  
  258.               groupFields: [  
  259.                 PropertyPaneTextField('description', {  
  260.                   label: strings.DescriptionFieldLabel  
  261.                 })  
  262.               ]  
  263.             }  
  264.           ]  
  265.         }  
  266.       ]  
  267.     };  
  268.   }  
  269. }  

Ensure you replace the URLs in the code with the URL of your API. Now it is time to test our web part. In the command prompt, go to the directory for your web part and type one after the other -

gulp build
gulp serve

gulp serve launches your browser and opens local workbench. Click the plus sign and add your web part. If everything works fine, the web part will display your data.

Now, go to your dev SharePointOnline, append _layouts/15/workbench.aspx to your URL. Something like https://yourspsite.sharepoint.com/sites/dev/_layouts/15/workbench.aspx.

Click the plus sign and look for your web part. If everything works fine, your web part should display the data from your Azure database. Your web part should look like the image below if everything is fine,


Congratulations!! You have succeeded in displaying your existing data on SQL Server in SharePoint Online.

Note

This loads the script directly from your local dev machine. If you try to access this web part outside your local dev environment, it will throw an error. You will need to package it and deploy before it can be accessible to your users. 

If you have any questions or suggestion, please leave them in the comment section. Happy coding!

<<Click here for previous part


Similar Articles