ASP.NET Core Web API for CRUD Operations with MySQL

Introduction

Creating a CRUD (Create, Read, Update, Delete) API in ASP.NET Core with a MySQL database is a common scenario in web development. In this article, we'll walk through building a complete ASP.NET Core Web API with a real-world use case. We'll create a model called CSharpCornerArticle and implement CRUD operations for it using Entity Framework Core with MySQL.

Prerequisites

1. Visual Studio or Visual Studio Code (with .NET Core SDK installed)
2. MySQL Server and MySQL Workbench (for database setup)

Step 1. Set Up the Project

Open Visual Studio and create a new ASP.NET Core Web API project.

Step 2. Create the Model

In this case, our model will be named CSharpCornerArticle. Create a new class under the "Models" folder.

using System;

Author: Sardar Mudassar Ali Khan
namespace YourProjectName.Models
{
    public class CSharpCornerArticle
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }
        public DateTime PublishedDate { get; set; }
    }
}

Step 3. Install Required Packages

You'll need the Entity Framework Core package for MySQL. Install it using the Package Manager Console or the Terminal.

dotnet add package Pomelo.EntityFrameworkCore.MySql

Step 4. Configure MySQL Connection

Open the appsettings.json file and configure your MySQL connection.

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=YourDatabaseName;User=root;Password=YourPassword;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  // ...
}

Step 5. Create a Database Context

Create a database context class that inherits from DbContext.

using Microsoft.EntityFrameworkCore;

Author: Sardar Mudassar Ali Khan
namespace YourProjectName.Data
{
    public class ApplicationDbContext: DbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
        {
        }

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

Step 6. Configure Dependency Injection

In the Startup.cs file, configure dependency injection for the ApplicationDbContext.

using Microsoft.EntityFrameworkCore;
using YourProjectName.Data;

Author: Sardar Mudassar Ali Khan

public void ConfigureServices(IServiceCollection services)
{
    // ...
    services.AddDbContext<ApplicationDbContext>(options =>
        options.UseMySql(Configuration.GetConnectionString("DefaultConnection")));
    // ...
}

Step 7. Create API Endpoints

Create a controller for CSharpCornerArticle CRUD operations.

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

Author: Sardar Mudassar Ali khan
namespace YourProjectName.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class CSharpCornerArticlesController : ControllerBase
    {
        private readonly ApplicationDbContext _context;

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

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

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

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

            return article;
        }

        [HttpPost]
        public async Task<ActionResult<CSharpCornerArticle>> PostCSharpCornerArticle(CSharpCornerArticle article)
        {
            _context.CSharpCornerArticles.Add(article);
            await _context.SaveChangesAsync();

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

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

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

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

            return NoContent();
        }

        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteCSharpCornerArticle(int id)
        {
            var article = await _context.CSharpCornerArticles.FindAsync(id);
            if (article == null)
            {
                return NotFound();
            }

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

            return NoContent();
        }

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

Step 8. Run Migrations

In the Package Manager Console or Terminal, run the following command to create the database and tables.

dotnet ef migrations add InitialCreate
dotnet ef database update

Step 9. Test the API

You can now test your API using tools like Postman or Swagger.

1. Create: Send a POST request to /api/CSharpCornerArticles with JSON data.
2. Read: Send a GET request to /api/CSharpCornerArticles to retrieve all articles or /api/CSharpCornerArticles/{id} to get a specific article.
3. Update: Send a PUT request to /api/CSharpCornerArticles/{id} with JSON data.
4. Delete: Send a DELETE request to /api/CSharpCornerArticles/{id}.

That's it! You've created a complete ASP.NET Core Web API for CRUD operations with a MySQL database using Entity Framework Core. You can now integrate this API into your front-end application or any other client.