Project Structure:
framework : 4.5
Dapper : 1.50.2.0
---------------------------------------------------------------------------------------------------------------
Dapper class:
1. Install Dapper from Nuget using this command..
Install-Package Dapper
2. configure connection string in your web.config..
3. Use this code for Dapper
public class DapperExecute
{
public string ConnectionString { get; set; }
public DapperExecute()
{
ConnectionString = ConfigurationManager.ConnectionStrings["ProductDBContext"].ConnectionString;
}
public ICollection<T> Execute<T>(string Statement, dynamic Parameters = null, CommandType commandtype = CommandType.StoredProcedure)
{
var result = (dynamic)null;
try
{
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
result = SqlMapper.Query<T>(connection, Statement, Parameters, null, true, 0, commandtype);
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
}
------------------------------------------------------------------------
SP:
CREATE PROCEDURE [dbo].[usp_CRUD_Product]
@qType AS NVARCHAR(50)='List',
@searchText AS NVARCHAR(max)=NULL,
@Id AS INT=NULL,
@Name AS NVARCHAR(max)=NULL,
@Qty AS INT=null
AS
BEGIN
SET NOCOUNT ON;
IF(@qType='List')
BEGIN
IF(@searchText='')
BEGIN
SET @searchText=NULL;
END
SELECT * FROM dbo.Products WHERE (@searchText IS NULL OR [Name] LIKE '%'+@searchText+'%')
END
ELSE IF(@qType='Insert')
BEGIN
IF NOT EXISTS(SELECT ProductId FROM dbo.Products WHERE [Name]=@Name)
BEGIN
INSERT INTO dbo.Products
(
[Name],
Qty
)
VALUES
( @Name, -- Name - nvarchar(200)
@Qty -- Qty - int
)
SELECT @@IDENTITY
END
ELSE
BEGIN
SELECT -1
END
END
ELSE IF (@qType='Update')
BEGIN
IF NOT EXISTS(SELECT ProductId FROM dbo.Products WHERE ProductId<>@Id AND [Name]=@Name)
BEGIN
UPDATE dbo.Products SET [Name]=@Name, Qty=@Qty WHERE ProductId=@Id
SELECT @Id
END
ELSE
BEGIN
SELECT -1
END
END
ELSE IF (@qType='Delete')
BEGIN
DELETE FROM dbo.Products WHERE ProductId=@Id
END
ELSE IF (@qType='GetById')
BEGIN
SELECT * FROM dbo.Products WHERE ProductId=@Id
END
END
-----------------------------------------------------------------------------------
Product.cs:
public class Product
{
public int ProductId { get; set; }
[Required]
public string Name { get; set; }
[Required]
public int Qty { get; set; }
}
-----------------------------------------------------------------------------------
Controller:
public class ProductController : Controller
{
DapperExecute de = new DapperExecute();
// GET: Product
public ActionResult Index()
{
return View();
}
[HttpGet]
public JsonResult List(string searchText)
{
var product = this.de.Execute<Product>("usp_CRUD_Product", new
{
qType = "List",
searchText = searchText
});
return Json(product, JsonRequestBehavior.AllowGet);
}
[HttpGet]
public JsonResult GetById(int Id)
{
var product = this.de.Execute<Product>("usp_CRUD_Product", new
{
qType = "GetById",
Id = Id
});
return Json(product.FirstOrDefault(), JsonRequestBehavior.AllowGet);
}
[HttpPost]
// [ValidateAntiForgeryToken]
public JsonResult SaveProduct(Product product)
{
int result = 0;
if (product.ProductId > 0)
{
var updated = this.de.Execute<int>("usp_CRUD_Product", new
{
qType = "Update",
Id = product.ProductId,
Name = product.Name,
Qty = product.Qty
});
result = updated.FirstOrDefault();
}
else
{
var inserted = this.de.Execute<int>("usp_CRUD_Product", new
{
qType = "Insert",
Name = product.Name,
Qty = product.Qty
});
result = inserted.FirstOrDefault();
}
return Json(result, JsonRequestBehavior.AllowGet);
}
[HttpPost]
//[ValidateAntiForgeryToken]
public JsonResult DeleteProduct(int Id)
{
var deleted = this.de.Execute<int>("usp_CRUD_Product", new
{
qType = "Delete",
Id = Id
});
return Json(true, JsonRequestBehavior.AllowGet);
}
}
--------------------------------------------------------------------------------------------
Product.js:
$(document).ready(function () {
loadData();
});
function loadData() {
$.ajax({
url: "/Product/List/?searchText=" + $("#txtSearch").val(),
type: "GET",
contentType: "application/json",
dataType: "json",
success: function (result) {
var html = '';
debugger;
$.each(result, function (key, item) {
html += '<tr>';
html += '<td>' + item.Name + '</td>';
html += '<td>' + item.Qty + '</td>';
html += '<td><a class="btn btn-primary" href="javascript:void(0);" onclick="return GetById(' + item.ProductId + ')">Edit</a> | <a class="btn btn-primary" href="javascript:void(0);" onclick="DeleteProduct(' + item.ProductId + ')">Delete</a></td>';
html += '</tr>';
});
$('.tbody').html(html);
},
error: function (errormessage) {
alert(errormessage.responseText);
}
});
};
function GetById(Id) {
debugger
$.ajax({
url: "/Product/GetById/" + Id,
typr: "GET",
contentType: "application/json;charset=UTF-8",
dataType: "json",
success: function (result) {
debugger;
$('#hdnProductId').val(result.ProductId);
$('#txtName').val(result.Name);
$('#txtQty').val(result.Qty);
$('#AddProduct').modal('show');
},
error: function (errormessage) {
alert(errormessage.responseText);
}
});
return false;
};
function SaveProduct() {
var isValid = Validate();
if (!isValid) {
return false;
}
var obj = {
ProductId: $("#hdnProductId").val(),
Name: $("#txtName").val(),
Qty: $("#txtQty").val()
};
$.ajax({
url: "/Product/SaveProduct",
data: JSON.stringify(obj),
type: "POST",
contentType: "application/json;charset=utf-8",
dataType: "json",
beforeSend: function (request) {
request.setRequestHeader("__RequestVerificationToken", $('[name="__RequestVerificationToken"]').val());
},
success: function (result) {
if (result == -1) {
alert("Product already exist with same name");
}
else {
$("#txtSearch").val("");
loadData();
$('#AddProduct').modal('hide');
Clearcontroll();
}
},
error: function (errormessage) {
alert(errormessage.responseText);
}
});
}
function DeleteProduct(ID) {
var ans = confirm("Are you sure you want to delete this Product?");
if (ans) {
$.ajax({
url: "/Product/DeleteProduct/" + ID,
type: "POST",
contentType: "application/json;charset=UTF-8",
dataType: "json",
success: function (result) {
loadData();
alert("Product deleted successfully");
},
error: function (errormessage) {
alert(errormessage.responseText);
}
});
}
}
function Clearcontroll() {
$("#txtName").val("");
$("#txtQty").val("");
$("#hdnProductId").val("");
$("#txtName").css("background-color", "white");
$("#txtQty").css("background-color", "white");
}
function Validate() {
var isValid = true;
if ($("#txtName").val().trim() == "") {
$("#txtName").css("background-color", "red");
isValid = false;
}
else if ($("#txtQty").val().trim() == "") {
$("#txtQty").css("background-color", "red");
isValid = false;
}
return isValid;
}
framework : 4.5
Dapper : 1.50.2.0
---------------------------------------------------------------------------------------------------------------
Dapper class:
1. Install Dapper from Nuget using this command..
Install-Package Dapper
2. configure connection string in your web.config..
3. Use this code for Dapper
public class DapperExecute
{
public string ConnectionString { get; set; }
public DapperExecute()
{
ConnectionString = ConfigurationManager.ConnectionStrings["ProductDBContext"].ConnectionString;
}
public ICollection<T> Execute<T>(string Statement, dynamic Parameters = null, CommandType commandtype = CommandType.StoredProcedure)
{
var result = (dynamic)null;
try
{
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
result = SqlMapper.Query<T>(connection, Statement, Parameters, null, true, 0, commandtype);
}
}
catch (Exception ex)
{
throw ex;
}
return result;
}
}
------------------------------------------------------------------------
SP:
CREATE PROCEDURE [dbo].[usp_CRUD_Product]
@qType AS NVARCHAR(50)='List',
@searchText AS NVARCHAR(max)=NULL,
@Id AS INT=NULL,
@Name AS NVARCHAR(max)=NULL,
@Qty AS INT=null
AS
BEGIN
SET NOCOUNT ON;
IF(@qType='List')
BEGIN
IF(@searchText='')
BEGIN
SET @searchText=NULL;
END
SELECT * FROM dbo.Products WHERE (@searchText IS NULL OR [Name] LIKE '%'+@searchText+'%')
END
ELSE IF(@qType='Insert')
BEGIN
IF NOT EXISTS(SELECT ProductId FROM dbo.Products WHERE [Name]=@Name)
BEGIN
INSERT INTO dbo.Products
(
[Name],
Qty
)
VALUES
( @Name, -- Name - nvarchar(200)
@Qty -- Qty - int
)
SELECT @@IDENTITY
END
ELSE
BEGIN
SELECT -1
END
END
ELSE IF (@qType='Update')
BEGIN
IF NOT EXISTS(SELECT ProductId FROM dbo.Products WHERE ProductId<>@Id AND [Name]=@Name)
BEGIN
UPDATE dbo.Products SET [Name]=@Name, Qty=@Qty WHERE ProductId=@Id
SELECT @Id
END
ELSE
BEGIN
SELECT -1
END
END
ELSE IF (@qType='Delete')
BEGIN
DELETE FROM dbo.Products WHERE ProductId=@Id
END
ELSE IF (@qType='GetById')
BEGIN
SELECT * FROM dbo.Products WHERE ProductId=@Id
END
END
-----------------------------------------------------------------------------------
Product.cs:
public class Product
{
public int ProductId { get; set; }
[Required]
public string Name { get; set; }
[Required]
public int Qty { get; set; }
}
-----------------------------------------------------------------------------------
Controller:
public class ProductController : Controller
{
DapperExecute de = new DapperExecute();
// GET: Product
public ActionResult Index()
{
return View();
}
[HttpGet]
public JsonResult List(string searchText)
{
var product = this.de.Execute<Product>("usp_CRUD_Product", new
{
qType = "List",
searchText = searchText
});
return Json(product, JsonRequestBehavior.AllowGet);
}
[HttpGet]
public JsonResult GetById(int Id)
{
var product = this.de.Execute<Product>("usp_CRUD_Product", new
{
qType = "GetById",
Id = Id
});
return Json(product.FirstOrDefault(), JsonRequestBehavior.AllowGet);
}
[HttpPost]
// [ValidateAntiForgeryToken]
public JsonResult SaveProduct(Product product)
{
int result = 0;
if (product.ProductId > 0)
{
var updated = this.de.Execute<int>("usp_CRUD_Product", new
{
qType = "Update",
Id = product.ProductId,
Name = product.Name,
Qty = product.Qty
});
result = updated.FirstOrDefault();
}
else
{
var inserted = this.de.Execute<int>("usp_CRUD_Product", new
{
qType = "Insert",
Name = product.Name,
Qty = product.Qty
});
result = inserted.FirstOrDefault();
}
return Json(result, JsonRequestBehavior.AllowGet);
}
[HttpPost]
//[ValidateAntiForgeryToken]
public JsonResult DeleteProduct(int Id)
{
var deleted = this.de.Execute<int>("usp_CRUD_Product", new
{
qType = "Delete",
Id = Id
});
return Json(true, JsonRequestBehavior.AllowGet);
}
}
--------------------------------------------------------------------------------------------
Product.js:
$(document).ready(function () {
loadData();
});
function loadData() {
$.ajax({
url: "/Product/List/?searchText=" + $("#txtSearch").val(),
type: "GET",
contentType: "application/json",
dataType: "json",
success: function (result) {
var html = '';
debugger;
$.each(result, function (key, item) {
html += '<tr>';
html += '<td>' + item.Name + '</td>';
html += '<td>' + item.Qty + '</td>';
html += '<td><a class="btn btn-primary" href="javascript:void(0);" onclick="return GetById(' + item.ProductId + ')">Edit</a> | <a class="btn btn-primary" href="javascript:void(0);" onclick="DeleteProduct(' + item.ProductId + ')">Delete</a></td>';
html += '</tr>';
});
$('.tbody').html(html);
},
error: function (errormessage) {
alert(errormessage.responseText);
}
});
};
function GetById(Id) {
debugger
$.ajax({
url: "/Product/GetById/" + Id,
typr: "GET",
contentType: "application/json;charset=UTF-8",
dataType: "json",
success: function (result) {
debugger;
$('#hdnProductId').val(result.ProductId);
$('#txtName').val(result.Name);
$('#txtQty').val(result.Qty);
$('#AddProduct').modal('show');
},
error: function (errormessage) {
alert(errormessage.responseText);
}
});
return false;
};
function SaveProduct() {
var isValid = Validate();
if (!isValid) {
return false;
}
var obj = {
ProductId: $("#hdnProductId").val(),
Name: $("#txtName").val(),
Qty: $("#txtQty").val()
};
$.ajax({
url: "/Product/SaveProduct",
data: JSON.stringify(obj),
type: "POST",
contentType: "application/json;charset=utf-8",
dataType: "json",
beforeSend: function (request) {
request.setRequestHeader("__RequestVerificationToken", $('[name="__RequestVerificationToken"]').val());
},
success: function (result) {
if (result == -1) {
alert("Product already exist with same name");
}
else {
$("#txtSearch").val("");
loadData();
$('#AddProduct').modal('hide');
Clearcontroll();
}
},
error: function (errormessage) {
alert(errormessage.responseText);
}
});
}
function DeleteProduct(ID) {
var ans = confirm("Are you sure you want to delete this Product?");
if (ans) {
$.ajax({
url: "/Product/DeleteProduct/" + ID,
type: "POST",
contentType: "application/json;charset=UTF-8",
dataType: "json",
success: function (result) {
loadData();
alert("Product deleted successfully");
},
error: function (errormessage) {
alert(errormessage.responseText);
}
});
}
}
function Clearcontroll() {
$("#txtName").val("");
$("#txtQty").val("");
$("#hdnProductId").val("");
$("#txtName").css("background-color", "white");
$("#txtQty").css("background-color", "white");
}
function Validate() {
var isValid = true;
if ($("#txtName").val().trim() == "") {
$("#txtName").css("background-color", "red");
isValid = false;
}
else if ($("#txtQty").val().trim() == "") {
$("#txtQty").css("background-color", "red");
isValid = false;
}
return isValid;
}
----------------------------------------------------------------------------------------
Product.cshtml:
@{
ViewBag.Title = "Index";
}
<script src="~/Scripts/jquery-3.3.1.js"></script>
<script src="~/Js/Products.js"></script>
@Html.AntiForgeryToken();
<div class="container">
<h2>Product List</h2>
<span style="float:left;margin-right:5px;">
<input type="text" id="txtSearch" name="txtSearch" class="form-control" />
</span>
<span style="float:left;"><button id="btnSearch" name="btnSearch" class="btn btn-primary" onclick="loadData();">Search</button> <button id="btnAdd" name="btnAdd" class="btn btn-primary" data-toggle="modal" data-target="#AddProduct" onclick="return Clearcontroll();">Add Product</button></span>
<table class="table table-hover">
<thead>
<tr>
<th>
Name
</th>
<th>
Qty
</th>
<th>
Action
</th>
</tr>
</thead>
<tbody class="tbody">
</tbody>
</table>
</div>
<div class="modal fade" id="AddProduct" tabindex="-1" role="dialog" aria-labelledby="lblHeader" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<button id="btnClose" class="close" data-dismiss="modal">X</button>
<h4 class="modal-title" id="lblHeader">Save Product</h4>
</div>
<div class="modal-body">
<form id="productForm">
<input type="hidden" id="hdnProductId" />
<div class="form-group">
<label for="Name">Name</label>
<input type="text" name="txtName" id="txtName" value="" class="form-control" placeholder="Name" />
</div>
<div class="form-group">
<label for="Qty">Qty</label>
<input type="text" name="txtQty" id="txtQty" value="" class="form-control" placeholder="Quantity" />
</div>
</form>
<div class="modal-footer">
<button id="btnSave" name="btnSave" class="btn btn-primary" onclick="return SaveProduct();">Save</button>
</div>
</div>
</div>
</div>
</div>
