Develop A REST API With Azure Functions Using SQL

Ahsan Siddique

5y90.6k14100Article

REST API Azure Functions

14 Comments

Join the conversation! Your thoughts help the community grow.

Jose Almeida5y ago
Hello. When trying connectio.open() it gives the following error: Executed 'ClientID2' (Failed, Id=22ad8465-45f5-4fff-ba90-f0ff7d0ee465, Duration=5895ms)[2021-11-25T10:49:57.575Z] System.Private.CoreLib: Exception while executing function: ClientID2. Microsoft.Data.SqlClient: The type initializer for 'Microsoft.Data.SqlClient.TdsParser' threw an exception. Microsoft.Data.SqlClient: Could not load file or assembly 'System.Text.Encoding.CodePages, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.
- Jose Almeida5y ago
  I already have those packages in references
SRINIVASARAO SANAKA5y ago
When I am try to deploy function app from Eclipse with mssql jdbc 9.4.0.jre8 I got the following error “java.io.IOException: Unable to delete file: E:\Work\SingPost\Code\Trials\Function Apps\sp-checksqlaccess\target\azure-functions\sp-checksqlaccess-20210917225958196.zip from windows machine. But it's working fine with mac Why ?. Give me the solution for windows machine. Thanks in advance.
SRINIVASARAO SANAKA5y ago
When I am trying to deploy function app from eclipse with mssql jdbc 9.4.0.jre8 jar I got the following error “java.io.IOException: Unable to delete file: E:\Work\SingPost\Code\Trials\Function Apps\sp-checksqlaccess\target\azure-functions\sp-checksqlaccess-20210917225958196.zip from windows machine. But it's working fine with mac please provide solution for that. Thanks
Prachi5y ago
Thanks for the detailed description,I am able to successfully run all the CRUD operations from my Visual Studio but the code is not working when published from Studio to Azure Portal. Any thoughts on this, as its a blocker for me presently
- Ahsan Siddique5y ago
  Yes, the local.settings.json file is ignored while deploying to the azure function. So, add the connection string of your database server in the azure function environment variable.
Malini Saranya5y ago
Good one!! I am getting below mentioned error while connecting to SQL ""A network - related or instance specific error occurred while establishing a connection to SQL Server The server was not found or was not accessible . verify that the instance name is correct and that SQL Server is configured to allow remote connections ". Any thoughts on this???
- Ahsan Siddique5y ago
  Which SQL Server is using you? Local SQL or Azure SQL? If local then make sure your server is running.
- Ahsan Siddique5y ago
  Oh, If you are using Azure SQL Server then please add your current machine IP Address in firewall rule of Azure SQL Server.
- Malini Saranya5y ago
  Unfortunately it is not.
Sesugh Hulugh5y ago
Thanks for this Ahsan, can you use EF Core instead of ADO.NET ?
- Ahsan Siddique5y ago
  Yes, we can use EF Core but for the sake of simplicity and avoidance of dependency, I used ADO.NET.
Hamid Khan5y ago
Very nit article...Thanks for sharing
- Ahsan Siddique5y ago
  Thanks for reading.

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

Microsoft.Azure.WebJobs.Extensions.Storage
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

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! Develop A REST API With Azure Functions Using SQL

Containerized Azure Functions Apps