Update In ASP.NET MVC 5 Using Ado.Net






Edit Update Ado.net in ASP.NET MVC 5 using without USING Entity Framework

4. Update In ASP.NET MVC 5 Using Ado.Net

Before proceed this tutorial you learn

 3 . Insert In Asp.Net MVC Using Ado.Net

 In these article we are going to learn Fetch records from database and bind into the List and Edit button click bind the value into textbox and Update the records. This article uses ADO.Net commands to work with SQL Server Database. 

 I promise, you will be able to write your own customize code in MVC . If you are beginners or trying to learn MVC don't worry you easy to understand.

 Following these step you can easily achieve this.

Step 1:  Create first model class Employee.Model.cs class.

using System.ComponentModel.DataAnnotations;

namespace CurdOperationAdo.Models
{
    public class StudentModel
    {
        [Display(Name = "Id")]
        public int Id { get; set; }
        [Required(ErrorMessage = "First name is required.")]
        public string Name { get; set; }
        [Required(ErrorMessage = "City is required.")]
        public string City { get; set; }
        [Required(ErrorMessage = "Address is required.")]
        public string Address { get; set; }
    }
}



Step 2: Create Database, Table and Store Procedure

CREATE TABLE [dbo].[StudentReg]
(
[Id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [Name] NVARCHAR(50) NULL, 
    [City] NVARCHAR(50) NULL, 
    [Address] NVARCHAR(100) NULL
)

Insert Records – Store Procedure
Create procedure [dbo].[AddNewStudent]  
(  
   @Name nvarchar (50),  
   @City nvarchar (50),  
   @Address nvarchar (100)  
)  
as  
begin  
  
  Update EmployeeReg
   set Name = @Name,  
   City = @City,  
   Address = @Address  
   where Id=@StdId  
End



Step 3: Create in web.config file to connect to Database .

<connectionStrings>
<add name="DBConn" connectionString="Data Source=.;
Initial Catalog=fly; Integrated Security=True;Pooling=False"/>
</connectionStrings>


Step 4: Create StudentDBHandle.cs class for handling all the database operations.

1. Right click on Models folder  Add  Class. Create new class StudentDBHandle.cs
namespace CurdOperationAdo.Models
{
    public class StudentDBHandle
    {
        private SqlConnection con;
        private void connection()
        {
            string constring = ConfigurationManager.ConnectionStrings["DBConn"].ToString();
            con = new SqlConnection(constring);
        }
        // ********** VIEW EMPLOYEE DETAILS ********************
        public List<StudentModel> GetStudent()
        {
            connection();
            List<StudentModel> studentlist = new List<StudentModel>();
            SqlCommand cmd = new SqlCommand("GetStudentDetails", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter sd = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();

             con.Open();
            sd.Fill(dt);
            con.Close();

             foreach (DataRow dr in dt.Rows)
            {
                studentlist .Add(
                    new StudentModel
                    {
                        Id = Convert.ToInt32(dr["Id"]),
                        Name = Convert.ToString(dr["Name"]),
                        City = Convert.ToString(dr["City"]),
                        Address = Convert.ToString(dr["Address"])
                    });
            }
            return studentlist ;

         }
     // ********** UPDATE EMPLOYEE DETAILS ********************
    public bool UpdateDetails(EmployeeModel  emodel)
    {
            connection();
            SqlCommand cmd = new SqlCommand("UpdateEmployeeDetails", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@Id", emodel.Id);
            cmd.Parameters.AddWithValue("@Name", emodel.Name);
            cmd.Parameters.AddWithValue("@City", emodel.City);
            cmd.Parameters.AddWithValue("@Address", emodel.Address);
            con.Open();
            int i = cmd.ExecuteNonQuery();
            con.Close();
            if (i >= 1)
                return true;
            else
                return false;
        }
   }
}



Step 5:  Add controller inside Action Method .

Open StudentController and add the following action methods. Here, I am going to add Create action methods for following purpose.

a. Index() - Showing All StudentDetails
b. Create() - Adding New Student
c. Edit () - Edit or Update Student Details
namespace CurdOperationAdo.Controllers
{
    public class HomeController : Controller
    {

        // 1. *************RETRIEVE ALL STUDENT DETAILS ******************
        // GET: Student
        public ActionResult Index()
        {
            StudentDBHandle dbhandle = new StudentDBHandle();
            ModelState.Clear();
            return View(dbhandle.GetStudent());
        }

        // 2. ************* EDIT EMPLOYEE DETAILS ******************
        // GET: Employee/Edit/5
        public ActionResult Edit(int id)
        {
            EmployeeDBHandler sdb = new EmployeeDBHandler ();
            return View(sdb.GetStudent().Find(smodel => smodel.Id == id));
        }

       // 3. ************* UPDATE EMPLOYEE DETAILS ******************
        // POST: Employee/Edit/5

        [HttpPost]
        public ActionResult Edit(int id, EmployeeModel emodel)
        {
            try
            {
                StudentDBHandle sdb = new StudentDBHandle();
                sdb.UpdateDetails(emodel);
                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }
  }
}



View Index


@model IEnumerable<CurdOperationAdo.Models.StudentModel>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.Name)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.City)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Address)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
           @Html.DisplayFor(modelItem => item.Name)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.City)
        </td>
        <td>
           @Html.DisplayFor(modelItem => item.Address)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id=item.Id }) |
            @Html.ActionLink("Details", "Details", new { id=item.Id }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.Id })
        </td>
    </tr>
}

</table>

List Binding Like this when you run the project





Edit Option click textbox open .

@model CurdOperationAdo.Models.StudentModel

@{
    ViewBag.Title = "Edit";
}

<h2>Edit</h2>


@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()
    
    <div class="form-horizontal">
        <h4>StudentModel</h4>
        <hr />
       @Html.ValidationSummary(true, "", new { @class = "text-danger" })
       @Html.HiddenFor(model => model.Id)

        <div class="form-group">
            @Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
               @Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
           @Html.LabelFor(model => model.City, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
               @Html.EditorFor(model => model.City, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.City, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
               @Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Update" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

@section Scripts {
    @Scripts.Render("~/bundles/jqueryval")
}