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. 

 

 

 

 

 

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.

 

 

 

 

 

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.

 

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 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)

 

 

 

 

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.

 

 

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.

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 Solution Explorer -> Project Name -> add a new class with the name TaskListFunction and add the following code with appropriate namespaces. 

 

using System;
using System.IO;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace RESTApiWithAzureFunction
{
    public static class TaskListFunction
    {
        [FunctionName("CreateTask")]
        public static async Task<IActionResult> CreateTask(
            [HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = "task")] HttpRequest req, ILogger log)
        {
            string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
            var input = JsonConvert.DeserializeObject<CreateTaskModel>(requestBody);
            try
            {
                using (SqlConnection connection = new SqlConnection(Environment.GetEnvironmentVariable("SqlConnectionString")))
                {
                    connection.Open();
                    if(String.IsNullOrEmpty(input.Description))
                    {
                        var query = $"INSERT INTO [TaskList] (Description,CreatedOn,IsDone) VALUES('{input.Description}', '{input.CreatedOn}' , '{false}')";
                        SqlCommand command = new SqlCommand(query, connection);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                log.LogError(e.ToString());
                return new BadRequestResult();
            }
            return new OkResult();
        }

        [FunctionName("GetTasks")]
        public static async Task<IActionResult> GetTasks(
            [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "task")] HttpRequest req, ILogger log)
        {
            List<TaskModel> taskList = new List<TaskModel>();
            try
            {
                using (SqlConnection connection = new SqlConnection(Environment.GetEnvironmentVariable("SqlConnectionString")))
                {
                    connection.Open();
                    var query = @"Select * from TaskList";
                    SqlCommand command = new SqlCommand(query, connection);
                    var reader = await command.ExecuteReaderAsync();
                    while (reader.Read())
                    {
                        TaskModel task = new TaskModel()
                        {
                            Id = (int)reader["Id"],
                            Description = reader["Description"].ToString(),
                            CreatedOn = (DateTime)reader["CreatedOn"],
                            IsDone = (bool)reader["IsDone"]
                        };
                        taskList.Add(task);
                    }
                }
            }
            catch (Exception e)
            {
                log.LogError(e.ToString());
            }
            if(taskList.Count > 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();
            try
            {
                using (SqlConnection connection = new SqlConnection(Environment.GetEnvironmentVariable("SqlConnectionString")))
                {
                    connection.Open();
                    var query = @"Select * from TaskList Where Id = @Id";
                    SqlCommand command = new SqlCommand(query, connection);
                    command.Parameters.AddWithValue("@Id", id);
                    SqlDataAdapter da = new SqlDataAdapter(command);
                    da.Fill(dt);
                }
            }
            catch (Exception e)
            {
                log.LogError(e.ToString());
            }
            if (dt.Rows.Count == 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)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(Environment.GetEnvironmentVariable("SqlConnectionString")))
                {
                    connection.Open();
                    var query = @"Delete from TaskList Where Id = @Id";
                    SqlCommand command = new SqlCommand(query, 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 Task<IActionResult> UpdateTask(
        [HttpTrigger(AuthorizationLevel.Anonymous, "put", Route = "task/{id}")] HttpRequest req, ILogger log, int id)
        {
            string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
            var input = JsonConvert.DeserializeObject<UpdateTaskModel>(requestBody);
            try
            {
                using (SqlConnection connection = new SqlConnection(Environment.GetEnvironmentVariable("SqlConnectionString")))
                {
                    connection.Open();
                    var query = @"Update TaskList Set Description = @Description , IsDone = @IsDone Where Id = @Id";
                    SqlCommand command = new SqlCommand(query, connection);
                    command.Parameters.AddWithValue("@Description", input.Description);
                    command.Parameters.AddWithValue("@IsDone", input.IsDone);
                    command.Parameters.AddWithValue("@Id", id);
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                log.LogError(e.ToString());
            }
            return new OkResult();
        }
    }
}

 

 

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)

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "UseDevelopmentStorage=true",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet",
    "SqlConnectionString": "Server=tcp:development.database.windows.net,1433;Initial Catalog=learning;Persist Security Info=False;User 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 function app and open your postman and test on your local.

 

 

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! 

• Containerized Azure Functions Apps