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.
Table of Contents
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.
- Create a stored procedure in the Azure SQL database using the Azure Portal.
- Create an Azure Timer Trigger Function using Visual Studio 2019.
- Deploy the Azure Timer Trigger Function to Azure From Visual Studio 2019.
- 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 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.
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.
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.
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
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.
Now The Azure Function project created successfully.
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
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 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;
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
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
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.
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. 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.
Now the next step is click on the Run button on the Input window
You can see the Output like below with response code as 202 Accepted.
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 CustomersYou 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 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.
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 CustomersYou 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')";
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 !!!