Introduction

Here, we are going to create a web application using ASP.NET Core MVC and ADO.NET. We will be creating a simple student record management system and performing CRUD operations on it.

Prerequisites

Creating the Table and Stored Procedures

We will be using a DB table to store all the records of the students.

First of all, we will create a database named "StudentManagement”.

  1. CREATE DATABASE StudentManagement

Then, we will create a table named “Student”.

  1. Create table Student(
  2. Id int IDENTITY(1,1) NOT NULL,
  3. FirstName varchar(50) NOT NULL,
  4. LastName varchar(50) NOT NULL,
  5. Email varchar(30) NOT NULL,
  6. Mobile varchar(20) NOT NULL,
  7. Address varchar(220) NULL,
  8. )

Now, we will create stored procedures to add, delete, update, and get student data.

To Insert a Student Record

  1. Create procedure spAddStudent
  2. (
  3. @FirstName VARCHAR(50),
  4. @LastName VARCHAR(50),
  5. @Email VARCHAR(30),
  6. @Mobile VARCHAR(20),
  7. @Address VARCHAR(220)
  8. )
  9. as
  10. Begin
  11. Insert into Student (FirstName,LastName,Email, Mobile,Address)
  12. Values (@FirstName,@LastName,@Email, @Mobile,@Address)
  13. End

To Update a Student Record

  1. Create procedure spUpdateStudent
  2. (
  3. @Id INTEGER ,
  4. @FirstName VARCHAR(50),
  5. @LastName VARCHAR(50),
  6. @Email VARCHAR(30),
  7. @Mobile VARCHAR(20),
  8. @Address VARCHAR(220)
  9. )
  10. as
  11. begin
  12. Update Student
  13. set FirstName=@FirstName,
  14. LastName=@LastName,
  15. Email=@Email,
  16. Mobile=@Mobile,
  17. Address=@Address
  18. where Id=@Id
  19. End

To Delete a Student Record

  1. Create procedure spDeleteStudent
  2. (
  3. @Id int
  4. )
  5. as
  6. begin
  7. Delete from Student where Id=@Id
  8. End

To View all Student Records

  1. Create procedure spGetAllStudent
  2. as
  3. Begin
  4. select *
  5. from Student
  6. order by Id
  7. End

Our database part has been completed.

Create the ASP.NET MVC Web Application

Now, we are going to create an ASP.NET MVC Web Application. The project name is “StudentRecordManagementSystem”.

CRUD Operations Using ASP.NET Core And ADO.NET

Click OK. The below window will appear on the screen.

CRUD Operations Using ASP.NET Core And ADO.NET

Click OK. The solution creation is done with the loading of all needed files. Given below is the picture of the solution structure.

CRUD Operations Using ASP.NET Core And ADO.NET

What is MVC

  1. Model
    Classes that represent the data of the solution
  1. View
    Simple word view means UI (User Interface) dynamically generates HTML responses.
  1. Controller
    A Controller is a link between User and the System. It handles incoming browser requests and after processing it using model data or specific task, returns a response to the browser.

Create a folder named “Utility” in the project. Now, we will create a class named “ConnectionString” within the Utility folder.

  1. public static class ConnectionString
  2. {
  3. private static string cName = "Data Source=.; Initial Catalog=StudentManagement;User ID=sa;Password=123";
  4. public static string CName { get => cName;
  5. }
  6. }

After that, we will create a class named “Student” within model folder.

  1. public class Student
  2. {
  3. public int Id { set; get; }
  4. [Required]
  5. public string FirstName { set; get; }
  6. [Required]
  7. public string LastName { set; get; }
  8. [Required]
  9. public string Email { set; get; }
  10. [Required]
  11. public string Mobile { set; get; }
  12. public string Address { set; get; }
  13. }

We will Rebuild our solution and create a “StudentController” within Controller folder. Right-Add-Controller-Select MVC Controller with read/write actions and click add.

CRUD Operations Using ASP.NET Core And ADO.NET

Another window will appear on screen

CRUD Operations Using ASP.NET Core And ADO.NET

Our StudentController has been created.

  1. public class StudentController : Controller
  2. {
  3. // GET: Student
  4. public ActionResult Index()
  5. {
  6. return View();
  7. }
  9. // GET: Student/Details/5
  10. public ActionResult Details(int id)
  11. {
  12. return View();
  13. }
  15. // GET: Student/Create
  16. public ActionResult Create()
  17. {
  18. return View();
  19. }
  21. // POST: Student/Create
  22. [HttpPost]
  23. [ValidateAntiForgeryToken]
  24. public ActionResult Create(IFormCollection collection)
  25. {
  26. try
  27. {
  28. // TODO: Add insert logic here
  30. return RedirectToAction(nameof(Index));
  31. }
  32. catch
  33. {
  34. return View();
  35. }
  36. }
  38. // GET: Student/Edit/5
  39. public ActionResult Edit(int id)
  40. {
  41. return View();
  42. }
  44. // POST: Student/Edit/5
  45. [HttpPost]
  46. [ValidateAntiForgeryToken]
  47. public ActionResult Edit(int id, IFormCollection collection)
  48. {
  49. try
  50. {
  51. // TODO: Add update logic here
  53. return RedirectToAction(nameof(Index));
  54. }
  55. catch
  56. {
  57. return View();
  58. }
  59. }
  61. // GET: Student/Delete/5
  62. public ActionResult Delete(int id)
  63. {
  64. return View();
  65. }
  67. // POST: Student/Delete/5
  68. [HttpPost]
  69. [ValidateAntiForgeryToken]
  70. public ActionResult Delete(int id, IFormCollection collection)
  71. {
  72. try
  73. {
  74. // TODO: Add delete logic here
  76. return RedirectToAction(nameof(Index));
  77. }
  78. catch
  79. {
  80. return View();
  81. }
  82. }
  83. }

We have to work with the database so we will create a data access layer class within model folder named “StudentDataAccessLayer”

  1. public class StudentDataAccessLayer
  2. {
  3. string connectionString = ConnectionString.CName;
  5. public IEnumerable<Student> GetAllStudent()
  6. {
  7. List<Student> lstStudent = new List<Student>();
  8. using (SqlConnection con = new SqlConnection(connectionString))
  9. {
  10. SqlCommand cmd = new SqlCommand("spGetAllStudent", con);
  11. cmd.CommandType = CommandType.StoredProcedure;
  12. con.Open();
  13. SqlDataReader rdr = cmd.ExecuteReader();
  15. while (rdr.Read())
  16. {
  17. Student student = new Student();
  18. student.Id = Convert.ToInt32(rdr["Id"]);
  19. student.FirstName = rdr["FirstName"].ToString();
  20. student.LastName = rdr["LastName"].ToString();
  21. student.Email = rdr["Email"].ToString();
  22. student.Mobile = rdr["Mobile"].ToString();
  23. student.Address = rdr["Address"].ToString();
  25. lstStudent.Add(student);
  26. }
  27. con.Close();
  28. }
  29. return lstStudent;
  30. }
  31. public void AddStudent(Student student)
  32. {
  33. using (SqlConnection con = new SqlConnection(connectionString))
  34. {
  35. SqlCommand cmd = new SqlCommand("spAddStudent", con);
  36. cmd.CommandType = CommandType.StoredProcedure;
  38. cmd.Parameters.AddWithValue("@FirstName", student.FirstName);
  39. cmd.Parameters.AddWithValue("@LastName", student.LastName);
  40. cmd.Parameters.AddWithValue("@Email", student.Email);
  41. cmd.Parameters.AddWithValue("@Mobile", student.Mobile);
  42. cmd.Parameters.AddWithValue("@Address", student.Address);
  43. con.Open();
  44. cmd.ExecuteNonQuery();
  45. con.Close();
  46. }
  47. }
  49. public void UpdateStudent(Student student)
  50. {
  51. using (SqlConnection con = new SqlConnection(connectionString))
  52. {
  53. SqlCommand cmd = new SqlCommand("spUpdateStudent", con);
  54. cmd.CommandType = CommandType.StoredProcedure;
  56. cmd.Parameters.AddWithValue("@Id", student.Id);
  57. cmd.Parameters.AddWithValue("@FirstName", student.FirstName);
  58. cmd.Parameters.AddWithValue("@LastName", student.LastName);
  59. cmd.Parameters.AddWithValue("@Email", student.Email);
  60. cmd.Parameters.AddWithValue("@Mobile", student.Mobile);
  61. cmd.Parameters.AddWithValue("@Address", student.Address);
  62. con.Open();
  63. cmd.ExecuteNonQuery();
  64. con.Close();
  65. }
  66. }
  68. public Student GetStudentData(int? id)
  69. {
  70. Student student = new Student();
  72. using (SqlConnection con = new SqlConnection(connectionString))
  73. {
  74. string sqlQuery = "SELECT * FROM Student WHERE Id= " + id;
  75. SqlCommand cmd = new SqlCommand(sqlQuery, con);
  76. con.Open();
  77. SqlDataReader rdr = cmd.ExecuteReader();
  79. while (rdr.Read())
  80. {
  81. student.Id = Convert.ToInt32(rdr["Id"]);
  82. student.FirstName = rdr["FirstName"].ToString();
  83. student.LastName = rdr["LastName"].ToString();
  84. student.Email = rdr["Email"].ToString();
  85. student.Mobile = rdr["Mobile"].ToString();
  86. student.Address = rdr["Address"].ToString();
  87. }
  88. }
  89. return student;
  90. }
  92. public void DeleteStudent(int? id)
  93. {
  94. using (SqlConnection con = new SqlConnection(connectionString))
  95. {
  96. SqlCommand cmd = new SqlCommand("spDeleteStudent", con);
  97. cmd.CommandType = CommandType.StoredProcedure;
  98. cmd.Parameters.AddWithValue("@Id", id);
  99. con.Open();
  100. cmd.ExecuteNonQuery();
  101. con.Close();
  102. }
  103. }
  104. }

Create Action

Now we will work with Create Action within Student Controller. There are two Create Actions one is GET and another is POST. Now we will create a view for creating action.

Before creating a view we will create a constructor

  1. StudentDataAccessLayer studentDataAccessLayer = null;
  2. public StudentController()
  3. {
  4. studentDataAccessLayer = new StudentDataAccessLayer();
  5. }

Right click on create (GET) action then click add view; the below window will appear on the screen.

CRUD Operations Using ASP.NET Core And ADO.NET

Click add

  1. @model StudentRecordManagementSystem.Models.Student
  2. @{
  3. ViewData["Title"] = "Create";
  4. }
  5. <h2>Create</h2>
  7. <h4>Student</h4>
  8. <hr />
  9. <div class="row">
  10. <div class="col-md-4">
  11. <form asp-action="Create">
  12. <div asp-validation-summary="ModelOnly" class="text-danger"></div>
  13. <div class="form-group">
  14. <label asp-for="Id" class="control-label"></label>
  15. <input asp-for="Id" class="form-control" />
  16. <span asp-validation-for="Id" class="text-danger"></span>
  17. </div>
  18. <div class="form-group">
  19. <label asp-for="FirstName" class="control-label"></label>
  20. <input asp-for="FirstName" class="form-control" />
  21. <span asp-validation-for="FirstName" class="text-danger"></span>
  22. </div>
  23. <div class="form-group">
  24. <label asp-for="LastName" class="control-label"></label>
  25. <input asp-for="LastName" class="form-control" />
  26. <span asp-validation-for="LastName" class="text-danger"></span>
  27. </div>
  28. <div class="form-group">
  29. <label asp-for="Email" class="control-label"></label>
  30. <input asp-for="Email" class="form-control" />
  31. <span asp-validation-for="Email" class="text-danger"></span>
  32. </div>
  33. <div class="form-group">
  34. <label asp-for="Mobile" class="control-label"></label>
  35. <input asp-for="Mobile" class="form-control" />
  36. <span asp-validation-for="Mobile" class="text-danger"></span>
  37. </div>
  38. <div class="form-group">
  39. <label asp-for="Address" class="control-label"></label>
  40. <input asp-for="Address" class="form-control" />
  41. <span asp-validation-for="Address" class="text-danger"></span>
  42. </div>
  43. <div class="form-group">
  44. <input type="submit" value="Create" class="btn btn-default" />
  45. </div>
  46. </form>
  47. </div>
  48. </div>
  50. <div>
  51. <a asp-action="Index">Back to List</a>
  52. </div>
  54. @section Scripts {
  55. @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
  56. }

Now run our application,

CRUD Operations Using ASP.NET Core And ADO.NET

We will remove Id field from view. We have done auto increment of Id field in database.

Now we will work with Create (POST),

  1. [HttpPost]
  2. [ValidateAntiForgeryToken]
  3. public ActionResult Create(Student student)
  4. {
  5. try
  6. {
  7. // TODO: Add insert logic here
  8. studentDataAccessLayer.AddStudent(student);
  10. return RedirectToAction(nameof(Index));
  11. }
  12. catch(Exception ex)
  13. {
  14. return View();
  15. }
  16. }

IndexAction

We have to call GetAllStudent method from StudentDataAccessLayer class for getting all students in Index action,

  1. public ActionResult Index()
  2. {
  3. IEnumerable<Student> students = studentDataAccessLayer.GetAllStudent();
  4. return View(students);
  5. }

Right click on Index action then click add view and the below window will appear on screen.

CRUD Operations Using ASP.NET Core And ADO.NET

Click add

  1. @model IEnumerable<StudentRecordManagementSystem.Models.Student>
  2. @{
  3. ViewData["Title"] = "Index";
  4. }
  5. <h2>Index</h2>
  7. <p>
  8. <a asp-action="Create">Create New</a>
  9. </p>
  10. <table class="table">
  11. <thead>
  12. <tr>
  13. <th>
  14. @Html.DisplayNameFor(model => model.Id)
  15. </th>
  16. <th>
  17. @Html.DisplayNameFor(model => model.FirstName)
  18. </th>
  19. <th>
  20. @Html.DisplayNameFor(model => model.LastName)
  21. </th>
  22. <th>
  23. @Html.DisplayNameFor(model => model.Email)
  24. </th>
  25. <th>
  26. @Html.DisplayNameFor(model => model.Mobile)
  27. </th>
  28. <th>
  29. @Html.DisplayNameFor(model => model.Address)
  30. </th>
  31. <th></th>
  32. </tr>
  33. </thead>
  34. <tbody>
  35. @foreach (var item in Model) {
  36. <tr>
  37. <td>
  38. @Html.DisplayFor(modelItem => item.Id)
  39. </td>
  40. <td>
  41. @Html.DisplayFor(modelItem => item.FirstName)
  42. </td>
  43. <td>
  44. @Html.DisplayFor(modelItem => item.LastName)
  45. </td>
  46. <td>
  47. @Html.DisplayFor(modelItem => item.Email)
  48. </td>
  49. <td>
  50. @Html.DisplayFor(modelItem => item.Mobile)
  51. </td>
  52. <td>
  53. @Html.DisplayFor(modelItem => item.Address)
  54. </td>
  55. <td>
  56. @Html.ActionLink("Edit", "Edit", new { id=item.Id }) |
  57. @Html.ActionLink("Details", "Details", new { id=item.Id }) |
  58. @Html.ActionLink("Delete", "Delete", new { id=item.Id })
  59. </td>
  60. </tr>
  61. }
  62. </tbody>
  63. </table>

Now we will save a student and see the student in list.

Save a student:

CRUD Operations Using ASP.NET Core And ADO.NET

Show student list:

CRUD Operations Using ASP.NET Core And ADO.NET

EditAction

Now we will work with Edit Action within Student Controller. There are two Edit Actions; one is GET and another is POST. Now we will create a view for creating action.

We have to call GetStudentData method from StudentDataAccessLayer class for getting student by Id.

  1. public ActionResult Edit(int id)
  2. {
  3. Student student = studentDataAccessLayer.GetStudentData(id);
  4. return View(student);
  5. }

Right click on Edit (GET) action then click add view; the below window will appear on screen.

CRUD Operations Using ASP.NET Core And ADO.NET

Click Add

  1. @model StudentRecordManagementSystem.Models.Student
  2. @{
  3. ViewData["Title"] = "Edit";
  4. }
  5. <h2>Edit</h2>
  7. <h4>Student</h4>
  8. <hr />
  9. <div class="row">
  10. <div class="col-md-4">
  11. <form asp-action="Edit">
  12. <div asp-validation-summary="ModelOnly" class="text-danger"></div>
  13. <div class="form-group">
  14. <label asp-for="Id" class="control-label"></label>
  15. <input asp-for="Id" class="form-control" readonly/>
  16. <span asp-validation-for="Id" class="text-danger"></span>
  17. </div>
  18. <div class="form-group">
  19. <label asp-for="FirstName" class="control-label"></label>
  20. <input asp-for="FirstName" class="form-control" />
  21. <span asp-validation-for="FirstName" class="text-danger"></span>
  22. </div>
  23. <div class="form-group">
  24. <label asp-for="LastName" class="control-label"></label>
  25. <input asp-for="LastName" class="form-control" />
  26. <span asp-validation-for="LastName" class="text-danger"></span>
  27. </div>
  28. <div class="form-group">
  29. <label asp-for="Email" class="control-label"></label>
  30. <input asp-for="Email" class="form-control" />
  31. <span asp-validation-for="Email" class="text-danger"></span>
  32. </div>
  33. <div class="form-group">
  34. <label asp-for="Mobile" class="control-label"></label>
  35. <input asp-for="Mobile" class="form-control" />
  36. <span asp-validation-for="Mobile" class="text-danger"></span>
  37. </div>
  38. <div class="form-group">
  39. <label asp-for="Address" class="control-label"></label>
  40. <input asp-for="Address" class="form-control" />
  41. <span asp-validation-for="Address" class="text-danger"></span>
  42. </div>
  43. <div class="form-group">
  44. <input type="submit" value="Update" class="btn btn-default" />
  45. </div>
  46. </form>
  47. </div>
  48. </div>
  50. <div>
  51. <a asp-action="Index">Back to List</a>
  52. </div>
  54. @section Scripts {
  55. @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
  56. }

Now we will work with Edit (POST):

  1. [HttpPost]
  2. [ValidateAntiForgeryToken]
  3. public ActionResult Edit(Student student)
  4. {
  5. try
  6. {
  7. // TODO: Add update logic here
  8. studentDataAccessLayer.UpdateStudent(student);
  9. return RedirectToAction(nameof(Index));
  10. }
  11. catch
  12. {
  13. return View();
  14. }
  15. }

Edit option has been done. Now we will test if it works or not.

CRUD Operations Using ASP.NET Core And ADO.NET
CRUD Operations Using ASP.NET Core And ADO.NET

It is working fine. Now we will work with the Delete action.

Delete action

Now we will work with Delete Action within Student Controller. There are two Delete Actions, one is GET and another is POST. Now we will create a view for deleting action.

We have to call GetStudentData method from StudentDataAccessLayer class for getting student by Id.

  1. public ActionResult Delete(int id)
  2. {
  3. Student student = studentDataAccessLayer.GetStudentData(id);
  4. return View(student);
  5. }

Right click on Delete (GET) action then click add view; the below window will appear on screen.

CRUD Operations Using ASP.NET Core And ADO.NET

Click Add

  1. @model StudentRecordManagementSystem.Models.Student
  3. @{
  4. ViewData["Title"] = "Delete";
  5. }
  7. <h2>Delete</h2>
  9. <h3>Are you sure you want to delete this?</h3>
  10. <div>
  11. <h4>Student</h4>
  12. <hr />
  13. <dl class="dl-horizontal">
  14. <dt>
  15. @Html.DisplayNameFor(model => model.Id)
  16. </dt>
  17. <dd>
  18. @Html.DisplayFor(model => model.Id)
  19. </dd>
  20. <dt>
  21. @Html.DisplayNameFor(model => model.FirstName)
  22. </dt>
  23. <dd>
  24. @Html.DisplayFor(model => model.FirstName)
  25. </dd>
  26. <dt>
  27. @Html.DisplayNameFor(model => model.LastName)
  28. </dt>
  29. <dd>
  30. @Html.DisplayFor(model => model.LastName)
  31. </dd>
  32. <dt>
  33. @Html.DisplayNameFor(model => model.Email)
  34. </dt>
  35. <dd>
  36. @Html.DisplayFor(model => model.Email)
  37. </dd>
  38. <dt>
  39. @Html.DisplayNameFor(model => model.Mobile)
  40. </dt>
  41. <dd>
  42. @Html.DisplayFor(model => model.Mobile)
  43. </dd>
  44. <dt>
  45. @Html.DisplayNameFor(model => model.Address)
  46. </dt>
  47. <dd>
  48. @Html.DisplayFor(model => model.Address)
  49. </dd>
  50. </dl>
  52. <form asp-action="Delete">
  53. <input type="submit" value="Delete" class="btn btn-default" /> |
  54. <a asp-action="Index">Back to List</a>
  55. </form>
  56. </div>

Now we will work with Delete (POST)

  1. [HttpPost]
  2. [ValidateAntiForgeryToken]
  3. public ActionResult Delete(Student student)
  4. {
  5. try
  6. {
  7. // TODO: Add delete logic here
  8. studentDataAccessLayer.DeleteStudent(student.Id);
  9. return RedirectToAction(nameof(Index));
  10. }
  11. catch
  12. {
  13. return View();
  14. }
  15. }

Details Action

We have to call GetStudentData method from StudentDataAccessLayer class for getting student by Id in Index action

  1. public ActionResult Details(int id)
  2. {
  3. Student student = studentDataAccessLayer.GetStudentData(id);
  4. return View(student);
  5. }

Right click on Details action then click add view; the below window will appear on screen.

CRUD Operations Using ASP.NET Core And ADO.NET

Click add

  1. @model StudentRecordManagementSystem.Models.Student
  2. @{
  3. ViewData["Title"] = "Details";
  4. }
  6. <h2>Details</h2>
  8. <div>
  9. <h4>Student</h4>
  10. <hr />
  11. <dl class="dl-horizontal">
  12. <dt>
  13. @Html.DisplayNameFor(model => model.Id)
  14. </dt>
  15. <dd>
  16. @Html.DisplayFor(model => model.Id)
  17. </dd>
  18. <dt>
  19. @Html.DisplayNameFor(model => model.FirstName)
  20. </dt>
  21. <dd>
  22. @Html.DisplayFor(model => model.FirstName)
  23. </dd>
  24. <dt>
  25. @Html.DisplayNameFor(model => model.LastName)
  26. </dt>
  27. <dd>
  28. @Html.DisplayFor(model => model.LastName)
  29. </dd>
  30. <dt>
  31. @Html.DisplayNameFor(model => model.Email)
  32. </dt>
  33. <dd>
  34. @Html.DisplayFor(model => model.Email)
  35. </dd>
  36. <dt>
  37. @Html.DisplayNameFor(model => model.Mobile)
  38. </dt>
  39. <dd>
  40. @Html.DisplayFor(model => model.Mobile)
  41. </dd>
  42. <dt>
  43. @Html.DisplayNameFor(model => model.Address)
  44. </dt>
  45. <dd>
  46. @Html.DisplayFor(model => model.Address)
  47. </dd>
  48. </dl>
  49. </div>
  50. <div>
  51. @Html.ActionLink("Edit", "Edit", new { id = Model.Id }) |
  52. <a asp-action="Index">Back to List</a>
  53. </div>

CRUD Operations Using ASP.NET Core And ADO.NET

I hope this will be helpful.