CRUD Operations in ASP.NET MVC Using ADO.NET

📌 Introduction

In real-world web applications, we need to:

Insert data
Display data
Update data
Delete data

This is called CRUD operations.

In this article, we will build a Student Management System using:

ASP.NET MVC
ADO.NET
SQL Server

Everything is explained in simple words for beginners.

🧱 STEP 1: Create Database

Open SQL Server and run:

CREATE DATABASE StudentMVCDB;

USE StudentMVCDB;

CREATE TABLE Students
(
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(100),
    Email NVARCHAR(100),
    City NVARCHAR(100)
);

✅ Explanation

IDENTITY(1,1) → Auto-increment ID
NVARCHAR(100) → Stores text data
PRIMARY KEY → Unique identifier

🖥 STEP 2: Create MVC Project

Open Visual Studio
Create new ASP.NET Web Application
Choose MVC Template

🔗 STEP 3: Add Connection String

Open Web.config

<connectionStrings>
  <add name="dbcs"
       connectionString="Data Source=YOUR_SERVER;Initial Catalog=StudentMVCDB;Integrated Security=True"
       providerName="System.Data.SqlClient"/>
</connectionStrings>

✅ Explanation

Data Source → SQL Server name
Initial Catalog → Database name
Integrated Security=True → Windows authentication

📁 STEP 4: Create Model

Right click Models → Add Class → Student.cs

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

✅ Explanation

This class represents the Students table.

Id → Primary key
Name, Email, City → Columns of table

Model is used to transfer data between Controller and View.

🎮 STEP 5: Create Controller

Right click Controllers → Add → MVC Empty Controller

Name: StudentController

Add namespaces:

using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
using YourProjectName.Models;

📌 READ (Display Data)

Code

public ActionResult Index()
{
    string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;

    List<Student> students = new List<Student>();

    using (SqlConnection con = new SqlConnection(cs))
    {
        string query = "SELECT * FROM Students";
        SqlCommand cmd = new SqlCommand(query, con);

        con.Open();

        SqlDataReader dr = cmd.ExecuteReader();

        while (dr.Read())
        {
            Student student = new Student();

            student.Id = Convert.ToInt32(dr["Id"]);
            student.Name = dr["Name"].ToString();
            student.Email = dr["Email"].ToString();
            student.City = dr["City"].ToString();

            students.Add(student);
        }
    }

    return View(students);
}

🔎 Line-by-Line Explanation

ConfigurationManager... → Gets connection string
List<Student> → Stores multiple students
SqlConnection → Connects to SQL Server
SqlCommand → Executes SQL query
ExecuteReader() → Reads data
while(dr.Read()) → Reads row one by one
students.Add() → Adds data to list
return View(students) → Sends data to View

🖼 Index View (Display Data)

@model IEnumerable<YourProjectName.Models.Student>

<h2>Student List</h2>

<p>@Html.ActionLink("Add New Student", "Create")</p>

<table border="1">
<tr>
    <th>Id</th>
    <th>Name</th>
    <th>Email</th>
    <th>City</th>
    <th>Action</th>
</tr>

@foreach (var item in Model)
{
<tr>
    <td>@item.Id</td>
    <td>@item.Name</td>
    <td>@item.Email</td>
    <td>@item.City</td>
    <td>
        @Html.ActionLink("Edit", "Edit", new { id = item.Id }) |
        @Html.ActionLink("Delete", "Delete", new { id = item.Id })
    </td>
</tr>
}
</table>

✅ Explanation

@model IEnumerable<Student> → Accepts list of students
foreach → Loops through list
ActionLink → Creates Edit & Delete links

📌 CREATE (Insert Data)

GET Method

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

Explanation

Shows empty form.

POST Method

[HttpPost]
public ActionResult Create(Student student)
{
    string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;

    using (SqlConnection con = new SqlConnection(cs))
    {
        string query = "INSERT INTO Students(Name,Email,City) VALUES(@Name,@Email,@City)";
        SqlCommand cmd = new SqlCommand(query, con);

        cmd.Parameters.AddWithValue("@Name", student.Name);
        cmd.Parameters.AddWithValue("@Email", student.Email);
        cmd.Parameters.AddWithValue("@City", student.City);

        con.Open();
        cmd.ExecuteNonQuery();
    }

    return RedirectToAction("Index");
}

Explanation

@Name → SQL parameter
AddWithValue() → Sends value safely
ExecuteNonQuery() → Executes Insert
RedirectToAction() → Goes back to list page

📌 UPDATE

GET

public ActionResult Edit(int id)
{
    string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
    Student student = new Student();

    using (SqlConnection con = new SqlConnection(cs))
    {
        string query = "SELECT * FROM Students WHERE Id=@Id";
        SqlCommand cmd = new SqlCommand(query, con);
        cmd.Parameters.AddWithValue("@Id", id);

        con.Open();
        SqlDataReader dr = cmd.ExecuteReader();

        if (dr.Read())
        {
            student.Id = Convert.ToInt32(dr["Id"]);
            student.Name = dr["Name"].ToString();
            student.Email = dr["Email"].ToString();
            student.City = dr["City"].ToString();
        }
    }

    return View(student);
}

POST

[HttpPost]
public ActionResult Edit(Student student)
{
    string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;

    using (SqlConnection con = new SqlConnection(cs))
    {
        string query = "UPDATE Students SET Name=@Name, Email=@Email, City=@City WHERE Id=@Id";
        SqlCommand cmd = new SqlCommand(query, con);

        cmd.Parameters.AddWithValue("@Id", student.Id);
        cmd.Parameters.AddWithValue("@Name", student.Name);
        cmd.Parameters.AddWithValue("@Email", student.Email);
        cmd.Parameters.AddWithValue("@City", student.City);

        con.Open();
        cmd.ExecuteNonQuery();
    }

    return RedirectToAction("Index");
}

📌 DELETE

public ActionResult Delete(int id)
{
    string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;

    using (SqlConnection con = new SqlConnection(cs))
    {
        string query = "DELETE FROM Students WHERE Id=@Id";
        SqlCommand cmd = new SqlCommand(query, con);

        cmd.Parameters.AddWithValue("@Id", id);

        con.Open();
        cmd.ExecuteNonQuery();
    }

    return RedirectToAction("Index");
}

🔐 Why We Use Parameters?

We use:

cmd.Parameters.AddWithValue()

Because:

Prevents SQL Injection
Improves security
Safe coding practice

🎉 Final Output

You will have:

✔ Student list page

✔ Add student form

✔ Edit student

✔ Delete student

Complete working MVC CRUD Application.