In this article we are going to create a web application using ASP.NET Core MVC with the help of Visual Studio Code and ADO.NET. We will be creating a sample Employee Record Management System and performing CRUD operation on it.
Before proceeding further i would recommend to download the source code from Github.
We will be using a DB table to store all the records of employees.
Open SQL Server and use the following script to create tblEmployee table.
Create table tblEmployee( EmployeeId int IDENTITY(1,1) NOT NULL, Name varchar(20) NOT NULL, City varchar(20) NOT NULL, Department varchar(20) NOT NULL, Gender varchar(6) NOT NULL )
Now, we will create stored procedures to add, delete, update, and get employee data.
Create procedure spAddEmployee ( @Name VARCHAR(20), @City VARCHAR(20), @Department VARCHAR(20), @Gender VARCHAR(6) ) as Begin Insert into tblEmployee (Name,City,Department, Gender) Values (@Name,@City,@Department, @Gender) End
Create procedure spUpdateEmployee ( @EmpId INTEGER , @Name VARCHAR(20), @City VARCHAR(20), @Department VARCHAR(20), @Gender VARCHAR(6) ) as begin Update tblEmployee set Name=@Name, City=@City, Department=@Department, Gender=@Gender where EmployeeId=@EmpId End
Create procedure spDeleteEmployee ( @EmpId int ) as begin Delete from tblEmployee where EmployeeId=@EmpId End
Create procedure spGetAllEmployees as Begin select * from tblEmployee order by EmployeeId End
Now, our Database part has been completed. So, we will proceed to create the MVC application using Visual Studio code.
We will be creating a source project from the terminal window in Visual Studio Code. Open VS code and navigate to view >> Integrated Terminal.
This will open the terminal window as shown in the image below.
Type the following sequence of commands in the terminal window. It will create our MVC application “MvcAdoDemo”
Now open this “MvcAdoDemo” project file using VS code. If it prompts the message “Required assets to build and debug are missing from MvcAdoDemo. Add them?”, select “Yes”.
You can observe in the solution explorer that we already have folders created with name Controllers, Models and Views. We will be adding our code files in these folders only.
using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.Linq; using System.Threading.Tasks; namespace MVCAdoDemo.Models { public class Employee { public int ID { get; set; } [Required] public string Name { get; set; } [Required] public string Gender { get; set; } [Required] public string Department { get; set; } [Required] public string City { get; set; } } }
Open EmployeeDataAccessLayer.cs and put the following code to handle database operations. Make sure to put your own connection string
using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Threading.Tasks; namespace MVCAdoDemo.Models { public class EmployeeDataAccessLayer { string connectionString = "Your Connection String"; //To View all employees details public IEnumerable<Employee> GetAllEmployees() { List<Employee> lstemployee = new List<Employee>(); using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("spGetAllEmployees", con); cmd.CommandType = CommandType.StoredProcedure; con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Employee employee = new Employee(); employee.ID = Convert.ToInt32(rdr["EmployeeID"]); employee.Name = rdr["Name"].ToString(); employee.Gender = rdr["Gender"].ToString(); employee.Department = rdr["Department"].ToString(); employee.City = rdr["City"].ToString(); lstemployee.Add(employee); } con.Close(); } return lstemployee; } //To Add new employee record public void AddEmployee(Employee employee) { using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("spAddEmployee", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@Name", employee.Name); cmd.Parameters.AddWithValue("@Gender", employee.Gender); cmd.Parameters.AddWithValue("@Department", employee.Department); cmd.Parameters.AddWithValue("@City", employee.City); con.Open(); cmd.ExecuteNonQuery(); con.Close(); } } //To Update the records of a particluar employee public void UpdateEmployee(Employee employee) { using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("spUpdateEmployee", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@EmpId", employee.ID); cmd.Parameters.AddWithValue("@Name", employee.Name); cmd.Parameters.AddWithValue("@Gender", employee.Gender); cmd.Parameters.AddWithValue("@Department", employee.Department); cmd.Parameters.AddWithValue("@City", employee.City); con.Open(); cmd.ExecuteNonQuery(); con.Close(); } } //Get the details of a particular employee public Employee GetEmployeeData(int? id) { Employee employee = new Employee(); using (SqlConnection con = new SqlConnection(connectionString)) { string sqlQuery = "SELECT * FROM tblEmployee WHERE EmployeeID= " + id; SqlCommand cmd = new SqlCommand(sqlQuery, con); con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { employee.ID = Convert.ToInt32(rdr["EmployeeID"]); employee.Name = rdr["Name"].ToString(); employee.Gender = rdr["Gender"].ToString(); employee.Department = rdr["Department"].ToString(); employee.City = rdr["City"].ToString(); } } return employee; } //To Delete the record on a particular employee public void DeleteEmployee(int? id) { using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("spDeleteEmployee", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@EmpId", id); con.Open(); cmd.ExecuteNonQuery(); con.Close(); } } } }
To use ADO.NET functionalities in VS code we need to add the nuget package reference toSystem.Data.SqlClient. Open MvcAdoDemo.csproj file and put following code into it.
<PackageReference Include="System.Data.SqlClient" Version="4.4.0" />
Put this code in the location highlighted in the image below.
To add views for our controller class, we need to create a folder inside Views folder with the same name as our controller and then add our views to that folder.
Now our Views folder will look like this
@model IEnumerable<MVCAdoDemo.Models.Employee> @{ ViewData["Title"] = "Index"; } <h2>Index</h2> <p> <a asp-action="Create">Create New</a> </p> <table class="table"> <thead> <tr> <th> @Html.DisplayNameFor(model => model.Name) </th> <th> @Html.DisplayNameFor(model => model.Gender) </th> <th> @Html.DisplayNameFor(model => model.Department) </th> <th> @Html.DisplayNameFor(model => model.City) </th> <th></th> </tr> </thead> <tbody> @foreach (var item in Model) { <tr> <td> @Html.DisplayFor(modelItem => item.Name) </td> <td> @Html.DisplayFor(modelItem => item.Gender) </td> <td> @Html.DisplayFor(modelItem => item.Department) </td> <td> @Html.DisplayFor(modelItem => item.City) </td> <td> <a asp-action="Edit" asp-route-id="@item.ID">Edit</a> | <a asp-action="Details" asp-route-id="@item.ID">Details</a> | <a asp-action="Delete" asp-route-id="@item.ID">Delete</a> </td> </tr> } </tbody> </table>
Open your EmployeeController.cs file, you can observer that it is empty. Put following code into it.
using System; using System.Collections.Generic; using System.Diagnostics; using System.Linq; using System.Threading.Tasks; using Microsoft.AspNetCore.Mvc; using MVCAdoDemo.Models; namespace MVCAdoDemo.Controllers { public class EmployeeController : Controller { EmployeeDataAccessLayer objemployee = new EmployeeDataAccessLayer(); public IActionResult Index() { List<Employee> lstEmployee = new List<Employee>(); lstEmployee = objemployee.GetAllEmployees().ToList(); return View(lstEmployee); } } }
Open Create.cshtml and put following code into it.
@model MVCAdoDemo.Models.Employee @{ ViewData["Title"] = "Create"; } <h2>Create</h2> <h4>Employees</h4> <hr /> <div class="row"> <div class="col-md-4"> <form asp-action="Create"> <div asp-validation-summary="ModelOnly" class="text-danger"></div> <div class="form-group"> <label asp-for="Name" class="control-label"></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="Gender" class="control-label"></label> <select asp-for="Gender" class="form-control"> <option value="">-- Select Gender --</option> <option value="Male">Male</option> <option value="Female">Female</option> </select> <span asp-validation-for="Gender" class="text-danger"></span> </div> <div class="form-group"> <label asp-for="Department" class="control-label"></label> <input asp-for="Department" class="form-control" /> <span asp-validation-for="Department" class="text-danger"></span> </div> <div class="form-group"> <label asp-for="City" class="control-label"></label> <input asp-for="City" class="form-control" /> <span asp-validation-for="City" class="text-danger"></span> </div> <div class="form-group"> <input type="submit" value="Create" class="btn btn-default" /> </div> </form> </div> </div> <div> <a asp-action="Index">Back to List</a> </div> @section Scripts { @{await Html.RenderPartialAsync("_ValidationScriptsPartial");} }
To handle the business logic of create, open EmployeeController.cs and put following code into it.
[HttpGet] public IActionResult Create() { return View(); } [HttpPost] [ValidateAntiForgeryToken] public IActionResult Create([Bind] Employee employee) { if (ModelState.IsValid) { objemployee.AddEmployee(employee); return RedirectToAction("Index"); } return View(employee); }
@model MVCAdoDemo.Models.Employee @{ ViewData["Title"] = "Edit"; } <h2>Edit</h2> <h4>Employees</h4> <hr /> <div class="row"> <div class="col-md-4"> <form asp-action="Edit"> <div asp-validation-summary="ModelOnly" class="text-danger"></div> <input type="hidden" asp-for="ID" /> <div class="form-group"> <label asp-for="Name" class="control-label"></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="Gender" class="control-label"></label> <select asp-for="Gender" class="form-control"> <option value="">-- Select Gender --</option> <option value="Male">Male</option> <option value="Female">Female</option> </select> <span asp-validation-for="Gender" class="text-danger"></span> </div> <div class="form-group"> <label asp-for="Department" class="control-label"></label> <input asp-for="Department" class="form-control" /> <span asp-validation-for="Department" class="text-danger"></span> </div> <div class="form-group"> <label asp-for="City" class="control-label"></label> <input asp-for="City" class="form-control" /> <span asp-validation-for="City" class="text-danger"></span> </div> <div class="form-group"> <input type="submit" value="Save" class="btn btn-default" /> </div> </form> </div> </div> <div> <a asp-action="Index">Back to List</a> </div> @section Scripts { @{await Html.RenderPartialAsync("_ValidationScriptsPartial");} }
To handle the business logic of Edit view, open EmployeeController.cs and add following code to it.
[HttpGet] public IActionResult Edit(int? id) { if (id == null) { return NotFound(); } Employee employee = objemployee.GetEmployeeData(id); if (employee == null) { return NotFound(); } return View(employee); } [HttpPost] [ValidateAntiForgeryToken] public IActionResult Edit(int id, [Bind]Employee employee) { if (id != employee.ID) { return NotFound(); } if (ModelState.IsValid) { objemployee.UpdateEmployee(employee); return RedirectToAction("Index"); } return View(employee); }
@model MVCAdoDemo.Models.Employee @{ ViewData["Title"] = "Details"; } <h2>Details</h2> <div> <h4>Employees</h4> <hr /> <dl class="dl-horizontal"> <dt> @Html.DisplayNameFor(model => model.Name) </dt> <dd> @Html.DisplayFor(model => model.Name) </dd> <dt> @Html.DisplayNameFor(model => model.Gender) </dt> <dd> @Html.DisplayFor(model => model.Gender) </dd> <dt> @Html.DisplayNameFor(model => model.Department) </dt> <dd> @Html.DisplayFor(model => model.Department) </dd> <dt> @Html.DisplayNameFor(model => model.City) </dt> <dd> @Html.DisplayFor(model => model.City) </dd> </dl> </div> <div> <a asp-action="Edit" asp-route-id="@Model.ID">Edit</a> | <a asp-action="Index">Back to List</a> </div>
To handle the business logic of Details view,open EmployeeController.cs and add following code to it
[HttpGet] public IActionResult Details(int? id) { if (id == null) { return NotFound(); } Employee employee = objemployee.GetEmployeeData(id); if (employee == null) { return NotFound(); } return View(employee); }
@model MVCAdoDemo.Models.Employee @{ ViewData["Title"] = "Delete"; } <h2>Delete</h2> <h3>Are you sure you want to delete this?</h3> <div> <h4>Employees</h4> <hr /> <dl class="dl-horizontal"> <dt> @Html.DisplayNameFor(model => model.Name) </dt> <dd> @Html.DisplayFor(model => model.Name) </dd> <dt> @Html.DisplayNameFor(model => model.Gender) </dt> <dd> @Html.DisplayFor(model => model.Gender) </dd> <dt> @Html.DisplayNameFor(model => model.Department) </dt> <dd> @Html.DisplayFor(model => model.Department) </dd> <dt> @Html.DisplayNameFor(model => model.City) </dt> <dd> @Html.DisplayFor(model => model.City) </dd> </dl> <form asp-action="Delete"> <input type="hidden" asp-for="ID" /> <input type="submit" value="Delete" class="btn btn-default" /> | <a asp-action="Index">Back to List</a> </form> </div>
To handle the business logic of Delete view, open EmployeeController.cs and add following code to it.
[HttpGet] public IActionResult Delete(int? id) { if (id == null) { return NotFound(); } Employee employee = objemployee.GetEmployeeData(id); if (employee == null) { return NotFound(); } return View(employee); } [HttpPost, ActionName("Delete")] [ValidateAntiForgeryToken] public IActionResult DeleteConfirmed(int? id) { objemployee.DeleteEmployee(id); return RedirectToAction("Index"); }
To complete Delete operation we need two Delete methods accepting same parameter (Employee Id). But two methods with same name and method signature will create a compile time error and if we rename the Delete method then routing won’t be able to find it as asp.net maps URL segments to action methods by name. So, to resolve this issue we put ActionName(“Delete”) attribute to the DeleteConfirmed method. This attribute performs mapping for the routing system so that a URL that includes /Delete/ for a POST request will find the DeleteConfirmed method.
When we click on Delete link on the Index page, it will send a Get request and return a View of the employee using HttpGet Delete method. When we click on Delete button on this view, it will send a Post request to delete the record which is handled by the HttpPost DeleteConfirmed method. Performing a delete operation in response to a Get request (or for that matter, performing an edit operation, create operation, or any other operation that changes data) opens up a security hole. Hence, we have two separate methods.
Before launching the application, we will configure route URLs. Open Startup.cs file to set the format for routing. Scroll down to app.UseMvc method, where you can set the route url.
Make sure that your route URL is set like this
app.UseMvc(routes => { routes.MapRoute( name: "default", template: "{controller=Home}/{action=Index}/{id?}"); });
We have learned about creating a sample MVC web application using ASP.Net Core 2.0, ADO.NET and SQL server with the help of Visual Studio Code. Post your valuable feedback in comment section.
You can also find this article at C# Corner.
Download the source code from Github.
You can check my other articles on ASP .NET Core here
Introduction Blazor is a .NET web framework that allows us to create client-side applications using…
Introduction In this article, we are going to create a sudoku solver with the help…
Introduction In this article, we will learn how to implement Azure serverless with Blazor web…
Introduction Angular is an open-source framework that allows us to create applications for multiple platforms…
Introduction In this article, we will create an optical character recognition (OCR) application using Angular…
Introduction In this article, we will create an optical character recognition (OCR) application using Blazor…
View Comments
It is very good. Thanks.
Hello
this is a very good post, quite helpful.
But i have an issue, i can not to the Sql Server.
Please can you help me with a solution to this problem?
this what the error says (With much detailed literature).
Exception has occurred: CLR/System.Data.SqlClient.SqlException
Please check of SQL Server is installed correctly in your machine. Also verify if you are able to connect to SQL Server directly ( not via your code)
Great idea man thanks keep it up all the time. am very happy to see your standard.