Crud Operations in Web API using Stored Procedures

In this tutorial I am going to implement basic CRUD operations in Web API using Stored Procedures that is created in SQL Server database. Previously we have build applications using the Entity Framework code first approach but never used the Stored Procedures , Now we are implementing it here step by step.

Steps to be followed here are :-

• Create a WEB API Project.
• Install all the required packages from Nuget package manager.
• Setting up the connection string into the AppSettings.Json file.
• Create the Model Class for CRUD operation here we are using the Employee Model Class
• Create a DbContext Class and Set the DBset Property with a Model Class.
• Register the DbContext Class into the Program.cs file.
• Create a Repository Folder and Create a IEmployee Interface inside it.
• Create a Service folder and create a EmployeeService Class inside it.
• Register the Repository and Service Class into the Program.cs file.
• Create a Controller for Employee Crud and define the methods into it.
• Create a Stored Procedures into the SQL Server for Insert, Update, Delete and Select.

To start with create a Web API project using the web api template as shown in below image and name the project then select for some default options for minimal api's and target framework as like in below images.

Install all the required packages from nuget package manager as shown in below image.

After installing the packages your solution explorer will be looks like as below image.

Now add Employee Model class and add the below properties into it.

                    
 [Key]
 public int EmployeeId { get; set; }
 public string EmployeeName { get; set; }
 public string EmployeeDescription { get; set; }
 public int EmployeeSalary { get; set; }
public int YearOfService { get; set; }

Now add DBContext class and add the below code into it.

                    
 public class ApplicationDbContext:DbContext
 {     
      public ApplicationDbContext(DbContextOptions
       options) 
      : base(options)
      {

      }
      public DbSettblUsers{get;set;}
      public DbSet Employees {get;set;}
}

Now add the Connection string into the AppSettings.json file

                    
  "ConnectionStrings": {
    "DBCS": "server=DESKTOP-1CO7VJ1\\SQLEXPRESS;
    database=FirstCrudDB;
      Trusted_Connection=true;encrypt=false;"
  }

Now add a repository folder in the project and create an Interface named it IEmployeeRepo and write the below methods inside it.

                    
public interface IEmployeeRepo
    {
        public Task> GetEmployeeListAsync();
        public Task> GetEmployeeByIdAsync(int Id);
        public Task AddEmployeeAsync(Employee Employee);
        public Task UpdateEmployeeAsync(Employee Employee);
        public Task DeleteEmployeeAsync(int Id);
    }

Now add a Service folder in the project and create an EmployeeService class into it and write the below code inside it.

Now add highlighted code into the program.cs file for accessing the DB Connection and register the employee services into it.

Now Create Stored procedures into the SQl database like below images.

Now create an API controller and named it Employee and write the code as shown in below images.

Now run the application you will get an swagger UI as shown in below images.