Azure Functions v2 Connectionstring from application settings

Question

I have made a simple Azure Functions v2 Web Service that connects to an Azure SQL database, runs a stored procedure with 3 parameters and returns the result as JSON output. It works as it is now (with the connectionstring in the run.csx file).

But how do I get it to get the connectionstring from Applications Settings?

I have tried various guides both here and other places. But all I can find is a long list of references and a whole bunch of code I need to add. I have followed the guides to the letter (also set the values in App Settings), but it just wont work. I'm rather new to C#, so it might be I just don't understand what I'm supposed to do.

Anyways this is my code and the suggested fix, as far as I'm able to tell:

#r "Newtonsoft.Json"
#r "System.Data"
using System.Net;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Primitives;
using Newtonsoft.Json;
using System.Data;
using System.Data.SqlClient;

public static async Task<ActionResult> Run(HttpRequest req, ILogger log)
{
   log.LogInformation("C# HTTP trigger function processed a request.");

   string login = req.Query["login"];
   string pwd = req.Query["password"];
   string TransID = req.Query["TransID"];

   string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
   dynamic data = JsonConvert.DeserializeObject(requestBody);
   login = login ?? data?.login;
   pwd = pwd ?? data?.password;
   TransID = TransID ?? data?.TransID;

   var cnnString = "Server=MyServer;Database=WebServices;User Id=MyUser;Password=MyPassword;Encrypt=True;";

        try
        {
            DataTable table = new DataTable();
            SqlConnection connection = new SqlConnection(cnnString);
            SqlCommand cmd = new SqlCommand("sp_GetRegRW", connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@Login", login));
            cmd.Parameters.Add(new SqlParameter("@Password", pwd));
            cmd.Parameters.Add(new SqlParameter("@TransID", TransID));
            await connection.OpenAsync();
            using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
            {
                adapter.Fill(table);
            }

            return (ActionResult)new OkObjectResult(JsonConvert.SerializeObject(table));
        }
        catch (SqlException sqlex)
        {
            return (ActionResult)new OkObjectResult($"The following SqlException happened: {sqlex.Message}");
        }
        catch (Exception ex)
        {
            return (ActionResult)new OkObjectResult($"The following Exception happened: {ex.Message}");
        }

}

suggested solution:

#r "Newtonsoft.Json"
#r "System.Data"
#r "Microsoft.Extensions.Configuration"
using System.Net;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Primitives;
using Newtonsoft.Json;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Extensions.Configuration;

public static async Task<ActionResult> Run(HttpRequest req, ILogger log, ExecutionContext context)
{
   var config = new ConfigurationBuilder()
    .SetBasePath(context.FunctionAppDirectory)
    .AddJsonFile("local.settings.json", optional: true, reloadOnChange: true)
    .AddEnvironmentVariables()
    .Build();

   var cnnString =config.GetConnectionString("connWS");
   var setting1 = config["Setting1"];

   log.LogInformation(cnnString);
   log.LogInformation(setting1);
   log.LogInformation("C# HTTP trigger function processed a request.");

   string login = req.Query["login"];
   string pwd = req.Query["password"];
   string TransID = req.Query["TransID"];

   string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
   dynamic data = JsonConvert.DeserializeObject(requestBody);
   login = login ?? data?.login;
   pwd = pwd ?? data?.password;
   TransID = TransID ?? data?.TransID;

        try
        {
            DataTable table = new DataTable();
            SqlConnection connection = new SqlConnection(cnnString);
            SqlCommand cmd = new SqlCommand("sp_GetRegRW", connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@Login", login));
            cmd.Parameters.Add(new SqlParameter("@Password", pwd));
            cmd.Parameters.Add(new SqlParameter("@TransID", TransID));
            await connection.OpenAsync();
            using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
            {
                adapter.Fill(table);
            }

            return (ActionResult)new OkObjectResult(JsonConvert.SerializeObject(table));
        }
        catch (SqlException sqlex)
        {
            return (ActionResult)new OkObjectResult($"The following SqlException happened: {sqlex.Message}");
        }
        catch (Exception ex)
        {
            return (ActionResult)new OkObjectResult($"The following Exception happened: {ex.Message}");
        }

}

Answer 1

In function v2, you should use Environment.GetEnvironmentVariable("string_name",EnvironmentVariableTarget.Process) to get values from Application settings and connection strings.

Note: When use the above method, the first parameter depends on the Type. It means that when the type of the connection string is SQLAZURE, then the first parameter should be SQLAZURE + CONNSTR + _stringName.

The screenshot is as below:

The code sample:

//for connection string
string connStr = Environment.GetEnvironmentVariable("SQLAZURECONNSTR_sqldb_connection",EnvironmentVariableTarget.Process);
log.LogInformation("the connection string is: " + connStr);

And the result snapshot:

I get the following error: The ConnectionString property has not been initialized.

You should probably create an instance of SqlConnection, with your connection string and open this connection before try to make any command.

SqlConnection con = new SqlConnection("connStr");
await con.OpenAsync();