Search This Blog

Thursday 29 March 2018

CRUD Operations In ASP.NET MVC 5 Using Dapper ORM


Step 1: - Open Visual Studio 2015 => Goto File Menu => New => Project...

Step 2: - In the Installed Templates list, select Visual C# => Web

Step 3: - Select ASP.Net Web Application from the Web list => Type DapperCRUD in the Name box and click OK

Step 4: - Select Empty template from ASP.NET Templates List and Checked MVC check box under Add folders and core references for:


Step 5: - Database Script

CREATE DATABASE DapperCRUD

USE DapperCRUD

Table Script

CREATE TABLE [dbo].[Product] (
    [ProductId]     INT IDENTITY (1, 1) NOT NULL,
    [ProductName]   VARCHAR (50)    NOT NULL,
    [ProductCode]   VARCHAR (50)    NOT NULL,
    [ProductQty]    INT             NOT NULL,
    [ProductWeight] DECIMAL (18, 2) NOT NULL,
    [ProductDesc]   VARCHAR (500)   NULL,
    PRIMARY KEY CLUSTERED ([ProductId] ASC)
);

Add Store Procedure Script

CREATE PROCEDURE [dbo].[Insert_Product]
@ProductName VARCHAR(50),
@ProductCode VARCHAR(50),
@ProductQty INT,
@ProductWeight DECIMAL(18,2),
@ProductDesc VARCHAR(500)
AS
BEGIN
            INSERT INTO Product(
                        ProductName,
                        ProductCode,
                        ProductQty,
                        ProductWeight,
                        ProductDesc
            )
            VALUES
            (
                        @ProductName,
                        @ProductCode,
                        @ProductQty,
                        @ProductWeight,
                        @ProductDesc
            )
END

Get Store Procedure Script

CREATE PROCEDURE [dbo].[Get_Product]
AS
BEGIN
            SELECT * FROM Product
END

Get Store Procedure Script

CREATE PROCEDURE [dbo].[Get_ProductByProductId]
@ProductId INT
AS
BEGIN
            SELECT * FROM Product WHERE ProductId = @ProductId
END

Update Store Procedure Script

CREATE PROCEDURE [dbo].[Update_Product]
@ProductId INT,
@ProductName VARCHAR(50),
@ProductCode VARCHAR(50),
@ProductQty INT,
@ProductWeight DECIMAL(18,2),
@ProductDesc VARCHAR(500)
AS
BEGIN
            UPDATE Product
            SET
                        ProductName = @ProductName,
                        ProductCode = @ProductCode,
                        ProductQty = @ProductQty,
                        ProductWeight = @ProductWeight,
                        ProductDesc = @ProductDesc
            WHERE ProductId = @ProductId  
END

Delete Store Procedure Script

CREATE PROCEDURE [dbo].[Delete_Product]
@ProductId INT
AS
BEGIN
            DELETE FROM Product WHERE ProductId = @ProductId
END

Step 6:- Add Dapper using NuGet Packages Manager

Step 7: - Right Click on Project Root folder => Click on Manage NuGet Packages…=> Search Dapper in search box and Select Dapper from list => Click on Install button





Step 8:- Right-click Models folder in Solution Explorer => Add => Click New Items... => Select Visual C# Code from left pane =>Select Class from middle pane => Type ProductModel.cs in the Name box => Click Add

Step 9:- Copy Past following code in ProductModel.cs

using System.ComponentModel.DataAnnotations;

namespace DapperCRUD.Models
{
    public class ProductModel
    {
        public int ProductId { get; set; }
        [Required]
        public string ProductName { get; set; }
        [Required]
        public string ProductCode { get; set; }
        [Required]
        public int ProductQty { get; set; }
        [Required]
        public decimal ProductWeight { get; set; }
        public string ProductDesc { get; set; }
    }
}

Step 10:- Right-click Models folder in Solution Explorer => Add => Click New Items... => Select Visual C# Code from left pane =>Select Class from middle pane => Type ProductRepository.cs in the Name box => Click Add

Step 11:- Copy Past following code in ProductRepository.cs

using Dapper;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace DapperCRUD.Models
{
    public class ProductRepository
    {
        private IDbConnection _con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);

        //Add Product     
        public int AddProduct(ProductModel objProduct)
        {
            int intFlag = 0;
            try
            {
                DynamicParameters param = new DynamicParameters();
                param.Add("@ProductName", objProduct.ProductName);
                param.Add("@ProductCode", objProduct.ProductCode);
                param.Add("@ProductQty", objProduct.ProductQty);
                param.Add("@ProductWeight", objProduct.ProductWeight);
                param.Add("@ProductDesc", objProduct.ProductDesc);
                intFlag = _con.Execute("Insert_Product", param, commandType: CommandType.StoredProcedure);
                _con.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intFlag;
        }

        //Get All Products 
        public List<ProductModel> GetAllProduct()
        {
            try
            {
                IList<ProductModel> productList = SqlMapper.Query<ProductModel>(_con, "Get_Product").ToList();
                _con.Close();
                return productList.ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Get Product By Product Id 
        public ProductModel GetProductByProductId(int productId)
        {
            try
            {
                DynamicParameters param = new DynamicParameters();
                param.Add("@ProductId", productId);
                ProductModel productList = SqlMapper.Query<ProductModel>(_con, "Get_ProductByProductId", param, commandType: CommandType.StoredProcedure).FirstOrDefault();
                _con.Close();
                return productList;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Update Product   
        public int UpdateProduct(ProductModel objProduct)
        {
            int intFlag = 0;
            try
            {
                DynamicParameters param = new DynamicParameters();
                param.Add("@ProductId", objProduct.ProductId);
                param.Add("@ProductName", objProduct.ProductName);
                param.Add("@ProductCode", objProduct.ProductCode);
                param.Add("@ProductQty", objProduct.ProductQty);
                param.Add("@ProductWeight", objProduct.ProductWeight);
                param.Add("@ProductDesc", objProduct.ProductDesc);
                intFlag = _con.Execute("Update_Product", param, commandType: CommandType.StoredProcedure);
                _con.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intFlag;
        }

        //Delete Product  
        public int DeleteProduct(int productId)
        {
            int intFlag = 0;
            try
            {
                DynamicParameters param = new DynamicParameters();
                param.Add("@ProductId", productId);
                intFlag = _con.Execute("Delete_Product", param, commandType: CommandType.StoredProcedure);
                _con.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return intFlag;
        }
    }
}

Step 12:- Right Click on Project Root folder => Click Build

Step 13:- Right-click Controllers folder in Solution Explorer => Add => Click Controllers...

Step 14:- Select MVC 5 Controller with read/write actions from controller list = > Click Add


Step 15:- Type ProductController in the Controller name box and click Add

Step 16:- Add following Action method in ProductController

using DapperCRUD.Models;
using System.Web.Mvc;

namespace DapperCRUD.Controllers
{
    public class ProductController : Controller
    {
        ProductRepository productRepository = null;
        // GET: Product
        public ActionResult Index()
        {
            productRepository = new ProductRepository();
            return View(productRepository.GetAllProduct());
        }

        // GET: Details
        [HttpGet]
        public ActionResult Details(int prodId)
        {
            productRepository = new ProductRepository();
            return View(productRepository.GetProductByProductId(prodId));
        }

        // GET: Create
        [HttpGet]
        public ActionResult Create()
        {
            return View();
        }

        // POST: Create
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Create(ProductModel p)
        {
            try
            {
                if (ModelState.IsValid)
                {
                    productRepository = new ProductRepository();
                    int x = productRepository.AddProduct(p);
                    if (x > 0)
                    {
                        return RedirectToAction("Index");
                    }
                }
                return View();
            }
            catch
            {
                return View();
            }
        }

        // GET: Edit
        [HttpGet]
        public ActionResult Edit(int prodId)
        {
            productRepository = new ProductRepository();
            return View(productRepository.GetProductByProductId(prodId));
        }

        // POST: Edit
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Edit(ProductModel p)
        {
            try
            {
                if (ModelState.IsValid)
                {
                    productRepository = new ProductRepository();
                    int x = productRepository.UpdateProduct(p);
                    if (x > 0)
                    {
                        return RedirectToAction("Index");
                    }
                }
                return View();
            }
            catch
            {
                return View();
            }
        }

        // GET: Delete
        [HttpGet]
        [ActionName("Delete")]
        public ActionResult Delete_Get(int prodId)
        {
            productRepository = new ProductRepository();
            return View(productRepository.GetProductByProductId(prodId));
        }

        // POST: Delete
        [HttpPost]
        [ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public ActionResult Delete_Post(int prodId)
        {
            try
            {
                productRepository = new ProductRepository();
                int x = productRepository.DeleteProduct(prodId);
                if (x > 0)
                {
                    return RedirectToAction("Index");
                }
                return View();
            }
            catch
            {
                return View();
            }
        }
    }
}

Step 17:- To Add View of Action Method, Right-click inside the Index Action body => Click Add View =>  Choose List from Template dropdown box and ProductModel(DapperCRUD.Models) from Model class dropdown box to generate strongly type view from class => Click Add



Copy Past following code in Index.cshtml

@model IEnumerable<DapperCRUD.Models.ProductModel>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.ProductId)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.ProductName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.ProductCode)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.ProductQty)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.ProductWeight)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.ProductDesc)
        </th>
        <th></th>
    </tr>

    @foreach (var item in Model)
    {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.ProductId)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.ProductName)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.ProductCode)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.ProductQty)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.ProductWeight)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.ProductDesc)
            </td>
            <td>
                @Html.ActionLink("Edit", "Edit", new { prodId = item.ProductId }) |
                @Html.ActionLink("Details", "Details", new { prodId = item.ProductId }) |
                @Html.ActionLink("Delete", "Delete", new { prodId = item.ProductId })
            </td>
        </tr>
    }

</table>

Step 18:- To Add View of Action Method, Right-click inside the Details Action body => Click Add View =>  Choose Details from Template dropdown box and ProductModel(DapperCRUD.Models) from Model class dropdown box to generate strongly type view from class => Click Add


Copy Past following code in Details.cshtml

@model DapperCRUD.Models.ProductModel

@{
    ViewBag.Title = "Details";
}

<h2>Details</h2>

<div>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.ProductId)
        </dt>

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

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

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

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

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

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

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

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

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

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

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

    </dl>
</div>
<p>
    @Html.ActionLink("Edit", "Edit", new { prodId = Model.ProductId })  |
    @Html.ActionLink("Back to List", "Index")
</p>

Step 19:- To Add View of Action Method, Right-click inside the Create Action body => Click Add View =>  Choose Create from Template dropdown box and ProductModel(DapperCRUD.Models) from Model class dropdown box to generate strongly type view from class => Click Add



Copy Past following code in Create.cshtml

@model DapperCRUD.Models.ProductModel

@{
    ViewBag.Title = "Create";
}

<h2>Create</h2>


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

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

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

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

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

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

<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 20:- To Add View of Action Method, Right-click inside the Edit Action body => Click Add View =>  Choose Edit  from Template dropdown box and ProductModel(DapperCRUD.Models) from Model class dropdown box to generate strongly type view from class => Click Add



Copy Past following code in Edit.cshtml

@model DapperCRUD.Models.ProductModel

@{
    ViewBag.Title = "Edit";
}

<h2>Edit</h2>


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

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

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

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

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

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

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

Step 13:- To Add View of Action Method, Right-click inside the Delete Action body => Click Add View =>  Choose Delete  from Template dropdown box and ProductModel(DapperCRUD.Models) from Model class dropdown box to generate strongly type view from class => Click Add


Copy Past following code in Delete.cshtml

@model DapperCRUD.Models.ProductModel

@{
    ViewBag.Title = "Delete";
}

<h2>Delete</h2>

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

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

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

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

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

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

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

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

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

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

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

        <dd>
            @Html.DisplayFor(model => model.ProductDesc)
        </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>

Project Structure

Run Project


Posted by at
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)