How to connect to a database from an Azure Function
I migrated this article to its new home here: https://benperk.github.io/msdn/2018/2018-04-how-to-connect-to-a-database-from-an-azure-function.html
I created a simple Azure Function and now I want to connect it to a database. Check out these other articles as well.
- How to create an Azure Function in Visual Studio
- Deploy an Azure Function created from Visual Studio
- Check out all my Azure Function articles here
I found this article also useful “Code and test Azure Functions locally”
I used ADO.NET to make this simple connection to a database. The following is the code I used.
I included these namespaces:
using System;
using System.Data.SqlClient;
Keep in mind that this is only an example and there may be a few places to optimize the code pattern. I am fine if you point them out, just for fun.
public static class Function1
{
private static SqlConnection connection = new SqlConnection();
[FunctionName("Function1")]
public static async Task<HttpResponseMessage>
Run([HttpTrigger
(AuthorizationLevel.Anonymous,
"get", "post", Route = null)]HttpRequestMessage req, TraceWriter log)
{
log.Info("C# HTTP trigger function processed a request.");
try
{
connection.ConnectionString =
Environment.GetEnvironmentVariable("DatabaseConnectionString");
await connection.OpenAsync();
return req.CreateResponse(HttpStatusCode.OK,
$"The database connection is: {connection.State}");
}
catch (SqlException sqlex)
{
return req.CreateResponse(HttpStatusCode.BadRequest,
$"The following SqlException happened: {sqlex.Message}");
}
catch (Exception ex)
{
return req.CreateResponse(HttpStatusCode.BadRequest,
$"The following Exception happened: {ex.Message}");
}
}
}
While I was testing locally I wasn’t able to get the Environment.GetEnvironmentVariable() method to get the ConnectionStrings values contained in my local.settings.json file. So I placed my connection string into the Values section and it worked fine.
When I executed the function locally using CURL I received the following successful result as seen in Figure 1.
Figure 1, Azure Function App connecting to a database example
After I deployed my Function, see here “Deploy an Azure Function created from Visual Studio” the code to read the information from the local.settings.json did not work any more. I received this, Figure 2, when I attempted to execute the Azure Function.
"The following Exception happened: The ConnectionString property has not been initialized."
Figure 2, Azure Function App connecting to a database example
To make this work, I simply added an Application Setting from my Azure Function App named “DatabaseConnectionString” with a value of the ADO.NET SQL connection string, Figure 3.
Figure 3, Azure Function App connecting to a database example, cannot read from local.settings.json file
While I was messing around with the Application Settings I got these errors, Figure 4.
"The following Exception happened: Keyword not supported: '\"server'."
"The following Exception happened: Format of the initialization string does
not conform to specification starting at index 0"
Figure 4, Azure Function App connecting to a database example, cannot read from local.settings.json file
The first one I resolved by Restarting the Azure App. I didn’t get a chance to troubleshoot it more. Or it was because I had the Application settings value wrapped in double quotes…?
The second one was because I fat fingered my connection string, once I got the correct value in there, all worked fine.
Comments
- Anonymous
September 28, 2018
The comment has been removed- Anonymous
October 10, 2018
Hi Mohit, the parameter to pass that method is the name of the environment variable and not the connection string itself. HTH
- Anonymous