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


  [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.

image

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."

image

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.

image

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"

image

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.

image

Figure 5, Azure Function App connecting to a database example