How to create Insert (Post), Read(Get), Patch(Update) and Delete ASP.NET Core API using Entity framework core and SQL Server as a database?

Procedure to create repository pattern asp.net core web API select(get), Insert (post), Update(put) or patch and delete operations using Entity framework core and SQL server as database.

ASP .net core API Crate,Update,Delet

Step 1: Create an empty API Project

Now, .NET is a free, cross-platform, open-source developer platform for building many different types of applications. I have installed Visual Studio 2019 and .net core SDK, if you have not installed yet, please find the below link to install the SDK.

Install the .net SDK

https://dotnet.microsoft.com/download

Open Visual Studio 2019 and click on Create New Project, by using this Wizard we can create asp.net core web Applications, API projects, and Single page applications as well.

So, as you can see in the below image, I’m selecting an API template to create an API project and in the Advanced tab in the right below, I unchecked the Configure for HTTPS option. After selecting the things click on Create to create the New API Project.

Step 2: Install the packages

Install the Below packages by clicking the On Browse button, if You have not installed the below packages

1. Microsoft.EntityFrameworkCore 
Entity Framework Core is a modern object-database mapper for dot NET. It supports LINQ queries, change tracking, updates, and schema migrations. EF Core works with SQL Server, Azure SQL Database, SQLite, Azure Cosmos DB, MySQL, PostgreSQL, and other databases through a provider plugin API.

2. Microsoft.EntityFrameworkCore.SqlServer
It’s a Microsoft SQL Server database provider for Entity Framework Core.

3. Microsoft.EntityFrameworkCore.Tools 
When we executing the migration scripts command in the package manager console this tool is required. So, the Entity Framework Core tools help with design-time development tasks. They’re primarily used to manage Migrations and to scaffold and entity types by reverse-engineering the schema of a database.

Step 3: Create Data, Model Folders

Now, it the time to create the respective folders and files in our project, In the below Image I have created the folders like this.

Controller –>WeatherForecastController.cs –> it’s API Controller

Data–> InCompareContext.cs –> DbContext files to intract to Data base like sql,mysql,mango

Interface –> ICategory.cs –>Interface file for dependency injection

Model–> Category.cs –> Its a Model Class to create respective columns in the database

SqlRepo–> CategoryRepository.cs –> Contains Data Access Methods like Add, Update, Select methods.

Files

Step 4: Add Model Class

The Model class is required to generate dataBase Objects using Entity Framework Code First Approach, Here in the below image as you can see, I have created a Category. cs class files with the following properties,

Category Id-> It’s a primary Key of the Category Table.

Category Name -> It’s a string property Store string values.

Step 5: Create Interface to the Model

To achieve Dependency Injection and Repository Pattern approach Interface class is required.

So, As you can see in the below images, I Have created a Public ICategory Interface and the following methods Create, Read, Update, Delete methods to interact with the DbContext Class to perform SQL Select, Insert, Update, Delete DML Commands.

Step 6: Create SQL Repository

The SQL Repository mainly consists of Entity Framework and Linq Queries to Add, Select, Update and delete Linq Methods.

using API_IN_COMPARE.Data;
using API_IN_COMPARE.Interface;
using API_IN_COMPARE.Model;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace API_IN_COMPARE.SqlRepo
{
    public class CategoryRepository : ICategory
    {
        private readonly InCompareContext context;
        public CategoryRepository(InCompareContext inCompareContext)
        {
            context = inCompareContext;
        }
        public Category AddCategory(Category category)
        {
            context.CATEGORY.Add(category);
            context.SaveChanges();
            return category;
        }
        public Category DeleteCategory(int categoryId)
        {
            var cat = context.CATEGORY.FirstOrDefault(e => e.CATEGORY_ID == categoryId);
            context.CATEGORY.Remove(cat);
            return cat;
        }
        public IEnumerable<Category> GetCategories()
        {
            return context.CATEGORY;
        }
        public Category GetCategory(int id)
        {
            return context.CATEGORY.FirstOrDefault(e => e.CATEGORY_ID == id);
        }
        public Category UpdateCategory(Category category)
        {
            var entity = context.CATEGORY.Attach(category);
            entity.State = EntityState.Modified;
            context.SaveChanges();
            return category;
        }
    }
}

Step 7: Crate DbContext Class

As Microsoft MDN says, A DbContext instance represents a combination of the Unit Of Work and Repository patterns such that it can be used to query from a database and group together changes that will then be written back to the store as a unit. DbContext is conceptually similar to ObjectContext.

So, Here I Added My Category Data model instance to DBSet, properties for the root entities of the model. These sets are automatically initialized when the instance of the derived class is created. 

Step 8: Create Your First API class

Now it the time to create our Controller Class, In the following image I did Constructor Dependency Injection to the Controller Class of My ICategory interface class (Line no: 14,17,20)

using System.Collections.Generic;
using API_IN_COMPARE.Interface;
using API_IN_COMPARE.Model;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;

namespace API_IN_COMPARE.Controllers
{
    [ApiController]
    [Route("[controller]")]
    public class WeatherForecastController : ControllerBase
    {
        private readonly ICategory _category;
        private readonly ILogger _logger;

        public WeatherForecastController(ILogger<WeatherForecastController>
            logger, ICategory category)
        {
            _logger = logger;
            _category = category;
        }

        [HttpGet]
        public IEnumerable<Category> Get()
        {
            // For testing purpose I'm Adding a row AddCategory() here in Get Method
            //, we can remove it later after everthing is ready
            
            _category.AddCategory(new Category
            {
                CATEGORY_NAME = "VAN"
            });
            return _category.GetCategories();
        }
 }
}

Step 9: Setup Startup.cs and App settings.json file configuration

Startup.cs

ASP.NET Core apps use a Startup class, which is named by convention. The Startup class:

  • Optionally includes a ConfigureServices method to configure the app’s services. A service is a reusable component that provides app functionality. Services are registered in ConfigureServices and consumed across the app via dependency injection (DI) or ApplicationServices.
  • Includes a Configure method to create the app’s request processing pipeline.
using API_IN_COMPARE.Data;
using API_IN_COMPARE.Interface;
using API_IN_COMPARE.SqlRepo;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;

namespace API_IN_COMPARE
{
    public class Startup
    {
        private IConfiguration _config;
        public Startup(IConfiguration configuration)
        {
            _config = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllers();
            services.AddMvc();
            services.AddDbContextPool<InCompareContext>(options =>
            {
                options.UseSqlServer(_config.GetConnectionString("EmployeeDbConnection"));
            });
            services.AddScoped<ICategory, CategoryRepository>();

        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            app.UseRouting();
            app.UseAuthorization();
            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
        }
    }
}

appsettings.json file

The appsettings.json file is typically used to store configuration settings such as database connections strings, any application scope global variables

Configuration providers read configuration data from key-value pairs using a variety of configuration sources

So, In our Project to set up the connection string to our database, we would set it up here. in the next step, I will show you how to add Connecting string to the appsettings.json file.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "ConnectionStrings": {
    "EmployeeDbConnection": "Copy and Paste your data base 'Connection string'"
  },
  "AllowedHosts": "*"

Step 10:Create Intial Migration script

Now, We are ready to Create a Database migration script for our Application, Can you remember we installed the Entity framework tools tool NuGet package manager?. So this is the time to utilize the package’s APIs to create a Database for our Category Entity in SQL server.

If you have not installed the package, follow the approach to install the EF core tool.

Run the ‘Add-Migration IntialMigration’ command is used to generate the Migration Script required to create the database objects. After the build is Successful enter the ‘Update-Migration’ command to generate the entities against the SQL server database.

If the entities are correct the command successfully creates the Database and its respective objects as shown in the below image.

Step 11: Your ‘Get’ Api Is ready, Enjoy

Hey, Let’s not waste your time now, You are ready to run the project, Hit Ctrl+F5 or Run the project to read the data from the database. as you can see in the below image, The Get() API method returning the Category Table Object Array. In the next article, we will perform Create, Update, Delete Operations using the Asp.net core API application.

Summery

In this article, we understand that how to create your first Asp.net core Api Project in a step-by-step procedure. In our next article, we will understand how to perform CRUD operations to our asp.net Core web API.

Default image
Shivaraju M
Over 3+ years experience in IT industry, good knowledge in . Net-based Web applications, windows applications, web services, and SPAs. I have played multiple roles related to technical delivery in multiple domains while working on over 10+ technologies. I am currently focused on continuous improvement and agile project management.

Leave a Reply