Create an ASP.NET Core Web API CRUD Operations using Database-First approach also consume in .net core app

In ASP.NET Core Web API, using the Database-First approach allows you to generate models based on an existing database. Here's a step-by-step guide to building a CRUD application using this approach.

Prerequisites:

• Visual Studio 2022 or later
• SQL Server with an existing database
• Entity Framework Core installed (can be added via NuGet)

Steps to create an ASP.NET Core Web API using Database-First approach:

---

1. Create a New ASP.NET Core Web API Project

• Open Visual Studio.
• Go to File > New > Project.
• Select ASP.NET Core Web API and click Next.
• Name your project (e.g., WebApiCRUD) and click Create.
• Select .NET 7.0 or .NET 6.0 as the framework and choose the template API.

---

2. Install Entity Framework Core Packages

In NuGet Package Manager or Package Manager Console, install the following packages:

Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Tools

---

3. Generate Models from an Existing Database

In the Package Manager Console, run the following command to scaffold your models based on an existing database:

Scaffold-DbContext "YourConnectionString" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

YourConnectionString = Server=YourServerName;Database=YourDatabaseName;User Id=YourUsername;Password=YourPassword;

• Replace YourConnectionString with the actual connection string of your database.
• The -OutputDir Models option specifies the folder where your models and DbContext will be generated.

This command will generate all the database tables as Entity Framework models and create a DbContext class that represents your database.

---

4. Create a Controller for CRUD Operations

Once the models and DbContext are generated, you can create a controller to perform CRUD operations.

Example: Creating a CRUD Controller for an Employee Entity

• Add a new controller:
  • Right-click on the Controllers folder.
  • Select Add > Controller > API Controller with actions, using Entity Framework.
  • Select your model (e.g., Employee) and DbContext (e.g., YourDbContext).

This will automatically generate a controller with all the CRUD operations (GET, POST, PUT, DELETE).

---

5. CRUD Methods

The generated controller will include the following actions:

a) GET All Records

[HttpGet]
public async Task<ActionResult<IEnumerable<Employee>>> GetEmployees()
{
    return await _context.Employees.ToListAsync();
}

b) GET Single Record by ID

[HttpGet("{id}")]
public async Task<ActionResult<Employee>> GetEmployee(int id)
{
    var employee = await _context.Employees.FindAsync(id);

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

    return employee;
}

c) POST (Create) a New Record

[HttpPost]
public async Task<ActionResult<Employee>> PostEmployee(Employee employee)
{
    _context.Employees.Add(employee);
    await _context.SaveChangesAsync();

    return CreatedAtAction("GetEmployee", new { id = employee.EmployeeId }, employee);
}

d) PUT (Update) a Record

[HttpPut("{id}")]
public async Task<IActionResult> PutEmployee(int id, Employee employee)
{
    if (id != employee.EmployeeId)
    {
        return BadRequest();
    }

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

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

    return NoContent();
}

e) DELETE a Record

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

    _context.Employees.Remove(employee);
    await _context.SaveChangesAsync();

    return NoContent();
}

---

6. Test the API Using Postman or Swagger

• Swagger is built into ASP.NET Core by default, so you can test your API directly from the browser.
• Launch the project, and navigate to https://localhost:<port>/swagger. You will see a UI where you can test all the CRUD operations.

Alternatively, you can use Postman for testing by making GET, POST, PUT, and DELETE requests to the appropriate API endpoints.

---

7. Connection String Configuration

Ensure your connection string is set up in the appsettings.json:

"ConnectionStrings": {
    "DefaultConnection": "Server=YourServer;Database=YourDatabase;Trusted_Connection=True;"
}

In the Startup.cs (or Program.cs in .NET 6/7), register the DbContext:

services.AddDbContext<YourDbContext>(options =>
    options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

---

That's it!

You now have a functional ASP.NET Core Web API using the Database-First approach.

Consume the Web API in a .NET Core Application

To integrate the ASP.NET Core Web API CRUD operations into a .NET Core app (such as an ASP.NET Core MVC or Razor Pages app), you need to consume the API by making HTTP requests from your application. Here's how to do it step by step.

Steps to Consume the Web API in a .NET Core Application:

1. Create a .NET Core MVC Application

• Open Visual Studio.
• Go to File > New > Project.
• Select ASP.NET Core Web App (Model-View-Controller) and click Next.
• Name your project (e.g., MvcApp) and click Create.
• Select .NET 7.0 or .NET 6.0 and click Create.

2. Install the Required NuGet Packages

Install Microsoft.AspNetCore.Mvc.NewtonsoftJson for handling JSON serialization/deserialization and System.Net.Http.Json for working with HTTP requests:

Install-Package Microsoft.AspNetCore.Mvc.NewtonsoftJson

3. Create a Service to Interact with the Web API

You will create a service class that interacts with the Web API using HttpClient.

• Create a folder named Services in your project.
• Inside the Services folder, create a new class named EmployeeService.cs.

Example: EmployeeService.cs

using System.Net.Http;
using System.Net.Http.Json;
using System.Threading.Tasks;
using MvcApp.Models;  // Import your models here
using System.Collections.Generic;

namespace MvcApp.Services
{
    public class EmployeeService
    {
        private readonly HttpClient _httpClient;

        public EmployeeService(HttpClient httpClient)
        {
            _httpClient = httpClient;
        }

        // Get all employees
        public async Task<List<Employee>> GetEmployeesAsync()
        {
            return await _httpClient.GetFromJsonAsync<List<Employee>>("api/Employees");
        }

        // Get employee by ID
        public async Task<Employee> GetEmployeeByIdAsync(int id)
        {
            return await _httpClient.GetFromJsonAsync<Employee>($"api/Employees/{id}");
        }

        // Create a new employee
        public async Task CreateEmployeeAsync(Employee employee)
        {
            await _httpClient.PostAsJsonAsync("api/Employees", employee);
        }

        // Update an existing employee
        public async Task UpdateEmployeeAsync(int id, Employee employee)
        {
            await _httpClient.PutAsJsonAsync($"api/Employees/{id}", employee);
        }

        // Delete an employee
        public async Task DeleteEmployeeAsync(int id)
        {
            await _httpClient.DeleteAsync($"api/Employees/{id}");
        }
    }
}

4. Configure Dependency Injection for HttpClient

In your Program.cs, configure the HttpClient for dependency injection to use in your service class.

Modify Program.cs to include the EmployeeService:

var builder = WebApplication.CreateBuilder(args);

// Register HttpClient with the base address of your Web API
builder.Services.AddHttpClient<EmployeeService>(client =>
{
    client.BaseAddress = new Uri("https://localhost:5001/"); // Your Web API's base URL
});

// Add services to the container.
builder.Services.AddControllersWithViews();

var app = builder.Build();

app.UseHttpsRedirection();
app.UseStaticFiles();

app.UseRouting();

app.UseAuthorization();

app.MapControllerRoute(
    name: "default",
    pattern: "{controller=Home}/{action=Index}/{id?}");

app.Run();

5. Create Views and Controllers to Display Data

a) Create a Model

Create a folder named Models and add a model class Employee.cs that matches the structure of the employee data from the Web API.

namespace MvcApp.Models
{
    public class Employee
    {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
        public string Position { get; set; }
        public decimal Salary { get; set; }
        // Other fields as per your database schema
    }
}

b) Create a Controller

• Right-click on the Controllers folder and select Add > Controller > MVC Controller - Empty.
• Name it EmployeeController.cs.

Example: EmployeeController.cs

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

namespace MvcApp.Controllers
{
    public class EmployeeController : Controller
    {
        private readonly EmployeeService _employeeService;

        public EmployeeController(EmployeeService employeeService)
        {
            _employeeService = employeeService;
        }

        // Display all employees
        public async Task<IActionResult> Index()
        {
            var employees = await _employeeService.GetEmployeesAsync();
            return View(employees);
        }

        // View details of a single employee
        public async Task<IActionResult> Details(int id)
        {
            var employee = await _employeeService.GetEmployeeByIdAsync(id);
            return View(employee);
        }

        // Create a new employee (GET)
        public IActionResult Create()
        {
            return View();
        }

        // Create a new employee (POST)
        [HttpPost]
        public async Task<IActionResult> Create(Employee employee)
        {
            if (ModelState.IsValid)
            {
                await _employeeService.CreateEmployeeAsync(employee);
                return RedirectToAction(nameof(Index));
            }
            return View(employee);
        }

        // Edit an employee (GET)
        public async Task<IActionResult> Edit(int id)
        {
            var employee = await _employeeService.GetEmployeeByIdAsync(id);
            return View(employee);
        }

        // Edit an employee (POST)
        [HttpPost]
        public async Task<IActionResult> Edit(int id, Employee employee)
        {
            if (ModelState.IsValid)
            {
                await _employeeService.UpdateEmployeeAsync(id, employee);
                return RedirectToAction(nameof(Index));
            }
            return View(employee);
        }

        // Delete an employee (GET)
        public async Task<IActionResult> Delete(int id)
        {
            var employee = await _employeeService.GetEmployeeByIdAsync(id);
            return View(employee);
        }

        // Delete an employee (POST)
        [HttpPost, ActionName("Delete")]
        public async Task<IActionResult> DeleteConfirmed(int id)
        {
            await _employeeService.DeleteEmployeeAsync(id);
            return RedirectToAction(nameof(Index));
        }
    }
}

c) Create Views for Employee

• Right-click the Views folder, add a folder named Employee, and add the following views: Index.cshtml, Create.cshtml, Edit.cshtml, Details.cshtml, and Delete.cshtml.

Example: Index.cshtml

@model IEnumerable<MvcApp.Models.Employee>

<h2>Employees List</h2>

<table class="table">
    <thead>
        <tr>
            <th>Name</th>
            <th>Position</th>
            <th>Salary</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var employee in Model)
        {
            <tr>
                <td>@employee.Name</td>
                <td>@employee.Position</td>
                <td>@employee.Salary</td>
                <td>
                    <a href="@Url.Action("Details", "Employee", new { id = employee.EmployeeId })">Details</a> |
                    <a href="@Url.Action("Edit", "Employee", new { id = employee.EmployeeId })">Edit</a> |
                    <a href="@Url.Action("Delete", "Employee", new { id = employee.EmployeeId })">Delete</a>
                </td>
            </tr>
        }
    </tbody>
</table>

<a href="@Url.Action("Create", "Employee")" class="btn btn-primary">Create New</a>

---

6. Run the Application

Now, when you run your .NET Core MVC app, you will see a list of employees fetched from the Web API. You can also create, edit, and delete employees by interacting with the API through the app.

This demonstrates how to consume a Web API in a .NET Core MVC application using HttpClient.