C# MVC Code - Asp.Net MVC Pattern

CREATE TABLE [dbo].[tbl_user](
	[id] [int] IDENTITY(1,1) PRIMARY KEY  NOT NULL,
	[name] [varchar](100) NULL,
	[Username] [varchar](100) NULL,
	[pwd] [varchar](100) NULL,
)

CREATE TABLE [dbo].[TblEmployee](
	[Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
	[EmployeeName] [varchar](100) NULL,
	[EmployeeNumber] [varchar](100) NULL,
	[EmployeeEmail] [varchar](100) NULL,
	[Address] [varchar](500) NULL,
	[BloodGroup] [varchar](100) NULL
	)
      
----------------------------------------

create procedure [dbo].[delete_id]
(
 @id [int]
)
as begin 
Delete FROM TblEmployee where Id=@id
end 
GO

---------------------------

create procedure [dbo].[sp_CreateEmployee]
(
 @EmployeeName [varchar](100),
@EmployeeNumber [varchar](100),
@EmployeeEmail [varchar](100),
@Address [varchar](500),
@BloodGroup [varchar](100)
)
as begin 
insert into [TblEmployee] values(@EmployeeName,@EmployeeNumber,@EmployeeEmail,@Address,
@BloodGroup)
end 
GO

-------------------------
 
 CREATE procedure [dbo].[sp_showEmployee]
 as begin
 select * from [TblEmployee]
 end
GO 

--------------------------

CREATE procedure [dbo].[sp_showEmployee_ID]
(
 @id [int]
)
as begin 
SELECT * FROM TblEmployee where ID=@id
end 
GO

--------------------------------

create proc [dbo].[sp_tbl_userlogin]
( @username varchar(100),
  @pwd varchar(100)
)
as begin
   select * from tbl_user 
   where Username=@username and
   pwd=@pwd
end
GO

-------------------------------


create procedure [dbo].[sp_update]
(
 @id [int],
 @EmployeeName [varchar](100),
 @EmployeeNumber [varchar](100),
 @EmployeeEmail [varchar](100),
 @Address [varchar](500),
 @BloodGroup [varchar](100)
)
as begin 
Update [TblEmployee] set EmployeeName= @EmployeeName,EmployeeNumber=@EmployeeNumber,EmployeeEmail=@EmployeeEmail,Address=@Address,BloodGroup=@BloodGroup where Id=@id
end 
GO

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

namespace BottleManufacturing_MVCwithOutEntity.Models
{
    public class CarModel
    {
        
        public int Id { get; set; }
        [StringLength(100)]
        [Required(ErrorMessage = "Employee name is required")]
        public string EmployeeName { get; set; }
        [StringLength(100)]
        public string EmployeeNumber { get; set; }
        [StringLength(100)]
        [EmailAddress(ErrorMessage = "Invalid Employee Email Address")]
        public string EmployeeEmail { get; set; }
        [StringLength(500)]
        public string Address { get; set; }
        [StringLength(100)]
        public string BloodGroup { get; set; }
    }
}

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

namespace BottleManufacturing_MVCwithOutEntity.Models
{
    public class tbl_user_Model
    {
        public int Id { get; set; }

        public string name { get; set; }

        public string Username { get; set; }
        public string pwd { get; set; }
    }
}

using BottleManufacturing_MVCwithOutEntity.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
 
using System.Data.SqlClient;
using System.Runtime.InteropServices;
using System.Net;

namespace BottleManufacturing_MVCwithOutEntity.Controllers
{
    public class EmployeeController : Controller
    {
        string constr = "Data Source=.\\sqlexpress;Initial Catalog=CAR_SHOWROOM;Integrated Security =true";
 
        // GET: venkat
        public ActionResult Index()
        {
            List<CarModel> CarObjModel = new List<CarModel>();
            using (SqlConnection con = new SqlConnection(constr))
            {
                SqlCommand cmd = new SqlCommand("sp_showEmployee", con);
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    CarObjModel.Add(new CarModel
                    {
                        Id = Convert.ToInt32(sdr["Id"]),
                        EmployeeName = sdr["EmployeeName"].ToString(),
                        EmployeeEmail = sdr["EmployeeEmail"].ToString(),
                        EmployeeNumber = sdr["EmployeeNumber"].ToString(),
                        Address = sdr["Address"].ToString(),
                        BloodGroup = sdr["BloodGroup"].ToString()
                    });
                }

                con.Close();
            }
            return View(CarObjModel);
        }

        // GET: venkat/Details/5
        public ActionResult Details(int id)
        {
            ViewBag.veera = "Hi Venkat....Get your details";

           CarModel CarObjModel = new CarModel();
            using (SqlConnection con = new SqlConnection(constr))
            {
                SqlCommand cmd = new SqlCommand("sp_showEmployee_ID " + id, con);
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    CarObjModel =  new CarModel
                    {
                        Id = Convert.ToInt32(sdr["Id"]),
                        EmployeeName = sdr["EmployeeName"].ToString(),
                        EmployeeEmail = sdr["EmployeeEmail"].ToString(),
                        EmployeeNumber = sdr["EmployeeNumber"].ToString(),
                        Address = sdr["Address"].ToString(),
                        BloodGroup = sdr["BloodGroup"].ToString()
                    } ;
                }

                con.Close();
            }
            return View(CarObjModel);
        }

        // GET: venkat/Create
        public ActionResult Create()
        {
            return View();
        }

        // POST: venkat/Create
        [HttpPost]
        public ActionResult Create(CarModel CarObjModel)
        {
            try
            {
                if (ModelState.IsValid)
                {
                    using (SqlConnection con = new SqlConnection(constr))
                    {
                        string query = "sp_CreateEmployee '" + CarObjModel.EmployeeName
                            + "','" + CarObjModel.EmployeeNumber
                            + "','" + CarObjModel.EmployeeEmail
                            + "','" + CarObjModel.Address
                            + "','" + CarObjModel.BloodGroup + "'";
                        SqlCommand cmd = new SqlCommand(query, con);
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();

                    }
                }
                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

      


        // GET: venkat/Edit/5
        public ActionResult Edit(int id)
        { 
            ViewBag.MSG = "Kaashiv Infotech .NET MVC Without EntityFramework Project";
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            else
            {
                CarModel CarObjModel = new CarModel();
                using (SqlConnection con = new SqlConnection(constr))
                {
                    SqlCommand cmd = new SqlCommand("sp_showEmployee_ID " + id, con);
                    con.Open();
                    SqlDataReader sdr = cmd.ExecuteReader();
                    while (sdr.Read())
                    {
                        CarObjModel = new CarModel
                        {
                            Id = Convert.ToInt32(sdr["Id"]),
                            EmployeeName = sdr["EmployeeName"].ToString(),
                            EmployeeEmail = sdr["EmployeeEmail"].ToString(),
                            EmployeeNumber = sdr["EmployeeNumber"].ToString(),
                            Address = sdr["Address"].ToString(),
                            BloodGroup = sdr["BloodGroup"].ToString()
                        };
                    }

                    con.Close();
                }
                return View(CarObjModel);
            }
            

        }

        // POST: venkat/Edit/5
        [HttpPost]
        public ActionResult Edit(int id, CarModel CarObjModel)
        {
            try
            {
                if (ModelState.IsValid)
                {
                    using (SqlConnection con = new SqlConnection(constr))
                    {
                        string query = "sp_update " + CarObjModel.Id
                            + ",'" + CarObjModel.EmployeeName
                            + "','" + CarObjModel.EmployeeNumber
                            + "','" + CarObjModel.EmployeeEmail
                            + "','" + CarObjModel.Address
                            + "','" + CarObjModel.BloodGroup + "'";
                        SqlCommand cmd = new SqlCommand(query, con);
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();

                    }
                }

                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

        // GET: venkat/Delete/5
        public ActionResult Delete(int id)
        {
            CarModel CarObjModel = new CarModel();
            using (SqlConnection con = new SqlConnection(constr))
            {
                SqlCommand cmd = new SqlCommand("sp_showEmployee_ID " + id, con);
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    CarObjModel = new CarModel
                    {
                        Id = Convert.ToInt32(sdr["Id"]),
                        EmployeeName = sdr["EmployeeName"].ToString(),
                        EmployeeEmail = sdr["EmployeeEmail"].ToString(),
                        EmployeeNumber = sdr["EmployeeNumber"].ToString(),
                        Address = sdr["Address"].ToString(),
                        BloodGroup = sdr["BloodGroup"].ToString()
                    };
                }

                con.Close();
            }
            return View(CarObjModel);

        }

        // POST: venkat/Delete/5
        [HttpPost]
        public ActionResult Delete(int id, FormCollection collection)
        {
            try
            {
                if (ModelState.IsValid)
                {
                    using (SqlConnection con = new SqlConnection(constr))
                    {
                        string query = "delete_id " + id;
                        SqlCommand cmd = new SqlCommand(query, con);
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();

                    }
                }


                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }
    }
}

using BottleManufacturing_MVCwithOutEntity.Models;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace BottleManufacturing_MVCwithOutEntity.Controllers
{
    public class LoginController : Controller
    {

        string constr = "Data Source=.\\sqlexpress;Initial Catalog=CAR_SHOWROOM;Integrated Security =true";

        public ActionResult Login()
        {
            return View();
        }

        // POST: venkat/Create
        [HttpPost]
        public ActionResult Login(tbl_user_Model tbl_user_model_obj)
        {
            try
            {
                int loggedID = 0;
                if (ModelState.IsValid)
                {
                    using (SqlConnection con = new SqlConnection(constr))
                    {
                        string query = "sp_tbl_userlogin '" + tbl_user_model_obj.Username
                            + "','" + tbl_user_model_obj.pwd + "'";
                        SqlCommand cmd = new SqlCommand(query, con);

                        con.Open();
                        SqlDataReader sdr = cmd.ExecuteReader();
                        while (sdr.Read())
                        {
                            tbl_user_model_obj = new tbl_user_Model
                            {
                                Id = Convert.ToInt32(sdr["id"]),
                                name = sdr["name"].ToString(),
                                Username = sdr["Username"].ToString(),
                                pwd = sdr["pwd"].ToString(),
                            };
                        }
                        con.Close();
                        loggedID = tbl_user_model_obj.Id;
                        if (loggedID == 0)
                        {
                            ModelState.AddModelError("", "Invalid Username and Password");
                        }
                        else
                        {
                            return RedirectToAction("Create","Employee");
                        }
                    }
                }
                return View();
            }
            catch
            {
                return View();
            }
        }





        // GET: Login
        public ActionResult Index()
        {
            return View();
        }

        // GET: Login/Details/5
        public ActionResult Details(int id)
        {
            return View();
        }

        // GET: Login/Create
        public ActionResult Create()
        {
            return View();
        }

        // POST: Login/Create
        [HttpPost]
        public ActionResult Create(FormCollection collection)
        {
            try
            {
                // TODO: Add insert logic here

                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

        // GET: Login/Edit/5
        public ActionResult Edit(int id)
        {
            return View();
        }

        // POST: Login/Edit/5
        [HttpPost]
        public ActionResult Edit(int id, FormCollection collection)
        {
            try
            {
                // TODO: Add update logic here

                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

        // GET: Login/Delete/5
        public ActionResult Delete(int id)
        {
            return View();
        }

        // POST: Login/Delete/5
        [HttpPost]
        public ActionResult Delete(int id, FormCollection collection)
        {
            try
            {
                // TODO: Add delete logic here

                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }
    }
}

@model BottleManufacturing_MVCwithOutEntity.Models.CarModel

@{
    ViewBag.Title = "Create";
}

<h2>Create</h2>


@using (Html.BeginForm()) 
{
    @Html.AntiForgeryToken()
    
    <div class="form-horizontal">
        <h4>CarModel</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(model => model.EmployeeName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.EmployeeName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.EmployeeName, "", new { @class = "text-danger" })
            </div>
        </div>

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

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

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

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

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

@model BottleManufacturing_MVCwithOutEntity.Models.CarModel

@{
    ViewBag.Title = "Delete";
}

<h2>Delete</h2>

<h3>Are you sure you want to delete this?</h3>
<div>
    <h4>CarModel</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.EmployeeName)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.EmployeeName)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.EmployeeNumber)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.EmployeeNumber)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.EmployeeEmail)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.EmployeeEmail)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.Address)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.Address)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.BloodGroup)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.BloodGroup)
        </dd>

    </dl>

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

        <div class="form-actions no-color">
            <input type="submit" value="Delete" class="btn btn-default" /> |
            @Html.ActionLink("Back to List", "Index")
        </div>
    }
</div>