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.
What I have tried:
appsettings.json
<pre>{
"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)
{
Console.WriteLine($"Error opening PostgreSQL connection: {ex.Message}");
}
catch (Exception ex)
{
Console.WriteLine($"An error occurred: {ex.Message}");
}
}
Program .CS class
var configuration = builder.Configuration;
var postgreconnection = configuration.GetSection("ConnectionStrings:DefaultConnection").Value;
builder.Services.AddDbContext<ApplicationDbContext>(options =>options.UseNpgsql(postgreconnection));
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
builder.Services.AddSingleton(new DataExtractor());
builder.Services.AddSingleton(new DataTransformer());
builder.Services.AddSingleton(new DataLoader(postgreconnection));