I'm failing to connect to PostgreSQL using Npgsql in my local environment I have checked each and every thing looks fine and I'm able to connect using my pgAmin and also I have managed to update tables after creating migrations I don't know where I'm doing it wrong where I try to open the connection so as I can insert data into the table I have created below is my implementation.
appsettings.json
{
"ConnectionStrings": {
"DefaultConnection": "Host=localhost;Port=5432;Database=customerdb;Username=postgres;Password=admin123;"
},
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"AllowedHosts": "*"
}
Controller method to insert data
private readonly string _connectionString;
public DataLoader(string connectionString)
{
_connectionString = connectionString;
}
public async Task LoadToDatabase(IEnumerable<Customer> data)
{
try
{
await using var connection = new NpgsqlConnection(_connectionString);
await connection.OpenAsync();
foreach (var customerDetails in data)
{
await connection.ExecuteAsync("INSERT INTO Customer (CustomerCode, CustomerNames,OrderId, Location) VALUES (@CustomerCode, @CustomerNames, @OrderId, @Location)", customerDetails);
}
await connection.CloseAsync();
}
catch (NpgsqlException ex)
{
// Log error (log to a file, console, etc.)
Console.WriteLine($"Error opening PostgreSQL connection: {ex.Message}");
}
catch (Exception ex)
{
// Log error (log to a file, console, etc.)
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
Program .CS class
// Add services to the container.
builder.Services.AddControllers();
// Adding configuration
builder.Configuration.AddJsonFile("appsettings.json", optional: false, reloadOnChange: true);
// Getting the configuration
var configuration = builder.Configuration;
// Retrieving connection string from configuration
var postgreconnection = configuration.GetSection("ConnectionStrings:DefaultConnection").Value;
// Configuring PostgreSQL Database connection
builder.Services.AddDbContext<ApplicationDbContext>(options =>options.UseNpgsql(postgreconnection));
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
builder.Services.AddSingleton(new DataExtractor());
builder.Services.AddSingleton(new DataTransformer());
builder.Services.AddSingleton(new DataLoader(postgreconnection));
No firewall is blocking, any suggestions will be much appreciated.
