I am getting a "The ConnectionString property has not been initialized." in my ASP.Net Core application

Question

I am really struggling with getting my ConnectionString from the appsettings.json file. I have looked through a lot of samples but most are using Entity Framework and or are using the older StartUp.cs example. My application is not using either one of those two options. Currently my Void Main looks like this.

public static void Main(string[] args)
{
    var builder = WebApplication.CreateBuilder(args);

    var configuration = new ConfigurationBuilder()
        .SetBasePath(Directory.GetCurrentDirectory())
        .AddJsonFile("appsettings.json").Build();

    builder.Services.AddRazorPages();
    builder.Services.AddSingleton<IConfiguration>(configuration);

    var app = builder.Build();

    if (!app.Environment.IsDevelopment())
    {
        app.UseExceptionHandler("/Error");
        app.UseHsts();
    }
          
    app.UseStaticFiles();
    app.UseRouting();
    app.MapRazorPages();
    app.Run();
}

And the is my DataAccess file where I am trying to read the connection string.

public class DataAccess
{
    private static string connectionstring;
    private readonly IConfiguration _configuration;

    public DataAccess(IConfiguration configuration)
    {
        _configuration = configuration;
        connectionstring = _configuration.GetConnectionString("P51InvConnection");
    }

    public static async Task<DataTable> ExecuteDataTableAsync(
        string storedProcedureName)
    {
        return await ExecuteDataTableAsync(storedProcedureName, null!);
    }

    public static async Task<DataTable> ExecuteDataTableAsync(
        string storedProcedure, params SqlParameter[] parameters)
    {
        DataTable dt = default;

        Console.WriteLine(connectionstring);

        try
        {
            await Task.Run(() =>
            {
                Console.WriteLine();
                using SqlConnection conn = new(connectionstring);
                conn.Open();
                using SqlCommand cmd = new(storedProcedure, conn);
                cmd.CommandTimeout = 3;
                cmd.CommandType = CommandType.StoredProcedure;

                if (parameters != null)
                {
                    foreach (SqlParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                }

                using SqlDataAdapter da = new(cmd);
                dt = new DataTable();
                da.Fill(dt);
            });
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
        }

        return dt; //?? throw new Exception("DataTable empty or null");
    }

    public static SqlParameter Parameter(
        string parameterName, object parameterValue)
    {
        SqlParameter param = new()
        {
            ParameterName = parameterName,
            Value = parameterValue
        };

        return param;
    }
}

The error I am getting is

The ConnectionString property has not been initialized

in the ExecuteDataTableAsync method. Also, I have verified that my connection string is named P51InvConnection is the appsettings.json.

Edited to include the contents of the appsettings.json file.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "P51InvConnection": "Data Source=xxx;User ID=xxx;Password=xxx;Connect Timeout=30;Encrypt=False;"
  }
}

One more edit. This is the first call to the database using the DataAccess class but I don't think the issue is here because this does work if I copy and paste the connection string from the appsettings into the SqlConnection initialization.

public static async Task<List<Entry>> EntryList()
{
    List<Entry> entryList = new();

    DataTable dt = await DataAccess.ExecuteDataTableAsync("[dbo].[OpenTransfer_Select]");

    foreach (DataRow row in dt.Rows)
    {
        Entry entry = new()
        {
            Id = Convert.IsDBNull(row[IdColumn])
            ? 0
            : Convert.ToInt32(row[IdColumn]),
            UserId = Convert.IsDBNull(row[UserIdColumn])
            ? string.Empty
            : Convert.ToString(row[UserIdColumn]),
            ItemNumber = Convert.IsDBNull(row[ItemNumberColumn])
            ? string.Empty
            : Convert.ToString(row[ItemNumberColumn]),
            EntryDate = Convert.IsDBNull(row[TransferDateColumn])
            ? Convert.ToDateTime("1/1/1900")
            : Convert.ToDateTime(row[TransferDateColumn])
        };

        entryList.Add(entry);
    }

    return entryList;
}

Answer 1

The problem is static method cannot visit configuration by injection. But if you try make a new configuraion builder it will work.

        public static async Task<DataTable> ExecuteDataTableAsync(
        string storedProcedure, params SqlParameter[] parameters)
        {
            var configuration = new ConfigurationBuilder().AddJsonFile("appsettings.json").Build();
            var connectionstring = configuration.GetConnectionString("P51InvConnection");
            Console.WriteLine(connectionstring);
        }

---

A better solution
create a static ConfigurationHelper.cs

    public static class ConfigurationHelper
    {
        public static IConfiguration config;
        public static void Initialize(IConfiguration Configuration)
        {
            config = Configuration;
        }
    }

In program.cs add

ConfigurationHelper.Initialize(builder.Configuration);

Then you could get any configuration value in the static method.

        public static async Task<DataTable> ExecuteDataTableAsync(
    string storedProcedure, params SqlParameter[] parameters)
        {
            var connectionstring = ConfigurationHelper.config.GetSection("ConnectionStrings").GetSection("P51InvConnection").Value;
            Console.WriteLine(connectionstring);
        }