Data Table With Sql Server and Stored Procedure
17:21
Introduction
In this post I try to show how to show data in a data table, which data from sql server and using stored procedure. Also the data table have the link (Edit, Delete, Block/Unblock). The data table alsohave sorting and pagination is working dynamically. I write all using scripts, css link and you can also download. There have model, view, jquery scripts, controller, table structure and stored procedure.
CSS and JS
<link href="bootstrap-dialog.css" rel="stylesheet" /> Download <link href="bootstrap.min.css" rel="stylesheet"> Download <link href="metisMenu.min.css" rel="stylesheet"> Download <link href="timeline.css" rel="stylesheet"> Download <link href="sb-admin-2.css" rel="stylesheet"> Download <link href="font-awesome.min.css" rel="stylesheet" type="text/css"> Download <link href="dataTables.colVis.css" rel="stylesheet" /> Download <link href="jquery.dataTables.css" rel="stylesheet" /> Download <script src="jquery.js"></script> Download <script src="jquery.dataTables.js"></script> Download <script src="ColReorderWithResize.js"></script> Download <script src="fnReloadAjax.js"></script> Download <script src="bootstrap.min.js"></script> Download <script src="jquery.validate.min.js"></script> Download <script src="jquery.validate.unobtrusive.min.js"></script> Download <script src="bootstrap-dialog.js"></script> Download
Data Table View Image
Model
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;
namespace DataTable_With_Editor_And_Database.Models
{
public class Common
{
public string ID { get; set; }
public bool IsActive { get; set; }
public string Name { get; set; }
public string EmailID { get; set; }
public string RoleName { get; set; }
public string Phone { get; set; }
}
public class Registration
{
public int ID { get; set; }
[Required(ErrorMessage = "Required First Name")]
public string FirstName { get; set; }
[Required(ErrorMessage = "Required Last Name")]
public string LastName { get; set; }
[Required(ErrorMessage = "Required Address")]
public string Address { get; set; }
[Required(ErrorMessage = "Required Email ID")]
[RegularExpression(@"^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$", ErrorMessage = "Please enter a valid e-mail address")]
public string EmailID { get; set; }
[Required(ErrorMessage = "Required Password")]
[RegularExpression(@"^([a-zA-Z0-9_\-\.]+){8,10}$", ErrorMessage = "Password should be minimum 8 and maximum 10 character")]
public string Password { get; set; }
[Required(ErrorMessage = "Required Confirm Password")]
[Compare("Password", ErrorMessage = "Password and Confirm Password Not Matched")]
public string ConfirmPassword { get; set; }
[Required(ErrorMessage = "Required Phone Number")]
[RegularExpression(@"^\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{4})$", ErrorMessage = "Entered phone format is not valid.")]
public string Phone { get; set; }
public int Role { get; set; }
}
public class RegistrationEdit
{
public int ID { get; set; }
[Required(ErrorMessage = "Required First Name")]
public string FirstName { get; set; }
[Required(ErrorMessage = "Required Last Name")]
public string LastName { get; set; }
[Required(ErrorMessage = "Required Address")]
public string Address { get; set; }
[Required(ErrorMessage = "Required Email ID")]
[RegularExpression(@"^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$", ErrorMessage = "Please enter a valid e-mail address")]
public string EmailID { get; set; }
[Required(ErrorMessage = "Required Phone Number")]
[RegularExpression(@"^\(?([0-9]{3})\)?[-. ]?([0-9]{3})[-. ]?([0-9]{4})$", ErrorMessage = "Entered phone format is not valid.")]
public string Phone { get; set; }
public int? Role { get; set; }
}
}
View Details Page
@{
Layout = "~/Views/Shared/_LayoutMain.cshtml";
}
<script>
$(document).ready(function () {
// For Active Inactive
$('#active').html("<a href='#' class='btn btn-success btnA' onclick=showActiveInactive('Active')>Active (@ViewBag.Active)</a>");
$('#inactive').html("<a href='#' class='btn btn-success btnIN' onclick=showActiveInactive('InActive')>InActive (@ViewBag.InActive)</a>");
// Calling data table function
getDetails('');
// Get value from controller
var result = '@ViewBag.Result';
if (result == 'Success') {
$('#suss').css('display', 'block');
}
if (result == 'DelSuccess') {
$('#delsuss').css('display', 'block');
}
});
// Data table function
function getDetails(val) {
var path = BaseURL();
var url = '';
if (val == '') {
$('.btnA').addClass('blockcol');
url = path + "/Test/getAdminData";
}
else
url = path + "/Test/getAdminData/?type=" + val;
$("#example").dataTable({
"bDestroy": true,
"processing": true,
"DeferRender": true,
"serverSide": true,
"ajax": {
"url": url,
"type": "Post",
"datatype": "JSON"
},
"lengthMenu": [10, 15, 20, 30, 50, 100, 200, 500],
"columns": [
{ "data": "Name", "autoWidth": true, },
{ "data": "EmailID", "autoWidth": true, },
{ "data": "Phone", "autoWidth": true, },
{ "data": "RoleName", "autoWidth": true, },
{
"targets": -1,
"data": null, "orderable": false,
"render": function (data, type, row, meta) {
var a = "";
var b = "";
var c = "";
a = "<a onclick=\"EditRow('" + row.ID + "')\" id=\"grideditebtn\" class=\"fa fa-edit grid-edit btn btn-default btn-circle\" title=\"Edit\" alt=\"Edit\"></a>";
b = "<a onclick=\"DelRow('" + row.ID + "')\" id=\"griddelebtn\" class=\"fa fa-trash-o grid-edit btn btn-default btn-circle\" title=\"Delete\" alt=\"Delete\"></a>";
if (row.IsActive == true) {
c = "<a onclick=\"ActiveInactive('" + row.EmailID + "',0)\" id=\"gridActiveInactive\" class=\"fa fa-times grid-edit btn btn-default btn-circle\" title=\"Block\" alt=\"Block\"></a>";
}
else {
c = "<a onclick=\"ActiveInactive('" + row.EmailID + "',1)\" id=\"gridActiveInactive\" class=\"fa fa-check grid-edit btn btn-default btn-circle\" title=\"Unblock\" alt=\"Unblock\"></a>";
}
return a + " " + b + " " + c;
}
}
],
"language": {
"sProcessing": "<img src='https://drive.google.com/file/d/0ByFl-nttZhzVR1FBLUhiOHRLTDQ/view?usp=sharing'>"
},
dom: 'CZRfrt<"bottom">lip',
colVis: {
exclude: [0, 9, 10]
},
"CellCallback": function (Row, Data, DisplayIndex) {
alert($(Row).attr("id", $('td:eq(0)', Row).html()));
},
"ServerData": function (Source, Data, Callback) {
Data.push({ "name": "_srch", "value": formvalue });
$.getJSON(Source, Data, function (json) {
Callback(json);
});
}
});
}
// Edit data table function
function EditRow(id) {
window.location.href = "/Test/EditPage/?uid=" + id;
}
// Delete data table function
function DelRow(id) {
BootstrapDialog.confirm('Are you sure to delete?', function (result, e) {
if (result) {
$('#uid').val(id);
$('#btndel').click();
}
else {
return false;
}
});
}
// Active inactive data table function
function ActiveInactive(id, val) {
var path = BaseURL();
var url = path + "/Test/ActiveInactiveAdmin";
$.ajax({
type: "GET",
url: url,
data: { 'id': id, 'active': val },
dataType: 'json',
success: function (data) {
$('#active').html("<a class='btn btn-success btnA' href='#' onclick=showActiveInactive('Active')>Active (" + data.Active + ")</a>");
$('#inactive').html("<a class='btn btn-success btnIN' href='#' onclick=showActiveInactive('InActive')>InActive (" + data.InActive + ")</a>");
$("#example").dataTable().fnDraw();
}
});
}
// Active inactive click function
function showActiveInactive(val) {
ActInac(val);
$("#example").dataTable().fnDestroy();
getDetails(val);
}
function ActInac(val) {
if (val == 'Active') {
$('.btnA').addClass('blockcol');
$('.btnIN').removeClass('blockcol');
}
else {
$('.btnA').removeClass('blockcol');
$('.btnIN').addClass('blockcol');
}
}
// Base URL function
function BaseURL() {
var appName = "ABCD".toLowerCase();
var loc = document.location.toString();
loc = loc.toLowerCase();
var systemRoot = "";
if (loc.indexOf(appName) != -1) {
systemRoot = document.location.protocol + "//" + document.location.host + "/" + appName;
}
return systemRoot;
}
</script>
<form action="/Test/DeleteUser" method="post" style="display: none;">
<input type="hidden" name="uid" id="uid" />
<input type="submit" id="btndel" />
</form>
<div id="page-wrapper">
<div class="row">
<div class="col-lg-12">
<h1 class="page-header">User Details</h1>
</div>
</div>
<div class="row">
<div class="col-lg-12">
<div class="panel panel-default">
<div class="panel-heading">
<div class="bs-example disp" id="suss">
<div class="alert alert-success fade in">
<a href="#" class="close" data-dismiss="alert">×</a>
<strong>Success!</strong> Data saved successfully.
</div>
</div>
<div class="bs-example disp" id="delsuss">
<div class="alert alert-success fade in">
<a href="#" class="close" data-dismiss="alert">×</a>
<strong>Success!</strong> Data deleted successfully.
</div>
</div>
</div>
<div class="bs-example">
<div class="alert alert-success-style fade in">
<span id="active"></span>
<span style="padding-left: 12px;"></span>
<span id="inactive"></span>
<span style="float: right;">
<a href="/Test/AddPage" class="btn btn-success">Add New User</a>
</span>
</div>
</div>
<div class="panel-body">
<div class="dataTable_wrapper">
<table class="table table-striped table-bordered table-hover" id="example">
<thead>
<tr>
<th>Name</th>
<th>Email ID</th>
<th>Phone</th>
<th>RoleName</th>
<th class="coldesign">Actions</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>
</div>
View Add Page
@model DataTable_With_Editor_And_Database.Models.Registration
@{
Layout = "~/Views/Shared/_LayoutMain.cshtml";
}
<script>
$(document).ready(function () {
var result = '@ViewBag.Result';
if (result == 'Exists') {
$('#war').css('display', 'block');
}
else if (result == 'Error') {
$('#err').css('display', 'block');
}
});
</script>
<div id="page-wrapper">
<div class="row">
<div class="col-lg-12">
<h1 class="page-header">User Add</h1>
</div>
</div>
<div class="row">
<div class="col-lg-12">
<div class="panel panel-default">
<div class="panel-heading">
<div class="bs-example disp" id="war">
<div class="alert alert-warning">
<a href="#" class="close" data-dismiss="alert">×</a>
<strong>Warning!</strong> Email id already exists
</div>
</div>
<div class="bs-example disp" id="err">
<div class="alert alert-danger fade in">
<a href="#" class="close" data-dismiss="alert">×</a>
<strong>Error!</strong> A problem has been occurred while submitting your data.
</div>
</div>
</div>
<div class="panel-body">
<div class="row">
<div class="col-lg-6">
<h1></h1>
<form role="form" action="/Test/AddPage" method="post" enctype="multipart/form-data">
<div class="form-group has-warning styfield">
<label class="control-label" for="inputSuccess">@Html.ValidationMessageFor(m=>m.FirstName)</label>
<div>First Name<span class="errorMark">*</span></div>
@Html.TextBoxFor(m => m.FirstName, new {placeholder="First Name", @class = "form-control" })
</div>
<div class="form-group has-warning">
<label class="control-label" for="inputSuccess">@Html.ValidationMessageFor(m=>m.LastName)</label>
<div>Last Name<span class="errorMark">*</span></div>
@Html.TextBoxFor(m => m.LastName, new {placeholder="Last Name", @class = "form-control" })
</div>
<div class="form-group has-warning">
<label class="control-label" for="inputSuccess">@Html.ValidationMessageFor(m=>m.Address)</label>
<div>Address<span class="errorMark">*</span></div>
@Html.TextAreaFor(m => m.Address, new {placeholder="Address", @class = "form-control" })
</div>
<div class="form-group has-warning">
<label class="control-label" for="inputSuccess">@Html.ValidationMessageFor(m=>m.EmailID)</label>
<div>Email ID<span class="errorMark">*</span></div>
@Html.TextBoxFor(m => m.EmailID, new {placeholder="Email ID", @class = "form-control" })
</div>
<div class="form-group has-warning">
<label class="control-label" for="inputSuccess">@Html.ValidationMessageFor(m=>m.Password)</label>
<div>Password<span class="errorMark">*</span></div>
@Html.TextBoxFor(m => m.Password, new {placeholder="Password", @class = "form-control", @maxlength = "10" })
</div>
<div class="form-group has-warning">
<label class="control-label" for="inputSuccess">@Html.ValidationMessageFor(m=>m.ConfirmPassword)</label>
<div>Confirm Password<span class="errorMark">*</span></div>
@Html.TextBoxFor(m => m.ConfirmPassword, new {placeholder="Confirm Password", @class = "form-control" })
</div>
<div class="form-group has-warning">
<label class="control-label" for="inputSuccess">@Html.ValidationMessageFor(m=>m.Phone)</label>
<div>Phone<span class="errorMark">*</span></div>
@Html.TextBoxFor(m => m.Phone, new {placeholder="Phone", @class = "form-control" })
</div>
<button type="submit" class="btn btn-outline btn-primary" name="btn" value="Submit">Submit</button>
<button type="button" class="btn btn-outline btn-primary" name="btn" value="Cancel" onclick="gotodetails();">Cancel</button>
</form>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
View Edit Page
@model DataTable_With_Editor_And_Database.Models.RegistrationEdit
@{
Layout = "~/Views/Shared/_LayoutMain.cshtml";
}
<style>
.disp {
display: none;
}
</style>
<script>
$(document).ready(function () {
var result = '@ViewBag.Result';
if (result == 'Success') {
$('#suss').css('display', 'block');
$('.panel-body').css('display', 'none');
}
else if (result == 'Exists') {
$('#war').css('display', 'block');
}
else if (result == 'Error') {
$('#err').css('display', 'block');
}
else if (result == 'Delete') {
$('#del').css('display', 'block');
$('.panel-body').css('display', 'none');
}
});
</script>
<div id="page-wrapper">
<div class="row">
<div class="col-lg-12">
<h1 class="page-header">User Edit</h1>
</div>
</div>
<div class="row">
<div class="col-lg-12">
<div class="panel panel-default">
<div class="panel-heading">
<div class="bs-example disp" id="suss">
<div class="alert alert-success fade in">
<a href="#" class="close" data-dismiss="alert">×</a>
<strong>Success!</strong> Data updated successfully.
</div>
</div>
<div class="bs-example disp" id="war">
<div class="alert alert-warning">
<a href="#" class="close" data-dismiss="alert">×</a>
<strong>Warning!</strong> Email id already exists
</div>
</div>
<div class="bs-example disp" id="err">
<div class="alert alert-danger fade in">
<a href="#" class="close" data-dismiss="alert">×</a>
<strong>Error!</strong> A problem has been occurred while submitting your data.
</div>
</div>
<div class="bs-example disp" id="del">
<div class="alert alert-success fade in">
<a href="#" class="close" data-dismiss="alert">×</a>
<strong>Success!</strong> Data deleted successfully.
</div>
</div>
</div>
<div class="panel-body">
<div class="row">
<div class="col-lg-6">
<h1></h1>
<form role="form" action="/Test/EditPage" method="post" enctype="multipart/form-data">
@Html.HiddenFor(m => m.ID)
<div class="form-group has-warning styfield">
<label class="control-label" for="inputSuccess">@Html.ValidationMessageFor(m => m.FirstName)</label>
<div>First Name<span class="errorMark">*</span></div>
@Html.TextBoxFor(m => m.FirstName, new { placeholder = "First Name", @class = "form-control" })
</div>
<div class="form-group has-warning">
<label class="control-label" for="inputSuccess">@Html.ValidationMessageFor(m => m.LastName)</label>
<div>Last Name<span class="errorMark">*</span></div>
@Html.TextBoxFor(m => m.LastName, new { placeholder = "Last Name", @class = "form-control" })
</div>
<div class="form-group has-warning">
<label class="control-label" for="inputSuccess">@Html.ValidationMessageFor(m => m.Address)</label>
<div>Address<span class="errorMark">*</span></div>
@Html.TextAreaFor(m => m.Address, new { placeholder = "Address", @class = "form-control" })
</div>
<div class="form-group has-warning">
<label class="control-label" for="inputSuccess">@Html.ValidationMessageFor(m => m.EmailID)</label>
<div>Email ID<span class="errorMark">*</span></div>
@Html.TextBoxFor(m => m.EmailID, new { placeholder = "Email ID", @class = "form-control" })
</div>
<div class="form-group has-warning">
<label class="control-label" for="inputSuccess">@Html.ValidationMessageFor(m => m.Phone)</label>
<div>Phone<span class="errorMark">*</span></div>
@Html.TextBoxFor(m => m.Phone, new { placeholder = "Phone", @class = "form-control" })
</div>
<button type="submit" class="btn btn-outline btn-primary" name="btn" value="Update">Update</button>
<button type="button" class="btn btn-outline btn-primary" name="btn" value="Cancel" onclick="gotodetails();">Cancel</button>
</form>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
Controller
using DataTable_With_Editor_And_Database.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace DataTable_With_Editor_And_Database.Controllers
{
public class TestController : Controller
{
public ActionResult DetailsPage(string status = "")
{
using (Entities db = new Entities())
{
var result = db.sp_getAdminActiveInactive("", false, "").SingleOrDefault();
ViewBag.Active = result.Active;
ViewBag.InActive = result.InActive;
if (status == "Success")
ViewBag.Result = "Success";
else if (status == "DelSuccess")
ViewBag.Result = "DelSuccess";
}
return View();
}
[HttpPost]
public ActionResult getAdminData(string type = "")
{
List data = new List();
Common common;
string draw = Convert.ToString(Request["draw"]);
int length = Convert.ToInt32(Request["length"]);
int start = Convert.ToInt32(Request["start"]) + 1;
string searchString = Convert.ToString(Request["search[value]"]);
string sortColumnIndex = Convert.ToInt32(Request["order[0][column]"]).ToString();
string sortDirection = Request["order[0][dir]"]; // asc or desc
var total = 0;
try
{
using (Entities db = new Entities())
{
var result = (dynamic)null;
if (type == "InActive")
{
result = db.sp_getAdmin("", "", "InActive", searchString, sortDirection, sortColumnIndex, start, length).ToList();
total = db.sp_getAdmin("", "", "TotalInActive", searchString, "", "", 0, 0).ToList().Count;
}
else
{
result = db.sp_getAdmin("", "", "Active", searchString, sortDirection, sortColumnIndex, start, length).ToList();
total = db.sp_getAdmin("", "", "TotalActive", searchString, "", "", 0, 0).ToList().Count;
}
foreach (var a in result)
{
common = new Common()
{
ID = Convert.ToString(a.ID),
Name = a.Name,
EmailID = a.EmailID,
Phone = a.Phone,
RoleName = a.Role,
IsActive = a.IsActive
};
data.Add(common);
}
return Json(new { draw = draw, recordsTotal = total, recordsFiltered = total, data = data }, JsonRequestBehavior.AllowGet);
}
}
catch (Exception)
{
return Json(new { draw = draw, recordsTotal = total, recordsFiltered = total, data = "" }, JsonRequestBehavior.DenyGet);
}
}
public ActionResult ActiveInactiveAdmin(string id, string active)
{
bool val = true;
try
{
using (Entities db = new Entities())
{
if (active == "0") { val = false; }
var result = db.sp_getAdminActiveInactive(id, val, "save").SingleOrDefault();
return Json(result, JsonRequestBehavior.AllowGet);
}
}
catch (Exception)
{
return Json("", JsonRequestBehavior.AllowGet);
}
}
public ActionResult AddPage()
{
Registration obj = new Registration();
return View(obj);
}
[HttpPost]
public ActionResult AddPage(Registration regn, string btn)
{
try
{
using (Entities db = new Entities())
{
if (btn == "Submit")
{
if (ModelState.IsValid)
{
db.sp_AddEditDelAdmin(0, regn.FirstName.Trim() + " " + regn.LastName.Trim(), regn.Address, regn.EmailID, regn.Password, regn.Phone, "Add", regn.Role);
return RedirectToAction("DetailsPage", "Test", new { status = "Success" });
}
}
else if (btn == "Cancel")
{
return RedirectToAction("DetailsPage", "Test", new { status = "" });
}
}
}
catch (Exception ex)
{
var msg = ex.InnerException.Message.ToString();
if (msg == "Exists")
ViewBag.Result = "Exists";
else
ViewBag.Result = "Error";
}
return View();
}
public ActionResult EditPage(string uid)
{
RegistrationEdit val = new RegistrationEdit();
using (Entities db = new Entities())
{
var resAdmin = db.sp_getAdmin(uid, "", "One", "", "", "", 0, 0).ToList();
foreach (var a in resAdmin)
{
string[] splitter = a.Name.Split(' ');
val = new RegistrationEdit()
{
ID = a.ID,
Address = a.Address,
EmailID = a.EmailID,
FirstName = splitter[0].ToString(),
LastName = splitter[1].ToString(),
Phone = a.Phone,
Role = a.ApplyRole
};
}
}
return View(val);
}
[HttpPost]
public ActionResult EditPage(RegistrationEdit regn, string btn)
{
try
{
ViewBag.SelectRole = regn.Role;
using (Entities db = new Entities())
{
if (btn == "Update")
{
if (ModelState.IsValid)
{
db.sp_AddEditDelAdmin(regn.ID, regn.FirstName.Trim() + " " + regn.LastName.Trim(), regn.Address, regn.EmailID, "", regn.Phone, "Upd", regn.Role);
return RedirectToAction("DetailsPage", "Test", new { status = "Success" });
}
}
else if (btn == "Cancel")
{
return RedirectToAction("DetailsPage", "Test", new { status = "" });
}
}
}
catch (Exception ex)
{
var msg = ex.InnerException.Message.ToString();
if (msg == "Exists")
ViewBag.Result = "Exists";
}
return View();
}
[HttpPost]
public ActionResult DeleteUser(string uid)
{
try
{
using (Entities db = new Entities())
{
db.sp_AddEditDelAdmin(Convert.ToInt32(uid), "", "", "", "", "", "Del", 0);
return RedirectToAction("DetailsPage", "Test", new { status = "DelSuccess" });
}
}
catch (Exception)
{
throw;
}
}
}
}
Table
CREATE TABLE [dbo].[LoginAdmin]( [ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL, [Name] [nvarchar](100) NOT NULL, [Address] [nvarchar](1000) NOT NULL, [EmailID] [nvarchar](100) NOT NULL, [Password] [nvarchar](100) NOT NULL, [Phone] [nvarchar](100) NULL, [Role] [nvarchar](100) NULL, [IsActive] [bit] NULL, [ApplyRole] [int] NULL )
Stored Procedure
CREATE procedure [dbo].[sp_AddEditDelAdmin]
@ID int,
@name nvarchar(100),
@address nvarchar(1000),
@emailid nvarchar(100),
@password nvarchar(100),
@phone nvarchar(100),
@type nvarchar(50),
@applyrole int
As
Begin
Declare @Error_MSG nvarchar(100)
declare @istEmail nvarchar(100)
Begin Try
Begin
Begin Tran
If(@type='Add')
Begin
if exists(select * from LoginAdmin where [EmailID] = @emailid)
Begin
SET @Error_MSG = 'Exists'
RAISERROR (@Error_MSG, 11, 1)
End
Else
Begin
Insert Into LoginAdmin([Name],[Address],[EmailID],[Role],[Password],[Phone],[IsActive],[ApplyRole])
values(@name,@address,@emailid,'Admin',@password,@phone,1,@applyrole)
End
End
Else if(@type='Upd')
Begin
select @istEmail = [EmailID] from LoginAdmin where ID = @ID
if(not exists(select * from LoginAdmin where EmailID = @emailid))
Begin
update LoginAdmin
set [Name]= @name,
[Address]=@address,
[EmailID]=@emailid,
[Phone]=@phone,
[ApplyRole]=@applyrole
where ID = @ID
End
Else if(@istEmail = @emailid)
Begin
update LoginAdmin
set [Name]= @name,
[Address]=@address,
[EmailID]=@emailid,
[Phone]=@phone,
[ApplyRole]=@applyrole
where ID = @ID
End
Else
Begin
SET @Error_MSG = 'Exists'
RAISERROR (@Error_MSG, 11, 1)
End
End
Else If(@type='Del')
Begin
Delete from LoginAdmin
where ID = @ID
End
Commit Tran
End
End Try
Begin Catch
Begin
SET @Error_MSG = 'Exists'
RAISERROR (@Error_MSG, 11, 1)
Rollback Tran
End
End Catch
End
CREATE procedure sp_getAdmin
(
@ID nvarchar(50),
@emailID nvarchar(50),
@type nvarchar(50),
@searchtext nvarchar(50),
@sortDirection nvarchar(50),
@sortColumnIndex nvarchar(50),
@start int,
@length int
)
As
Begin
declare @sql nvarchar(max)
declare @T table(
[rown] [int] NOT NULL,
[ID] [int] NOT NULL,
[Name] [nvarchar](100) NOT NULL,
[Address] [nvarchar](1000) NOT NULL,
[EmailID] [nvarchar](100) NOT NULL,
[Password] [nvarchar](100) NOT NULL,
[Phone] [nvarchar](100) NULL,
[Role] [nvarchar](100) NULL,
[IsActive] [bit] NULL,
[ApplyRole] int)
declare @direction nvarchar(50)
declare @page int
if (@sortColumnIndex = '0')
Begin
set @direction = 'Name ';
End
else if (@sortColumnIndex = '1')
Begin
set @direction = 'EmailID ';
End
else if (@sortColumnIndex = '2')
Begin
set @direction = 'Phone ';
End
else if (@sortColumnIndex = '3')
Begin
set @direction = 'Role ';
End
--Input-- 1-10 11-10 21-10
--Output-- 1-10 11-20 21-30
DECLARE @p0 Int = @start
DECLARE @p1 Int = (@length + @start - 1)
declare @q nvarchar(50)
If(@type = 'All')
Begin
set @q = ' AND t0.IsActive = 1 '
End
Else If(@type = 'Active')
Begin
set @q=' AND t0.IsActive = 1 '
End
Else If(@type = 'InActive')
Begin
set @q=' AND t0.IsActive = 0 '
End
set @sql = 'SELECT *
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY '+@direction+' '+@sortDirection+') as rown, t0.*
FROM LoginAdmin AS t0
where ([name] like ''%'+@searchtext+'%''
or [EmailID] like ''%'+@searchtext+'%''
or [Phone] like ''%'+@searchtext+'%''
or [Role] like ''%'+@searchtext+'%'')
'+@q+'
)[t1]
WHERE [t1].rown BETWEEN '+convert(varchar,@p0)+' AND '+convert(varchar,@p1)+'
ORDER BY [t1].rown'
If(@type = 'One')
Begin
set @sql = 'Select 0 as rown, t0.* from [dbo].[LoginAdmin] t0
where t0.ID = '''+@ID+''''
End
If(@type = 'TotalActive')
Begin
set @sql = 'Select 0 as rown, t0.* from [dbo].[LoginAdmin] t0
where t0.IsActive = 1 and ([name] like ''%'+@searchtext+'%''
or [EmailID] like ''%'+@searchtext+'%''
or [Phone] like ''%'+@searchtext+'%''
or [Role] like ''%'+@searchtext+'%'')'
End
If(@type = 'TotalInActive')
Begin
set @sql = 'Select 0 as rown, t0.* from [dbo].[LoginAdmin] t0
where t0.IsActive = 0 and ([name] like ''%'+@searchtext+'%''
or [EmailID] like ''%'+@searchtext+'%''
or [Phone] like ''%'+@searchtext+'%''
or [Role] like ''%'+@searchtext+'%'')'
End
INSERT INTO @T
EXEC(@sql)
SET NOCOUNT ON;
Select * from @T
end
CREATE procedure sp_getAdminActiveInactive
@ID nvarchar(50),
@active bit,
@type nvarchar(50)
AS
Begin
declare @activecount int
declare @inactivecount int
declare @T table(
[Active] [int] NOT NULL,
[InActive] [int] NOT NULL
)
If(@type='save')
Begin
update LoginAdmin
set IsActive = @active
where EmailID=@ID
End
Select @activecount = count(*) from LoginAdmin where IsActive = 1
Select @inactivecount = count(*) from LoginAdmin where IsActive = 0
insert into @T
Select @activecount 'Active', @inactivecount 'InActive'
select * from @T
End
0 comments