Building a Robust ASP.NET Core Web API with PostgreSQL

Introduction

Creating a CRUD (Create, Read, Update, Delete) operation with a PostgreSQL database in an ASP.NET Core Web API is a common task in web development. In this article, we'll walk through the steps to build a Web API using ASP.NET Core, connect it to a PostgreSQL database, and perform CRUD operations on a model named CSharpCornerArticle. We'll also include a real-world use case for this model.

Prerequisites

1. Visual Studio or Visual Studio Code (with .NET Core SDK installed)
2. PostgreSQL database server installed and running
3. Postman or a similar tool for testing the API endpoints
4. Let's start by following these steps:

1. Create a new ASP.NET Core Web API project

Open Visual Studio or Visual Studio Code and create a new ASP.NET Core Web API project. You can use the following command in your terminal if you're using Visual Studio Code.

dotnet new webapi -n CSharpCornerApi

C#

Copy

This command will create a new ASP.NET Core Web API project named CSharpCornerApi.

2. Set up the PostgreSQL Database

Ensure you have PostgreSQL installed and running. Create a new database for your project if you haven't already. You can use tools like pgAdmin or the PostgreSQL command line to create a database.

CREATE DATABASE CSharpCornerDB;

C#

Copy

3. Install Entity Framework Core

In your project directory, add Entity Framework Core and Npgsql.EntityFrameworkCore.PostgreSQL packages by running the following commands.

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL.Design

C#

Copy

4. Create the Model

Create a model class named CSharpCornerArticle.cs in the Models folder.

// Models/CSharpCornerArticle.cs

using System;

namespace CSharpCornerApi.Models
{
    public class CSharpCornerArticle
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }
        public DateTime CreatedAt { get; set; }
    }
}

C#

Copy

5. Configure the Database Context

Create a database context class to configure the connection to the PostgreSQL database.

// Data/AppDbContext.cs

using Microsoft.EntityFrameworkCore;

namespace CSharpCornerApi.Data
{
    public class AppDbContext: DbContext
    {
        public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
        {
        }

        public DbSet<CSharpCornerArticle> Articles { get; set; }
    }
}

C#

Copy

6. Configure Database Connection

In the appsettings.json file, add your PostgreSQL connection string.

{
  "ConnectionStrings": {
    "DefaultConnection": "Host=localhost;Port=5432;Database=CSharpCornerDB;Username=yourusername;Password=yourpassword"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  // ...
}

C#

Copy

Replace your username and your password with your PostgreSQL credentials.

7. Configure Dependency Injection

In the Startup.cs file, configure dependency injection for the database context.

// Startup.cs

using CSharpCornerApi.Data;

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<AppDbContext>(options =>
        options.UseNpgsql(Configuration.GetConnectionString("DefaultConnection")));

    // ...
}

C#

Copy

8. Create CRUD Endpoints

In the Controllers folder, create a controller to handle CRUD operations.

// Controllers/CSharpCornerArticlesController.cs

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using CSharpCornerApi.Data;
using CSharpCornerApi.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;


[Route("API/[controller]")]
[ApiController]
public class CSharpCornerArticlesController: ControllerBase
{
    private readonly AppDbContext _context;

    public CSharpCornerArticlesController(AppDbContext context)
    {
        _context = context;
    }

    [HttpGet]
    public async Task<ActionResult<IEnumerable<CSharpCornerArticle>>> GetArticles()
    {
        return await _context.Articles.ToListAsync();
    }

    [HttpGet("{id}")]
    public async Task<ActionResult<CSharpCornerArticle>> GetArticle(int id)
    {
        var article = await _context.Articles.FindAsync(id);

        if (article == null)
        {
            return NotFound();
        }

        return article;
    }

    [HttpPost]
    public async Task<ActionResult<CSharpCornerArticle>> PostArticle(CSharpCornerArticle article)
    {
        article.CreatedAt = DateTime.Now;
        _context.Articles.Add(article);
        await _context.SaveChangesAsync();

        return CreatedAtAction("GetArticle", new { id = article.Id }, article);
    }

    [HttpPut("{id}")]
    public async Task<IActionResult> PutArticle(int id, CSharpCornerArticle article)
    {
        if (id != article.Id)
        {
            return BadRequest();
        }

        _context.Entry(article).State = EntityState.Modified;

        try
        {
            await _context.SaveChangesAsync();
        }
        catch (DbUpdateConcurrencyException)
        {
            if (!ArticleExists(id))
            {
                return NotFound();
            }
            else
            {
                throw;
            }
        }

        return NoContent();
    }

    [HttpDelete("{id}")]
    public async Task<IActionResult> DeleteArticle(int id)
    {
        var article = await _context.Articles.FindAsync(id);

        if (article == null)
        {
            return NotFound();
        }

        _context.Articles.Remove(article);
        await _context.SaveChangesAsync();

        return NoContent();
    }

    private bool ArticleExists(int id)
    {
        return _context.Articles.Any(e => e.Id == id);
    }
}

C#

Copy

9. Run Migrations

Generate and apply migrations to create the database schema.

dotnet ef migrations add InitialCreate
dotnet ef database update

C#

Copy

Run your ASP.NET Core Web API project, and you can use Postman or any other API testing tool to test the CRUD operations on the CSharpCornerArticle model.

• To create a new article, make a POST request to https://localhost:5001/api/CSharpCornerArticles.
• To retrieve all articles, make a GET request to https://localhost:5001/api/CSharpCornerArticles.
• To retrieve a specific article, make a GET request to https://localhost:5001/api/CSharpCornerArticles/{id}.
• To update an article, make a PUT request to https://localhost:5001/api/CSharpCornerArticles/{id}.
• To delete an article, make a DELETE request to https://localhost:5001/api/CSharpCornerArticles/{id}.

CRUD API with a PostgreSQL database in ASP.NET Core, complete with a real-world use case model named CSharpCornerArticle. You can now build upon this foundation to create more advanced features for your application.