Crud Operation Using AJAX in ASP.NET MVC
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");
}
}
}
