1 / 4
2 / 4
3 / 4
4 / 4

Thursday 25 August 2022

ADO.Net

ASP .Net Core CRUD Operation with Photo/File Upload using ADO.Net in MVC Pattern

In this post we will see an Asp .Net Core CREATE, READ, UPDATE and DELETE functionality using ADO .Net using MVC architecture . In next post we will see the same example with Entity Framework Core. So lets get started.

Database Table Creation

Open SQL Server --> click on "New Query" new query then write the following code for generating a new Database Table tblEmployee.



USE [Your Database]
CREATE TABLE [dbo].[tblEmployee](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NULL,
	[Email] [varchar](50) NULL,
	[Phone] [varchar](50) NULL,
	[Address] [varchar](100) NULL,
	[Photo] [varchar](1000) NULL,
	[Gender] [varchar](10) NULL,
	[DOB] [datetime2](7) NULL,
	[Salary] [decimal](18, 0) NULL,
	[IsActive] [bit] NULL,
PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO 

(fig. 1)



Now Open Visual Studio ,then click on Create a new project (fig. 2)--> Select template ASP.NET Core Web App(Model-View-Controller) (fig. 3)--> Set Name and Location (fig. 4)--> Choose the version of .Net here I am choosing ".Net Core 3.1 (Log term support)".



                        (fig. 2)




                         (fig. 3)




                         (fig. 4)


                         (fig. 5)


After doing this a ASP.Net Core Project is created successfully.


Model

(ConnectionString.cs)

This class establishing the connection with Sql Server to our project.

 namespace MVC_CRUD.Models
{
    public class ConnectionString
    {
        private static string constr = "Data Source=<Your Server Name>; Initial Catalog=<Your Database>;Trusted_Connection=True;MultipleActiveResultSets=true";
        
        public static string Constr
        {
            get => constr;
        }
    }
}

(tblEmployee.cs)

This this the model mapping class of tblEmployee table.

using Microsoft.AspNetCore.Http;
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace MVC_CRUD.Models
{
    [Table("tblEmployee")]
    public class tblEmployee
    {
        [Key]
        public int Id { get; set; } 
        public string Name { get; set; }    
        public string Email { get; set; }
        public string Phone { get; set; }
        public string Address { get; set; }
        [NotMapped]
        public IFormFile Photo { get; set; }
        public string PhotoPath { get; set; }
        public string Gender { get; set; }
        public DateTime DOB { get; set; }
        public string Salary { get; set; }
        public bool IsActive { get; set; }
    }
}

Controller(EmployeeController.cs)

Add a new controller by Right Clicking on Controller folder -->MVC Controller - Empty --> Name it as EmployeeController.cs. 
(fig. 6)


using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using MVC_CRUD.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

using System.IO;

namespace MVC_CRUD.Controllers
{
    public class EmployeeController : Controller
    {
        SqlConnection con;
        SqlCommand cmd;
        SqlDataAdapter sda;
        DataTable dt;
        string connectionString = ConnectionString.Constr;
        IWebHostEnvironment _webHostEnvironment;
        public EmployeeController(IWebHostEnvironment webHostEnvironment)
        {
            _webHostEnvironment = webHostEnvironment;
        } 
        [HttpGet]
        public IActionResult Add()
        {
            return View();
        }
        [HttpPost]
        public IActionResult Add(tblEmployee employee)
        {
            if (ModelState.IsValid)
            {
                using (con = new SqlConnection(connectionString))
                {
                    string img = SaveImage(employee);
                    if (img != "")
                    {
                        employee.PhotoPath = img;
                    }
                    cmd = new SqlCommand("Insert into tblEmployee(Name,Email,Phone,Address,Photo,Gender,DOB,Salary,IsActive) " +
                        "values('" + employee.Name + "','" + employee.Email + "','" + employee.Phone + "','" + employee.Address + "','" + employee.PhotoPath + "'" +
                        ",'" + employee.Gender + "','" + DateTime.Parse(employee.DOB.ToString()).ToString("yyyy-MM-dd hh:MM:ss") + "','" + employee.Salary + "',1)", con);
                    cmd.CommandType = CommandType.Text;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                    ViewBag.msg = "Success";
                }
            }
            return RedirectToAction("AllEmployees");
        }
        public IActionResult AllEmployees(int? id)
        {
            using (con = new SqlConnection(connectionString))
            {
                List<tblEmployee> employee;
                tblEmployee emp;
                if (id != null)
                {
                    //cmd = new SqlCommand("Update tblEmployee set IsActive=0 where Id=" + id + "", con); to update the IsActive field 0
                    cmd = new SqlCommand("Delete from tblEmployee where Id=" + id + "", con);
                    cmd.CommandType = CommandType.Text;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                    ViewBag.msg = "Removed";
                }
                
                cmd = new SqlCommand("select * from tblEmployee where IsActive=1", con); //get all data where IsActive is 1
                cmd.CommandType = CommandType.Text;
                con.Open();
                employee = new List<tblEmployee>();
                using (sda = new SqlDataAdapter(cmd))
                {
                    using (dt = new DataTable())
                    {
                        sda.Fill(dt);
                        foreach (DataRow dr in dt.Rows)
                        {
                            emp = new tblEmployee();
                            emp.Id = Convert.ToInt32(dr[0].ToString());
                            emp.Name = dr[1].ToString();
                            emp.Email = dr[2].ToString();
                            emp.Phone = dr[3].ToString();
                            employee.Add(emp);
                        }
                    }
                }
                con.Close();
                //ViewBag.employee = dt;
                return View(employee);
            }

        }
        public IActionResult Details(int id)
        {
            tblEmployee emp = GetbyId(id);
            return View(emp);
        }
        [HttpGet]
        public IActionResult Edit(int id)
        {
            tblEmployee emp = GetbyId(id);
            return View(emp);
        }
        [HttpPost]
        public IActionResult Edit(tblEmployee employee)
        {
            if (ModelState.IsValid)
            {
                if (employee.Photo != null)
                {
                    string img = SaveImage(employee);
                    employee.PhotoPath = img;
                }

                using (con = new SqlConnection(connectionString))
                {
                    cmd = new SqlCommand("Update tblEmployee set Name='" + employee.Name + "',Email='" + employee.Email + "',Phone='" + employee.Phone + "'," +
                        "Address='" + employee.Address + "',Photo='" + employee.PhotoPath + "',Gender='" + employee.Gender + "'," +
                        "DOB='" + DateTime.Parse(employee.DOB.ToString()).ToString("yyyy-MM-dd hh:ss:mm") + "',Salary='" + employee.Salary + "',IsActive=1 where Id=" + employee.Id + "", con);
                    cmd.CommandType = CommandType.Text;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                    ViewBag.msg = "Success";
                }
            }
            tblEmployee emp = GetbyId(employee.Id);
            return View(emp);

        }
        public tblEmployee GetbyId(int id)
        {
            tblEmployee emp;
            using (con = new SqlConnection(connectionString))
            {
                cmd = new SqlCommand("Select * from tblEmployee where Id=" + id + "", con);
                cmd.CommandType = CommandType.Text;
                con.Open();
                cmd.ExecuteReader();
                con.Close();
                using (sda = new SqlDataAdapter(cmd))
                {
                    using (dt = new DataTable())
                    {
                        sda.Fill(dt);
                        emp = new tblEmployee();
                        emp.Id = Convert.ToInt32(dt.Rows[0][0].ToString());
                        emp.Name = dt.Rows[0][1].ToString();
                        emp.Email = dt.Rows[0][2].ToString();
                        emp.Phone = dt.Rows[0][3].ToString();
                        emp.Address = dt.Rows[0][4].ToString();
                        emp.PhotoPath = dt.Rows[0][5].ToString();
                        emp.Gender = dt.Rows[0][6].ToString();
                        emp.DOB = Convert.ToDateTime(dt.Rows[0][7].ToString());
                        emp.Salary = dt.Rows[0][8].ToString();
                    }
                }
            }
            return emp;
        }
        public string SaveImage(tblEmployee employee)
        {
            string filename = "";
            string path = Path.Combine(_webHostEnvironment.WebRootPath, "ProfileImage");

            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            if (employee.PhotoPath != null)
            {
                FileInfo file = new FileInfo(Path.Combine(path, employee.PhotoPath));
                if (file.Exists)
                {
                    file.Delete();
                }
            }
            string ext = Path.GetExtension(employee.Photo.FileName);
            filename = Guid.NewGuid().ToString() + "_cover" + ext;
            var filepath = Path.Combine(path, filename);
            using (var fileStream = new FileStream(filepath, FileMode.Create))
            {
                employee.Photo.CopyTo(fileStream);

            }

            return filename;
        }
    }
}

View

To add view for Add method, Hover on method body then Right Click on it then click on Add View --> Choose Razor View - Empty

(fig. 7)


(Add.cshtml)


@model MVC_CRUD.Models.tblEmployee
@{
    ViewData["Title"] = "Employee";
}
<style>
    .card-header {
        background-color: #b8daff;
    }
</style>
<section>
    <div class="row">
        <div class="col-lg-12">
            <a class="btn btn-primary mb-2 float-right" asp-area="" asp-controller="Employee" asp-action="AllEmployees">All Employees</a>
        </div>
    </div>

    <div class="card">
        <div class="card-header">
            <h2>Employee Registration Form</h2>
        </div>
        <div class="card-body">
            <form asp-action="Add" asp-controller="Employee" method="post" enctype="multipart/form-data" autocomplete="off">
                <div class="form-group">

                    <div class="row">
                        <div class="col-lg-6">
                            <label asp-for="Name"></label>
                            <input type="text" class="form-control" id="txtName" asp-for="Name">
                        </div>
                        <div class="col-lg-6">
                            <label asp-for="Email"></label>
                            <input type="email" class="form-control" id="txtEmail" asp-for="Email" placeholder="">
                        </div>
                    </div>
                    <div class="row mt-3">
                        <div class="col-lg-6">
                            <label asp-for="Phone"></label>
                            <input type="text" class="form-control" id="txtPhone" asp-for="Phone">
                        </div>
                        <div class="col-lg-6">
                            <label asp-for="Photo"></label>
                            <input type="file" class="form-control" id="Photo" asp-for="Photo">
                        </div>

                    </div>
                    <div class="row mt-3">
                        <div class="col-lg-3">
                            <label asp-for="Gender"></label>
                            <select class="form-control" asp-for="Gender">
                                <option value="">--Select Gender--</option>
                                <option value="Male">Male</option>
                                <option value="Female">Female</option>
                                <option value="Other">Other</option>

                            </select>
                        </div>
                        <div class="col-lg-3">
                            <label asp-for="DOB"></label>
                            <input type="date" class="form-control" id="txtDob" asp-for="DOB">
                        </div>
                        <div class="col-lg-6">
                            <label asp-for="Salary"></label>
                            <input type="text" class="form-control" id="txtSalary" asp-for="Salary">
                        </div>
                    </div>
                    <div class="row mt-3">

                        <div class="col-lg-12">
                            <label asp-for="Address"></label>
                            <textarea class="form-control" id="txtName" asp-for="Address" rows="3"></textarea>
                        </div>
                    </div>

                    <div class="row mt-2">
                        <div class="col-lg-12">
                            <input type="submit" class="btn btn-primary float-right" value="Submit" />
                            <input type="reset" class="btn btn-default float-right" />
                        </div>

                    </div>

                </div>
            </form>
        </div>

    </div>

</section>


(AllEmployees.cshtml)


@using System.Data
@model IEnumerable<tblEmployee>

@{
    ViewData["Title"] = "Employee";
    int sl = 1;
}
<div class="row">
    <div class="col-lg-12">
        <a class="btn btn-primary mb-2 float-right" asp-area="" asp-controller="Employee" asp-action="Add">Add Employee</a>
    </div>
</div>
<div class="table-responsive-sm">

    @if (ViewBag.msg != null)
    {
        <div class="row">
            <div class="col-md-12 text-center  bg-danger">
                <span class="text-light">@ViewBag.msg</span>
                <span class="text-light float-right">x</span>
            </div>
        </div>
    }
    <table class="table table-bordered table-hover">
        <thead>
            <tr class="table-primary">
                <th scope="col">Sl No.</th>
                <th scope="col">Name</th>
                <th scope="col">Email Id</th>
                <th scope="col">Phone</th>
                <th scope="col">Action</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var item in Model)
            {

                <tr>
                    <th scope="row">@sl.</th>
                    <td>@item.Name</td>
                    <td>@item.Email</td>
                    <td>@item.Phone</td>
                    <td>
                        <a asp-action="Details" asp-controller="Employee" asp-route-id="@item.Id" class="btn btn-primary">View</a>
                        <a asp-action="Edit" asp-controller="Employee" asp-route-id="@item.Id" class="btn btn-primary">Edit</a>
                        <a asp-action="AllEmployees" asp-controller="Employee" asp-route-id="@item.Id" class="btn btn-danger">Delete</a>
                    </td>
                </tr>
                sl++;
            }

        </tbody>
    </table>
</div>

(Details.cshtml)

@model tblEmployee
@{
    ViewData["Title"] = "Employee";
}
<section>
    <div class="row">
        <div class="col-lg-12">
            <a class="btn btn-primary mb-2 float-right" asp-area="" asp-controller="Employee" asp-action="AllEmployees">All Employees</a>
        </div>
    </div>

    <div class="card">
        <div class="card-header">
            <h2>Employee Details</h2>
        </div>
        <div class="card-body">
            <div class="row">
                <div class="col-lg-4">
                    <div class="card mb-4">
                        <div class="card-body text-center">
                            @if (Model.PhotoPath.ToString() != "")
                            {
                                <img src='~/ProfileImage/@Model.PhotoPath' alt="avatar" class="rounded-circle img-fluid" style="width: 150px;height:150px;">
                            }
                            else
                            {
                                <img src="/Images/park.jpg" alt="avatar" class="rounded-circle img-fluid" style="width: 150px;">
                            }
                            <h5 class="my-3">@Model.Name </h5>
                            <p class="text-muted mb-1">@Model.Email</p>
                            <div class="d-flex justify-content-center mb-2">
                                <a asp-action="Edit" asp-controller="Employee" asp-route-id="@Model.Id" class="btn btn-primary">Edit</a>
                            </div>
                        </div>
                    </div>
                </div>
                <div class="col-lg-8">
                    <div class="card mb-4">
                        <div class="card-body">
                            <div class="row">
                                <div class="col-sm-3">
                                    <p class="mb-0">Phone</p>
                                </div>
                                <div class="col-sm-9">
                                    <p class="text-muted mb-0">@Model.Phone</p>
                                </div>
                            </div>
                            <hr>
                            <div class="row">
                                <div class="col-sm-3">
                                    <p class="mb-0">Gender</p>
                                </div>
                                <div class="col-sm-9">

                                    <p class="text-muted mb-0">@Model.Gender</p>


                                </div>
                            </div>
                            <hr>
                            <div class="row">
                                <div class="col-sm-3">
                                    <p class="mb-0">DOB</p>
                                </div>
                                <div class="col-sm-9">
                                    <p class="text-muted mb-0">@Model.DOB.ToShortDateString()</p>
                                </div>
                            </div>
                            <hr>
                            <div class="row">
                                <div class="col-sm-3">
                                    <p class="mb-0">Salary</p>
                                </div>
                                <div class="col-sm-9">
                                    <p class="text-muted mb-0">@Model.Salary</p>
                                </div>
                            </div>
                            <hr>
                            <div class="row">
                                <div class="col-sm-3">
                                    <p class="mb-0">Address</p>
                                </div>
                                <div class="col-sm-9">
                                    <p class="text-muted mb-0">@Model.Address</p>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>

        </div>
    </div>
</section>

(Details.cshtml)

@model tblEmployee
@{
    ViewData["Title"] = "Employee";
}
<style>
    img {
        height: 71px;
        width: 71px;
        padding: 5px;
    }

    .card-header {
        background-color: #b8daff;
    }
</style>
<section>
    <div class="row">
        <div class="col-lg-12">
            <a class="btn btn-primary mb-2 float-right" asp-area="" asp-controller="Employee" asp-action="AllEmployees">All Employees</a>
        </div>
    </div>

    <div class="card">
        <div class="card-header">
            <h2>Edit Employee</h2>
        </div>
        <div class="card-body">
            <form asp-action="Edit" asp-controller="Employee" method="post" enctype="multipart/form-data">
                <div class="form-group">
                    @if (ViewBag.msg != null)
                    {
                        <div class="row bg-success">
                            <div class="col-lg-12 text-center">
                                <span class="text-light">@ViewBag.msg</span>
                                <span class="text-light float-right">x</span>
                            </div>
                        </div>
                    }
                    <div class="row">
                        <div class="col-lg-6">
                            <label asp-for="Name"></label>
                            <input type="text" class="form-control" id="txtName" asp-for="Name">
                        </div>
                        <div class="col-lg-6">
                            <label asp-for="Email"></label>
                            <input type="email" class="form-control" id="txtEmail" asp-for="Email" placeholder="">
                        </div>
                    </div>
                    <div class="row mt-3">
                        <div class="col-lg-6">
                            <label asp-for="Phone"></label>
                            <input type="text" class="form-control" id="txtPhone" asp-for="Phone">
                        </div>
                        <div class="col-lg-4">
                            <label asp-for="Photo"></label>
                            <input type="file" class="form-control" id="txtPhoto" asp-for="Photo">
                        </div>
                        <div class="col-lg-2">
                            <input type="hidden" asp-for="PhotoPath" value="@Model.PhotoPath" />
                            <img src="~/ProfileImage/@Model.PhotoPath" alt="" />
                        </div>
                    </div>
                    <div class="row mt-3">
                        <div class="col-lg-3">
                            <label asp-for="Gender"></label>
                            <select class="form-control" asp-for="Gender">
                                <option value="">--Select Gender--</option>
                                <option value="Male">Male</option>
                                <option value="Female">Female</option>
                                <option value="Other">Other</option>

                            </select>
                        </div>
                        <div class="col-lg-3">
                            <label asp-for="DOB"></label>
                            <input type="date" class="form-control" id="txtDob" asp-for="DOB">
                        </div>
                        <div class="col-lg-6">
                            <label asp-for="Salary"></label>
                            <input type="text" class="form-control" id="txtSalary" asp-for="Salary">
                        </div>
                    </div>
                    <div class="row mt-3">

                        <div class="col-lg-12">
                            <label asp-for="Address"></label>
                            <textarea class="form-control" id="txtName" asp-for="Address" rows="3"></textarea>
                        </div>
                    </div>
                    <div class="row mt-2">
                        <div class="col-lg-12">
                            <input type="submit" class="btn btn-primary float-right" value="Save" />
                        </div>

                    </div>
                </div>
            </form>
        </div>

    </div>

</section>
Demo Video


In the next Post we will do the ASP.NET CORE project using Entity Framework Core.



Thank You

at
Tags ,

2 comments:

  1. Anonymous4 September 2022 at 15:09

    nice

    ReplyDelete
  2. Anonymous17 December 2022 at 18:35

    Hi. I liek your tutorial much but the clip so short. Please step by step a-z please. I need it

    ReplyDelete

If you have any doubts please let me know

Subscribe to: Post Comments (Atom)