How to connect to a database from an Azure Function

I created a simple Azure Function and now I want to connect it to a database.  Check out these other articles as well.

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();

         public static async Task<HttpResponseMessage>
                 "get", "post", Route = null)]HttpRequestMessage req, TraceWriter log)
             log.Info("C# HTTP trigger function processed a request.");

                 connection.ConnectionString =
                 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.