Develop A REST API With Azure Features Utilizing SQL
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
- Microsoft.Azure.WebJobs.Extensions.Storage
- 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.
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.
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.
- GO
- CREATE TABLE [dbo].[TaskList](
- [ID] [int] IDENTITY(1,1) NOT NULL,
- [Description] [nvarchar](max) NULL,
- [IsDone] [bit] NULL,
- [CreatedOn] [datetime] NULL,
- CONSTRAINT [PK_TaskList] PRIMARY KEY CLUSTERED
- (
- [ID] ASC
- )WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
- ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
- 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)
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.
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.
- public class TaskModel
- {
- public int Id { get; set; }
- public DateTime CreatedOn { get; set; }
- public string Description { get; set; }
- public bool IsDone { get; set; }
- }
- public class CreateTaskModel
- {
- public DateTime CreatedOn { get; set; } = DateTime.UtcNow;
- public string Description { get; set; }
- }
- public class UpdateTaskModel
- {
- public string Description { get; set; }
- public bool IsDone { get; set; }
- }
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.
- utilizing System;
- utilizing System.IO;
- utilizing System.Threading.Duties;
- utilizing Microsoft.AspNetCore.Mvc;
- utilizing Microsoft.Azure.WebJobs;
- utilizing Microsoft.Azure.WebJobs.Extensions.Http;
- utilizing Microsoft.AspNetCore.Http;
- utilizing Microsoft.Extensions.Logging;
- utilizing Newtonsoft.Json;
- utilizing System.Collections.Generic;
- utilizing System.Information;
- utilizing System.Information.SqlClient;
- namespace RESTApiWithAzureFunction
- {
- public static class TaskListFunction
- {
- [FunctionName(“CreateTask”)]
- public static async Process<IActionResult> CreateTask(
- [HttpTrigger(AuthorizationLevel.Anonymous, “post”, Route = “task”)] HttpRequest req, ILogger log)
- {
- string requestBody = await new StreamReader(req.Physique).ReadToEndAsync();
- var enter = JsonConvert.DeserializeObject<CreateTaskModel>(requestBody);
- attempt
- {
- utilizing (SqlConnection connection = new SqlConnection(Setting.GetEnvironmentVariable(“SqlConnectionString”)))
- {
- connection.Open();
- if(String.IsNullOrEmpty(enter.Description))
- {
- var question = $“INSERT INTO [TaskList] (Description,CreatedOn,IsDone) VALUES(‘{enter.Description}’, ‘{enter.CreatedOn}’ , ‘{false}’)”;
- SqlCommand command = new SqlCommand(question, connection);
- command.ExecuteNonQuery();
- }
- }
- }
- catch (Exception e)
- {
- log.LogError(e.ToString());
- return new BadRequestResult();
- }
- return new OkResult();
- }
- [FunctionName(“GetTasks”)]
- public static async Process<IActionResult> GetTasks(
- [HttpTrigger(AuthorizationLevel.Anonymous, “get”, Route = “task”)] HttpRequest req, ILogger log)
- {
- Listing<TaskModel> taskList = new Listing<TaskModel>();
- attempt
- {
- utilizing (SqlConnection connection = new SqlConnection(Setting.GetEnvironmentVariable(“SqlConnectionString”)))
- {
- connection.Open();
- var question = @“Choose * from TaskList”;
- SqlCommand command = new SqlCommand(question, connection);
- var reader = await command.ExecuteReaderAsync();
- whereas (reader.Learn())
- {
- TaskModel job = new TaskModel()
- {
- Id = (int)reader[“Id”],
- Description = reader[“Description”].ToString(),
- CreatedOn = (DateTime)reader[“CreatedOn”],
- IsDone = (bool)reader[“IsDone”]
- };
- taskList.Add(job);
- }
- }
- }
- catch (Exception e)
- {
- log.LogError(e.ToString());
- }
- if(taskList.Depend > 0)
- {
- return new OkObjectResult(taskList);
- }
- else
- {
- return new NotFoundResult();
- }
- }
- [FunctionName(“GetTaskById”)]
- public static IActionResult GetTaskById(
- [HttpTrigger(AuthorizationLevel.Anonymous, “get”, Route = “task/{id}”)] HttpRequest req, ILogger log, int id)
- {
- DataTable dt = new DataTable();
- attempt
- {
- utilizing (SqlConnection connection = new SqlConnection(Setting.GetEnvironmentVariable(“SqlConnectionString”)))
- {
- connection.Open();
- var question = @“Choose * from TaskList The place Id = @Id”;
- SqlCommand command = new SqlCommand(question, connection);
- command.Parameters.AddWithValue(“@Id”, id);
- SqlDataAdapter da = new SqlDataAdapter(command);
- da.Fill(dt);
- }
- }
- catch (Exception e)
- {
- log.LogError(e.ToString());
- }
- if (dt.Rows.Depend == 0)
- {
- return new NotFoundResult();
- }
- return new OkObjectResult(dt);
- }
- [FunctionName(“DeleteTask”)]
- public static IActionResult DeleteTask(
- [HttpTrigger(AuthorizationLevel.Anonymous, “delete”, Route = “task/{id}”)] HttpRequest req, ILogger log, int id)
- {
- attempt
- {
- utilizing (SqlConnection connection = new SqlConnection(Setting.GetEnvironmentVariable(“SqlConnectionString”)))
- {
- connection.Open();
- var question = @“Delete from TaskList The place Id = @Id”;
- SqlCommand command = new SqlCommand(question, connection);
- command.Parameters.AddWithValue(“@Id”, id);
- command.ExecuteNonQuery();
- }
- }
- catch (Exception e)
- {
- log.LogError(e.ToString());
- return new BadRequestResult();
- }
- return new OkResult();
- }
- [FunctionName(“UpdateTask”)]
- public static async Process<IActionResult> UpdateTask(
- [HttpTrigger(AuthorizationLevel.Anonymous, “put”, Route = “task/{id}”)] HttpRequest req, ILogger log, int id)
- {
- string requestBody = await new StreamReader(req.Physique).ReadToEndAsync();
- var enter = JsonConvert.DeserializeObject<UpdateTaskModel>(requestBody);
- attempt
- {
- utilizing (SqlConnection connection = new SqlConnection(Setting.GetEnvironmentVariable(“SqlConnectionString”)))
- {
- connection.Open();
- var question = @“Replace TaskList Set Description = @Description , IsDone = @IsDone The place Id = @Id”;
- SqlCommand command = new SqlCommand(question, connection);
- command.Parameters.AddWithValue(“@Description”, enter.Description);
- command.Parameters.AddWithValue(“@IsDone”, enter.IsDone);
- command.Parameters.AddWithValue(“@Id”, id);
- command.ExecuteNonQuery();
- }
- }
- catch (Exception e)
- {
- log.LogError(e.ToString());
- }
- return new OkResult();
- }
- }
- }
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)
- {
- “IsEncrypted”: false,
- “Values”: {
- “AzureWebJobsStorage”: “UseDevelopmentStorage=true”,
- “FUNCTIONS_WORKER_RUNTIME”: “dotnet”,
- “SqlConnectionString”: “Server=tcp:improvement.database.home windows.internet,1433;Preliminary Catalog=studying;Persist Safety Data=False;Consumer ID={Your ID};
- Password {Your Password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;”
- }
- }
Step 9 – Testing API’s with Postman
Run your Azure operate app and open your postman and check in your native.
What Subsequent?
