Azure Function Apps with .NET Core & SQL server

Azure function apps are the perfect example of the KISS principle (Keep it stupid simple). In very simplest terms Azure Function is a piece of code you run based on some trigger.

• Azure Functions are triggered by an event such as HTTP request, file added, deleted in blob storage, database (record added, deleted or modified in a table), event hub, service bus or azure queue etc.
• Azure functions are quick to build, easy to deploy and maintain and very powerful to run.
• These functions are scalable and lightweight.
• Function Apps are essentials in building serverless microservices on Azure.

Create Azure Functions

You can write & manage Azure Function apps entirely on Azure portal but it is not recommended. The proper way to write Azure functions is through Code editors like Visual studio. Let’s create Function App from Azure portal and after that update it from Visual studio.

First of all, you log in to Azure portal, Search for Function Apps and click on create.

In the next screen, you need to specify the resource group that you want to use. It could be an existing resource group or a new resource group.

After that, you need to name your function. If the given name is already in use then you can see validation message appears on the fly.

The next option is where you are you going to be publishing to your function? Are you wrapping it in a Docker container or you are deploying it as a piece of code. For now, lets select “code”.

Azure Functions supports Docker containers and publishing code. For this blog let’s choose Code because it also gives us the option to choose runtime stack, which basically tells the runtime what is the code that you are going to be writing on? you can select

• PowerShell
• Java
• Python
• Node.js
• .NET Core

In this example, I’m going to select the .NET Core. The next is; selecting your region. I have selected the UK South.

Now at this point, you can “Review & Create” and what this does is it defaults you to consumption pricing tier, which is also a serverless tier. So in this tier, you do not need to configure scaling; your app will scale up or scale down automatically depending on the number of requests or time that it gets triggered. You can change it by clicking the Hosting tab.

Finally, we can go to “review and create” and this page going to give us a summary of all of the things that will be created.

Normally, you create a storage plan, monitoring resources (application insights) resource group etc once and use it for all Function Apps

Azure functions App can be created and deployed from Visual studio.

Let’s start a project with template “Azure Function” and default function trigger “HTTP Request”. The visual studio gives you a default function template

public static class Function1
    {
        [FunctionName("Function1")]
        public static async Task<IActionResult> Run(
            [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
            ILogger log)
        {
            log.LogInformation("C# HTTP trigger function processed a request.");
            string name = req.Query["name"];
            string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
            dynamic data = JsonConvert.DeserializeObject(requestBody);
            name = name ?? data?.name;
            string responseMessage = string.IsNullOrEmpty(name)
                ? "This HTTP triggered function executed successfully. Pass a name in the query string or in the request body for a personalized response."
                : $"Hello, {name}. This HTTP triggered function executed successfully.";
            return new OkObjectResult(responseMessage);
        }
    }

Integrate Azure Function with SQL Server

The easiest thing is; to install SqlClient Nuget package

Install-Package System.Data.SqlClient

The next step is to add your connection string.

In the following example function, I create a connection with the database and get records based on a parameter from the database.

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 System;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace AzureFunction
{
    public static class GetCustomerInfoFunction
    {
        [FunctionName("GetCustomerInfo")]
        public static async Task<IActionResult> Run(
            [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
            ILogger log)
        {
            log.LogInformation("C# HTTP trigger function processed a request.");
            string id = req.Query["id"];
            var results = new List<string>();
            var str = Environment.GetEnvironmentVariable("DefaultConnection");
            using (SqlConnection conn = new SqlConnection(str))
            {
                conn.Open();
                var text = "SELECT id, Name, Email " +
                           "FROM [dbo].[Customers] " +
                           $"Where id={id}";
                using (SqlCommand cmd = new SqlCommand(text, conn))
                {
                    // Execute the command and log the # rows affected.
                    var reader = await cmd.ExecuteReaderAsync();
                    while (reader.Read())
                    {
                        results.Add(reader.GetString(1));
                    }
                }
            }
            return new OkObjectResult(results);
        }
    }
}

Test Azure Function App locally

If you run azure function in visual studio it will gives you a local url like below

Now you can use this URL to test Function through tools like the postman. see below

Deploy Azure Function App.

Ideal deployment of Function app should be through CI/CD pipelines. However, the easiest way to deploy function app is by using the Publish profile in Visual studio.

Specify environment variables. You can configure connection string as environment variable as well.

Dos and Don’ts for Azure Functions

FunctionApp

When you write Azure Functions you are writing an App. The main unit of development and unit of scale is an App and an App can have many functions. When you scale, you will scale the whole app.

Triggers

All of your functions should have a trigger. A function can only have one trigger. The trigger is the function’s listening source and data would be passed-in through trigger to process.

You are not get charged when function is listening to a trigger.

Bindings

Bindings are the data you Pull-in or push-out from your function without having to directly integrate with service inside your code. a few examples of binding are

• HTTP
• Storage Queues
• Cosmos DB
• Event Hubs
• Blob Storage
• Service Bus Queues/Topics
• Event Grid
• IoT Hub
• and more ….

Monitoring Solutions

Always hook your Azure function app to monitoring solutions like Application insight. These tools give you a lot of insight into how your function is performing and how you can optimize your functions.

Please remember in micro services architecture you are paying for executing time and resources. So you need to make sure all your resources are optimize & efficient.

Instances & Resources

Azure function apps scale up and down automatically, it depends on number of requests.

Each instance can run multiple requests and on scale up multiple of instance run at same time. In code your should leverage this by sharing code resources on execution.

Avoid instantiate a lot of single-use variables. Always use Singleton coding pattern. The prime example is SQL client connection.

If you wish to restrict amount of load a instance can have, you can define it in hosts.json file.