Create ASP.NET Core CRUD Web API with the Repository pattern

How to create ASP.NET Core CRUD Web API with the Repository pattern with Azure SQL Database and Azure CI/CD pipeline

Hi all, Hope all are doing well!. So In the last articles, I showed you how to create CI/CD pipeline for ASP.NET Web API application by using the Azure DevOps starter and how to create Azure SQL Database in the Azure portal. I am going to show you how to create a simple CRUD Web API with a Repository pattern in ASP.NET Core. This is a final part of this article series.

1st Part

Create CI/CD Pipeline for ASP.NET Core Web API by using Azure DevOps Starter

2nd Part

Create Azure SQL Database in Azure Portal

Create ASP.NET Core CRUD Web API with the Repository pattern

In this article, I will tell you,

1. Connect Azure SQL Database to Web API Project.
2. Create CRUD operations

Connect Azure SQL Database to Web API project

We create an Azure SQL database for our web API project. You can get an idea about it from our second article. I use EF core code-first approach and it’s focusing on the domain of our application and start creating classes for your domain entity rather than design your database first and then create the classes which match your database design.

First, install these packages into your project,

Microsoft.EntityFrameworkCore,Microsoft.EntityFrameworkCore.SqlServer and Microsoft.EntityFrameworkCore.Tools.

Create Model Class

Then You have to create model classes. Model classes will represent the tables inside the database. The model (entity) class is a class that Entity Framework Core uses for mapping to the database table.

So First create a new folder with the name Models. Then create student model class inside the model folder.

using System;
using System.ComponentModel.DataAnnotations;

namespace CoreAPIDemo.Models
{
    public class Student
    {

        public int StudentId { get; set; }
        [Required]
        public string FirstName { get; set; }
        [Required]
        public string LastName { get; set; }
        [Required]
        public string City { get; set; }
        [Required]
        [StringLength(10)]
        public string MobileNumber { get; set; }
        [Required]
        [DataType(DataType.EmailAddress)]
        public string Email { get; set; }

    }
}

Now you can see there is one model class called student that will represent the student table inside the StudentDB database. All the mandatory fields have the attribute [Required]and if we want to constrain the strings, we can use the [StringLength]attribute. Also, EF Core uses a naming convention to create a primary key, from the StudentId property.

Context class and the database connection

Now we have to create context class and it will be a middleware component for the communication with the Azure SQL database.

Create a folder called Context and then create our context class called MainContext.

using CoreAPIDemo.Models;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace CoreAPIDemo.Context
{
    public class MainContext : DbContext
    {

        public MainContext(DbContextOptions options) : base(options)
        {
        }
        public DbSet<Student> Students { get; set; }

    }
}

Now you can see our context class. Additional options are sent to the base DbContext class through the ApplicationContext constructor by using DbContextOptions parameter. Also, It has DbSet properties that contain the table data from the database

After that, Now you have to go to our database dashboard in the Azure portal. Because we have to get the connection string from it. You have to navigate to the connection string and copy it.

Now we have to add our connection string to the project. For that, we use appSetting.json file and add the DB Connection string inside it like the below picture. appSettings.json file holds the setting that is common to both environments.

{
  "ConnectionStrings": {
    "DefaultConnection": "(paste your connection string to here)"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

Now we have to register the context class into our project. So for that, we have to open a startup.cs class and have to add context class to modifying the ConfigureServices method. ConfigureServices is used to configure Dependency Injection.

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllers();
    string connectionString =   Configuration.GetConnectionString("DefaultConnection");
    services.AddDbContext<MainContext>(option => option.UseSqlServer(connectionString));

}

Now we have to create a table inside the SQL database.

First, build your project and then have to do code-based migrations for creating the table inside the database.

First, execute this command,

dotnet ef migrations add InitialCreate

Add migrations command will create a new migration class as per the specified name with the Up() and Down() methods. after the successful execution, you can see, it created the folder called migration.

Then execute the update — database command

dotnet ef update database

It will execute the last migration file created by the Add-Migration command and applies changes to the database schema.

After the successful execution, you can navigate to the Azure SQL database and can see created table inside the Azure SQL database.

Create CRUD operations

Now we have to create CRUD operations and I planned to use a Repository pattern for creating CRUD operation. The repository pattern creates the abstraction between database access and business logic. Instead of writing entire data access logic on the controller, it’s better to write this logic in a different class called a repository. This will make our code more maintainable and understandable.

First, create a Data folder inside the project and create an Interface called IStudentRepository and class called StudentRepository under the Repositories folder.

using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace CoreAPIDemo
{
    public interface IStudentRespository
    {
        ActionResult<IEnumerable<Student>> Get();

        void Post(Student student);

        void Update(Student student);

        void Delete(int? id);
    }
}

StudentRepository

using CoreAPIDemo.Context;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using CoreAPIDemo.Models;

namespace CoreAPIDemo.Respostories
{
    public class StudentRepository : IStudentRespository
    {
        private MainContext _mainContext;
        public StudentRepository(MainContext context)
        {
            _mainContext = context;
        }

        public ActionResult<IEnumerable<Student>> Get()
        {
            var stud =  _mainContext.Students.ToList();     
            return stud;
        }

        public async  void Post(Student student)
        {
            if(student == null)
            {
                throw new ArgumentNullException(nameof(student));
            }

            await _mainContext.Students.AddAsync(student);
            await _mainContext.SaveChangesAsync();

        }
        
    }
}

Now you can see the IStudentRepository interface and StudentRepository class which implements the IStudentRepository interface. Also, this interface is planned to be an abstraction layer over different ORMs (Object-relational mapping). Now we created our services with StudentRepository class. You can see other operations from the Github Repository.

Now we have to register the Repository with the dependency injection system. after that DI system will provide this service to StudentController.Open the Startup.cs file and modify the ConfigureServices method like the below code.

public void ConfigureServices(IServiceCollection services)
{
   services.AddControllers();
   string connectionString = Configuration.GetConnectionString("DefaultConnection");
   services.AddDbContext<MainContext>(option => option.UseSqlServer(connectionString));

   services.AddScoped<IStudentRespository, StudentRepository>();
}

Then now we have to create an API controller to expose the END-POINT. So create StudentController class under the controller folder.

using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using CoreAPIDemo.Models;
using Microsoft.AspNetCore.Mvc;

namespace CoreAPIDemo.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class StudentController : ControllerBase
    {
       private readonly IStudentRespository _repo;

        public StudentController(IStudentRespository repo)
        {
            _repo = repo;
        }

        // GET: api/[controller]
        [HttpGet]
        public ActionResult<IEnumerable<Student>> GetValues()
        {
            try {
                var students = _repo.Get();
                return Ok(students);
            }catch(Exception e)
            {
                return BadRequest(e);
            }
        }

        // POST: api/[controller]
        public async Task<ActionResult> Post([FromBody] Student student)
        {
            if(student == null)
            {
                return NotFound();
            }
            try
            {
                _repo.Post(student);
                return Ok("Value Added");
            }catch(Exception e)
            {
                return BadRequest(e);
            }
        }
    }
}

Now you can see StudentController class with Create and Read operations. So we can get the instance of the StudentRepository using Constructor Dependency Injection and then first get the list of the students using Get() and Post() EndPoint and then similarly we can define the rest of the endpoint for CRUD operations in this application. You can see other CRUD operations (Update and Delete) from my Github Repository.

So now we have to check to push our code into GitHub. Then changes you made are automatically built and deployed through a CI/CD pipeline. After that, we can check our ASP.NET Core CRUD Web API by use postman.

Test Deployed API using postman

Now you can see, we are able to get the correct result from our CRUD Web API. So guys we created ASP.NET CRUD Web API with Azure SQL database and created the CI/CD pipeline for the deploy our WEB API. So successfully done our operation.!!!

So That’s it for today. I think you learned something new from my article.

See you again soon with another article !!

Happy Coding & Good Luck All!!

Learn More

Deploy your React App to GitHub pages

JavaScript Hoisting