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

In this article, we'll walk through the process of building a web API that interacts with a PostgreSQL database.

To interact with PostgreSQL we have several ways like

1. ADO.NET
2. Entity Framework Core
3. Dapper

Today we will use ADO.NET for database operations along with .NET 8 and some of its new features.

Prerequisites

• Install .NET SDK 8.0 or later: Download .NET 
• Install PostgreSQL: Download PostgreSQL
• A code editor (Visual Studio)

Step 1. Install the PostgreSQL database if it's not there.

Step 2. Open PostgreSQL and create a database with the name "Employee_Tracker"

Step 3. Open the Employee_Tracker database go to Schemas and create a new schema called employee_tracker,

Open the SQL query and run the below query to create an employee table in PostgreSQL.        

CREATE TABLE employee_tracker.employee (
            id SERIAL PRIMARY KEY,
            first_name VARCHAR(50),
            last_name VARCHAR(50),
            job_title VARCHAR(100),
            salary NUMERIC(10, 2),
            hire_date DATE
        );

Step 4. Right click on the Database and go to properties. Get the connection string detail from PgAdmin4 as shown in below screenshot

Step 5. Create a brand new Web API project with Target Framework as .NET 8 and add the connection string in the AppSettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "PostgreDB": "Host=localhost;Username=postgres;Password=1122;Database=Employee_Tracker"
  },
  "AllowedHosts": "*"
}

Step 6. Install the PostgreSQL provider DLL using the NuGet package manager 

PM> dotnet add package Npgsql

Npgsql is a .NET data provider for PostgreSQL, and it allows .NET applications to connect to, query, and interact with PostgreSQL databases. It serves as a bridge between your .NET application and the PostgreSQL database, providing a set of classes and methods that facilitate database operations.

Step 7. Add the below code to the Program. cs to read the connection and the DI Container for the Connection and Repository class

var connectionString = builder.Configuration.GetConnectionString("PostgreDB");
builder.Services.AddScoped((provider) => new NpgsqlConnection(connectionString));
builder.Services.AddScoped<IEmployeeRepository, EmployeeRepository>();

Step 8. Create the IEmployeeRepository interface and define the required method

public interface IEmployeeRepository
{
    Task<IEnumerable<Employee>> GetAll();
    Task<bool> Create(Employee employee);
    Task<bool> Update(Employee employee);
    Task<bool> Delete(int id);
}

Step 9. Create an EmployeeRepository class where we can write all the code related to database CRUD(Create, Read, Update, and Delete), which implements the IEmployeeRepository and IDisposable interface.

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!!