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.

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.

Comments (2)

  1. Environment.GetEnvironmentVariable() still locally not working also i passed connection string as a value.

    connection.ConnectionString = Environment.GetEnvironmentVariable(“Data Source=xxxxxxxxx.windows.net;Initial Catalog=MicroServiceDemo;User Id=xxxxxxx;Password=xxxxxx;”);

    can you look over it

    1. Hi Mohit, the parameter to pass that method is the name of the environment variable and not the connection string itself. HTH

Skip to main content