Delete Data From Database Table | ADO.NET | CRUD App ASP.NET MVC Using ADO.NET

using System;

using System.Collections.Generic;

using System.ComponentModel.DataAnnotations;

using System.Linq;

using System.Web;


namespace CRUDAppUsingADO.Models

{

    public class Employee

    {

        public int id { get; set; }


        [Required]

        public string name { get; set; }


        [Required]

        public string gender { get; set; }


        [Required]

        public int age { get; set; }


        [Required]

        public int salary { get; set; }


        [Required]

        public string city { get; set; }

    }

}

Above File is CRUDAppUsingADO\Models\Employee.cs







using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data.SqlClient;

using System.Data;

using System.Configuration;

using CRUDAppUsingADO.Models;

using System.Runtime.InteropServices.WindowsRuntime;


namespace CRUDAppUsingADO

{

    public class EmployeeDBContext

    {

        string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;


        public List<Employee> GetEmployees()

        {

            List<Employee> EmployeeList = new List<Employee>();

            SqlConnection con = new SqlConnection(cs);

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

            cmd.CommandType = CommandType.StoredProcedure;

            con.Open();

            SqlDataReader dr = cmd.ExecuteReader();

            while(dr.Read())

            {

                Employee emp = new Employee();

                emp.id = Convert.ToInt32(dr.GetValue(0).ToString());

                emp.name = dr.GetValue(1).ToString();

                emp.gender = dr.GetValue(2).ToString();

                emp.age = Convert.ToInt32(dr.GetValue(3).ToString());

                emp.salary = Convert.ToInt32(dr.GetValue(4).ToString());

                emp.city = dr.GetValue(5).ToString();

                EmployeeList.Add(emp);

            }

            con.Close();



            return EmployeeList;

        }


        public bool AddEmployee(Employee emp)

        {

            SqlConnection con = new SqlConnection(cs);

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

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@name",emp.name);

            cmd.Parameters.AddWithValue("@gender",emp.gender);

            cmd.Parameters.AddWithValue("@age",emp.age);

            cmd.Parameters.AddWithValue("@salary",emp.salary);

            cmd.Parameters.AddWithValue("@city",emp.city);

            con.Open();

            int i = cmd.ExecuteNonQuery();  //We use ExecuteNonQuery() for Insert,Update,Delete

            con.Close();


            if (i > 0)

            {

                return true;

            }

            else

            {

                return false;

            }

        }


        public bool UpdateEmployee(Employee emp)

        {

            SqlConnection con = new SqlConnection(cs);

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

            cmd.CommandType = CommandType.StoredProcedure;

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

            cmd.Parameters.AddWithValue("@name",emp.name);

            cmd.Parameters.AddWithValue("@gender",emp.gender);

            cmd.Parameters.AddWithValue("@age",emp.age);

            cmd.Parameters.AddWithValue("@salary",emp.salary);

            cmd.Parameters.AddWithValue("@city",emp.city);

            con.Open();

            int i = cmd.ExecuteNonQuery();  //We use ExecuteNonQuery() for Insert,Update,Delete

            con.Close();

            if (i > 0)

            {

                return true;

            }

            else

            {

                return false;

            }

        }


        public bool DeleteEmployee(int id)

        {

            SqlConnection con = new SqlConnection(cs);

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

            cmd.CommandType = CommandType.StoredProcedure;

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

            con.Open();

            int i = cmd.ExecuteNonQuery();  //We use ExecuteNonQuery() for Insert,Update,Delete

            con.Close();

            if (i > 0)

            {

                return true;

            }

            else

            {

                return false;

            }

        }

    }

}

Above File is CRUDAppUsingADO\EmployeeDBContext.cs








using System;

using System.Collections.Generic;

using System.Diagnostics;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using CRUDAppUsingADO.Models;


namespace CRUDAppUsingADO.Controllers

{

    public class HomeController : Controller

    {

        // GET: Home

        public ActionResult Index()

        {

            EmployeeDBContext db = new EmployeeDBContext();

            List<Employee> obj = db.GetEmployees();


            return View(obj);

        }


        public ActionResult Create()

        {

            return View();

        }


        [HttpPost]

        public ActionResult Create(Employee emp)

        {

            try

            {

                if (ModelState.IsValid == true)

                {

                    EmployeeDBContext context = new EmployeeDBContext();

                    bool check = context.AddEmployee(emp);

                    if (check == true)

                    {

                        TempData["InsertMessage"] = "Data has been Inserted Successfully.";

                        ModelState.Clear();

                        return RedirectToAction("Index");

                    }

                }

                return View();

            }

            catch (Exception ex)

            {

                return View();

            }

        }


        public ActionResult Edit(int id)

        {

            EmployeeDBContext context = new EmployeeDBContext();

            var row = context.GetEmployees().Find(model => model.id == id);

            return View(row);

        }


        [HttpPost]

        public ActionResult Edit(int id,Employee emp)

        {

            if(ModelState.IsValid == true)

            {

                EmployeeDBContext context = new EmployeeDBContext();

                bool check = context.UpdateEmployee(emp);

                if (check == true)

                {

                    TempData["UpdateMessage"] = "Data has been Updated Successfully.";

                    ModelState.Clear();

                    return RedirectToAction("index");

                }

            }

            return View();

        }


        public ActionResult Details(int id)

        {

            EmployeeDBContext context = new EmployeeDBContext();

            var row = context.GetEmployees().Find(model => model.id == id);

            return View(row);

        }


        public ActionResult Delete(int id)

        {

            EmployeeDBContext context = new EmployeeDBContext();

            var row = context.GetEmployees().Find(model => model.id == id);

            return View(row);

        }


        [HttpPost]

        public ActionResult Delete(int id,Employee emp)

        {

            EmployeeDBContext context = new EmployeeDBContext();

            bool check = context.DeleteEmployee(id);

            if (check == true)

            {

                TempData["DeleteMessage"] = "Data has been Deleted Successfully.";

                return RedirectToAction("index");

            }

            return View();

        }

    }

}

Above File is CRUDAppUsingADO\Controllers\HomeController.cs







@model IEnumerable<CRUDAppUsingADO.Models.Employee>


@{

    ViewBag.Title = "Index";

}


<h2>Index</h2>


@if (TempData["InsertMessage"] != null)

{

    <div class="alert alert-success alert-dismissible fade show" role="alert">

        <strong>Success!</strong> @TempData["InsertMessage"]

        <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>

    </div>

}

@if (TempData["UpdateMessage"] != null)

{

    <div class="alert alert-success alert-dismissible fade show" role="alert">

        <strong>Success!</strong> @TempData["UpdateMessage"]

        <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>

    </div>

}

@if (TempData["DeleteMessage"] != null)

{

    <div class="alert alert-success alert-dismissible fade show" role="alert">

        <strong>Success!</strong> @TempData["DeleteMessage"]

        <button type="button" class="btn-close" data-bs-dismiss="alert" aria-label="Close"></button>

    </div>

}


<p>

    @Html.ActionLink("Create New", "Create")

</p>

<table class="table table-bordered table-hover table-striped">

    <tr>

        <th>

            @Html.DisplayNameFor(model => model.id)

        </th>

        <th>

            @Html.DisplayNameFor(model => model.name)

        </th>

        <th>

            @Html.DisplayNameFor(model => model.gender)

        </th>

        <th>

            @Html.DisplayNameFor(model => model.age)

        </th>

        <th>

            @Html.DisplayNameFor(model => model.salary)

        </th>

        <th>

            @Html.DisplayNameFor(model => model.city)

        </th>

        <th>Operations</th>

    </tr>


@foreach (var item in Model) {

    <tr>

        <td>

            @Html.DisplayFor(modelItem => item.id)

        </td>

        <td>

            @Html.DisplayFor(modelItem => item.name)

        </td>

        <td>

            @Html.DisplayFor(modelItem => item.gender)

        </td>

        <td>

            @Html.DisplayFor(modelItem => item.age)

        </td>

        <td>

            @Html.DisplayFor(modelItem => item.salary)

        </td>

        <td>

            @Html.DisplayFor(modelItem => item.city)

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

Above File is CRUDAppUsingADO\Views\Home\Index.cshtml











@model CRUDAppUsingADO.Models.Employee

@{
    ViewBag.Title = "Create";
}

<h2>Create</h2>


@using (Html.BeginForm()) 
{
    @Html.AntiForgeryToken()
    
    <div class="form-horizontal">
        <h4>Employee</h4>
        <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.gender, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.gender, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.gender, "", new { @class = "text-danger" })
            </div>
        </div>

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

        <div class="form-group">
            @Html.LabelFor(model => model.salary, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.salary, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.salary, "", 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">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Create" class="mt-2 btn btn-primary" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</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>
Above File is CRUDAppUsingADO\Views\Home\Create.cshtml












<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>@ViewBag.Title - My ASP.NET Application</title>
    <link href="~/Content/Site.css" rel="stylesheet" type="text/css" />
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous" />
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz" crossorigin="anonymous"></script>
    <link href="~/Content/bootstrap.min.css" rel="stylesheet" type="text/css" />
    <script src="~/Scripts/modernizr-2.6.2.js"></script>
</head>
<body>
    <div class="navbar navbar-inverse navbar-fixed-top">
        <div class="container">
            <div class="navbar-header">
                <button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                </button>
                @Html.ActionLink("Application name", "Index", "Home", new { area = "" }, new { @class = "navbar-brand" })
            </div>
            <div class="navbar-collapse collapse">
                <ul class="nav navbar-nav">
                </ul>
            </div>
        </div>
    </div>

    <div class="container body-content">
        @RenderBody()
        <hr />
        <footer>
            <p>&copy; @DateTime.Now.Year - My ASP.NET Application</p>
        </footer>
    </div>
    <script src="~/Scripts/jquery-1.10.2.min.js"></script>
    <script src="~/Scripts/bootstrap.min.js"></script>
</body>
</html>
Above File is CRUDAppUsingADO\Views\Shared\_Layout.cshtml











body {
    padding-top: 50px;
    padding-bottom: 20px;
}
/*.navbar-header {
    background-color:black;
}*/

/* Set padding to keep content from hitting the edges */
.body-content {
    padding-left: 15px;
    padding-right: 15px;
}

/* Set width on the form input elements since they're 100% wide by default */
input,
select,
textarea {
    max-width: 280px;
}
Above File is CRUDAppUsingADO\Content\Site.css














@model CRUDAppUsingADO.Models.Employee

@{
    ViewBag.Title = "Edit";
}

<h2>Edit</h2>


@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()
    
    <div class="form-horizontal">
        <h4>Employee</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.gender, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.gender, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.gender, "", new { @class = "text-danger" })
            </div>
        </div>

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

        <div class="form-group">
            @Html.LabelFor(model => model.salary, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.salary, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.salary, "", 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">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Save" class="mt-2 btn btn-primary" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</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>
Above File is CRUDAppUsingADO\Views\Home\Edit.cshtml

















@model CRUDAppUsingADO.Models.Employee

@{
    ViewBag.Title = "Details";
}

<h2>Details</h2>

<div>
    <h4>Employee</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.id)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.id)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.name)
        </dt>

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

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

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

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

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

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

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

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

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

    </dl>
</div>
<p>
    @Html.ActionLink("Edit", "Edit", new { id = Model.id }) |
    @Html.ActionLink("Back to List", "Index")
</p>
Above File is CRUDAppUsingADO\Views\Home\Details.cshtml



Comments

Post a Comment

Popular posts from this blog

Create a User Registration Form in ASP.NET using SQL Server, Visual Studio 2022 & Bootstrap

Image
 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="userreg.aspx.cs" Inherits="Registration_Form.userreg" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server">     <title>User Registration</title>     <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous"/>     <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz" crossorigin="anonymous"></script> </head> <body>     <div class="container">         <form class="row g-3 bg-light" runat="server" meth...
Read more

Create a Simple Login Form in ASP.NET using Visual Studio 2022

Image
 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="login.aspx.cs" Inherits="LoginFormASP.login" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server">     <title></title> </head> <body>     <h1 align="center">Login Form</h1>     <form id="form1" runat="server">         <table align="center">             <tr>                 <td>Username</td>                 <td>                     <asp:TextBox ID="txtUser" runat="server"></asp:TextBox>                 </td>             </tr>             <tr>       ...
Read more

SqlCommand Class ADO.Net | ExecuteNonQuery | ExecuteReader | ExecuteScalar

Image
 using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data; using System.Data.SqlClient; using System.Configuration; namespace SQLConnectionADO {     internal class Program     {         static void Main(string[] args)         {             Program.Connection();             Console.ReadLine();         }         static void Connection()         {             string cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;             SqlConnection con = null;             try             {                 using (con = new SqlConnection(cs))       ...
Read more