How To Call A Stored Procedure From Azure Functions

How To make a Call to 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, 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.

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.

  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

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

Once, you will click on the SQL databases, You can able to 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 you can try again logging in with your Credentials and this time you should able to log in.

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

Let’s create a table name as Customers first, On the Query editor copy and paste the below SQL query 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.

How To Call A Stored Procedure From Azure Functions

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 able to see, it created the Stored procedure successfully.

Call A Stored Procedure From Azure Functions

Now our Stored Procedure is ready. The next step is to create the Azure Function from where we are going to 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

Create an Azure Timer Trigger Function using Visual Studio 2019

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 requirement. You can find more details on CORN expression now.

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

How to Create an Azure Timer Trigger Function using Visual Studio 2019

Now The Azure Function project created successfully.

Call A Stored Procedure From Azure Timer Trigger Functions

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

azure function connect to Azure sql database

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

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

Now you need the connection string for the Azure SQL Database to interact with the Azure SQL DB, which you need to copy it 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;
azure function query database

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

Now Copy and paste the below code 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

azure serverless functions sql database

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 any thing on the local.settings.json file. The default file code should looks like 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

azure function SQL query database

Now the next step is to Deploy the Azure Function Timer Trigger to the Azure Portal form 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 the Visual Studio 2019.

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

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

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.

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

Click on the Publish button from the below window.

How To Call A Stored Procedure From Azure Function

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.

How To Call Stored Procedure From Azure Functions

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

Call Stored Procedure From Azure Functions

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

Call Stored Procedure From Azure Function

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

Steps to Call Stored Procedure From Azure Function

After you run the Azure Function, navigate to the Azure SQL Database again and 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 able to see the record got inserted that we have inserted from the Stored Procedure. Meaning, After we ran the Azure Function Successfully the Stored Procedure 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 query azure sql

Azure Functions SQL Query

Same way, if you want to execute any SQL Query from your Azure Function Code, You can also do that 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 and then we will deploy the Azure Function to the Azure Portal and then we will run the Azure Function and we will retrieve the records from the Customers table and we will check if the Item 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.

Azure Functions SQL Query

Now Deploy the Azure Function in the same way 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 and 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 able to see below we got the Expected output. Our Insert SQL query was as below

"INSERT INTO Customers(customerID, LastName, FirstName) VALUES (15, 'SaiRam', 'SaiRam')";
Azure Function Executing Stored Procedure every few seconds 1

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

You may also like following the below Articles

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

Leave a Comment