Edit and Update Operations in Asp.net mvc using Ado.net Approach

In Index.cshtml page you can add Edit and delete buttons

So, Copy the below code in Index.cshtml page

<table>

<tr>

<th>Action</th>

</tr>

<tr>

<td>

                @Html.ActionLink("Edit", "Edit", new { id = item.EmpId })||

  @Html.ActionLink("Delete", "Delete", new { id = item.EmpId })

</td>

</tr>

</table>

Edit Operations:

Store Procedure for edit operation

USE [MvcApplicaion]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create PROCEDURE sp_GetEmployeeById_Warriors

@EmpId int

AS

BEGIN

                    SELECT * From EmployeeModels where EmpId=@EmpId;

END

Now we go for the EmployeeContext.cs page and copy the below code

   public EmployeeModel GetEmployeeDetailsById(int? id)

        {

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

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@EmpId", id);

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            DataTable dt = new DataTable();

            da.Fill(dt);

            EmployeeModel emp = new EmployeeModel();

            foreach (DataRow dr in dt.Rows)

            {

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

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

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

            }

            return emp;

        }

Now we go for the EmployeeController.cs page copy the below code.

   [HttpGet]

        public ActionResult Edit(int? id)

        {

            EmployeeModel obj = db.GetEmployeeDetailsById(id);

            return View(obj);

        }

Add view For Edit Method.

Now we go for Edit.cshtml page and copy the below code

@model Asp.net_Approach.Models.EmployeeModel

@{

    ViewBag.Title = "Edit";

}

<h2>Edit</h2>

@using (Html.BeginForm())

{

    @Html.Hidden("EmpId"", Model.EmpId)

    @Html.Label("EmpName")

    @Html.TextBox("EmpName", Model.EmpName)

    <br />

    @Html.Label("EmpSalary")

    @Html.TextBox("EmpSalary", Model.EmpSalary)

    <br />

    <input type="submit" value="save" class="btn btn-success" />

}

Update Operations:

Store Procedure

USE [MvcApplicaion]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create PROCEDURE sp_UpdateEmployeeDetails_Warriors

@EmpName Varchar(50),

@EmpSalary int,

@EmpId int

AS

BEGIN

                    update EmployeeModels

                    set

                    EmpName = @EmpName,

                    EmpSalary = @EmpSalary

                    where EmpId = @EmpId;

END

Now we go for the EmployeeContext.cs page and copy the below code

        public int UpdateEmployeeDetailsById(EmployeeModel obj)

        {

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

            cmd.CommandType = CommandType.StoredProcedure;

            con.Open();

            cmd.Parameters.AddWithValue("@EmpId", obj.EmpId);

            cmd.Parameters.AddWithValue("@EmpName", obj.EmpName);

            cmd.Parameters.AddWithValue("@EmpSalary", obj.EmpSalary);

            object i = cmd.ExecuteNonQuery();

            int result = Convert.ToInt32(i);

            con.Close();

            return result;

        }

Now we go EmployeeController.cs page and copy the below code:

[HttpPost]

        public ActionResult Edit(EmployeeModel e)

        {

            int i = db.UpdateEmployeeDetailsById(e);

            if (i > 0)

            {

                return RedirectToAction("index");

            }

            else

            {

                return View();

            }

        }

ExecuteScalar() : only returns the value from the first column of the first row of your query.(its only return the id of which record you store that particular Empid will return).

ExecuteNonQuery() :  does not return data at all: only the number of rows affected by an insert, update, or delete.

ExecuteReader() : returns an object that can iterate over the entire result set.

Create operation in asp.net mvc using Ado.net Approach

Create operation: 

We take one more ActioResult class in EmployeeController page like below

        public ActionResult Create()

        {

            return View();

        }

Add the view for Create method.

In Create.cshtml View page we create some controls here

First we have two types of controls here

     1.Html helper controls: we can write html controls and basic C# methods

 2. Html controls: only html controls

Now we take html helper controls

Syntax for form tag:

       @using (Html.BeginForm())

{

}

Html.BeginForm used to create form tag for us.

Now we create some controls inside the form tag(add below code in Create.cshtml page)

@using (Html.BeginForm())

{

    @Html.Label("EmpName")

    @Html.TextBox("EmpName")

    <br />

    @Html.Label("EmpSalary")

    @Html.TextBox("EmpSalary")

    <br />

    <input type="submit" value="save" class="btn btn-success" />

}

Now we go back employeecontroller before create method we define one attribute like [HttpGet]

Copy the below code in EmployeeController Page

        [HttpGet]       

        public ActionResult Create()

        {

            return View();

        }

        [HttpPost]

        public ActionResult Create(EmployeModel emp)

        {

            int i = db.saveEmployeeDetails(emp);

            if (i > 0)

            {

                return RedirectToAction("index");

            }

            else

            {

                return View();

            }

        }

HttpGet and HttpPost this are called as action verbs. 

HttpGet will handle get request   

HttpPost will handle post request

Its nothing but when we want some data or page to be loaded at the time we will go for HttpGet request only.

When we submit some data to httppost. this post will send that data to server.

Create store procedure for save employee details

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE sp_SaveEmployeeDetails_Warriors

@EmpName varchar(50),

@EmpSalary int

AS

BEGIN

                    insert into EmployeeModels(EmpName,EmpSalary)values(@EmpName, @EmpSalary)

                    select @@IDENTITY as int

END

GO

Copy below code in EmployeeContext.cs file

   public int saveEmployeeDetails(EmployeModel obj)

        {

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

            cmd.CommandType = CommandType.StoredProcedure;

            con.Open();

            cmd.Parameters.AddWithValue("@EmpName",obj.EmpName );

            cmd.Parameters.AddWithValue("@EmpSalary", obj.EmpSalary);

            object i = cmd.ExecuteScalar();

            int result = Convert.ToInt32(i);

            con.Close();

            return result;

        }

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>