using System.Data.Entity;
using System.Data;
using Asp.NETMVCCRUD.Models;
using System.Data.SqlClient;
namespace Asp.NETMVCCRUD.Controllers
{
public class EmployeeController : Controller
{
private SqlConnection con;
private SqlCommand cmd;
private void connection()
{
con = new SqlConnection("Data Source=.;Integrated Security=true;Initial Catalog=MvcCRUDDB");
}
public ActionResult Index()
{
return View();
}
public ActionResult GetData(int id=0)
{
List<Employee> emplst = new List<Employee>();
DataSet ds = new DataSet();
connection();
cmd = new SqlCommand("USP_GetEmployeeDetails", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmployeeID",id);
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Employee empobj = new Employee();
empobj.EmployeeID = Convert.ToInt32(ds.Tables[0].Rows[i]["EmployeeID"].ToString());
empobj.Name = ds.Tables[0].Rows[i]["Name"].ToString();
empobj.Position = ds.Tables[0].Rows[i]["Position"].ToString();
empobj.Office = ds.Tables[0].Rows[i]["Office"].ToString();
empobj.Age = Convert.ToInt32(ds.Tables[0].Rows[i]["Age"].ToString());
empobj.Salary = Convert.ToInt32(ds.Tables[0].Rows[i]["Salary"].ToString());
empobj.Gender = ds.Tables[0].Rows[i]["Gender"].ToString();
empobj.isGujarati = Convert.ToBoolean(ds.Tables[0].Rows[i]["isGujarati"].ToString());
empobj.isHindi = Convert.ToBoolean(ds.Tables[0].Rows[i]["isHindi"].ToString());
empobj.isEnglish = Convert.ToBoolean(ds.Tables[0].Rows[i]["isEnglish"].ToString());
empobj.City = ds.Tables[0].Rows[i]["City"].ToString();
empobj.State = ds.Tables[0].Rows[i]["State"].ToString();
empobj.Country = ds.Tables[0].Rows[i]["Country"].ToString();
emplst.Add(empobj);
}
con.Close();
return Json(new { data = emplst }, JsonRequestBehavior.AllowGet);
}
public JsonResult CountryData()
{
List<Employee> emplst = new List<Employee>();
DataSet ds = new DataSet();
connection();
cmd = new SqlCommand("usp_getcountry_data", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Employee empobj = new Employee();
empobj.Countryid = ds.Tables[0].Rows[i]["Countryid"].ToString();
empobj.Country = ds.Tables[0].Rows[i]["Country"].ToString();
emplst.Add(empobj);
}
con.Close();
return Json(new { data = emplst }, JsonRequestBehavior.AllowGet);
}
public JsonResult StateData(string country_id)
{
List<Employee> emplst = new List<Employee>();
DataSet ds = new DataSet();
connection();
cmd = new SqlCommand("usp_getstate_data", con);
cmd.Parameters.AddWithValue("@country_id", country_id);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Employee empobj = new Employee();
empobj.Stateid = ds.Tables[0].Rows[i]["stateid"].ToString();
empobj.State = ds.Tables[0].Rows[i]["state"].ToString();
emplst.Add(empobj);
}
con.Close();
return Json(new { data = emplst }, JsonRequestBehavior.AllowGet);
}
public JsonResult CityData(string state_id)
{
List<Employee> emplst = new List<Employee>();
DataSet ds = new DataSet();
connection();
cmd = new SqlCommand("usp_getcity_data", con);
cmd.Parameters.AddWithValue("@state_id", state_id);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
Employee empobj = new Employee();
empobj.Cityid = ds.Tables[0].Rows[i]["Cityid"].ToString();
empobj.City = ds.Tables[0].Rows[i]["City"].ToString();
emplst.Add(empobj);
}
con.Close();
return Json(new { data = emplst }, JsonRequestBehavior.AllowGet);
}
public ActionResult EmployeeData(int id)
{
return Json(GetData(id));
}
public ActionResult AddOrEdit(int id)
{
return View(GetData(id));
}
[HttpPost]
public ActionResult AddOrEdit(Employee empobj, string Gujarati, string Hindi, string English)
{
try
{
connection();
if (Gujarati == "true")
{
empobj.isGujarati = true;
}
else
{
empobj.isGujarati = false;
}
if (Hindi == "true")
{
empobj.isHindi = true;
}
else
{
empobj.isHindi = false;
}
if (English == "true")
{
empobj.isEnglish = true;
}
else
{
empobj.isEnglish = false;
}
connection();
string spname = "USP_Add_Edit_Employee";
SqlCommand cmd = new SqlCommand(spname, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name", empobj.Name);
cmd.Parameters.AddWithValue("@Position", empobj.Position);
cmd.Parameters.AddWithValue("@Office", empobj.Office);
cmd.Parameters.AddWithValue("@Age", empobj.Age);
cmd.Parameters.AddWithValue("@Salary", empobj.Salary);
cmd.Parameters.AddWithValue("@Gender", empobj.Gender);
cmd.Parameters.AddWithValue("@isGujarati", empobj.isGujarati);
cmd.Parameters.AddWithValue("@isHindi", empobj.isHindi);
cmd.Parameters.AddWithValue("@isEnglish", empobj.isEnglish);
cmd.Parameters.AddWithValue("@Cityid", empobj.Cityid);
cmd.Parameters.AddWithValue("@City", empobj.Cityval);
cmd.Parameters.AddWithValue("@Stateid", empobj.Stateid);
cmd.Parameters.AddWithValue("@State", empobj.Stateval);
cmd.Parameters.AddWithValue("@Countryid", empobj.Countryid);
cmd.Parameters.AddWithValue("@Country", empobj.Countryval);
con.Open();
int i = cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
ex.Message.ToString();
return View("Error");
}
return RedirectToAction("Index");
}
[HttpPost]
public ActionResult delete(int id)
{
try
{
Employee empobj = new Employee();
connection();
string spname = "USP_deleteEmployee";
SqlCommand cmd = new SqlCommand(spname, con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmployeeID", id);
con.Open();
int i = cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
ex.Message.ToString();
return View("Error");
}
return RedirectToAction("Index");
}
}
}
-------------------------------------------------------------------------------------------------
public class Employee
{
public int EmployeeID { get; set; }
public string Name { get; set; }
public string Position { get; set; }
public string Office { get; set; }
public int Age { get; set; }
public int Salary { get; set; }
public string Gender { get; set; }
public bool isGujarati { get; set; }
public bool isHindi { get; set; }
public bool isEnglish { get; set; }
public string Countryid { get; set; }
public string Country { get; set; }
public string Countryval { get; set; }
//State property
public string Stateid { get; set; }
public string State { get; set; }
public string Stateval { get; set; }
//City property
public string Cityid { get; set; }
public string City { get; set; }
public string Cityval { get; set; }
}
}
-----------------------------------------------------------------------------------------
AddOrEdit.cshtml
@model IEnumerable<Asp.NETMVCCRUD.Models.Employee>
@{
Layout = null;
}
@using (Html.BeginForm("AddOrEdit", "Employee", FormMethod.Post, new { onsubmit = "return SubmitForm(this)" }))
{
@Html.HiddenFor(model => model.EmployeeID)
<div class="form-group">
@Html.LabelFor(model => model.Name, new { @class = "control-label" })
@Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Name)
@Html.HiddenFor(model => model.Countryid)
@Html.HiddenFor(model => model.Stateid)
@Html.HiddenFor(model => model.Cityid)
@Html.HiddenFor(model => model.Countryval)
@Html.HiddenFor(model => model.Stateval)
@Html.HiddenFor(model => model.Cityval)
</div>
<div class="form-group">
@Html.LabelFor(model => model.Position, new { @class = "control-label" })
@Html.EditorFor(model => model.Position, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Position)
</div>
<div class="form-group">
@Html.LabelFor(model => model.Office, new { @class = "control-label" })
@Html.EditorFor(model => model.Office, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Office)
</div>
<div class="form-group">
@Html.LabelFor(model => model.Age, new { @class = "control-label" })
@Html.EditorFor(model => model.Age, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Age)
</div>
<div class="form-group">
@Html.LabelFor(model => model.Salary, new { @class = "control-label" })
<div class="input-group">
<span class="input-group-addon">
<i class="fa fa-inr" aria-hidden="true"></i>
</span>
@Html.EditorFor(model => model.Salary, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.Salary)
</div>
<br />
</div>
<div class="form-group">
@Html.LabelFor(model => model.Gender, new { @class = "control-label" })
<label class="radio-inline">
@Html.RadioButtonFor(model => model.Gender, "Male", new { @name = " Gender" })
Male
</label>
<label class="radio-inline">
@Html.RadioButtonFor(model => model.Gender, "Female", new { @name = " Gender" })
Female
</label>
@Html.ValidationMessageFor(model => model.Gender)
</div>
<div class="form-group" id="AjaxCheckbox">
<label class="control-label">
Languages Known
</label>
<label class="checkbox-inline">
@Html.CheckBox("Gujarati")
@Html.ValidationMessageFor(model => model.isGujarati)
Gujarati
</label>
<label class="checkbox-inline">
@Html.CheckBox("Hindi")
@Html.ValidationMessageFor(model => model.isHindi)
Hindi
</label>
<label class="checkbox-inline">
@Html.CheckBox("English")
@Html.ValidationMessageFor(model => model.isEnglish)
English
</label>
</div>
<select id="Country" name="Country" class="form-control"></select>
<select id="State" name="State" class="form-control"></select>
<select id="City" name="City" class="form-control"></select>
<div class="form-group">
<input type="submit" value="Submit" class="btn btn-primary" />
<input type="reset" value="Reset" class="btn" />
</div>
}
<script src="~/Scripts/jquery-1.12.4.min.js"></script>
<script>
var ddlCountry = $("#Country");
ddlCountry.empty().append('<option selected="selected" value="0" disabled = "disabled">Loading.....</option>');
$.ajax({
type: "GET",
url: "/Employee/CountryData",
data: '{}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (response) {
ddlCountry.empty().append('<option selected="selected" value="0">Please select</option>');
$.each(response.data, function () {
ddlCountry.append($("<option></option>").val(this['Countryid']).html(this['Country']));
});
}
});
$("#Country").change(function () {
var id = $(this).val();
var ddlState = $("#State");
ddlState.empty().append('<option selected="selected" value="0" disabled = "disabled">Loading.....</option>');
$.ajax({
type: "GET",
url: "/Employee/StateData",
data: { country_id: id },
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (response) {
ddlState.empty().append('<option selected="selected" value="0">Please select</option>');
$.each(response.data, function () {
ddlState.append($("<option></option>").val(this['Stateid']).html(this['State']));
});
$("#Countryid").val($("#Country").val());
$("#Countryval").val($("#Country option:selected").text());
}
});
});
$("#State").change(function () {
var id = $(this).val();
var ddlState = $("#City");
ddlState.empty().append('<option selected="selected" value="0" disabled = "disabled">Loading.....</option>');
$.ajax({
type: "GET",
url: "/Employee/CityData",
data: { state_id: id },
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (response) {
ddlState.empty().append('<option selected="selected" value="0">Please select</option>');
$.each(response.data, function () {
ddlState.append($("<option></option>").val(this['Cityid']).html(this['City']));
});
$("#Stateval").val($("#State option:selected").text());
$("#Stateid").val($("#State").val());
}
});
$("#City").change(function () {
$("#Cityval").val($("#City option:selected").text());
$("#Cityid").val($("#City").val());
});
});
</script>
---------------------------------------------------------------------------------------
Index.cshtml
@{
ViewBag.Title = "Employee List";
}
<h2>Employee CRUD Operations</h2>
<a class="btn btn-success" style="margin-bottom:10px" onclick="PopupForm('@Url.Action("AddOrEdit","Employee")')"><i class="fa fa-plus"></i> Add New</a>
<table id="employeeTable" class="table table-striped table-bordered" style="width:100%">
<thead>
<tr>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Age</th>
<th>Salary</th>
<th>Gender</th>
<th>Languages Known</th>
<th>Country</th>
<th>State</th>
<th>City</th>
<th></th>
</tr>
</thead>
</table>
<link href="https://cdn.datatables.net/1.10.21/css/dataTables.bootstrap.min.css" rel="stylesheet" />
<link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet" />
@section scripts{
@*<script src="https://cdn.datatables.net/1.10.21/js/dataTables.bootstrap.min.js"></script>*@
<script>
var Popup, dataTable;
$(document).ready(function () {
dataTable = $("#employeeTable").DataTable({
"ajax": {
"url": "/Employee/GetData",
"type": "GET",
"datatype": "json"
},
"columns": [
{ "data": "Name" },
{ "data": "Position" },
{ "data": "Office" },
{ "data": "Age" },
{ "data": "Salary" },
{ "data": "Gender" },
{
"data": null,
"render": function (empdata) {
//debugger
//console.log(empdata)
var gujarati = (empdata.isGujarati === false) ? " " : "Gujarati";
var hindi = (empdata.isHindi === false) ? " " : "Hindi";
var english = (empdata.isEnglish === false) ? " " : "English";
return gujarati + " " + hindi + " " + english;
}
},
{ "data": "Country" },
{ "data": "State" },
{ "data": "City" },
{ "data":"EmployeeID" , "render" : function (data) {
return "<a class='btn btn-default btn-sm' onclick=PopupForm('@Url.Action("AddOrEdit", "Employee")/" +data+ "')><i class='fa fa-pencil'></i> Edit</a><a class='btn btn-danger btn-sm' style='margin-left:5px' onclick=Delete("+data+")><i class='fa fa-trash'></i> Delete</a>";
},
"paging": true,
"sort": false,
"orderable": true,
"searchable": true,
"scrollY":200,
"width": "300px"
}
],
"language": {
"emptyTable" : "No data found, Please click on <b>Add New</b> Button"
}
});
});
function PopupForm(url) {
var formDiv = $('<div/>');
$.get(url)
.done(function (response) {
console.log(response)
formDiv.html(response);
Popup = formDiv.dialog({
autoOpen: true,
resizable: false,
title: 'Fill Employee Details',
height: 500,
width: 700,
close: function () {
Popup.dialog('destroy').remove();
}
});
});
}
function SubmitForm(form) {
$.validator.unobtrusive.parse(form);
if($(form).valid()){
$.ajax({
type : "POST",
url : form.action,
data: $(form).serialize(),
success : function (data) {
if(data.success)
{
Popup.dialog('close');
dataTable.ajax.reload();
$.notify(data.message,{
globalPosition :"top center",
className : "success"
})
}
}
});
}
return false;
}
function Delete(id) {
if(confirm('Are You Sure to Delete this Employee Record ?'))
{
$.ajax({
type: "POST",
url: '@Url.Action("Delete","Employee")/' + id,
success: function (data) {
if (data.success)
{
dataTable.ajax.reload();
$.notify(data.message, {
globalPosition: "top center",
className: "success"
})
}
}
});
}
}
</script>
}
Tables:
CREATE TABLE [dbo].[countryState](
[StateId] [int] NOT NULL,
[CountryId] [int] NULL,
[State] [varchar](30) NULL,
PRIMARY KEY CLUSTERED
(
[StateId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
----------------------------------------------
CREATE TABLE [dbo].[Employee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Position] [varchar](50) NULL,
[Office] [varchar](50) NULL,
[Age] [int] NULL,
[Salary] [int] NULL,
[Gender] [varchar](50) NULL,
[isGujarati] [bit] NULL,
[isHindi] [bit] NULL,
[isEnglish] [bit] NULL,
[CityId] [int] NULL,
[City] [varchar](30) NULL,
[StateId] [int] NULL,
[State] [varchar](30) NULL,
[CountryId] [int] NULL,
[Country] [varchar](30) NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
------------------------------------------------------------------------------
CREATE TABLE [dbo].[stateCity](
[CityId] [int] NULL,
[StateId] [int] NULL,
[City] [varchar](30) NULL
) ON [PRIMARY]
GO
------------------------------------------------------------------------------
CREATE TABLE [dbo].[tbl_city](
[City_id] [int] IDENTITY(1,1) NOT NULL,
[State_id] [int] NULL,
[City] [nvarchar](max) NULL,
CONSTRAINT [PK_tbl_city] PRIMARY KEY CLUSTERED
(
[City_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_Country](
[Country_id] [int] IDENTITY(1,1) NOT NULL,
[Country_Name] [nvarchar](50) NULL,
CONSTRAINT [PK_tbl_Country] PRIMARY KEY CLUSTERED
(
[Country_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl_state](
[State_id] [int] IDENTITY(1,1) NOT NULL,
[Country_id] [int] NULL,
[State] [nvarchar](max) NULL,
CONSTRAINT [PK_tbl_state] PRIMARY KEY CLUSTERED
(
[State_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
GO
CREATE PROCEDURE [dbo].[USP_Add_Edit_Employee]
@EmployeeID int = 0,
@Name varchar(50),
@Position varchar(50),
@Office varchar(50),
@Age int,
@Salary int,
@Gender varchar(50),
@isGujarati bit,
@isHindi bit,
@isEnglish bit,
@Cityid varchar(30),
@City varchar(30),
@Stateid varchar(30),
@State varchar(30),
@Countryid varchar(30),
@Country varchar(30),
@result int = 0 out
AS
BEGIN
SET NOCOUNT ON;
if @EmployeeID=0
BEGIN
insert into Employee(Name, Position, Office,Age,Salary,Gender,isGujarati,isHindi,isEnglish,CityId,City,StateId,State,CountryId,Country)
values(@Name,@Position,@Office,@Age,@Salary,@Gender,@isGujarati,@isHindi,@isEnglish,@Cityid,@City,@Stateid,@State,@Countryid,@Country)
set @result = SCOPE_IDENTITY()
END
ELSE
BEGIN
update Employee set
Name = @Name,
Position = @Position,
Office =@Office,
Age = @Age,
Salary =@Salary,
Gender =@Gender,
isGujarati =@isGujarati,
isHindi =@isHindi,
isEnglish = @isEnglish,
CityId = @Cityid,
City = @City,
StateId= @Stateid,
State= @State,
CountryId = @Countryid,
Country = @Country
where EmployeeID=@EmployeeID
select @EmployeeID
END
END
GO
Create PROCEDURE [dbo].[USP_deleteEmployee]
@EmployeeID int
AS
BEGIN
SET NOCOUNT ON;
Delete from Employee where EmployeeID=@EmployeeID
END
GO
CREATE proc [dbo].[usp_getcity_data]
@state_id varchar(15)
as
select cityid,city from stateCity where stateid=@state_id
GO
CREATE proc [dbo].[usp_getcountry_data]
as
select CountryId,Country as country from Country
GO
CREATE PROCEDURE [dbo].[USP_GetEmployeeDetails]
@EmployeeID int = 0
AS
BEGIN
SET NOCOUNT ON;
if @EmployeeID=0
BEGIN
select * from Employee
END
ELSE
BEGIN
select * from Employee where EmployeeID=@EmployeeID
END
END
GO
CREATE proc [dbo].[usp_getstate_data]
@country_id varchar(15)
as
select stateid,state from countryState where countryid=@country_id
GO
USE [master]
GO
ALTER DATABASE [MvcCRUDDB] SET READ_WRITE
GO
No comments:
Post a Comment