Performing CRUD operations on Azure SQL Database using SharePoint Framework Web Part and ASP.NET Web API - Part 2

In part 1 of this article we looked at how to set up SQL database in Azure, create a simple get operation in web API and publish the API to Azure. You can read part 1 here:
Performing CRUD operations on Azure SQL Database using SharePoint Framework Web Part and ASP.NET Web API - Part 1
In this post we will be further developing our web API to including Post, Update and Delete methods, then call the API within an SPFX web part.

Agenda for this post are:

• 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:

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 prepare environment 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:

1. Open command line, cmd

2. Create a folder using the command md, call it spfx (i.e. md spfx)

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

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.  

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.

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.
7. To add jquery to your project, on command line type: 
     npm jquery --save
     npm @types/jquery --save

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

9. Click the drop down arrow next to src >Webparts>YourWebpartName 
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.
11. Open Config.json. YourWebpartName > Config > Config.json
12. Modify it as shown below

       
"externals": {
    "jquery": {
      "path": "node_modules/jquery/dist/jquery.min.js",
      "globalName": "jQuery"
    }

       
 

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:

"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:
.container .row .column{
width: 70%;
}
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
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';

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';

16. Copy and paste the css file below in notepad and save as styles.css

17. Upload it to Site Asset library in your SharePoint site. Note you can use any library of your choice.
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.
19. Copy https://yourspsite.sharepoint.com/sites/dev/SiteAssets/styles.css out of the link and go back to YourWebpartName.ts
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.
21. Replace the code block in public render () with the code block below

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
1. Still in YourWebpartName.ts, update the code block inside Public render() as follows

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:

Ensure you replace the urls in the code with the url for your API

Now it is time to test our web part:

in 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 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 SharePointOnline.  
Note: This load 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. I will cover that in another post.

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