CRUD Operations Using ASP.NET Core MVC without Entity Framework | SQL

 A.Tech Stack

• ASP.NET Core MVC (.NET 6/7/8)

• SQL Server

• ADO.NET (SqlConnection, SqlCommand, etc.)

• Stored Procedures (optional but recommended)

• Bootstrap (for UI - optional)

B. Folder Structure

1.Controllers/
    EmployeeController.cs
2.Models/
    Employee.cs
3.DataAccess/
    DbHelper.cs
4.Views/
    Employee/
        Index.cshtml
        Create.cshtml
        Edit.cshtml
        Delete.cshtml
        Details.cshtml

 C. Create SQL Table

CREATE TABLE Employees (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100),
    Age INT,
    Department NVARCHAR(50)
);

D. Optional: Create Stored Procedures

CREATE PROCEDURE GetAllEmployees
AS
BEGIN
    SELECT * FROM Employees
END

CREATE PROCEDURE InsertEmployee
    @Name NVARCHAR(100),
    @Age INT,
    @Department NVARCHAR(50)
AS
BEGIN
    INSERT INTO Employees(Name, Age, Department)
    VALUES (@Name, @Age, @Department)
END

-- Add UpdateEmployee and DeleteEmployee as needed

 

E. Model - Employee.cs

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public string Department { get; set; }
}

 

F. Data Access Helper - DbHelper.cs

using System.Data;
using System.Data.SqlClient;
using YourNamespace.Models;

public class DbHelper
{
    private readonly string _connectionString;

    public DbHelper(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection");
    }

    public List GetAll()
    {
        List list = new();
        using SqlConnection conn = new(_connectionString);
        SqlCommand cmd = new("GetAllEmployees", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        conn.Open();
        SqlDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            list.Add(new Employee
            {
                Id = Convert.ToInt32(rdr["Id"]),
                Name = rdr["Name"].ToString(),
                Age = Convert.ToInt32(rdr["Age"]),
                Department = rdr["Department"].ToString()
            });
        }
        return list;
    }

    public void Insert(Employee emp)
    {
        using SqlConnection conn = new(_connectionString);
        SqlCommand cmd = new("InsertEmployee", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Name", emp.Name);
        cmd.Parameters.AddWithValue("@Age", emp.Age);
        cmd.Parameters.AddWithValue("@Department", emp.Department);
        conn.Open();
        cmd.ExecuteNonQuery();
    }

    // Add Update & Delete methods similarly
}
 

G. Controller - EmployeeController.cs

public class EmployeeController : Controller
{
    private readonly DbHelper _db;

    public EmployeeController(IConfiguration config)
    {
        _db = new DbHelper(config);
    }

    public IActionResult Index()
    {
        var data = _db.GetAll();
        return View(data);
    }

    public IActionResult Create() => View();

    [HttpPost]
    public IActionResult Create(Employee emp)
    {
        if (ModelState.IsValid)
        {
            _db.Insert(emp);
            return RedirectToAction("Index");
        }
        return View(emp);
    }

    // Add Edit, Delete, Details actions similarly
}

H. Views - Example: Index.cshtml

@model List

Employee List


Add New


   
       
           
       
   
   
        @foreach(var emp in Model)
        {
           
               
               
               
               
           
        }
   


Name	Age	Department	Action
@emp.Name	@emp.Age	@emp.Department	Edit |                     Delete

 

I. Connection String (in appsettings.json)

"ConnectionStrings": {
  "DefaultConnection": "Server=.;Database=YourDbName;Trusted_Connection=True;"
}
 

If you have any doubts, Dm me on Chat.