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

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.

create table tbl_Employee
(
Sr_no int not null primary key identity(1,1),
Emp_Name  nvarchar(250),
 City nvarchar(100),
 State nvarchar(100),
 Country nvarchar(100),
 Department nvarchar(50)
)

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

create procedure sp_Employee_Add
@Emp_Name  nvarchar(250),
 @City nvarchar(100),
 @State nvarchar(100),
 @Country nvarchar(100),
 @Department nvarchar(50)
 AS
 BEGIN
 Insert into tbl_Employee(Emp_Name,City,State,Country,Department)
 values(@Emp_Name,@City,@State,@Country,@Department)
 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.

 

 

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.

 

 

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.

 

 

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.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.ComponentModel.DataAnnotations;

namespace AspNetCoreCURDMVC_Demo.Models
{
    public class EmployeeEntities
    {
        [Required]
        public string Emp_Name { get; set; }
        [Required]
        public string City { get; set; }
        [Required]
        public string State { get; set; }
        [Required]
        public string Country { get; set; }
        [Required]
        public string Department { get; set; }
    }
}

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.

 

 

Open EmployeeDBAccessLayer.cs and put the following code to handle the database operations. Make sure to put your connection string.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using AspNetCoreCURDMVC_Demo.Models;

namespace AspNetCoreCURDMVC_Demo.Models
{
    public class EmployeeDBAccessLayer
    {
        SqlConnection con = new SqlConnection(“Put your connection string here”);
        public string AddEmployeeRecord(EmployeeEntities employeeEntities)
        {
            try
            {
                SqlCommand cmd = new SqlCommand("sp_Employee_Add",con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Emp_Name", employeeEntities.Emp_Name);
                cmd.Parameters.AddWithValue("@City", employeeEntities.City);
                cmd.Parameters.AddWithValue("@State", employeeEntities.State);
                cmd.Parameters.AddWithValue("@Country", employeeEntities.Country);
                cmd.Parameters.AddWithValue("@Department", employeeEntities.Department);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
                return ("Data save Successfully");
            }
            catch(Exception ex)
            {
                if(con.State==ConnectionState.Open)
                {
                    con.Close();
                }
                return (ex.Message.ToString());
            }
        }
    }
}

Step 3 - Adding the new controller to the application

 

Right-click on Controllers folder and select Add >> New Items...

 

 

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.

 

 

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.

namespace AspNetCoreCURDMVC_Demo.Controllers
{
    public class Employee12Controller : Controller
    {
        EmployeeDBAccessLayer empdb = new EmployeeDBAccessLayer();

        [HttpGet]
        public IActionResult Create()
        {
            return View();
        }
}

To handle the business logic of Create operation, open Employee12Controller.cs and put following code into it.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using AspNetCoreCURDMVC_Demo.Models;

namespace AspNetCoreCURDMVC_Demo.Controllers
{
    public class Employee12Controller : Controller
    {
        EmployeeDBAccessLayer empdb = new EmployeeDBAccessLayer();

        [HttpGet]
        public IActionResult Create()
        {
            return View();
        }
        [HttpPost]
        public IActionResult Create([Bind] EmployeeEntities employeeEntities)
        {
            try
            {
                if (ModelState.IsValid)
                {
                    string resp = empdb.AddEmployeeRecord(employeeEntities);
                    TempData["msg"] = resp;
                }
            }
            catch (Exception ex)
            {
                TempData["msg"] = ex.Message;
            }
            return View();
            }
        }
    }

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.

 

 

Right-click on the Views/Employee12 folder, and then select Add >> New Item.

 

 

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.

 

 

Create View

 

This View will be used to add new employee data into the database.

 

Open Create.cshtml and put following code into it.

@model AspNetCoreCURDMVC_Demo.Models.EmployeeEntities
@{
    ViewData["Title"] = "Create Employee";
}
<h2>Create</h2>
<h4>Employees</h4>
<hr />
<form asp-action="Create" class="form-horizontal">
    <div asp-validation-summary="ModelOnly" class="text-danger"></div>
    <div class="form-group">
        <label class="control-label">Name</label>
        <input asp-for="Emp_Name" class="form-control" />
        <span asp-validation-for="Emp_Name" class="text-danger"></span>
    </div>
    <div class="form-group">
        <label class="control-label">City</label>
        <input asp-for="City" class="form-control" />
        <span asp-validation-for="City" class="text-danger"></span>
    </div>
    <div class="form-group">
        <label class="control-label">State</label>
        <input asp-for="State" class="form-control" />
        <span asp-validation-for="State" class="text-danger"></span>
    </div>
    <div class="form-group">
        <label class="control-label">Country</label>
        <input asp-for="Country" class="form-control" />
        <span asp-validation-for="Country" class="text-danger"></span>
    </div>
    <div class="form-group">
        <label class="control-label">Department</label>
        <input asp-for="Department" class="form-control" />
        <span asp-validation-for="Department" class="text-danger"></span>
    </div>
    <div class="form-group">
        <input type="submit" value="Submit" class="btn bg-primary" />

    </div>
</form>
@{
    if (@TempData["Msg"] != null)
    {
        <script>
            alert('@TempData["msg"]')
        </script>
    }
}

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.

<div class="navbar-collapse collapse">
                <ul class="nav navbar-nav">
                    <li><a asp-area="" asp-controller="Home" asp-action="Index">Home</a></li>
                    <li><a asp-area="" asp-controller="Home" asp-action="About">About</a></li>
                    <li><a asp-area="" asp-controller="Home" asp-action="Contact">Contact</a></li>

                    <li><a asp-area="" asp-controller="Employee12" asp-action="Create">Add Employee</a></li>
                </ul>

Step 6

 

Now, press F5 to launch the application or run the application. You can see the page as shown below.

 

 

Click on "Add Employee" to navigate to the Create view. Add a new Employee record as shown in the image below.

 

 

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.

 

 

When data is saved, the success message will show. Click OK.

 

 

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.