How To Call A Stored Procedure From Azure Functions

In this Azure tutorial, we will discuss how to call a stored procedure from Azure Functions. Along with this, we will also discuss a few other topics like Create A Stored Procedure In The Azure SQL Database Using The Azure Portal, Creating an Azure Timer Trigger Function using Visual Studio 2019, and Deploy the Azure Timer Trigger Function to Azure From Visual Studio 2019 and Azure Functions SQL Query.

How To Call A Stored Procedure From Azure Functions? Call a stored procedure from the Azure Function using the below steps

• Log in to the Azure Portal
• Navigate to your Azure SQL Database and click on the Query Editor (Preview) option from the left navigation and enter your Credentials and run a SQL query to create a Stored Procedure.
• Create an Azure Function App in Visual Studio 2019 and use the SQL Database connection string from Azure Portal, SQLConnection, and SQLCommand object to call the Stored Procedure.

How To Call A Stored Procedure From Azure Functions

Well, Let’s start discussing a very important topic How to call a stored procedure from Azure Functions. As part of the development activities, Below are the steps that we are going to do exactly.

• How Much Memory Available For Azure Functions
• How To Monitor Azure Functions

1. Create a stored procedure in the Azure SQL database using the Azure Portal.
2. Create an Azure Timer Trigger Function using Visual Studio 2019.
3. Deploy the Azure Timer Trigger Function to Azure From Visual Studio 2019.
4. Run and Test the Azure Function

Create A Stored Procedure In The Azure SQL Database Using The Azure Portal

The first thing we will do is, we will create a stored procedure in your Azure SQL Database using the Azure Portal. Follow the below steps to create a Stored Procedure in the Azure SQL Database using the Azure Portal.

Login to the Azure Portal (https://portal.azure.com/)

Search for the SQL Database and click on the search result

Once you click on the SQL databases, You can see the lists of Azure SQL databases, then click on the one where you want to create the Stored Procedure. Now, on the SQL Database page, click on the Query editor (preview) link. Enter your Login username and password, and then click on the OK button.

After you click on the OK button, there is a chance that you will get the error Cannot Open Server Requested By The Login Azure SQL. You can fix it by following the same URL, and then try logging in again with your Credentials. This time, you should be able to log in.

Let’s create a table named Customers first. In the Query editor, copy and paste the SQL query below and click on the Run button to create the Azure SQL table.

CREATE TABLE Customers (
    customerID int,
    LastName varchar(255),
    FirstName varchar(255)
);

You can see it here, the table Customers got Created Successfully.

The next step is to create the Stored Procedure with the insert query with a few hardcoded values. You can copy and paste the below SQL query in the query editor to create a stored procedure in the Azure SQL database

CREATE PROCEDURE CustomersNew
AS
INSERT INTO Customers(customerID, LastName, FirstName) VALUES (15, 'Bijay', 'Sahoo')
GO;

You can see that it created the Stored procedure successfully.

Now our Stored Procedure is ready. The next step is to create the Azure Function from where we will call this stored procedure.

We will create An Azure Timer Trigger Function using Visual Studio 2019.

Create an Azure Timer Trigger Function using Visual Studio 2019

To create an Azure Timer Trigger Function, Follow the below steps

Open the Visual Studio 2019 and click on the Create a new Project button to create an Azure Function Project.

Select Azure Function as the Project template and then click on the next button.

On the Configure your new project window, Provide the Project name, Choose a location to save your Project, and then click on the Create button

On the Create a new Azure Functions Application, select the Timer Trigger and choose the Storage Emulator as the Storage Account (AzureWebjobsStorage) option. Choose the Schedule as 0*/5**** as the CORN expression. You can keep a CORN expression based on your requirements.

Finally, click on the Create button to create the Azure function project.

Now, The Azure Function project has been created successfully.

Now, the next step is to add the System.Data.SqlClient Nuget package, Right-click on the Project and click on the Manage Nuget Packages

Search for the System.Data.SqlClient nuget package and click on the install button.

Now, you need the connection string for the Azure SQL Database to interact with the Azure SQL DB, which you need to copy from the Azure Portal. Follow the below steps to copy the connection string

Go to the Azure Portal

Search for the SQL Database and click on the search result.

Navigate to the Azure SQL Database that you are using here; click on the connection strings from the left navigation. From the ADO.NET tab, click on the copy button to copy the connection strings for your Azure SQL Database.

The connection string should look like below

Server=tcp:sql1246.database.windows.net,1433;Initial Catalog=MySQLDB;Persist Security Info=False;User ID=Rajkishore;Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Copy the connection string and keep it as we need to use this in our Azure Function code. You can refer to the code below for the Azure Function. There, we have used this connection string with the SQL Connection object.

Now Copy and paste the code below for the class file where your Azure Function is there

using System;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Host;
using Microsoft.Extensions.Logging;
using System.Data.SqlClient;
using System.Data;

namespace AzureFunctionSQL
{
    public static class Function1
    {
        [FunctionName("Function1")]
        public static void Run([TimerTrigger("0 */5 * * * *")]TimerInfo myTimer, ILogger log)
        {
            log.LogInformation($"C# Timer trigger function executed at: {DateTime.Now}");
            using (var con1 = new SqlConnection("Server=tcp:sql1246.database.windows.net,1433;Initial Catalog=MySQLDB;Persist Security Info=False;User ID=Rajkishore;Password=#######9;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"))

            {
                try
                {
                    SqlCommand command1 = new SqlCommand();
                    command1.CommandText = "CustomersNew";
                    command1.CommandType = CommandType.StoredProcedure;
                    command1.Connection = con1;
                    con1.Open();
                    command1.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    log.LogInformation(ex.Message);

                }

            }

        }
    }
}

You can see it as below

No need to change anything on the the host.JSON file. Below is the code for the default file

{
    "version": "2.0",
    "logging": {
        "applicationInsights": {
            "samplingSettings": {
                "isEnabled": true,
                "excludedTypes": "Request"
            }
        }
    }
}

No need to change anything on the local.settings.json file. The default file code should look the same as below

{
    "IsEncrypted": false,
    "Values": {
        "AzureWebJobsStorage": "UseDevelopmentStorage=true",
        "FUNCTIONS_WORKER_RUNTIME": "dotnet"
    }
}

Your Projectname.csproj file should look like below

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>netcoreapp2.1</TargetFramework>
    <AzureFunctionsVersion>v2</AzureFunctionsVersion>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.NET.Sdk.Functions" Version="1.0.36" />
    <PackageReference Include="System.Data.SqlClient" Version="4.8.2" />
  </ItemGroup>
  <ItemGroup>
    <None Update="host.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
    <None Update="local.settings.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
      <CopyToPublishDirectory>Never</CopyToPublishDirectory>
    </None>
  </ItemGroup>
</Project>

You can see below

Now, the next step is to Deploy the Azure Function Timer Trigger to the Azure Portal from the Visual Studio 2019.

Deploy the Azure Timer Trigger Function to Azure From Visual Studio 2019

Follow the below steps to deploy the Azure Timer Trigger Function to Azure From Visual Studio 2019.

Right-click on the Project name and click on the Publish button.

On the App Service window, Provide the below details

• Name: Provide the name for the Azure Function App.
• Subscription: Choose your correct Azure Subscription.
• Resource Group: Choose your existing Resource Group or if you don’t have click on the New link to create the new Resource Group.
• Location: Choose the location for the Azure Function App.
• Azure Storage: Select your existing Storage account, or if you don’t have any existing storage account, click on the New button to create a new storage account.

Finally, click on the Create button to create the Azure Function App.

Click on the Publish button from the below window.

Now, Navigate to the Azure Function App in the Azure Portal by clicking on the App services and click on the Azure Function App that you have created just now from the list of the App services.

On the Azure Function App page, click on the Functions option from the left navigation, You can able to see the Function that you have deployed from the Visual Studio 2019.

Click on the Function name to navigate to the Azure Function. From the left navigation on the Azure Function page, click on the Code + Test link, and then click on the Test/Run button to test the Azure function to see if it is working properly.

Now, the next step is to click on the Run button on the Input window

You can see the Output below with the response code 202 Accepted.

After you run the Azure Function, navigate to the Azure SQL Database again, click on the Query editor (preview) link, and retrieve the records from the Customers table using the below SQL query

Select * from Customers

You can see the record that we have inserted from the Stored Procedure. Meaning that after we ran the Azure Function Successfully, the Stored Procedure was called from the Azure Function Successfully so that it inserted the Record as below that we kept the insert statement in the Stored Procedure.

Azure Functions SQL Query

In the same way, if you want to execute any SQL Query from your Azure Function Code, You can also do that by following the below Steps.

Above, we have already created a table named Customers. Now We will execute one SQL insert query from the Azure Function Code, then we will deploy the Azure Function to the Azure Portal, and then we will run the Azure Function we will retrieve the records from the Customers table, and we will check if the Item was inserted successfully or not.

Once You created the Azure Function project successfully, add the System.Data.SqlClient NuGet package to the Azure Function project in the same way as above.

Same way, copy the Azure SQL Database connection string from the Azure Portal and copy it and we will use that in our Azure Function code to interact with the database

Follow the above steps to create an Azure timer trigger Azure Function and copy and paste the below code in your Azure Function class file.

using System;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Host;
using Microsoft.Extensions.Logging;
using System.Data.SqlClient;
using System.Data;

namespace AzureFunctionSQL
{
    public static class Function1
    {
        [FunctionName("Function1")]
        public static void Run([TimerTrigger("0 */5 * * * *")]TimerInfo myTimer, ILogger log)
        {
            log.LogInformation($"C# Timer trigger function executed at: {DateTime.Now}");
            using (var con1 = new SqlConnection("Server=tcp:sql1246.database.windows.net,1433;Initial Catalog=MySQLDB;Persist Security Info=False;User ID=Rajkishore;Password=######19;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"))

            {
                try
                {
                    SqlCommand command1 = new SqlCommand();
                    command1.CommandText = "INSERT INTO Customers(customerID, LastName, FirstName) VALUES (15, 'SaiRam', 'SaiRam')";
                    command1.Connection = con1;
                    con1.Open();
                    command1.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    log.LogInformation(ex.Message);

                }

            }

        }
    }
}

You can see the SQL Query as highlighted below.

Now, Deploy the Azure Function similarly, following the above steps. Once You Deployed the Azure Function App to Azure Successfully. Navigate to the Azure function and click on the Test/Run button and then on the Input window, click on the Run button.

Now navigate to the Azure SQL Database click on the Query editor (preview) link and retrieve the records from the Customers table using the SQL Query as below.

Select * from Customers

You can see below we got the Expected output. Our Insert SQL query was as below

"INSERT INTO Customers(customerID, LastName, FirstName) VALUES (15, 'SaiRam', 'SaiRam')";

This is how you can interact with the Azure SQL Database from the Azure Function.

You may also like following the below Articles

• How To Figure Out IP Address For Azure Functions
• What Are Azure Durable Functions
• Azure How Many Functions In One Function App

Wrapping Up

Well, in this Article, We discussed, How To Call A Stored Procedure From Azure Functions, Create A Stored Procedure In The Azure SQL Database Using The Azure Portal, Create an Azure Timer Trigger Function using Visual Studio 2019 and we also discussed Deploy the Azure Timer Trigger Function to Azure From Visual Studio 2019 and Azure Functions SQL Query. Hope You have enjoyed this Article !!!

Rajkishore

I am Rajkishore, and I am a Microsoft Certified IT Consultant. I have over 14 years of experience in Microsoft Azure and AWS, with good experience in Azure Functions, Storage, Virtual Machines, Logic Apps, PowerShell Commands, CLI Commands, Machine Learning, AI, Azure Cognitive Services, DevOps, etc. Not only that, I do have good real-time experience in designing and developing cloud-native data integrations on Azure or AWS, etc. I hope you will learn from these practical Azure tutorials. Read more.