ASP .Net Core CRUD operations without using Entity Framework Step By Step Guide

In this article, we will learn how to perform insert, update, and delete operations in ASP.NET Core without using Entity Framework. This tutorial utilizes ADO.NET commands to interact with SQL databases. 

You will gain a comprehensive understanding of ASP.NET Core applications with ADO.NET connectivity through step-by-step examples accompanied by images for each step. By the end of this article, you will have a thorough understaing of ASP.NET Core with ADO.NET. Let's begin, step by step.

Similar Post

• Crud operation in Asp .Net Core using Razor Pages Step By Step
• Crud operation in Asp.Net Core Mvc using jquery datatable with entity framework core database first

To illustrate this example, we have chosen the 'student' table as our model. Below is the SQL script for creating this table, which you can copy for your testing purposes.

CREATE TABLE Student (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100) NOT NULL,
    Email NVARCHAR(100) NOT NULL,
    City NVARCHAR(50) NOT NULL
);

INSERT INTO Student (Name, Email, City) VALUES
('Rahul Sharma', '[email protected]', 'Mumbai'),
('Priya Patel', '[email protected]', 'Delhi'),
('Aarav Singh', '[email protected]', 'Kolkata'),
('Sneha Gupta', '[email protected]', 'Bangalore');

NuGet\Install-Package System.Data.SqlClient -Version 4.8.6

using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace DemoWebApp.Controllers
{
    public class StudentController : Controller
    {
        private readonly string _connectionString = "Data Source=DESKTOP-MFLCOI2;Initial Catalog=TestDb;User ID=sa;Password=adk@1234";
        public IActionResult Index()
        {
            List<Student> students = new List<Student>();

            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                using (var command = new SqlCommand("SELECT * FROM Student", connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            students.Add(new Student
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                Name = reader["Name"].ToString(),
                                Email = reader["Email"].ToString(),
                                City = reader["City"].ToString()
                            });
                        }
                    }
                }
            }

            return View(students);
        }

        public IActionResult Create()
        {
            return View();
        }

        [HttpPost]
        public IActionResult Create(Student student)
        {
            if (ModelState.IsValid)
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    connection.Open();
                    using (var command = new SqlCommand("INSERT INTO Student (Name, Email, City) VALUES (@Name, @Email, @City)", connection))
                    {
                        command.Parameters.AddWithValue("@Name", student.Name);
                        command.Parameters.AddWithValue("@Email", student.Email);
                        command.Parameters.AddWithValue("@City", student.City);
                        command.ExecuteNonQuery();
                    }
                }
                return RedirectToAction("Index");
            }
            return View(student);
        }

        public IActionResult Edit(int id)
        {
            // Retrieve student information from database and populate the form for editing
            Student student = new Student();

            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                using (var command = new SqlCommand("SELECT * FROM Student WHERE Id = @Id", connection))
                {
                    command.Parameters.AddWithValue("@Id", id);
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            student.Id = Convert.ToInt32(reader["Id"]);
                            student.Name = reader["Name"].ToString();
                            student.Email = reader["Email"].ToString();
                            student.City = reader["City"].ToString();
                        }
                    }
                }
            }

            return View(student);
        }

        [HttpPost]
        public IActionResult Edit(Student student)
        {
            if (ModelState.IsValid)
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    connection.Open();
                    using (var command = new SqlCommand("UPDATE Student SET Name = @Name, Email = @Email, City = @City WHERE Id = @Id", connection))
                    {
                        command.Parameters.AddWithValue("@Name", student.Name);
                        command.Parameters.AddWithValue("@Email", student.Email);
                        command.Parameters.AddWithValue("@City", student.City);
                        command.Parameters.AddWithValue("@Id", student.Id);
                        command.ExecuteNonQuery();
                    }
                }
                return RedirectToAction("Index");
            }
            return View(student);
        }

        public IActionResult Delete(int id)
        {
            Student studentToDelete = GetStudentById(id);
            if (studentToDelete == null)
            {
                return NotFound();
            }
            return View(studentToDelete);
        }

        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public IActionResult DeleteConfirmed(int id)
        {
            // Retrieve student by id and delete it
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                using (var command = new SqlCommand("DELETE FROM Student WHERE Id = @Id", connection))
                {
                    command.Parameters.AddWithValue("@Id", id);
                    command.ExecuteNonQuery();
                }
            }
            return RedirectToAction("Index");
        }

        // Helper method to get student by id
        private Student GetStudentById(int id)
        {
            Student student = null;
            using (var connection = new SqlConnection(_connectionString))
            {
                connection.Open();
                using (var command = new SqlCommand("SELECT * FROM Student WHERE Id = @Id", connection))
                {
                    command.Parameters.AddWithValue("@Id", id);
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            student = new Student
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                Name = reader["Name"].ToString(),
                                Email = reader["Email"].ToString(),
                                City = reader["City"].ToString()
                            };
                        }
                    }
                }
            }
            return student;
        }
    }
}

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public string City { get; set; }
}

1. Create Views: Create corresponding views for the Index, Create, Edit, and Delete actions under the Views/Student folder.
2. Index.cshtml: Display a list of students with links to create, edit, and delete.
3. Create.cshtml: Form to create a new student.
4. Edit.cshtml: Form to edit an existing student.
5. Delete.cshtml: Confirmation page for deleting a student.

Index.cshtml

@{
    ViewData["Title"] = "Index";
    Layout = "~/Views/Shared/_Layout.cshtml";
}
@model List<Student>

<h2>Students</h2>

<a asp-action="Create">Add New</a>

<table class="table">
    <thead>
        <tr>
            <th>Id</th>
            <th>Name</th>
            <th>Email</th>
            <th>City</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var student in Model)
        {
            <tr>
                <td>@student.Id</td>
                <td>@student.Name</td>
                <td>@student.Email</td>
                <td>@student.City</td>
                <td>
                    <a asp-action="Edit" asp-route-id="@student.Id">Edit</a> |
                    <a asp-action="Delete" asp-route-id="@student.Id">Delete</a>
                </td>
            </tr>
        }
    </tbody>
</table>

@{
    ViewData["Title"] = "Create";
    Layout = "~/Views/Shared/_Layout.cshtml";
}
@model Student

<h2>Create Student</h2>

<form asp-action="Create" method="post">
    <div class="form-group">
        <label asp-for="Name">Name:</label>
        <input asp-for="Name" class="form-control" />
        <span asp-validation-for="Name" class="text-danger"></span>
    </div>
    <div class="form-group">
        <label asp-for="Email">Email:</label>
        <input asp-for="Email" class="form-control" />
        <span asp-validation-for="Email" class="text-danger"></span>
    </div>
    <div class="form-group">
        <label asp-for="City">City:</label>
        <input asp-for="City" class="form-control" />
        <span asp-validation-for="City" class="text-danger"></span>
    </div>
    <button type="submit" class="btn btn-primary">Create</button>
</form>

@{
    ViewData["Title"] = "Edit";
    Layout = "~/Views/Shared/_Layout.cshtml";
}
@model Student

<h2>Edit Student</h2>

<form asp-action="Edit" method="post">
    <input type="hidden" asp-for="Id" />
    <div class="form-group">
        <label asp-for="Name">Name:</label>
        <input asp-for="Name" class="form-control" />
        <span asp-validation-for="Name" class="text-danger"></span>
    </div>
    <div class="form-group">
        <label asp-for="Email">Email:</label>
        <input asp-for="Email" class="form-control" />
        <span asp-validation-for="Email" class="text-danger"></span>
    </div>
    <div class="form-group">
        <label asp-for="City">City:</label>
        <input asp-for="City" class="form-control" />
        <span asp-validation-for="City" class="text-danger"></span>
    </div>
    <button type="submit" class="btn btn-primary">Save</button>
</form>

@{
    ViewData["Title"] = "Delete";
    Layout = "~/Views/Shared/_Layout.cshtml";
}
@model Student

<h2>Delete Student</h2>
<p>Are you sure you want to delete this student?</p>
<dl class="row">
    <dt class="col-sm-2">Name:</dt>
    <dd class="col-sm-10">@Model.Name</dd>
    <dt class="col-sm-2">Email:</dt>
    <dd class="col-sm-10">@Model.Email</dd>
    <dt class="col-sm-2">City:</dt>
    <dd class="col-sm-10">@Model.City</dd>
</dl>
<form asp-action="Delete" method="post">
    <input type="hidden" asp-for="Id" />
    <button type="submit" class="btn btn-danger">Delete</button>
    <a asp-action="Index" class="btn btn-secondary">Cancel</a>
</form>