Question 103058 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – Minimal Web Api Not Returning Desired Result

I have created a minimal ASP.NET Core Web API project in Visual Studio. And then deploying this to Azure Web App Service to be able to ‘GET’ results remotely. Locally, the /weatherforecast sample data runs fine as well as the Azure SQL data, however, after publishing it, only the weatherforecast is accessible but /Members isn’t.

Program.cs

using Microsoft.Data.SqlClient;
using Microsoft.AspNetCore.OpenApi;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

var app = builder.Build();

// Configure the HTTP request pipeline.

app.UseHttpsRedirection();

var summaries = new[]
{
    "Freezing", "Bracing", "Chilly", "Cool", "Mild", "Warm", "Balmy", "Hot", "Sweltering", "Scorching"
};

app.MapGet("/weatherforecast", () =>
{
    var forecast = Enumerable.Range(1, 5).Select(index =>
        new WeatherForecast
        (
            DateOnly.FromDateTime(DateTime.Now.AddDays(index)),
            Random.Shared.Next(-20, 55),
            summaries[Random.Shared.Next(summaries.Length)]
        ))
        .ToArray();
    return forecast;
});
string connectionString = Environment.GetEnvironmentVariable("AZURE_SQL_CONNECTIONSTRING");

app.MapGet("/Members", () => {
        var rows = new List<string>();

        using var conn = new SqlConnection(connectionString);
        conn.Open();

        var command = new SqlCommand("SELECT * FROM WHATEVER", conn);
        using SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)
        { 
            while (reader.Read())
            {
                rows.Add($"{reader.GetInt32(0)}, {reader.GetString(1)}, {reader.GetString(2)}, {reader.GetString(3)}, {reader.GetString(4)}, {reader.GetInt32(5)}, {reader.GetInt32(6)}, {reader.GetDecimal(7)}, {reader.GetInt32(8)}, {reader.GetDateTime(9)}");
            }
        }

        return rows;
    })
    .WithName("GetMembers")
    .WithOpenApi();


app.Run();

internal record WeatherForecast(DateOnly Date, int TemperatureC, string? Summary)
{
    public int TemperatureF => 32 + (int)(TemperatureC / 0.5556);
}

Service Dependencies

General Notes:

  1. My account credentials in Visual Studio are the same as in Azure Portal running Azure SQL.
  2. I have been following this link for the project.
  3. My connection string is "Server=tcp:xxx;Database=xxx;Authentication=Active Directory Default;" which is declared under environmentVariable in launchSettings.json. Also delcared in appsettings.json.
  4. I get a HTTP ERROR 500 when /Members endpoint called.

Your help will be much appreciated, thank you.

Tags:

2

Answers


  1. 0

    which is declared under environmentVariable in launchSettings.json. Also delcared in appsettings.json.

    Docs about launchSettings.json:

    The launchSettings.json file:

    • Is only used on the local development machine.
    • Is not deployed.
    • Contains profile settings.

    Environment.GetEnvironmentVariable("AZURE_SQL_CONNECTIONSTRING");

    appsettings.json do not affect environment variables, they are used to provide configuration values – check out Configuration in ASP.NET Core docs.

    I would recommend not to get values from the environment variables directly but use them as one of configuration sources following the more idiomatic approach. To access configuration you can use Configuration property exposed both by builder and app, for example:

    var value = builder.Configuration.GetValue<string>("AZURE_SQL_CONNECTIONSTRING");
var value = app.Configuration.GetValue<string>("AZURE_SQL_CONNECTIONSTRING");

    Quite often connection strings are placed into ConnectionStrings section, for example in appsettings in can look like:

    {
  // ...
  "ConnectionStrings": {
    "AZURE_SQL_CONNECTIONSTRING" : "..."
  }
}

    Then it can be accessed via GetConnectionString method:

    string connectionString = Configuration.GetConnectionString("AZURE_SQL_CONNECTIONSTRING");

    Also check the Connection string prefixes section of the docs.

    But in general I highly recommend to go over the aforementioned configuration doc and determine the best approach for your use case (there are several configuration sources options specifically for Azure).

    Login or Signup to reply.
  2. 0

    What I assume is happening, that it can’t get the environmental variable declared in launchSettings.json simply because this file is not being published because it is meant to use in dev environment. Since it can’t get the value of this env variable, it falls back to string.Empty which you’re trying to use later to connect. 500 error indicates that something is terribly wrong, in this case, you’re trying to connect to DB using "" (empty string) as a connections string.

    If you already have it declared in your appsettings.json then you should be able to grab connection string like this, instead of the environmental variable

    string connectionString = builder.Configuration.GetConnectionString("AZURE_SQL_CONNECTIONSTRING");

    Assuming that your connection string is defined as follows

    "ConnectionStrings": {
    "AZURE_SQL_CONNECTIONSTRING" : "..."
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search