📌 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.
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.