How To Insert Data To Database Using Model In ASP.NET Core 2.1 MVC Using ADO.NET - Part One

In this article, you will learn how to insert data into database using a model in ASP.NET Core 2.1 MVC using ADO.NET.

Introduction

 
In this article, I am going to show how to create an ASP.NET Core 2.1 MVC application and how to save data into a database using Model in ASP.NET Core 2.1 MVC application using stored procedure and ADO.NET.
 
We will be using Visual Studio 2017 (version 15.9.13 or above) and SQL Server 2017 or you can use SQL Server 2008 or above versions.
 
Prerequisites
  1. Install Visual Studio 2017 updated version 15.9.13
  2. Install .NET Core SDK 2.1 or above
  3. SQL Server 2017
Now, let us create our ASP.NET Core 2.1 MVC application. 
 
First of all, we will create a database, table and a stored procedure
 

Creating Database, Table, and Stored Procedures

 
Step 1 - Create a database
 
Open your SQL Server and use the following script to create the “CoreMvcDB” database.
 
Create a database named CoreMvcDB.
 
Step 2 - Create a table
 
Open your SQL Server and use the following script to create a “tbl_Employee” table.
  1. create table tbl_Employee  
  2. (  
  3. Sr_no int not null primary key identity(1,1),  
  4. Emp_Name  nvarchar(250),  
  5.  City nvarchar(100),  
  6.  State nvarchar(100),  
  7.  Country nvarchar(100),  
  8.  Department nvarchar(50)  
  9. )  
Step 3 - Create a stored procedure
 
Now, we will create a stored procedure to add Employee data into the database.
 
Open your SQL Server and use the following script to create a procedure.
 
Insert an Employee Record
  1. create procedure sp_Employee_Add  
  2. @Emp_Name  nvarchar(250),  
  3.  @City nvarchar(100),  
  4.  @State nvarchar(100),  
  5.  @Country nvarchar(100),  
  6.  @Department nvarchar(50)  
  7.  AS  
  8.  BEGIN  
  9.  Insert into tbl_Employee(Emp_Name,City,State,Country,Department)  
  10.  values(@Emp_Name,@City,@State,@Country,@Department)  
  11.  END  
Now, our database part has been completed. So, we will create ASP.NET Core MVC application.
 
Step 1 - Create an ASP.NET Core 2.1 MVC Project
 
Open Visual Studio and select File -> New -> Project.
 
How To Insert Data To Database Using Model In ASP.NET Core 2.1 MVC Using ADO.NET
 
After selecting the project, a "New Project" dialog will open. Select .NET Core inside the Visual C# menu from the left side panel.
 
Then, select “ASP.NET Core web application“ from available project types. Give a name to the project as “AspNetCoreCURDMVC-­Demo” and press OK.
 
How To Insert Data To Database Using Model In ASP.NET Core 2.1 MVC Using ADO.NET
 
After clicking on the OK button, a new dialog will open to select the project template. You can see two drop-down menus at the top left of the template window. Then, select “.NET Core” and “ ASP.NET Core 2.1” from these dropdowns. Select “ Web application (Model-View-Controller)” template and press OK to create Asp.Net Core MVC project.
 
How To Insert Data To Database Using Model In ASP.NET Core 2.1 MVC Using ADO.NET
 
Step 2 - Add the model to the application
 
Right-click on the solution and add a new folder named “Models”. Now, add a new class on the Models folder.
 
Right-click on Models folder and select "Add a new class". The name of your class is EmployeeEntities.cs. This class will contain our Employee model properties.
 
Now, open the EmployeeEntities.cs class file and put the following code in it. We are also adding the required validators to the fields of EmployeeEntities class, so we need to use System.ComponentModel.DataAnnotations at the top.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Threading.Tasks;  
  5. using System.ComponentModel.DataAnnotations;  
  6.   
  7. namespace AspNetCoreCURDMVC_Demo.Models  
  8. {  
  9.     public class EmployeeEntities  
  10.     {  
  11.         [Required]  
  12.         public string Emp_Name { getset; }  
  13.         [Required]  
  14.         public string City { getset; }  
  15.         [Required]  
  16.         public string State { getset; }  
  17.         [Required]  
  18.         public string Country { getset; }  
  19.         [Required]  
  20.         public string Department { getset; }  
  21.     }  
  22. }  
Now, add one more class file to the Models folder. Name it as EmployeeDBAccessLayer.cs. This class will contain our database related operations.
 
Now, the Models folder has the following structure.
 
How To Insert Data To Database Using Model In ASP.NET Core 2.1 MVC Using ADO.NET
 
Open EmployeeDBAccessLayer.cs and put the following code to handle the database operations. Make sure to put your connection string.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Threading.Tasks;  
  5. using System.Data;  
  6. using System.Data.SqlClient;  
  7. using AspNetCoreCURDMVC_Demo.Models;  
  8.   
  9. namespace AspNetCoreCURDMVC_Demo.Models  
  10. {  
  11.     public class EmployeeDBAccessLayer  
  12.     {  
  13.         SqlConnection con = new SqlConnection(“Put your connection string here”);  
  14.         public string AddEmployeeRecord(EmployeeEntities employeeEntities)  
  15.         {  
  16.             try  
  17.             {  
  18.                 SqlCommand cmd = new SqlCommand("sp_Employee_Add",con);  
  19.                 cmd.CommandType = CommandType.StoredProcedure;  
  20.                 cmd.Parameters.AddWithValue("@Emp_Name", employeeEntities.Emp_Name);  
  21.                 cmd.Parameters.AddWithValue("@City", employeeEntities.City);  
  22.                 cmd.Parameters.AddWithValue("@State", employeeEntities.State);  
  23.                 cmd.Parameters.AddWithValue("@Country", employeeEntities.Country);  
  24.                 cmd.Parameters.AddWithValue("@Department", employeeEntities.Department);  
  25.                 con.Open();  
  26.                 cmd.ExecuteNonQuery();  
  27.                 con.Close();  
  28.                 return ("Data save Successfully");  
  29.             }  
  30.             catch(Exception ex)  
  31.             {  
  32.                 if(con.State==ConnectionState.Open)  
  33.                 {  
  34.                     con.Close();  
  35.                 }  
  36.                 return (ex.Message.ToString());  
  37.             }  
  38.         }  
  39.     }  
  40. }  
Step 3 - Adding the new controller to the application
 
Right-click on Controllers folder and select Add >> New Items...
 
How To Insert Data To Database Using Model In ASP.NET Core 2.1 MVC Using ADO.NET
 
An “Add New Item” dialog box will open. Select Web from the left panel, then select “MVC Controller Class” from templates panel, and put the name as Employee12Controller.cs. Press OK.
 
How To Insert Data To Database Using Model In ASP.NET Core 2.1 MVC Using ADO.NET
 
Now, our Employee12Controller has been created. We will put our business logic into this controller.
 
To handle database operations, we will create an object of EmployeeDBAccessLayer class inside the Employee12Controller class.
  1. namespace AspNetCoreCURDMVC_Demo.Controllers  
  2. {  
  3.     public class Employee12Controller : Controller  
  4.     {  
  5.         EmployeeDBAccessLayer empdb = new EmployeeDBAccessLayer();  
  6.   
  7.         [HttpGet]  
  8.         public IActionResult Create()  
  9.         {  
  10.             return View();  
  11.         }  
  12. }  
To handle the business logic of Create operation, open Employee12Controller.cs and put following code into it.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Threading.Tasks;  
  5. using Microsoft.AspNetCore.Mvc;  
  6. using AspNetCoreCURDMVC_Demo.Models;  
  7.   
  8. namespace AspNetCoreCURDMVC_Demo.Controllers  
  9. {  
  10.     public class Employee12Controller : Controller  
  11.     {  
  12.         EmployeeDBAccessLayer empdb = new EmployeeDBAccessLayer();  
  13.   
  14.         [HttpGet]  
  15.         public IActionResult Create()  
  16.         {  
  17.             return View();  
  18.         }  
  19.         [HttpPost]  
  20.         public IActionResult Create([Bind] EmployeeEntities employeeEntities)  
  21.         {  
  22.             try  
  23.             {  
  24.                 if (ModelState.IsValid)  
  25.                 {  
  26.                     string resp = empdb.AddEmployeeRecord(employeeEntities);  
  27.                     TempData["msg"] = resp;  
  28.                 }  
  29.             }  
  30.             catch (Exception ex)  
  31.             {  
  32.                 TempData["msg"] = ex.Message;  
  33.             }  
  34.             return View();  
  35.             }  
  36.         }  
  37.     } 
Step 4 - Adding Views to the application
 
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.
 
Right-click on the Views folder, and then Add >> New Folder and name the folder as Employee12.
 
How To Insert Data To Database Using Model In ASP.NET Core 2.1 MVC Using ADO.NET
 
Right-click on the Views/Employee12 folder, and then select Add >> New Item.
 
How To Insert Data To Database Using Model In ASP.NET Core 2.1 MVC Using ADO.NET
 
An “Add New Item” dialog box will open. Select Web from the left panel, then select “Razor View Page” from templates panel, and put the name as Create.cshtml. Press OK.
 
How To Insert Data To Database Using Model In ASP.NET Core 2.1 MVC Using ADO.NET
 
Create View
 
This View will be used to add new employee data into the database.
 
Open Create.cshtml and put following code into it.
  1. @model AspNetCoreCURDMVC_Demo.Models.EmployeeEntities  
  2. @{  
  3.     ViewData["Title"] = "Create Employee";  
  4. }  
  5. <h2>Create</h2>  
  6. <h4>Employees</h4>  
  7. <hr />  
  8. <form asp-action="Create" class="form-horizontal">  
  9.     <div asp-validation-summary="ModelOnly" class="text-danger"></div>  
  10.     <div class="form-group">  
  11.         <label class="control-label">Name</label>  
  12.         <input asp-for="Emp_Name" class="form-control" />  
  13.         <span asp-validation-for="Emp_Name" class="text-danger"></span>  
  14.     </div>  
  15.     <div class="form-group">  
  16.         <label class="control-label">City</label>  
  17.         <input asp-for="City" class="form-control" />  
  18.         <span asp-validation-for="City" class="text-danger"></span>  
  19.     </div>  
  20.     <div class="form-group">  
  21.         <label class="control-label">State</label>  
  22.         <input asp-for="State" class="form-control" />  
  23.         <span asp-validation-for="State" class="text-danger"></span>  
  24.     </div>  
  25.     <div class="form-group">  
  26.         <label class="control-label">Country</label>  
  27.         <input asp-for="Country" class="form-control" />  
  28.         <span asp-validation-for="Country" class="text-danger"></span>  
  29.     </div>  
  30.     <div class="form-group">  
  31.         <label class="control-label">Department</label>  
  32.         <input asp-for="Department" class="form-control" />  
  33.         <span asp-validation-for="Department" class="text-danger"></span>  
  34.     </div>  
  35.     <div class="form-group">  
  36.         <input type="submit" value="Submit" class="btn bg-primary" />  
  37.   
  38.     </div>  
  39. </form>  
  40. @{  
  41.     if (@TempData["Msg"] != null)  
  42.     {  
  43.         <script>  
  44.             alert('@TempData["msg"]')  
  45.         </script>  
  46.     }  
  47. }  
Step 5 - Add a new menu
 
Edit the Views/Shared/_Layout page and add a new menu as “Add Employee”. For that, add the below code.
  1. <div class="navbar-collapse collapse">  
  2.                 <ul class="nav navbar-nav">  
  3.                     <li><a asp-area="" asp-controller="Home" asp-action="Index">Home</a></li>  
  4.                     <li><a asp-area="" asp-controller="Home" asp-action="About">About</a></li>  
  5.                     <li><a asp-area="" asp-controller="Home" asp-action="Contact">Contact</a></li>  
  6.   
  7.                     <li><a asp-area="" asp-controller="Employee12" asp-action="Create">Add Employee</a></li>  
  8.                 </ul>  
Step 6
 
Now, press F5 to launch the application or run the application. You can see the page as shown below.
 
How To Insert Data To Database Using Model In ASP.NET Core 2.1 MVC Using ADO.NET
 
Click on "Add Employee" to navigate to the Create view. Add a new Employee record as shown in the image below.
 
How To Insert Data To Database Using Model In ASP.NET Core 2.1 MVC Using ADO.NET
 
We have also added the validation using DataAnnotations on added Create View page. If we miss the data in any field while creating the Employee record, we will get a required field validation error message.
 
How To Insert Data To Database Using Model In ASP.NET Core 2.1 MVC Using ADO.NET
 
When data is saved, the success message will show. Click OK.
 
How To Insert Data To Database Using Model In ASP.NET Core 2.1 MVC Using ADO.NET
 
After the data is saved, you can check your database.
 
Conclusion 
 
We have learned about creating a sample MVC web application with ASP.NET Core 2.1 using ADO.NET and SQL Server. Post your valuable feedback in the comments section.