Reading record via REST api from database in asp.net core mvc

In this tip you will learn how to write Api for getting records from database and request it from asp.net core. Read the following articles before continuing this.

Enabling Code First in asp.net Core MVC (Entity framework Core)

Some useful Extension methods for Executing stored procedures in (Entity Framework Core)

Install the package (Microsoft.AspNet.WebApi.Client -Version 5.2.7) in your project

PM> Install-Package Microsoft.AspNet.WebApi.Client -Version 5.2.7

Add table(Employee) to your database

CREATE TABLE [dbo].[Employee](
	[EmpID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](150) NULL,
	[DOB] [datetime] NULL,
	[JobTitle] [varchar](50) NULL,
	[Address] [varchar](200) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
	[EmpID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Add two stored procedures to your databases (sp_SelectAll_Employees,sp_Employee_SaveNew)

create proc [dbo].[sp_Employee_SaveNew]
@Name varchar(150),
@DOB datetime,
@JobTitle varchar(50),
@Address varchar(200)
as
INSERT INTO [dbo].[Employee]
           ([Name]
           ,[DOB]
           ,[JobTitle]
           ,[Address])
     VALUES
           (@Name
           ,@DOB
           ,@JobTitle
           ,@Address)
GO

Create Proc [dbo].[sp_SelectAll_Employees]
as
SELECT [EmpID]
      ,[Name]
      ,[DOB]
      ,[JobTitle]
      ,[Address]
  FROM [Employee]

Add One interface(IEmployeeDbService) and three classes(EmployeeDbService, EmployeeRootModel, EmployeeModel) to your project.

EmployeeRootModel, EmployeeModel

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

namespace MyCoreProject.Models
{
    public class EmployeeRootModel
    {
        public int Code { get; set; }
        public string Message { get; set; }
        public List<EmployeeModel> Employees { get; set; }
    }
    public class EmployeeModel
    {
        public int EmpID { get; set; }
        [Required]
        [Display(Name="Employee Name")]
        public string Name { get; set; }
        [Required]
       [Display(Name="Date Of Birth")]
        public DateTime? DOB { get; set; }
        [Required]
        [Display(Name="Job Title")]
        public string JobTitle { get; set; }
        [Required]
        [Display(Name = "Address")]
        public string Address { get; set; }
    }
   
}

IEmployeeDbService

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

namespace MyCoreProject.Areas.coreapi.Services
{
   public interface IEmployeeDbService
    {
        List<EmployeeModel> SelectAll();
        int SaveNew(EmployeeModel model);
    }
}

EmployeeDbService

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

namespace MyCoreProject.Areas.coreapi.Services
{
    public class EmployeeDbService : IEmployeeDbService
    {
        public int SaveNew(EmployeeModel model)
        {
            using (var db = new MyDbContext())
            {
                var output = db.LoadStoredProc("sp_SelectAll_Employees")
                    .WithSqlParam("",model.Name)
                    .WithSqlParam("",model.DOB)
                    .WithSqlParam("",model.JobTitle)
                    .WithSqlParam("",model.Address).ExecuteStoredProc();
                return output;
            }
        }

        public List<EmployeeModel> SelectAll()
        {
            using (var db = new MyDbContext())
            {
                var employees = db.LoadStoredProc("sp_SelectAll_Employees").ExecuteStoredProc<EmployeeModel>();
                return employees;
            }
        }
    }
}

Add apiController (EmployeeController) to your project

using Microsoft.AspNetCore.Mvc;
using MyCoreProject.Areas.coreapi.Services;
using MyCoreProject.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

// For more information on enabling Web API for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860

namespace MyCoreProject.Areas.coreapi.Controllers
{
    [Area("coreapi")]
    [Route("coreapi/Employee")]
    [ApiController]
    public class EmployeeController : ControllerBase
    {
        private readonly IEmployeeDbService _employeeDbService;
        public EmployeeController(IEmployeeDbService employeeDbService)
        {
            _employeeDbService = employeeDbService;
        }       
        [HttpGet]
        public JsonResult Get()
        {           
            var employees = _employeeDbService.SelectAll();
            return new JsonResult(new
            {
                Code = 0,
                Message = "Success",
                Employees = employees
            });
        }
        [HttpPost]
        public JsonResult Post(EmployeeModel model)
        {
            var effectedRows = _employeeDbService.SaveNew(model);
            return new JsonResult(new
            {
                Code =effectedRows>0? 0:1,
                Message = effectedRows > 0? "Success":"failure"               
            });
        }

    }
}

Add one interface and one class to your project

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

namespace MyCoreProject.Services
{
   public interface IEmployeeService
    {
        EmployeeRootModel GetAll();
        EmployeeRootModel AddNew(EmployeeModel model);
    }
}

using MyCoreProject.Models;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Threading.Tasks;

namespace MyCoreProject.Services
{
    public class EmployeeService : IEmployeeService
    {
        string baseUrl = "http://localhost:64448/coreapi/";
        private EmployeeRootModel EmployeeRootObject;

        public EmployeeRootModel AddNew(EmployeeModel model)
        {
            Task.Run(() => addNew(model)).Wait();
            return EmployeeRootObject;
        }

        public EmployeeRootModel GetAll()
        {
            Task.Run(() => Get()).Wait();
            return EmployeeRootObject;
        }
        private async Task<EmployeeRootModel> Get()
        {
            string url = baseUrl + "employee";
            HttpClient client = new HttpClient();
            client.BaseAddress = new Uri(url);
            client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
            using (HttpResponseMessage response = await client.GetAsync(url))
            {
                if (response.IsSuccessStatusCode)
                {
                    var returnResult = response.Content.ReadAsAsync<EmployeeRootModel>().Result;
                    EmployeeRootObject = returnResult;
                    return returnResult;
                }
            }

            return null;
        }

        private async Task<EmployeeRootModel> addNew(EmployeeModel model)
        {
            dynamic jobj = new JObject();
            jobj.Name = model.Name;
            jobj.DOB = model.DOB;
            jobj.JobTitle = model.JobTitle;
            jobj.Address = model.Address;
            string url = baseUrl + "employee";

            HttpClient client = new HttpClient();
            client.BaseAddress = new Uri(url);
            var content = new StringContent(jobj.ToString(), Encoding.UTF8, "application/json");

            using (HttpResponseMessage response = await client.PostAsync(url, content))
            {
                if (response.IsSuccessStatusCode)
                {
                    var res = response.Content.ReadAsAsync<EmployeeRootModel>().Result;
                    EmployeeRootObject = res;
                    return res;
                }
            }

            return null;
        }
    }
}

Startup method (ConfigureServices) will look like this

 public void ConfigureServices(IServiceCollection services)
        {
            services.AddSingleton<IEmployeeService, EmployeeService>();
            services.AddSingleton<IEmployeeDbService, EmployeeDbService>();
            services.AddMvc();
            services.AddControllers().AddJsonOptions(
                x => x.JsonSerializerOptions.WriteIndented=true
                ); 
            services.AddControllersWithViews();
            services.AddDbContext<MyDbContext>();
           
            
        }

Add Employee Controller to your project.

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using MyCoreProject.Models;
using MyCoreProject.Services;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace MyCoreProject.Controllers
{
  
    public class EmployeeController : Controller
    {
        IEmployeeService _employeeServices;
        public EmployeeController(IEmployeeService employeeService)
        {
            _employeeServices = employeeService;
        }
        // GET: EmployeeController
        public ActionResult Index()
        {
            var employees = _employeeServices.GetAll().Employees;           
            return View(employees);
        }

        public ActionResult Create()
        {
            var emp = new EmployeeModel();
            return View(emp);
        }
        
       
    }
}

Add view to Index action method (Index.cshtml)

@model IEnumerable<MyCoreProject.Models.EmployeeModel>

@{
    ViewData["Title"] = "Index";
}

<h1>Index</h1>

<p>
    <a asp-action="Create">Create New</a>
</p>
<table class="table">
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.EmpID)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Name)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.DOB)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.JobTitle)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Address)
            </th>
           
        </tr>
    </thead>
    <tbody>
@foreach (var item in Model) {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.EmpID)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Name)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.DOB)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.JobTitle)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Address)
            </td>
           
        </tr>
}
    </tbody>
</table>

Reading record via REST api from database in asp.net core mvc

Post a Comment

Post a Comment

Reading record via REST api from database in asp.net core mvc

You might like

Post a Comment

Post a Comment