How do I make my connectionString available to my DataAccess Class using the builder (WebApplication.CreateBuilder)

Question

I have my connection string set in the appsettings.json file and I can see it in the Main using Console.WriteLine so I know I am getting it to the program.cs correctly.

var builder = WebApplication.CreateBuilder(args);
string? constring = builder.Configuration.GetConnectionString("P51InvConnection");
Console.WriteLine(constring);

I think the next thing I need to do is use Builder.Services to make it available to the rest of the application. Specifically, my DataAccess class. So far every sample I have found uses EF Core or are examples when StartUp.cs was still in use and I can figure out how to make it work using Program.cs and the absence of EF.

This is the method in my DataAccess class where I am trying to read the connection string.

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

    try
    {
        await Task.Run(() =>
        {
            Console.WriteLine();
            using SqlConnection conn = new("Connection String Here");
            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)
    {
        LogError(ex);
    }

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

What do I need to do to make that connection available to my method?

Answer 1

inject IConfiguration into your target class,follow this document

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

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

    //use connectionstring in your target method
    
    
}

Answer 2

Use below method in your static class to get your service

private static IConfiguration GetConfiguration()
{
    var _accessor = new HttpContextAccessor();
    return _accessor.HttpContext!.RequestServices.GetRequiredService<IConfiguration>();
}

Then you can get configuration

Answer 3

Make sure that you have the Microsoft.Extensions.Configuration and Microsoft.Extensions.Configuration.Json packages installed in your project. In your C# code, you can use the ConfigurationBuilder class to read the connection string from the appsettings.json file. Here's an example:

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

string connectionString = configuration.GetConnectionString("MyConnectionString");