How to connect to a database from an Azure Function

By Benjamin Perkins · · , ,

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