Here’s how you can create Insert, Edit, Update, Delete operations in an ASP.NET MVC GridView using a single stored procedure:
Create Stored Procedure
First, create a stored procedure that handles all the CRUD operations for the Student table. Here’s an example:
sql Code:
CREATE PROCEDURE [dbo].[usp_StudentCRUD]
@StudentId INT = NULL,
@Stu_Firstname VARCHAR(50) = NULL,
@Stu_Lastname VARCHAR(50) = NULL,
@Stu_FathersName VARCHAR(50) = NULL,
@Address VARCHAR(100) = NULL,
@City VARCHAR(50) = NULL,
@Sex VARCHAR(10) = NULL,
@Email VARCHAR(50) = NULL,
@ContactNo VARCHAR(20) = NULL,
@Action VARCHAR(10) = NULL
AS
BEGIN
IF @Action = ‘Select’
SELECT FROM Student ORDER BY Stu_Firstname
ELSE IF @Action = ‘Insert’
INSERT INTO Student (Stu_Firstname, Stu_Lastname, Stu_FathersName, Address, City, Sex, Email, ContactNo)
VALUES (@Stu_Firstname, @Stu_Lastname, @Stu_FathersName, @Address, @City, @Sex, @Email, @ContactNo)
ELSE IF @Action = ‘Update’
UPDATE Student
SET Stu_Firstname = @Stu_Firstname,
Stu_Lastname = @Stu_Lastname,
Stu_FathersName = @Stu_FathersName,
Address = @Address,
City = @City,
Sex = @Sex,
Email = @Email,
ContactNo = @ContactNo
WHERE StudentId = @StudentId
ELSE IF @Action = ‘Delete’
DELETE FROM Student WHERE StudentId = @StudentId
END
This stored procedure takes parameters for each student field and an Action parameter to determine which operation to perform.
Create Model
Create a model class for the Student table:
csharp Code:
public class Student
{
public int StudentId { get; set; }
public string Stu_Firstname { get; set; }
public string Stu_Lastname { get; set; }
public string Stu_FathersName { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string Sex { get; set; }
public string Email { get; set; }
public string ContactNo { get; set; }
}
Create Controller
In the controller, create actions for each operation:
csharp_code:
public class StudentController : Controller
{
private string connectionString = “YourConnectionString”;
public ActionResult Index()
{
List<Student> students = GetStudents();
return View(students);
}
private List<Student> GetStudents()
{
List<Student> students = new List<Student>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(“usp_StudentCRUD”, connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue(“@Action”, “Select”);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Student student = new Student
{
StudentId = (int)reader[“StudentId”],
Stu_Firstname = reader[“Stu_Firstname”].ToString(),
Stu_Lastname = reader[“Stu_Lastname”].ToString(),
Stu_FathersName = reader[“Stu_FathersName”].ToString(),
Address = reader[“Address”].ToString(),
City = reader[“City”].ToString(),
Sex = reader[“Sex”].ToString(),
Email = reader[“Email”].ToString(),
ContactNo = reader[“ContactNo”].ToString()
};
students.Add(student);
}
}
return students;
}
[HttpPost]
public ActionResult Create(Student student)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(“usp_StudentCRUD”, connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue(“@Stu_Firstname”, student.Stu_Firstname);
command.Parameters.AddWithValue(“@Stu_Lastname”, student.Stu_Lastname);
command.Parameters.AddWithValue(“@Stu_FathersName”, student.Stu_FathersName);
command.Parameters.AddWithValue(“@Address”, student.Address);
command.Parameters.AddWithValue(“@City”, student.City);
command.Parameters.AddWithValue(“@Sex”, student.Sex);
command.Parameters.AddWithValue(“@Email”, student.Email);
command.Parameters.AddWithValue(“@ContactNo”, student.ContactNo);
command.Parameters.AddWithValue(“@Action”, “Insert”);
connection.Open();
command.ExecuteNonQuery();
}
return RedirectToAction(“Index”);
}
public ActionResult Edit(int id)
{
Student student = GetStudents().Find(s => s.StudentId == id);
return View(student);
}
[HttpPost]
public ActionResult Edit(Student student)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(“usp_StudentCRUD”, connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue(“@StudentId”, student.StudentId);
command.Parameters.AddWithValue(“@Stu_Firstname”, student.Stu_Firstname);
command.Parameters.AddWithValue(“@Stu_Lastname”, student.Stu_Lastname);
command.Parameters.AddWithValue(“@Stu_FathersName”, student.Stu_FathersName);
command.Parameters.AddWithValue(“@Address”, student.Address);
command.Parameters.AddWithValue(“@City”, student.City);
command.Parameters.AddWithValue(“@Sex”, student.Sex);
command.Parameters.AddWithValue(“@Email”, student.Email);
command.Parameters.AddWithValue(“@ContactNo”, student.ContactNo);
command.Parameters.AddWithValue(“@Action”, “Update”);
connection.Open();
command.ExecuteNonQuery();
}
return RedirectToAction(“Index”);
}
public ActionResult Delete(int id)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(“usp_StudentCRUD”, connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue(“@StudentId”, id);
command.Parameters.AddWithValue(“@Action”, “Delete”);
connection.Open();
command.ExecuteNonQuery();
}
return RedirectToAction(“Index”);
}
}
This controller has actions for displaying the student list, creating a new student, editing an existing student, and deleting a student.
Create View
Create a view to display the GridView:
xml_code:
@model IEnumerable<Student>
@{
ViewBag.Title = “Students”;
}
<h2>Students</h2>
<p>
@Html.ActionLink(“Create New”, “Create”)
</p>
<table class=”table”>
<tr>
<th>
@Html.DisplayNameFor(model => model.Stu_Firstname)
</th>
<th>
@Html.DisplayNameFor(model => model.Stu_Lastname)
</th>
<th>
@Html.DisplayNameFor(model => model.Stu_FathersName)
</th>
<th>
@Html.DisplayNameFor(model => model.Address)
</th>
<th>
@Html.DisplayNameFor(model => model.City)
</th>
<th>
@Html.DisplayNameFor(model => model.Sex)
</th>
<th>
@Html.DisplayNameFor(model => model.Email)
</th>
<th>
@Html.DisplayNameFor(model => model.ContactNo)
</th>
<th></th>
</tr>
@foreach (var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.Stu_Firstname)
</td>
<td>
@Html.DisplayFor(modelItem => item.Stu_Lastname)
</td>
<td>
@Html.DisplayFor(modelItem => item.Stu_FathersName)
</td>
<td>
@Html.DisplayFor(modelItem => item.Address)
</td>
<td>
@Html.DisplayFor(modelItem => item.City)
</td>
<td>
@Html.DisplayFor(modelItem => item.Sex)
</td>
<td>
@Html.DisplayFor(modelItem => item.Email)
</td>
<td>
@Html.DisplayFor(modelItem => item.ContactNo)
</td>
<td>
@Html.ActionLink(“Edit”, “Edit”, new { id = item.StudentId }) |
@Html.ActionLink(“Delete”, “Delete”, new { id = item.StudentId })
</td>
</tr>
}
</table>
This view has links for adding, modifying, and removing students along with a GridView format that shows the student data.
You may use a single stored method to implement CRUD operations in an ASP.NET MVC GridView by following these steps and utilizing the accompanying code snippets.
