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

When it comes to storing and retrieving large data on SharePoint, my approach (depending on scope though) is usually to store the data in an external database  where I can have full control over the data retrieval and totally avoid dealing with the large list issue due to SharePoint list throttling limit, then render the data on a SharePoint page using visual web part. But like I indicated above, there are a number of things you have to consider before deciding weather you want to store your data on a SharePoint list or directly on an SQL database table.  Important questions to answer are:


  • Will end users be creating views based on different filtering parameter from time to time? if the answer is yes, you might want to consider using a SharePoint list as it already has administrative tools for power users to perform these tasks. This may be a quick win approach for you, particularly where deadline is against you. But you have to bear in mind that your users might have to deal with the large list issue when retrieving data via the views. One way to avoid list throwing error when retrieving data is to index a column on the list. Hope it works the way you want, good luck with that.  Note: In SharePoint online and SharePoint 2016, Microsoft has included auto indexing tool to manage lists that are reaching or getting over the throttling limit.  See here for more info. 
  • Would the solution be required to export large result data to excel for reporting purpose? If yes, you might want to consider storing your data directly on sql database. One thing is certain here, You can always run a query against your table and export the result to an excel sheet no matter how large. SharePoint list tends to error out when the data is too large. I don't know if Microsoft has made any improvement on this in the latest version. 
  • Are there likely going to be an upgrade to SharePoint farm or Office 365 that can break the solution? Ensure you follow best practices when building your web part. Upgrade may not really break your code if care is taken to keep your artifacts in safe places. I have deployed a web part I built in 2010 to a 2013 environment and it worked just fine. Thankfully, with SharePoint app model/SharePoint framework and the support for RESTful APIs, things are now made easy.
  • Another thing that can constrain you is your team or client's development approach. You have to conform to what the style is in your organization unless you can make them see convincing reasons why they have to adopt a new approach.
Enough said!!! Let's drop the debate and get back to the topic. Supposing there is an existing data in an sql database, be it azure or on premise database server and you are required to make the data available for CRUD operations in SharePoint for your users. This is a scenario where this article will be of help to you. In this article, I will show you how you can perform CRUD operations on Azure SQL database from a SharePoint web part. Note that the steps are the same for on-premise SQL database except for steps required in setting up azure database.

In this article, I will be covering:



  • How to set up SQL database in Azure
  • Creating restful web API and connect to the azure sql database
  • Publish the API to Azure
  • Enable cross-origin in the Web API
In part 2 of this article, I will be covering:

  • How to create SPFX web part 
  • Consume the Web API created in part 1 in the Spfx web part
  • Implement CRUD operations in the Spfx web part
  • Deploy to SharePoint Online

Setting up SQL Azure database

Log into your azure account and follow the steps below to create an SQL database (note that you can create a trial azure account for testing purpose). 


The steps are pretty straightforward.

1. Enter a name for your database as shown below
2. Create a new server, name it and click the select button to select the server on which to create the database. 
3. Then click create to create the database (note the creation process might take sometime, and you might have to refresh the page to see the new database). 


Ensure you remember your password***

The next step is to create a firewall rule and add your current client IP address to it. This step is important for you to connect to the database server from your client system.

4. Click on the newly created database, you should see an interface similar to the one below.  Note down the server name (i.e. yourserver.database.windows.net). You will need it when you want to connect to the server.


5. Click on Set server firewall in the toolbar
6. Click Add client IP in the toolbar to add your current client IP address to the firewall. Note your public IP will be automatically retrieved.

7. Click Save to finish the process.

Now you are done with the first task. The next step is to create our web API solution.
Let's fire up visual studio. I will be using visual studio 2017 community edition.


Creating restful web API and connect to the azure sql database

Before you start this step, ensure you download SQL server management studio here if you don't already have it installed. We will need it to connect to our database. You could also connect to it within VS but I prefer using SSMS.
1. Launch visual studio and create a new ASP.NET Web Application project
2. Name it DemoAPI or whatever you like


3. Follow the screenshot below to complete the process. When you are done, click Ok


Now let's connect to our database server and create a table in our database
4. Open SQL server management studio and connect your server (the server name you saved earlier)

5. Put in the sever login Id and password you created when you set up the server in azure
6. Click Connect to open the the server
7. Run the script below to create a table in your database. Be sure to change the database name to match the database you created earlier.
       
Use StaffDemoDB
Go

Create table Employees
(
 ID int primary key identity,
 FirstName nvarchar(50),
 LastName nvarchar(50),
 Gender nvarchar(50),
 Salary int

)
Go
Insert into Employees values ('Mark', 'Hastings', 'Males', 6000)
Insert into Employees values ('Jas', 'Hastings', 'Males', 6000)
Insert into Employees values ('Mark', 'Mikel', 'Males', 6000)
Insert into Employees values ('Mitchell', 'Hastings', 'Males', 6000)
Insert into Employees values ('Mark', 'Jones', 'Males', 6000)
Insert into Employees values ('Kunle', 'Hastings', 'Males', 6000)
Insert into Employees values ('Mark', 'Peters', 'Males', 6000)
Insert into Employees values ('Deji', 'Hastings', 'Males', 6000)
Insert into Employees values ('Tao', 'Oyemade', 'Males', 6000)

       
 

If everything works fine, your Employee table should be created and look like this:
The next step is to create connection to our db using ADO.NET Entity Data Model

8. In the solution explorer of your project, right click on Models folder, select Add > New Item > Data and click ADO.NET Entity Data Model
9. Name it EmployeeModel


10. Click Add, then select EF Designer from database in the next screen and click Next
11. In the Choose your database connection, click New Connection and supply details to connect to your database, click Ok and click Next


12.Select Entity framework 6x and click Next
13. Select your table and click Finish

The next step is to publish our API to azure. Follow the steps below to do this

Publish the API to Azure

Before publishing the API, lets create a Get operation to fetch our list of employees from the database

1. Right click on the Controller folder > Add > Controller.. > Web API 2 Controller -Empty
2. Name it Employee. Note you only have to rename the highlighted part. If you use Employee as advised, you will have EmployeeController as name. See the image below


3. Click Add
4. Type out the code below into your controller. If you have used the same database and table name, you should have no error


5. Now let's publish. Right click the project name and click publish...
6. Click on Microsoft Azure App Service and select Create New  
7. Create an App Name with other settings as shown below. You can leave the default names if you wish. When you are done, click Create. This may take some time to complete.

8. When it is finished, your web API will be published to Azure and opens in browser
9. To test your web API, append /api/employee to the end of the url displayed in the browser. You should have something like this "http://apiname.azurewebsites.net/api/employee". Notice the last parameter in the url-"employee". If you did not name your controller employee, this url won't work for you. You have to use the name of your controller. 
10. When you open the url, you should see your data in XML format, or JSON if you have formatted your output. 

The last step in this article is to enable cross origin resource sharing

Enable cross-origin in the Web API

To be able to call this web API from another domain other than azure, you need to enable cross origin attribute in your code. Since we are going to be calling this API in SharePoint, we will add this attribute to our code. 
1. Right click on your project > Manage NuGet Packages > Microsoft.ASPNET.WebAPI.Cors
2. Click Install to add it to your project. 
3. Click Ok to the warning and accept the installation


4. Once installation completes, open WebAPIConfig.cs in App_Start folder and add the following highlighted code inside the Register method

Note: The * in the above code means I want to accept all domains, apply it to all headers and methods. This may not be what you want to do in production. Be sure to understand what you are doing. 

5. Right click your project and click publish to publish your changes to Azure.

Summary

Now we have completed the part 1 of this article. We have seen how we can publish web API to azure and enable cross origin for resource sharing with other domains. In part 2 of this article, we will discuss how we can call this API within our spfx web part. 

Till then, happy coding

Comments

  1. This comment has been removed by a blog administrator.

    ReplyDelete

  2. Princess Prom Photoshoot
    Baseball Hero
    Football Tricks
    I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.

    ReplyDelete
  3. Whaoo, I appreciate this site, have been dragging my feet to move up to SP online development but this site is a perfect Intro for me.
    Cheers

    ReplyDelete
  4. Everytime I try to do this, it comes up with "An error has ocurred". Does anyone have any ideas how to fix this?

    Thanks in advance!

    ReplyDelete
  5. Extremely useful information which you have shared here. This is a great way to enhance knowledge for us, and also beneficial for us. Thank you for sharing an article like this. application maintenance service usa

    ReplyDelete
  6. Happy to visit your blog, I am by all accounts forward to more solid articles and I figure we as a whole wish to thank such huge numbers of good articles, blog to impart to us.

    SQL Server Load Rest API

    ReplyDelete
  7. Nice post. Please Look, we are the florists in Los Angeles
    (service provider).
    Thank you

    ReplyDelete
  8. Thank You for sharing this post. I really Like your content. Hope you will share more information about Web Designers in San diego

    ReplyDelete

Post a Comment

Popular posts from this blog

Creating SharePoint Framework Client Web Part and solving the challenges with _RequestDigest Token value using React

Generate Word Document From A SharePoint List Item Using Microsoft Flow

Creating SharePoint Aspx Pages from Word Documents using PowerShell