You will need to install and add reference to the Npgsql package to communicate with PostgreSQL database.
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using Npgsql;
using Dapper;
public class EmployeeRepository
{
private readonly string _connectionString;
public EmployeeRepository(string connectionString)
{
_connectionString = connectionString;
}
public async Task<int> CreateEmployeeAsync(Employee employee)
{
using (IDbConnection connection = new NpgsqlConnection(_connectionString))
{
string query = "INSERT INTO Employees (FirstName, LastName, Email, PhoneNumber) " +
"VALUES (@FirstName, @LastName, @Email, @PhoneNumber);" +
"SELECT CAST(CURRENT_VAL('employees_id_seq') AS INTEGER)";
return await connection.ExecuteScalarAsync<int>(query, employee);
}
}
public async Task<IEnumerable<Employee>> GetEmployeesAsync()
{
using (IDbConnection connection = new NpgsqlConnection(_connectionString))
{
string query = "SELECT * FROM Employees";
return await connection.QueryAsync<Employee>(query);
}
}
public async Task<Employee> GetEmployeeByIdAsync(int id)
{
using (IDbConnection connection = new NpgsqlConnection(_connectionString))
{
string query = "SELECT * FROM Employees WHERE Id = @id";
return await connection.QuerySingleOrDefaultAsync<Employee>(query, new { id });
}
}
public async Task<bool> UpdateEmployeeAsync(Employee employee)
{
using (IDbConnection connection = new NpgsqlConnection(_connectionString))
{
string query = "UPDATE Employees SET FirstName = @FirstName, LastName = @LastName, " +
"Email = @Email, PhoneNumber = @PhoneNumber WHERE Id = @Id";
int rowsAffected = await connection.ExecuteAsync(query, employee);
return rowsAffected > 0;
}
}
public async Task<bool> DeleteEmployeeAsync(int id)
{
using (IDbConnection connection = new NpgsqlConnection(_connectionString))
{
string query = "DELETE FROM Employees WHERE Id = @id";
int rowsAffected = await connection.ExecuteAsync(query, new { id });
return rowsAffected > 0;
}
}
}
