Azure

Develop A REST API With Azure Features Utilizing SQL

Harsh Bakshi January 16, 2021
0 242 4 minutes read

Introduction

 

On this article, I’ll exhibit how we will develop a REST API With Azure Features utilizing SQL. So, we’ll use the HTTP triggered sort operate for these APIs and Visual Studio 2019. I can’t use SQL binding whereas creating REST API as a result of within the newer model of Azure Perform SDK (like 3.x) there isn’t any longer help for SQL binding.

 

Conditions

 

You’re required to have primary data of Azure Features, newbie degree data of ADO.NET, and a primary concept of Azure SQL database.

 

Required NuGet Packages

  1. Microsoft.Azure.WebJobs.Extensions.Storage
  2. Microsoft.NET.Sdk.Features

Steps

  • Create SQL Server and Database in Azure
  • Set Server Firewall Rule
  • Create a New Desk
  • Create a brand new Azure Perform in Visual Studio 2019
  • Add References to NuGet Packages
  • Create Fashions  
  • Add CRUD Operations
  • Add Connection String in native.settings.json file
  • Testing API’s with Postman 

Operations

Default Route

REST Route

Duties Listing

api/GetTasks

GET api/job

Process by Id

api/GetTaskById

GET api/job/{id}

Create a Process

api/CreateTask

POST api/job

Replace a Process

api/UpdateTask

PUT api/job/{id}

Delete a Process

api/DeleteTask

DELETE api/job /{id}

 

Step 1 – Create SQL Server and Database in Azure

 

Login to https://portal.azure.com and click on on Create a useful resource after which select SQL Database. Earlier than creating a brand new database in Azure, it is best to have an SQL Server for creating an SQL database in Azure. 

 

 

 

 

Develop A REST API With Azure Functions Using SQL

 

Step 2 – Set Server Firewall Rule

 

After creating a brand new database in Azure, now we have to create a desk inside our database. For that, we have to add our present machine IP Tackle to Firewall Settings in our Azure database. To try this, go to your newly created database and click on on the set server firewall possibility on the Overview display of yours database. Click on on the Add consumer IP button it should mechanically seize your machine IP, add it to a brand new row, after which 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 Desk

 

After including your IP Tackle to firewall guidelines, now we’d like a desk in our database for information storing to try this click on on Question Editor in your database display. Enter your server credentials to entry your database. After logging in, click on on the brand new question tab, paste the next question 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 brand new Azure Perform

 

Open Visual Studio-> Create a New Undertaking-> Search Azure Perform and provides the challenge title RESTApiWithAzureFunction, then choose HTTP Set off 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

 

To start with, in References, add a reference to Microsoft.Azure.WebJobs.Extensions.Storage utilizing NuGet Bundle Supervisor, as proven beneath.

 

Develop A REST API With Azure Functions Using SQL

 

Step 6 – Create Fashions

 

Add a brand new class to your challenge with the title Fashions. Add the next properties to get the consequence and set the info with an applicable 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 Resolution Explorer -> Undertaking Identify -> add a brand new class with the title TaskListFunction and add the next code with applicable namespaces. 

 

  1. utilizing System;  
  2. utilizing System.IO;  
  3. utilizing System.Threading.Duties;  
  4. utilizing Microsoft.AspNetCore.Mvc;  
  5. utilizing Microsoft.Azure.WebJobs;  
  6. utilizing Microsoft.Azure.WebJobs.Extensions.Http;  
  7. utilizing Microsoft.AspNetCore.Http;  
  8. utilizing Microsoft.Extensions.Logging;  
  9. utilizing Newtonsoft.Json;  
  10. utilizing System.Collections.Generic;  
  11. utilizing System.Information;  
  12. utilizing System.Information.SqlClient;  
  13.   
  14. namespace RESTApiWithAzureFunction  
  15. {  
  16.     public static class TaskListFunction  
  17.     {  
  18.         [FunctionName(“CreateTask”)]  
  19.         public static async Process<IActionResult> CreateTask(  
  20.             [HttpTrigger(AuthorizationLevel.Anonymous, “post”, Route = “task”)] HttpRequest req, ILogger log)  
  21.         {  
  22.             string requestBody = await new StreamReader(req.Physique).ReadToEndAsync();  
  23.             var enter = JsonConvert.DeserializeObject<CreateTaskModel>(requestBody);  
  24.             attempt  
  25.             {  
  26.                 utilizing (SqlConnection connection = new SqlConnection(Setting.GetEnvironmentVariable(“SqlConnectionString”)))  
  27.                 {  
  28.                     connection.Open();  
  29.                     if(String.IsNullOrEmpty(enter.Description))  
  30.                     {  
  31.                         var question = $“INSERT INTO [TaskList] (Description,CreatedOn,IsDone) VALUES(‘{enter.Description}’, ‘{enter.CreatedOn}’ , ‘{false}’)”;  
  32.                         SqlCommand command = new SqlCommand(question, 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 Process<IActionResult> GetTasks(  
  47.             [HttpTrigger(AuthorizationLevel.Anonymous, “get”, Route = “task”)] HttpRequest req, ILogger log)  
  48.         {  
  49.             Listing<TaskModel> taskList = new Listing<TaskModel>();  
  50.             attempt  
  51.             {  
  52.                 utilizing (SqlConnection connection = new SqlConnection(Setting.GetEnvironmentVariable(“SqlConnectionString”)))  
  53.                 {  
  54.                     connection.Open();  
  55.                     var question = @“Choose * from TaskList”;  
  56.                     SqlCommand command = new SqlCommand(question, connection);  
  57.                     var reader = await command.ExecuteReaderAsync();  
  58.                     whereas (reader.Learn())  
  59.                     {  
  60.                         TaskModel job = 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(job);  
  68.                     }  
  69.                 }  
  70.             }  
  71.             catch (Exception e)  
  72.             {  
  73.                 log.LogError(e.ToString());  
  74.             }  
  75.             if(taskList.Depend > 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.             attempt  
  91.             {  
  92.                 utilizing (SqlConnection connection = new SqlConnection(Setting.GetEnvironmentVariable(“SqlConnectionString”)))  
  93.                 {  
  94.                     connection.Open();  
  95.                     var question = @“Choose * from TaskList The place Id = @Id”;  
  96.                     SqlCommand command = new SqlCommand(question, 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.Depend == 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.             attempt  
  118.             {  
  119.                 utilizing (SqlConnection connection = new SqlConnection(Setting.GetEnvironmentVariable(“SqlConnectionString”)))  
  120.                 {  
  121.                     connection.Open();  
  122.                     var question = @“Delete from TaskList The place Id = @Id”;  
  123.                     SqlCommand command = new SqlCommand(question, 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 Process<IActionResult> UpdateTask(  
  138.         [HttpTrigger(AuthorizationLevel.Anonymous, “put”, Route = “task/{id}”)] HttpRequest req, ILogger log, int id)  
  139.         {  
  140.             string requestBody = await new StreamReader(req.Physique).ReadToEndAsync();  
  141.             var enter = JsonConvert.DeserializeObject<UpdateTaskModel>(requestBody);  
  142.             attempt  
  143.             {  
  144.                 utilizing (SqlConnection connection = new SqlConnection(Setting.GetEnvironmentVariable(“SqlConnectionString”)))  
  145.                 {  
  146.                     connection.Open();  
  147.                     var question = @“Replace TaskList Set Description = @Description , IsDone = @IsDone The place Id = @Id”;  
  148.                     SqlCommand command = new SqlCommand(question, connection);  
  149.                     command.Parameters.AddWithValue(“@Description”, enter.Description);  
  150.                     command.Parameters.AddWithValue(“@IsDone”, enter.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 native.settings.json file

 

To entry the Azure database we’d like a connection string to attach with our database. Seize your connection string out of your database and add it to the native settings file for our native testing.

 

(FileName: native.settings.json)

  1. {  
  2.   “IsEncrypted”: false,  
  3.   “Values”: {  
  4.     “AzureWebJobsStorage”: “UseDevelopmentStorage=true”,  
  5.     “FUNCTIONS_WORKER_RUNTIME”: “dotnet”,  
  6.     “SqlConnectionString”: “Server=tcp:improvement.database.home windows.internet,1433;Preliminary Catalog=studying;Persist Safety Data=False;Consumer 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 operate app and open your postman and check in your native.

 

Develop A REST API With Azure Functions Using SQL

 

What Subsequent? 

 

Within the subsequent a part of this text, I’ll exhibit the right way to run Azure Features contained in the container, and in addition I’ll clarify all steps of the right way to dockerize your dot internet stack purposes. Keep Tuned! Develop A REST API With Azure Functions Using SQL

Tags
Show More

Related Articles

3 weeks ago
222

Knowledge Transformations At Scale Utilizing Azure Cosmos DB

3 weeks ago
272

Azure Sentinel – Actual Time Menace Detection And Analytics For Small Companies

3 weeks ago
303

How To Configure A Incremental Snapshot For Managed Disks

3 weeks ago
264

Getting Began With Azure SQL Database

Leave a Reply

Your email address will not be published. Required fields are marked *

© Copyright 2023, All Rights Reserved, Privacy Policy, Built with <3 at Skróts
Close