In this Azure tutorial, we will discuss Where To Instantiate Database Connection In Azure Functions. Along with this, we will also discuss a few other topics like Set the connection string in the existing Azure Function project and we will also discuss Azure Function Connect To On-Premise Database and Azure Functions Entity Framework.
Where To Instantiate Database Connection In Azure Functions? Define the Database connection string under the Application Settings in the Azure Portal using the below steps
- Log in to the Azure Portal
- Navigate to the Azure Function App
- Click on the Configuration option from the left navigation on the Azure Function App page
- Click on the + New Application Settings button or if you are using the Entity Framework, click on the + New Connection String to add the database connection string.
Table of Contents
Where To Instantiate Database Connection In Azure Functions
Well, here we will discuss an important topic i.e Where To Instantiate Database Connection In Azure Functions, while you are working with the Azure Function and interacting with the databases from the Azure Function.
There two scenarios here Either you will develop the Azure Function locally with the help of any IDE like Visual Studio and you are trying to interact with the database. So in that case, you need to define the database connection strings in the local.settings.JSON file, and then you can call that key from your Azure Function code.
One important thing here is, it will help you for your development and testing purpose locally, Once you will deploy your Azure Function into Azure, you will not get the reference of the local.settings.json file in the Azure so again manually you have to define the database connection string details under the application settings section in the Azure Portal.
Most importantly, you need to remember one thing that local.settings.JSON file is not for Azure, it is only for your local development and testing purpose.
So ultimately, when you are working with the Azure function and you will have to interact with the database, Even you are developing the Azure Function in Visual Studio or any command-line tools or even you are developing the Azure Function in the Azure Portal, you need to define your database connection strings under the Application Settings in the Azure Portal.
To define the Connection string in the Azure Portal, you need to follow the below steps
Login to the Azure Portal (https://portal.azure.com/)
Now, Once you have successfully logged in to the Azure Portal, navigate to the Azure Function App for which one, you are working with and for the one you want to set the Database connection string.
On the Azure Function App page, you need to click on the Configuration from the left navigation and then click on the + New Connection String
Note: One important thing to note down here is, If You are working with Entity Framework, then click on the + New connection String like below to add the database connection string, If you are using any other than Entity framework then Use the + New Application Setting button to add the database connection string.
On the Add/Edit connection string window, Provide a name for the connection, Value of the Database connection string and then you need to select the Database type and then click on the OK button.
Now, the connection string for your database will be added like below
So once, you will define the Database connection string, then you need to read that connection string value in your Azure Function Code. You can use the below way to call it
var newconn = System.Configuration.ConfigurationManager
.ConnectionStrings["MyDBConnname"].ConnectionString;This is the way when you are working with the Azure Function using the .Net languages. For other than the .Net language, the syntax will be different.
Now, you might be thinking about where you will get the Database connection string value. Follow the below steps to get the Connection string value for the Azure SQL database using the Azure Portal
Login to the Azure Portal (https://portal.azure.com/)
Search for the SQL Database in the Azure Portal and click on the Search result SQL databases
You will see the list of SQL databases available that you have created before. Click on the specific database for which one, you need to copy the Connection string value.
On the SQL Database page, click on the Connection strings link from the left navigation and then under the ADO.NET tab, you can able to see the connection-string and next to that you will find a copy button. Click on the Copy button to copy the connection string as highlighted below.
Set the connection string in the existing Azure Function project
You might have already deployed your Azure Function Project into Azure. Right-click on the Azure Function solution and click on the Publish option
Click on the Edit Azure App Service settings link as highlighted below.
On the below Application Settings window, click on the + Add Setting, Provide the name for the App settings, and then click on the OK button.
Now Provide the connection string value that we have copied following the above-mentioned steps and then click on the Ok button.
Azure Function Connect To On-Premise Database
Yes, You can able to connect to the On-Premise Database from your Azure Function using the Hybrid connection option. This is possible with the App Service Plan.
Note: If you want to use the Hybrid connection option, you will get this option, if your Azure Function App belongs to the App Service Plan.
To get the Hybrid connection option, You need to follow the below steps
Login to the Azure Portal(https://portal.azure.com/)
Create an Azure Function App using the App Service Plan, To create that, You will have to provide the below options
On the Hosting tab, Choose the plan type as App service plan and provide the other options as highlighted and then click on the Review + Create button.
Now On the next window, click on the create button to create the Azure Function App with the App service plan.
Now Navigate to the Azure Function App that you have created just now, click on the Networking option from the left navigation, and then you can able to see the Hybrid connections option as highlighted below.
Click on the Configure hybrid connection end points
Click on the + Add hybrid connection on the below window. On the next window, click on the Create new hybrid connection button.
On the Create new hybrid connection window, Provide a name for Hybrid connection Name, Specify the Endpoint Host, Provide the Endpoint Port and then select the Create new option for the servicebus namespace, the next is choose the location and name and finally click on the Ok button.
Now you can able to see below, the Hybrid connection created successfully
Now the next step is to Connection manager which is the main counter part of the Hybrid connection.
Click on the Download connection manager button which is present just above the + Add hybrid connection button. It will download the setup file in your local system. You can configure the setup file and choose the Hybrid connection that we have created above while setting up the connection manager.
Azure Functions Entity Framework
One of the best ways to work with the Azure Function is to use the Entity framework with your Azure Function Code. So here, I will show you the way to implement the Entity Framework with Azure Function using C# code.
Without making a delay, Let’s start creating the Azure Function Project where we are going to implement the Entity Framework Code. We will use Visual Studio 2019 to develop the Azure Function Project.
Open the Visual Studio 2019, click on the Create a New Project button to create the new Azure Function Project
On the next window, choose the Azure Function Project template and then click on the Next button.
Provide the Project Name and the location where you want to save the Azure Function project on the Configure your new project window and then click on the Create button.
On the Create a new Azure Functions Application window, select the HTTP Trigger, Select the Storage Account(AzureWebJobsStorage) option as the Storage Emulator, and For the Authorization level option, select the Function option.
Now the Azure Function Project has been created sucessfully.
Now since we need to implement the Entity Framework Provider for our Azure Function Project, so we need to add the 4 NuGet packages. To add the Nuget Package, Right-click on the Azure Function Project —> Click on the Manage NuGet Packages option.
Search for the Microsoft.Azure.Functions.Extensions and then click on the Install button to install the Microsoft.Azure.Functions.Extensions package
The next step is, click on the I Accept button to accept the License Agreement. Once you will accept the License Agreement, then Microsoft.Azure.Functions.Extensions NuGet package will install successfully for the Azure Function Project.
in the same way, you can add the below Nuget packages, You can install the latest available versions
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Design
Microsoft.EntityFrameworkCore.ToolsNow the next step is to create the Data Context class that needs to be inherited from the DbContext class
Below is my model class that is ArticleContext.cs. It contains the below code
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using System;
using System.Collections.Generic;
using System.Text;
namespace MyAzurefunctionEF
{
public class ArticleContext : DbContext
{
public ArticleContext(DbContextOptions<ArticleContext> options)
: base(options)
{ }
public DbSet<Article> Articles { get; set; }
public DbSet<PostArticle> PostArticles { get; set; }
}
public class Article
{
public int ArticleId { get; set; }
public ICollection<PostArticle> PostArticles { get; set; }
}
public class PostArticle
{
public int PostArticleId { get; set; }
public string Title { get; set; }
public string Body { get; set; }
public int ArticleId { get; set; }
public Article Article { get; set; }
}
public class BloggingContextFactory : IDesignTimeDbContextFactory<ArticleContext>
{
public ArticleContext CreateDbContext(string[] args)
{
var oB = new DbContextOptionsBuilder<ArticleContext>();
oB.UseSqlServer(Environment.GetEnvironmentVariable("mysqlconn"));
return new ArticleContext(oB.Options);
}
}
}
Your Function1.cs file code will be like below
using System;
using System.IO;
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 Newtonsoft.Json;
using System.Linq;
using System.Threading;
namespace MyAzurefunctionEF
{
public class Function1
{
private readonly ArticleContext _context;
public Function1(ArticleContext context)
{
_context = context;
}
[FunctionName("GetArticleDetails")]
public IActionResult GetArticleDetails(
[HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
ILogger log)
{
log.LogInformation("GetArticleDetails function processed a request.");
var articleArray = _context.PostArticles.OrderBy(p => p.Title).ToArray();
return new OkObjectResult(articleArray);
}
[FunctionName("SubmitArticlePost")]
public async Task<IActionResult> SubmitArticlePost(
[HttpTrigger(AuthorizationLevel.Function, "post", Route = "article/{articleId}/postarticle")] HttpRequest req,
int articleId,
CancellationToken ctoken,
ILogger log)
{
log.LogInformation("SubmitArticlePost trigger function processed a request.");
string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
PostArticle pa = JsonConvert.DeserializeObject<PostArticle>(requestBody);
PostArticle p = new PostArticle
{
ArticleId = articleId,
Body = pa.Body,
Title = pa.Title
};
var ent = await _context.PostArticles.AddAsync(p, ctoken);
await _context.SaveChangesAsync(ctoken);
return new OkObjectResult(JsonConvert.SerializeObject(ent.Entity));
}
[FunctionName("SubmitArticle")]
public async Task<IActionResult> SubmitArticle(
[HttpTrigger(AuthorizationLevel.Function, "post", Route = "article")] HttpRequest req,
CancellationToken c,
ILogger log)
{
log.LogInformation("SubmitArticle trigger function processed a request.");
var entity = await _context.Articles.AddAsync(new Article(), c);
await _context.SaveChangesAsync(c);
return new OkObjectResult(JsonConvert.SerializeObject(entity.Entity));
}
}
}
Your Local.settings.JSON file code should look like below
{
"IsEncrypted": false,
"Values": {
"AzureWebJobsStorage": "UseDevelopmentStorage=true",
"FUNCTIONS_WORKER_RUNTIME": "dotnet",
"mysqlconn": "Server=tcp:sql1246.database.windows.net,1433;Initial Catalog=MySQLDB;Persist Security Info=False;User ID=YourUserID;Password=yourpassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
}
}Your Projectname.csproj should looks like below
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<TargetFramework>netcoreapp3.1</TargetFramework>
<AzureFunctionsVersion>v3</AzureFunctionsVersion>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Microsoft.Azure.Functions.Extensions" Version="1.0.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="3.1.8">
<PrivateAssets>all</PrivateAssets>
<IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.8" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.1.8">
<PrivateAssets>all</PrivateAssets>
<IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>
<PackageReference Include="Microsoft.Extensions.DependencyInjection.Abstractions" Version="3.1.8" />
<PackageReference Include="Microsoft.NET.Sdk.Functions" Version="1.0.36" />
</ItemGroup>
<ItemGroup>
<None Update="host.json">
<CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
</None>
<None Update="local.settings.json">
<CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
<CopyToPublishDirectory>Never</CopyToPublishDirectory>
</None>
</ItemGroup>
</Project>
Azure Function Configuration
When we are working with the Azure Function and we will have to deploy the Azure Function App to the Azure Portal. Many times we need to store different settings like connection name, container name, ClientID, etc in our Azure Function App to connect to the Azure resources.
Overall, we will have to do the below implementation.
- Need to write the code to use the ConfigurationManager.AppSetting value in the Azure Function App
- Then we need to add the all the Azure Function settings value in the Azure Portal manually since you will not get any reference of the Local.settings.JSON file value, once you deploy your Azure Function in the Azure Portal.
You can check out How to configure the Azure Function Appseetings now.
You may also like following the below Articles
- How to add bulk guest users in Azure AD B2B from Azure Portal and PowerShell
- How To Secure Azure Function With Azure AD
Wrapping Up
Well, in this article, we discussed, Where To Instantiate Database Connection In Azure Functions, Set the connection string in the existing Azure Function project and we also discussed Azure Function Connect To On-Premise Database and Azure Functions Entity Framework. Hope You have enjoyed this article !!!
I am Rajkishore, and I have over 14 years of experience in Microsoft Azure and AWS, with good experience in Azure Functions, Storage, Virtual Machine, 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.