CRUD Operation in Asp.net MVC using Ado.net Approach

CRUD operations are defined as the operations performed to create, read, update, and delete documents. 

Step 1: Create a New Asp.net MVC Application.

Right click on solution explore -> Add -> New Project -> Asp.net Web Application (.NET Framework) then provide the project a name like Ado.net_Approach and click on ok. After clicking, the following window will appear:

As shown in the above screenshot, click on Empty template and check Mvc option, then click ok. This will create an empty mvc web application.

Step 2: Create Model Class.

Now let us create the model class named EmployeeModel.cs by right clicking on model folder as in the following screenshot:

In model class we can define some properties like below:

using System.ComponentModel.DataAnnotations;

namespace Ado.net_approach.Models

{

    public class EmployeModel

    {

        [Key]

        public int EmpId { get; set; }

        public string EmpName { get; set; }

        public int EmpSalary { get; set; }

    }   

}

Now I define EmpId is primary key, so we define like [Key]

Now before creating the connection let us create the table name EmployeeModel in database according to our model fields to store the details:

Step 3: Create Table and Stored procedures.

First create database name like MvcApplication -> Add table

1.Create Table in database

use MvcApplicaion 

create table EmployeeModels (
    EmpId int IDENTITY(1,1) PRIMARY KEY,
    EmpName varchar(50),
    EmpSalary int
);

 2.Create Stored Procedures 

Open Programmability -> right click on Stored Procedures -> New Stored Procedures

use MvcApplicaion

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE sp_GetEmployee

AS

BEGIN

                    SELECT * From [dbo].[EmployeeModels]

END

GO

Step 4: Copy below code in web.config file.

  <connectionStrings>

    <add name="con" connectionString="Data Source= LENOVO-PC;Initial Catalog= MvcApplicationion;Integrated Security=true" providerName="System.Data.SqlClient"/>

  </connectionStrings>

Let’s take another model class (EmployeeContext.cs) to where we connect to the database.

So, copy below code in EmployeeContext.cs model class.

using System.Data;

using System.Configuration;

using System.Data.SqlClient;

namespace Ado.net_approach.Models

{

    public class EmployeeContext

    {

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ToString());

        public List<EmployeModel> GetEmployeeDetails() {

            SqlCommand cmd = new SqlCommand("sp_GetEmployee", con);

            cmd.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            DataTable dt = new DataTable();

            da.Fill(dt);

            List<EmployeModel> list = new List<EmployeModel>();

            foreach (DataRow dr in dt.Rows)

            {

                EmployeModel emp = new EmployeModel();

                emp.EmpId = Convert.ToInt32(dr[0]);

                emp.EmpName = Convert.ToString(dr[1]);

                emp.EmpSalary = Convert.ToInt32(dr[2]);

                list.Add(emp);

           }

            return list;

        }

    }

}

Step 5: Create Controller

Right click on Controllers folder -> Add -> Controller 

Now let us add the MVC 5 controller as in the following screenshot:

After clicking on add button it will show the following window. Now specify the Controller name as EmployeeController and click the Add button

Add the below code in EmployeeController

using Ado.net_approach.Models;

namespace Ado.net_approach.Controllers

{

    public class EmployeeController : Controller

    {

        // GET: Employee

        EmployeeContext db = new EmployeeContext();

        public ActionResult Index()

        {

            return View(db.GetEmployeeDetails());

        }

    }

}

Step 6: Create View

To create the view, right click on ActionResult method and then click Add View. Now Specify the view name and click add as in the following screenshot:

After create view page you copy the below code

@model List<Ado.net_approach.Models.EmployeModel>

@{

    ViewBag.Title = "Index";

}

<h2>Index</h2>

<table>

    <tr>

        <th>Empid</th>

        <th>EmpName</th>

        <th>EmpSalary</th>

    </tr>

   

        @foreach (var item in Model)

        {

           <tr>

               <td>@item.EmpId</td>

               <td>@item.EmpName</td>

               <td>@item.EmpSalary</td>

           </tr>

        }

  

</table>