Post
Ask Question

Develop A REST API With Azure Functions Using SQL

Introduction

 
In this article, I will demonstrate how we can develop a REST API With Azure Functions using SQL. So, we will use the HTTP triggered type function for these APIs and Visual Studio 2019. I will not use SQL binding while creating REST API because in the newer version of Azure Function SDK (like 3.x) there is no longer support for SQL binding.
 
Prerequisites
 
You are required to have basic knowledge of Azure Functions, beginner level knowledge of ADO.NET, and a basic idea of Azure SQL database.
 
Required NuGet Packages
  1. Microsoft.Azure.WebJobs.Extensions.Storage
  2. Microsoft.NET.Sdk.Functions

Steps

  • Create SQL Server and Database in Azure
  • Set Server Firewall Rule
  • Create a New Table
  • Create a new Azure Function in Visual Studio 2019
  • Add References to NuGet Packages
  • Create Models  
  • Add CRUD Operations
  • Add Connection String in local.settings.json file
  • Testing API’s with Postman 
Operations
Default Route
REST Route
Tasks List
api/GetTasks
GET api/task
Task by Id
api/GetTaskById
GET api/task/{id}
Create a Task
api/CreateTask
POST api/task
Update a Task
api/UpdateTask
PUT api/task/{id}
Delete a Task
api/DeleteTask
DELETE api/task /{id}
 
Step 1 - Create SQL Server and Database in Azure
 
Login to https://portal.azure.com and click on Create a resource and then choose SQL Database. Before creating a new database in Azure, you should have an SQL Server for creating an SQL database in Azure. 
 
Develop A REST API With Azure Functions Using SQL
 
 
 
Develop A REST API With Azure Functions Using SQL
 
Step 2 - Set Server Firewall Rule
 
After creating a new database in Azure, now we need to create a table inside our database. For that, we need to add our current machine IP Address to Firewall Settings in our Azure database. To do that, go to your newly created database and click on the set server firewall option on the Overview screen of yours database. Click on the Add client IP button it will automatically capture your machine IP, add it to a new row, and then hit the Save button.
 
Develop A REST API With Azure Functions Using SQL
 
 
 
Develop A REST API With Azure Functions Using SQL
 
Step 3 - Create a New Table
 
After adding your IP Address to firewall rules, now we need a table in our database for data storing to do that click on Query Editor on your database screen. Enter your server credentials to access your database. After logging in, click on the new query tab, paste the following query into it, and hit run.
 
Develop A REST API With Azure Functions Using SQL
  1. GO  
  2.   
  3. CREATE TABLE [dbo].[TaskList](  
  4.     [ID] [int] IDENTITY(1,1) NOT NULL,  
  5.     [Description] [nvarchar](max) NULL,  
  6.     [IsDone] [bit] NULL,  
  7.     [CreatedOn] [datetime] NULL,  
  8.  CONSTRAINT [PK_TaskList] PRIMARY KEY CLUSTERED   
  9. (  
  10.     [ID] ASC  
  11. )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]  
  12. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
  13.   
  14. GO   
Step 4 - Create a new Azure Function
 
Open Visual Studio-> Create a New Project-> Search Azure Function and give the project name RESTApiWithAzureFunction, then select HTTP Trigger and hit create. 
(ProjectName: RESTApiWithAzureFunction)
 
Develop A REST API With Azure Functions Using SQL
 
Develop A REST API With Azure Functions Using SQL
 
Develop A REST API With Azure Functions Using SQL
 
Step 5 - Add references to NuGet Packages
 
First of all, in References, add a reference to Microsoft.Azure.WebJobs.Extensions.Storage using NuGet Package Manager, as shown below.
 
Develop A REST API With Azure Functions Using SQL
 
Step 6 - Create Models
 
Add a new class to your project with the name Models. Add the following properties to get the result and set the data with an appropriate namespace.
  1. public class TaskModel  
  2. {  
  3.     public int Id { get; set; }  
  4.     public DateTime CreatedOn { get; set; }  
  5.     public string Description { get; set; }  
  6.     public bool IsDone { get; set; }  
  7. }  
  8. public class CreateTaskModel  
  9. {  
  10.     public DateTime CreatedOn { get; set; } = DateTime.UtcNow;  
  11.     public string Description { get; set; }  
  12. }  
  13. public class UpdateTaskModel  
  14. {  
  15.     public string Description { get; set; }  
  16.     public bool IsDone { get; set; }  
  17. }  
Step 7 - Add CRUD Operations
 
Now, go to Solution Explorer -> Project Name -> add a new class with the name TaskListFunction and add the following code with appropriate namespaces. 
 
  1. using System;  
  2. using System.IO;  
  3. using System.Threading.Tasks;  
  4. using Microsoft.AspNetCore.Mvc;  
  5. using Microsoft.Azure.WebJobs;  
  6. using Microsoft.Azure.WebJobs.Extensions.Http;  
  7. using Microsoft.AspNetCore.Http;  
  8. using Microsoft.Extensions.Logging;  
  9. using Newtonsoft.Json;  
  10. using System.Collections.Generic;  
  11. using System.Data;  
  12. using System.Data.SqlClient;  
  13.   
  14. namespace RESTApiWithAzureFunction  
  15. {  
  16.     public static class TaskListFunction  
  17.     {  
  18.         [FunctionName("CreateTask")]  
  19.         public static async Task<IActionResult> CreateTask(  
  20.             [HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = "task")] HttpRequest req, ILogger log)  
  21.         {  
  22.             string requestBody = await new StreamReader(req.Body).ReadToEndAsync();  
  23.             var input = JsonConvert.DeserializeObject<CreateTaskModel>(requestBody);  
  24.             try  
  25.             {  
  26.                 using (SqlConnection connection = new SqlConnection(Environment.GetEnvironmentVariable("SqlConnectionString")))  
  27.                 {  
  28.                     connection.Open();  
  29.                     if(String.IsNullOrEmpty(input.Description))  
  30.                     {  
  31.                         var query = $"INSERT INTO [TaskList] (Description,CreatedOn,IsDone) VALUES('{input.Description}', '{input.CreatedOn}' , '{false}')";  
  32.                         SqlCommand command = new SqlCommand(query, connection);  
  33.                         command.ExecuteNonQuery();  
  34.                     }  
  35.                 }  
  36.             }  
  37.             catch (Exception e)  
  38.             {  
  39.                 log.LogError(e.ToString());  
  40.                 return new BadRequestResult();  
  41.             }  
  42.             return new OkResult();  
  43.         }  
  44.   
  45.         [FunctionName("GetTasks")]  
  46.         public static async Task<IActionResult> GetTasks(  
  47.             [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "task")] HttpRequest req, ILogger log)  
  48.         {  
  49.             List<TaskModel> taskList = new List<TaskModel>();  
  50.             try  
  51.             {  
  52.                 using (SqlConnection connection = new SqlConnection(Environment.GetEnvironmentVariable("SqlConnectionString")))  
  53.                 {  
  54.                     connection.Open();  
  55.                     var query = @"Select * from TaskList";  
  56.                     SqlCommand command = new SqlCommand(query, connection);  
  57.                     var reader = await command.ExecuteReaderAsync();  
  58.                     while (reader.Read())  
  59.                     {  
  60.                         TaskModel task = new TaskModel()  
  61.                         {  
  62.                             Id = (int)reader["Id"],  
  63.                             Description = reader["Description"].ToString(),  
  64.                             CreatedOn = (DateTime)reader["CreatedOn"],  
  65.                             IsDone = (bool)reader["IsDone"]  
  66.                         };  
  67.                         taskList.Add(task);  
  68.                     }  
  69.                 }  
  70.             }  
  71.             catch (Exception e)  
  72.             {  
  73.                 log.LogError(e.ToString());  
  74.             }  
  75.             if(taskList.Count > 0)  
  76.             {  
  77.                 return new OkObjectResult(taskList);  
  78.             }  
  79.             else  
  80.             {  
  81.                 return new NotFoundResult();  
  82.             }  
  83.         }  
  84.   
  85.         [FunctionName("GetTaskById")]  
  86.         public static IActionResult GetTaskById(  
  87.         [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "task/{id}")] HttpRequest req, ILogger log, int id)  
  88.         {  
  89.             DataTable dt = new DataTable();  
  90.             try  
  91.             {  
  92.                 using (SqlConnection connection = new SqlConnection(Environment.GetEnvironmentVariable("SqlConnectionString")))  
  93.                 {  
  94.                     connection.Open();  
  95.                     var query = @"Select * from TaskList Where Id = @Id";  
  96.                     SqlCommand command = new SqlCommand(query, connection);  
  97.                     command.Parameters.AddWithValue("@Id", id);  
  98.                     SqlDataAdapter da = new SqlDataAdapter(command);  
  99.                     da.Fill(dt);  
  100.                 }  
  101.             }  
  102.             catch (Exception e)  
  103.             {  
  104.                 log.LogError(e.ToString());  
  105.             }  
  106.             if (dt.Rows.Count == 0)  
  107.             {  
  108.                 return new NotFoundResult();  
  109.             }  
  110.             return new OkObjectResult(dt);  
  111.         }  
  112.   
  113.         [FunctionName("DeleteTask")]  
  114.         public static IActionResult DeleteTask(  
  115.         [HttpTrigger(AuthorizationLevel.Anonymous, "delete", Route = "task/{id}")] HttpRequest req, ILogger log, int id)  
  116.         {  
  117.             try  
  118.             {  
  119.                 using (SqlConnection connection = new SqlConnection(Environment.GetEnvironmentVariable("SqlConnectionString")))  
  120.                 {  
  121.                     connection.Open();  
  122.                     var query = @"Delete from TaskList Where Id = @Id";  
  123.                     SqlCommand command = new SqlCommand(query, connection);  
  124.                     command.Parameters.AddWithValue("@Id", id);  
  125.                     command.ExecuteNonQuery();  
  126.                 }  
  127.             }  
  128.             catch (Exception e)  
  129.             {  
  130.                 log.LogError(e.ToString());  
  131.                 return new BadRequestResult();  
  132.             }  
  133.             return new OkResult();  
  134.         }  
  135.   
  136.         [FunctionName("UpdateTask")]  
  137.         public static async Task<IActionResult> UpdateTask(  
  138.         [HttpTrigger(AuthorizationLevel.Anonymous, "put", Route = "task/{id}")] HttpRequest req, ILogger log, int id)  
  139.         {  
  140.             string requestBody = await new StreamReader(req.Body).ReadToEndAsync();  
  141.             var input = JsonConvert.DeserializeObject<UpdateTaskModel>(requestBody);  
  142.             try  
  143.             {  
  144.                 using (SqlConnection connection = new SqlConnection(Environment.GetEnvironmentVariable("SqlConnectionString")))  
  145.                 {  
  146.                     connection.Open();  
  147.                     var query = @"Update TaskList Set Description = @Description , IsDone = @IsDone Where Id = @Id";  
  148.                     SqlCommand command = new SqlCommand(query, connection);  
  149.                     command.Parameters.AddWithValue("@Description", input.Description);  
  150.                     command.Parameters.AddWithValue("@IsDone", input.IsDone);  
  151.                     command.Parameters.AddWithValue("@Id", id);  
  152.                     command.ExecuteNonQuery();  
  153.                 }  
  154.             }  
  155.             catch (Exception e)  
  156.             {  
  157.                 log.LogError(e.ToString());  
  158.             }  
  159.             return new OkResult();  
  160.         }  
  161.     }  
  162. }  
 
 
Step 8 -  Add Connection String in local.settings.json file
 
To access the Azure database we need a connection string to connect with our database. Grab your connection string from your database and add it to the local settings file for our local testing.
 
(FileName: local.settings.json)
  1. {  
  2.   "IsEncrypted": false,  
  3.   "Values": {  
  4.     "AzureWebJobsStorage": "UseDevelopmentStorage=true",  
  5.     "FUNCTIONS_WORKER_RUNTIME": "dotnet",  
  6.     "SqlConnectionString": "Server=tcp:development.database.windows.net,1433;Initial Catalog=learning;Persist Security Info=False;User ID={Your ID};
  7.       Password {Your Password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"  
  8.   }  
  9. }  
Step 9 - Testing API’s with Postman
 
Run your Azure function app and open your postman and test on your local.
 
Develop A REST API With Azure Functions Using SQL
 
What Next? 
 
In the next part of this article, I will demonstrate how to run Azure Functions inside the container, and also I will explain all steps of how to dockerize your dot net stack applications. Stay Tuned! Develop A REST API With Azure Functions Using SQL


Similar Articles