Mastering PostgreSQL CRUD Operations in .NET 8 with ADO.NET

public class EmployeeRepository(NpgsqlConnection connection) : IEmployeeRepository, IDisposable
{
    public async Task<IEnumerable<Employee>> GetAll()
    {
        var employees = new List<Employee>();
        using var cmd = connection.CreateCommand();
        cmd.CommandText = "SELECT id, first_name, last_name, job_title, salary, hire_date FROM employee_tracker.employee;";
        await connection.OpenAsync();
        using var reader = await cmd.ExecuteReaderAsync();
        if (reader is not null)
        {
            while (await reader.ReadAsync())
            {
                employees.Add(new Employee(
                            Id: Convert.ToInt32(reader["id"]),
                            FirstName: Convert.ToString(reader["first_name"]),
                            LastName: Convert.ToString(reader["last_name"]),
                            JobTitle: Convert.ToString(reader["job_title"]),
                            Salary: Convert.ToDecimal(reader["salary"]),
                            HireDate: Convert.ToDateTime(reader["hire_date"])
                        ));
            }
        }
        await connection.CloseAsync();
        return employees.ToList();
    }

    public async Task<bool> Create(Employee employee)
    {
        const string insertQuery =
            "INSERT INTO employee_tracker.employee (first_name, last_name, job_title, salary, hire_date) " +
            "VALUES (@FirstName, @LastName, @JobTitle, @Salary, @HireDate)";

        using var cmd = connection.CreateCommand();
        cmd.CommandText = insertQuery;
        AddParameters(cmd, employee);
        await connection.OpenAsync();
        var rowAffected = await cmd.ExecuteNonQueryAsync();
        await connection.CloseAsync();
        return rowAffected > 0;

    }

    public async Task<bool> Update(Employee employee)
    {
        const string updateQuery =
        "UPDATE employee_tracker.employee SET first_name = @FirstName, last_name = @LastName, " +
        "job_title = @JobTitle, salary = @Salary, hire_date = @HireDate WHERE id = @Id";

        using var cmd = connection.CreateCommand();
        cmd.CommandText = updateQuery;
        AddParameters(cmd, employee);
        await connection.OpenAsync();
        var rowAffected = await cmd.ExecuteNonQueryAsync();
        await connection.CloseAsync();
        return rowAffected > 0;
    }

    public async Task<bool> Delete(int id)
    {
        const string deleteQuery = "DELETE FROM employee_tracker.employee WHERE id = @Id";
        using var cmd = connection.CreateCommand();
        cmd.CommandText = deleteQuery;
        cmd.Parameters.AddWithValue("@Id", id);
        await connection.OpenAsync();
        var rowAffected = await cmd.ExecuteNonQueryAsync();
        await connection.CloseAsync();
        return rowAffected > 0;

    }

    private static void AddParameters(NpgsqlCommand command, Employee employee)
    {
        var parameters = command.Parameters;

        parameters.AddWithValue("@Id", employee.Id);
        parameters.AddWithValue("@FirstName", employee.FirstName ?? string.Empty);
        parameters.AddWithValue("@LastName", employee.LastName ?? string.Empty);
        parameters.AddWithValue("@JobTitle", employee.JobTitle ?? string.Empty);
        parameters.AddWithValue("@Salary", employee.Salary);
        parameters.AddWithValue("@HireDate", employee.HireDate);
    }


    public void Dispose()
    {
        if (connection.State != ConnectionState.Closed)
        {
            connection.Close();
        }
        GC.SuppressFinalize(this);
    }
}

In the above code, I have used the Primary Constructor to inject the connection dependency, and for the DTO(Employee) I am using the record, to follow the recent feature and best practices from the. NET.

public record Employee(int Id, string? FirstName, string? LastName, string? JobTitle, decimal Salary, DateTime HireDate);

Step 10. Create an Employee Controller and inject the EmployeeRepository also add all the necessary endpoints for the CRUD operation.

[Route("api/[controller]")]
[ApiController]
public class EmployeeController(IEmployeeRepository employeeRepository) : ControllerBase
{
    [HttpGet]
    [Route("employees")]
    public async Task<ActionResult> Get()
    {
        return Ok(await employeeRepository.GetAll());
    }

    [HttpPost]
    [Route("create")]
    public async Task<ActionResult> Create([FromBody] Employee employee)
    {
        return Ok(await employeeRepository.Create(employee));
    }

    [HttpPut]
    [Route("update")]
    public async Task<ActionResult> Update([FromBody] Employee employee)
    {
        return Ok(await employeeRepository.Update(employee));
    }        

    [HttpDelete]
    [Route("delete")]
    public async Task<ActionResult> Get(int id)
    {
        return Ok(await employeeRepository.Delete(id));
    }
}

Step 11. Now we are done with all the changes to perform the CRUD operation. let's run the application.

• GET: All Employee
  
• Create
  
  New entry in database
  
• Update
  
  Updated employee in DB:
  
• Delete:
  
  Delete employee from DB:
  

I hope you found this article helpful. Thank you for taking the time to read it. If you have further questions please comment in the comment box.

Happy Coding!!