Connecting .NET API To ApsaraDB With postgreSQL

This is a guide to creating a .NET API connected to ApsaraDB with PostgreSQL

1. Create the API using the command "dotnet new webapi"

2. Add the nugets for Entity framework and PostgreSQL

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

3. Create the class TodoItem, TodoItem.cs

namespace ApiPostgre;
public class TodoItem
{
    public int Id { get; set; }
    public string Title { get; set; }
    public bool IsCompleted { get; set; }
}

4. Create TodoItemContext.cs for Entity framework

using Microsoft.EntityFrameworkCore;
namespace ApiPostgre;
public class TodoItemContext : DbContext
{    
    public DbSet<TodoItem> TodoItems { get; set; }

    public TodoItemContext(DbContextOptions<TodoItemContext> options) : base(options) 
    {
    }        
}

5. Add TodoItemController including all method (get, post, put, delete)

using Microsoft.AspNetCore.Mvc;
namespace ApiPostgre.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class TodoItemController : ControllerBase
    {
        TodoItemContext bd;
        public TodoItemController(TodoItemContext context)
        {
            bd = context;
            bd.Database.EnsureCreated();
        }

        [HttpGet("")]
        public ActionResult<IEnumerable<TodoItem>> GetTodoItems()
        {
            return  bd.TodoItems;
        }

        [HttpGet("{id}")]
        public ActionResult<TodoItem> GetTodoItemById(int id)
        {
            var currentItem = bd.TodoItems.FirstOrDefault(p=> p.Id == id);

            if(currentItem == null) return NotFound();

            return currentItem;
        }

        [HttpPost("")]
        public async Task PostTodoItem(TodoItem model)
        {
            bd.Add(model);
            await bd.SaveChangesAsync();
        }

        [HttpPut("{id}")]
        public async Task<IActionResult> PutTodoItem(int id, TodoItem model)
        {
            var currentItem = bd.TodoItems.FirstOrDefault(p=> p.Id == id);

            if(currentItem == null) return NotFound();

            currentItem.Title = model.Title;
            currentItem.IsCompleted = model.IsCompleted;
            await bd.SaveChangesAsync();

            return NoContent();
        }

        [HttpDelete("{id}")]
        public ActionResult<TodoItem> DeleteTodoItemById(int id)
        {
            return null;
        }
    }
}

6. ApsaraDB RDS is a service created by alibaba to host relationational database like SQL Server, PostgreSQL y MySQL

Let's create a new ApsaraDB RDS instance in Alibaba Cloud