Connection String not found Azure Data Studio to VS2022 - Mac

Question

I am trying to setup my uni work (which is windows based) onto my mac. I have a docker container running with AzureSQLEdge, which I have connected to my Azure Data Studio and can run queries no problem. However in VS2022 trying to set up the connection to the database in Azure Data Studio.

At the moment I am just trying to get some IDs printed to the page (we are using @razor)

@using WebMatrix.Data

@{ 

    ViewBag.Title = "About Us";

    var db = Database.Open("AmazonOrders");

    var select = "SELECT * FROM Customers";

    var data = db.Query(select);

}

<h2 class="text-center">@ViewBag.Title</h2>
<h3>@ViewBag.Message</h3>

<p>Use this area to provide additional information</p>


@foreach (var row in data)
{
    @row.customerID
}

My connection string in appsettings.json is:

  "AllowedHosts": "*",
  "ConnectionStrings": {
    "AmazonOrders": "Server=tcp:127.0.0.1,1433;Database=AmazonOrders;User=sa;Password=SQLserver123!;"
  }

However I am getting this error message when I load the page:

InvalidOperationException: Connection string "AmazonOrders" was not found.

Setting up the connection string is all the windows users have to do, they have a local version of the database in their project solution. Any help is appreciated, the resources online are more about creating db contexts which I would prefer to avoid if possible, and just connect directly to my database on docker/azure data studio.

edit Here is the full appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "AmazonOrders": "Server=tcp:127.0.0.1,1433;Database=AmazonOrders;User=sa;Password=SQLserver123!;"
  }
}

Answer 1

@using WebMatrix.Data

This is an old data access library intended solely for use with ASP.NET Web Pages, which is a pretty ancient (and virtually obsoleted) web development framework that targets the full .NET Framework. As such, it knows nothing about appsettings.json files which were introduced in .NET Core. The primary configuration mechanism for Web Pages is a web.config file and that's where this library looks for a connection string.

If I were you, I would remove WebMatrix.Data from your project (assuming it is actually a Razor Pages app, not Web Pages) and use Entity Framework Core instead. If it is a Web Pages app and that's the framework you want to work with, remove the appsettings.json file and add a connection string to the web.config file: https://www.connectionstrings.com/store-connection-string-in-webconfig/