CRUD operations in ASP.NET MVC using ADO.NET ~ MiTechDev.com

Mitechdev.com is a programming blog which focused on Web Programming and development

Saturday, 26 March 2016

CRUD operations in ASP.NET MVC using ADO.NET

07:38:00 Posted by raviteja swayampu No comments

Introduction:

Hello every one welcome again.In this tutorial we are going to learn the CRUD operations is ASP.NET MVC. It is a hot technology in now a days.This post will target the beginners of ASP.NET MVC. Thanks for comments and likes for the previous article "CRUD operations in ASP.NET MVC using ADO.NET".Some of the friends asked me same post using ADO.NET .that why i am posting this.If you are the beginner to MVC it's the better start with the basics .just follow the below links.
Create a project in Visual studio 2013
1.I am going to explain this in step by step manner for better understanding and readability.

CRUD operations in ASP.NET MVC using ADO.NET

2.Now a Empty ASP.NET MVC project is created with some folders like below

CRUD operations in ASP.NET MVC using ADO.NET

Step 2:
Create model class in Model folder and name it as EmpModel.cs.We can create model class any where in the solution explorer for better readability and maintainability of the project just place it in the Models folder only.follow below screenshot

CRUD operations in ASP.NET MVC using ADO.NET

EmpModel.cs code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;

namespace Ado.netapplication.Models
{
    public class EmpModel
    {
        [Display(Name = "Id")]
        public int Empid { 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; }

    }
}
Note:We have added some validations to the properties those are called "Data Annotations".which are present in namespace "System.ComponentModel.DataAnnotations".

Step 3:Create a Controller class

CRUD operations in ASP.NET MVC using ADO.NET


Note:Controller name must have prefix "Controller" because it required for the ASP.NET MVC execution engine to differentiate the model classes with Controller classes.

Step 4: Create Table and Stored procedures
1.create a table with the name "Employee".

CRUD operations in ASP.NET MVC using ADO.NET

2.Add Sql server file "Database.mdf" and create table

CRUD operations in ASP.NET MVC using ADO.NET

create table


3.Now create stored procedures to insert,update,delete,create
Insert Records:


Create procedure [dbo].[AddNewEmpDetails]  
(  
   @Name varchar (50),  
   @City varchar (50),  
   @Address varchar (50)  
)  
as  
begin  
   Insert into Employee values(@Name,@City,@Address)  
End 

To View Records:
Create Procedure [dbo].[GetEmployees]  
as  
begin  
   select *from Employee  
End

Update records:
Create procedure [dbo].[UpdateEmpDetails]  
(  
   @EmpId int,  
   @Name varchar (50),  
   @City varchar (50),  
   @Address varchar (50)  
)  
as  
begin  
   Update Employee   
   set [email protected],  
   [email protected],  
   [email protected]  
   where [email protected]  
End 

Delete records:
Create procedure [dbo].[DeleteEmpById]  
(  
   @EmpId int  
)  
as   
begin  
   Delete from Employee where [email protected]  
End 

Step 5:create a Repository class
Now create a repository class that contains the server operations.


EmpRepository.cs:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using CRUDUsingMVC.Models;
using System.Linq;
namespace CRUDUsingMVC.Repository
{
    public class EmpRepository
    {
        private SqlConnection con;
        //To Handle connection related activities
        private void connection()
        {
            string constr = ConfigurationManager.ConnectionStrings["getconn"].ToString();
            con = new SqlConnection(constr);
        }
        //To Add Employee details
        public bool AddEmployee(EmpModel obj)
        {
            connection();
            SqlCommand com = new SqlCommand("AddNewEmpDetails", con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.AddWithValue("@Name", obj.Name);
            com.Parameters.AddWithValue("@City", obj.City);
            com.Parameters.AddWithValue("@Address", obj.Address);
            con.Open();
            int i = com.ExecuteNonQuery();
            con.Close();
            if (i >= 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //To view employee details with generic list 
        public List GetAllEmployees()
        {
            connection();
            List EmpList =new List();
            SqlCommand com = new SqlCommand("GetEmployees", con);
            com.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(com);
            DataTable dt = new DataTable();
            con.Open();
            da.Fill(dt);
            con.Close();
            //Bind EmpModel generic list using LINQ 
            EmpList = (from DataRow dr in dt.Rows

                       select new EmpModel()
                       {
                           Empid = Convert.ToInt32(dr["Id"]),
                           Name = Convert.ToString(dr["Name"]),
                           City = Convert.ToString(dr["City"]),
                           Address = Convert.ToString(dr["Address"])
                       }).ToList();
            return EmpList;
        }
        //To Update Employee details
        public bool UpdateEmployee(EmpModel obj)
        {
            connection();
            SqlCommand com = new SqlCommand("UpdateEmpDetails", con);
           
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.AddWithValue("@EmpId", obj.Empid);
            com.Parameters.AddWithValue("@Name", obj.Name);
            com.Parameters.AddWithValue("@City", obj.City);
            com.Parameters.AddWithValue("@Address", obj.Address);
            con.Open();
            int i = com.ExecuteNonQuery();
            con.Close();
            if (i >= 1)
            {        
                return true;
            }
            else
            {
                return false;
            }
        }
        //To delete Employee details
        public bool DeleteEmployee(int Id)
        {
            connection();
            SqlCommand com = new SqlCommand("DeleteEmpById", con);

            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.AddWithValue("@EmpId", Id);
           
            con.Open();
            int i = com.ExecuteNonQuery();
            con.Close();
            if (i >= 1)
            {
               
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}

Step 5:Create controller action methods for Create,Delete,update,Select

EmployeeController.cs code:

public class EmployeeController : Controller    
    {             
        // GET: Employee/GetAllEmpDetails    
        public ActionResult GetAllEmpDetails()    
        {                 
            EmpRepository EmpRepo = new EmpRepository();    
            ModelState.Clear();    
            return View(EmpRepo.GetAllEmployees());    
        }    
        // GET: Employee/AddEmployee    
        public ActionResult AddEmployee()    
        {    
            return View();    
        }         
        // POST: Employee/AddEmployee    
        [HttpPost]    
        public ActionResult AddEmployee(EmpModel Emp)    
        {    
            try    
            {    
                if (ModelState.IsValid)    
                {    
                    EmpRepository EmpRepo = new EmpRepository();    
      
                    if (EmpRepo.AddEmployee(Emp))    
                    {    
                        ViewBag.Message = "Employee details added successfully";    
                    }    
                }    
                  
                return View();    
            }    
            catch    
            {    
                return View();    
            }    
        }    
      
        // GET: Employee/EditEmpDetails/5    
        public ActionResult EditEmpDetails(int id)    
        {    
            EmpRepository EmpRepo = new EmpRepository();    
      
              
      
            return View(EmpRepo.GetAllEmployees().Find(Emp => Emp.Empid == id));    
      
        }    
      
        // POST: Employee/EditEmpDetails/5    
        [HttpPost]    
          
        public ActionResult EditEmpDetails(int id,EmpModel obj)    
        {    
            try    
            {    
                    EmpRepository EmpRepo = new EmpRepository();    
                      
                    EmpRepo.UpdateEmployee(obj);       
      
                return RedirectToAction("GetAllEmpDetails");    
            }    
            catch    
            {    
                return View();    
            }    
        }    
      
        // GET: Employee/DeleteEmp/5    
        public ActionResult DeleteEmp(int id)    
        {    
            try    
            {    
                EmpRepository EmpRepo = new EmpRepository();    
                if (EmpRepo.DeleteEmployee(id))    
                {    
                    ViewBag.AlertMsg = "Employee details deleted successfully";    
      
                }    
                return RedirectToAction("GetAllEmpDetails");    
      
            }    
            catch    
            {    
                return View();    
            }    
        }                 
    }    
Step:7
Create Views for the Controller Action methods
Click on Action methods and create partial strongly typed views for the action methods
right click any where on the action methods  select the templates and model class and click on "Add".Then strongly typed views generate like below with code.

CRUD operations in ASP.NET MVC using ADO.NET

AddEmployee.cshtml:
@model CRUDUsingMVC.Models.EmpModel 

@using (Html.BeginForm()) 
{ 
    @Html.AntiForgeryToken() 

    <div class="form-horizontal"> 

        <h4>Add Employee</h4> 

        <div> 

            @Html.ActionLink("Back to Employee List", "GetAllEmpDetails") 

        </div> 

        <hr /> 

        @Html.ValidationSummary(true, "", new { @class = "text-danger" }) 

        <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="Save" class="btn btn-default" /> 

            </div> 

        </div> 

        <div class="form-group"> 

            <div class="col-md-offset-2 col-md-10" style="color:green"> 

                @ViewBag.Message 

            </div> 

        </div> 

    </div> 

}  

<script src="~/Scripts/jquery-1.10.2.min.js"></script> 

<script src="~/Scripts/jquery.validate.min.js"></script> 

<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>


GetallEmployees:
To see the inserted the value create a GetallEmployees.cshtml

GetAllEmployeeDetails.cshtml:

@model IEnumerable<CRUDUsingMVC.Models.EmpModel> 
<p> 

    @Html.ActionLink("Add New Employee", "AddEmployee") 

</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) 

    { 

        @Html.HiddenFor(model => item.Empid) 

        <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", "EditEmpDetails", new { id = item.Empid }) | 

                @Html.ActionLink("Delete", "DeleteEmp", new { id = item.Empid }, new { onclick = "return confirm('Are sure wants to delete?');" }) 

            </td> 

        </tr> 

    } 

</table>
<
Update Employees:

Follow the same procedure and create EditEmpDetails view to edit the employees. After creating the view the code will be like the following

EditEmpDetails.cshtml:

@model CRUDUsingMVC.Models.EmpModel 

@using (Html.BeginForm()) 
{ 

    @Html.AntiForgeryToken() 

    <div class="form-horizontal"> 

        <h4>Update Employee Details</h4> 

        <hr /> 

        <div> 

            @Html.ActionLink("Back to Details", "GetAllEmployees") 

        </div> 

        <hr /> 

        @Html.ValidationSummary(true, "", new { @class = "text-danger" }) 

        @Html.HiddenFor(model => model.Empid) 

        <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> 

} 

<script src="~/Scripts/jquery-1.10.2.min.js"></script> 

<script src="~/Scripts/jquery.validate.min.js"></script> 

<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>

Step 8:

Finally Configure the Routing.cs file for the default routing page.

public class RouteConfig
 {
     public static void RegisterRoutes(RouteCollection routes)
     {
         routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

         routes.MapRoute(
             name: "Default",
             url: "{controller}/{action}/{id}",
             defaults: new { controller = "Employee", action = "AddEmployee", id = UrlParameter.Optional }
         );
     }
 }

Here we set the default action as "AddEmployee" in route.config that means when we run the application it will opens.
Configure the Edit and Delete action links in "GetAllEmpDetails" page for doing this edit and delete actions


Run the application and see the output
if u have doubts feel free to ask me.

Download code


0 comments:

Post a Comment