Build & Deploy Azure Function Using C# and Integration with Azure SQL

What is Azure Function?

Azure Functions is a managed platform-as-a-service (PaaS) provider that provides event-driven and scheduled compute resources for Azure cloud services.

Azure Functions is a serverless computing service provided by Microsoft Azure to help us run tasks on a schedule like processing data, working with the Internet of Things (IoT), and integrating systems. We can also use them to develop server-less applications, simple APIs, and microservices. It's designed to execute code in response to a variety of triggers, such as HTTP requests, messages in a queue, timers, or events from other Azure services. You only pay for the compute time consumed when your function runs.

There are many more integration and automation services that can be used to automate the process and define inputs, conditions, actions, and output.

Today we are going to build an Azure function that perform CRUD operation on Azure SQL using Azure Function in the form of API code.

For a start, we will be creating the Azure SQL and Database resource on Azure Portal.

Click on the “Create” button to create a SQL Database instance. It provides you different options to create an SQL Server, Single instance of Database, Single Database, or a SQL Server using VM as per your requirements.

In here we are we selecting a Single SQL Database as a resource. Defining Server Name and Authentication method to access resources.

Create a database and configure its environment and backup storage options as per your requirements.

Review all selected options and configurations for Azure SQL resource and create. Select the Add current client IP address option to access that resource from the same machine.

At Visual Studio, create a new project and select Azure function to perform CRUD operation in API format [i.e. HTTP Trigger Function].

Execute the above code by connecting to the local database. In the below snapshot, we can see all functions enlisted and on the left side, we have connected to the database successfully.

At start, we are executing CreateTable Functions to create a database by the name “EmployeeTable” by inserting random data in it as shown in the below snap.

Next implement, the Update Function to alter one of the records from the above data set.

Now we have tested the function on local with local database. Next, we need to publish our code on Azure Function resource by linking it to Azure SQL database to replicate the above same result on the remote database.

Select Target “Azure”à Select Specific Target as “Azure Function[Window]” for the above scenario.

Create a new instance of Azure Function to Azure portal’s subscription.

Configure the Azure Function as per requirement and click Create.

Click Finish to complete the publish profile.

We need to point the Azure Function to Azure SQL by making the required changes in the service setting as shown in the above snap. Instead of a local connection string, we need to point to an Azure SQL connection string.

Go to the Azure SQL resource and select the connection string in an overview or from the side menu under settings.

Select the connection string for SQL authentication to connect Azure Function remotely.

Copy-paste the connection string in the application setting and replace the {your password} placeholder with an actual password which we had set while configuring the SQL Database. Click ok and publish the Azure function to Azure cloud.

On Azure Portal, in Azure Function we can see our functions are deployed successfully and ready to invoke/use.

Before that, we will be connecting Azure SQL Database to Visual Studio to check the DB connection and we can use the Server name and credentials to log in to Azure SQL.

As we can see DB has successfully connected and we are about to execute the “CreateTable” Function to create a table in Azure SQL Database.

Click on a specific function and go for Test/Run which opens a side modal to select a respective method to execute that trigger function. By default, to that function, the HTTP method will appear in the dropdown.

There will be a scenario where it might call out for CORS policy to enable. We need to set the CORS to enable for open to all domains or to a respective site. Here, we are adding portal.azure.com to test on the Azure portal.

After executing the function, we successfully created a table on Azure SQL DB.

Now we will update that table with some employee objects and validate the data set in Azure SQL DB.

Same we can do for the “GET” method function where we can use query parameters to retrieve the result set.

As we have tested our function on the Azure portal and now we need to implement this functions in out world. We need a function URL to access this Functions across internet.

We will be implementing the URL in the same manner as we do in local testing using Postman and we can see it updates the Azure SQL Database.

Types of Azure Functions

Azure Functions are categorized primarily by their trigger, which is the event that starts the function execution. Some common types include.

• HTTP Trigger: Executes in response to an HTTP request.
• Timer Trigger: Executes based on a predefined schedule (cron expression).
• Blob Trigger: Executes when a new or updated blob is detected in Azure Storage.
• Queue Trigger: Executes when a message is added to an Azure Storage queue or Service Bus queue.
• Event Hub Trigger: Executes in response to events delivered to an Azure Event Hub.
• Service Bus Trigger: Executes in response to messages from a Service Bus queue or topic.
• Cosmos DB Trigger: Executes when documents are created or updated in an Azure Cosmos DB collection.
• Event Grid Trigger: Executes in response to events published to an Azure Event Grid topic.
• IoTHub Trigger: Executes in response to events from Azure IoT Hub.
• Durable Functions: An extension that allows you to write stateful workflows in a serverless environment by orchestrating the execution of multiple functions.
• Custom Handlers: Allows you to run functions written in any language by implementing a simple HTTP-based interface.

Benefits of Using Azure Functions

• Serverless Architecture: No need to manage or provision servers, allowing developers to focus solely on writing code.
• Pay-per-use Pricing: You only pay for the compute time your code consumes, which can be very cost-effective for event-driven workloads.
• Automatic Scaling: Azure Functions automatically scale based on demand, handling fluctuations in traffic without requiring manual intervention.
• Choice of Languages: Supports various programming languages, including C#, F#, JavaScript, Python, Java, and PowerShell, providing flexibility for developers.
• Integrated Ecosystem: Seamlessly integrates with other Azure services and third-party applications through triggers and bindings, simplifying complex workflows.
• Rapid Development: The serverless nature and rich tooling enable faster development and deployment cycles.
• Simplified Integration: Triggers and bindings provide a declarative way to connect to other services, reducing the amount of boilerplate code needed.
• Flexibility in Hosting: Offers different hosting plans to optimize for cost, performance, and features based on specific application needs.