How to implement async CRUD methods for an Employee model using Dapper with PostgreSQL

 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;

        }

    }

}