Mvc Crude using ajax

Store Procedure:

ALTER PROCEDURE [dbo].[SP_EMPLOYEE]

@DID int=null,
@EID int=null,
@SID int=null,
@CountryID int=null,
@StateID int=null,
@CityID int=null,
@DeptName varchar(50)=null,
@DName varchar(50)=null,
@EName varchar(50)=null,
@Amount decimal=null,
@StateName varchar(50)=null,
@CountryName varchar(50)=null,
@CityName varchar(50)=null,
@Gender varchar(50)=null,
@Hobby varchar(50)=null,
@BDate datetime=null,
@Query varchar(50)=null

AS
if(@Query='DeptAll')
BEGIN
select DID,DeptName from tbl_DP;
END

else if(@Query='SelectCountry')
BEGIN
select CountryID,CountryName from tbl_Country;
END

else if(@Query='SelectState')
BEGIN
select StateID,StateName from tbl_State where CountryID=@CountryID;
END

else if(@Query='SelectCity')
BEGIN
select CityID,CityName from tbl_City where StateID=@StateID;
END
else if(@Query='Insert')
BEGIN
Insert into tbl_Department values(@DName);
Insert into tbl_Employee values(@EName,@CountryName,@StateName,@CityName,@Gender,@Gender,SCOPE_IDENTITY(),@BDate);
Insert into tbl_Salary values(@Amount,SCOPE_IDENTITY());
END
else if(@Query='SelectALL')
BEGIN
select d.DName,e.EID,e.City,e.State,e.Country,e.Date,e.EName,e.Gender from tbl_Department as d inner join tbl_Employee as e on d.DID=e.DID;

END
else if(@Query='delete')
BEGIN
delete from tbl_Employee where tbl_Employee.EID=@EID;
delete from tbl_Salary where tbl_Salary.EID=@EID;

END

EmpModel :

public class EmpModel

{

public int DID { get; set; }

public int EID { get; set; }

public int SID { get; set; }

public string DeptName { get; set; }

public string DName { get; set; }

public string Password { get; set; }

public string EName { get; set; }

public decimal Amount { get; set; }

public string StateName { get; set; }

public string CountryName { get; set; }

public string CityName { get; set; }

public string Gender { get; set; }

public string Hobby { get; set; }

public DateTime BDate { get; set; }

}

Controller :

public class EmployeeController : Controller

{

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conString"].ConnectionString);

[HttpGet]

public ActionResult Index()

{

ViewBag.Dept = GetDepartment().ToList();

ViewBag.CountryID = Country().ToList();

con.Close();

return View();

}

public IList<SelectListItem> GetDepartment()

{

con.Open();

SqlCommand cmd = new SqlCommand("SP_EMPLOYEE", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@query", SqlDbType.VarChar);

cmd.Parameters["@query"].Value = "DeptAll";

SqlDataReader dr = cmd.ExecuteReader();

List<SelectListItem> Dept = new List<SelectListItem>();

while (dr.Read())

{

Dept.Add(new SelectListItem { Value = dr["DID"].ToString(), Text = dr["DeptName"].ToString() });

}

con.Close();

return Dept;

}

public IList<SelectListItem> Country()

{

con.Open();

SqlCommand cmd1 = new SqlCommand("SP_EMPLOYEE", con);

cmd1.CommandType = CommandType.StoredProcedure;

cmd1.Parameters.Add("@query", SqlDbType.VarChar);

cmd1.Parameters["@query"].Value = "SelectCountry";

SqlDataReader dr1 = cmd1.ExecuteReader();

List<SelectListItem> Dept1 = new List<SelectListItem>();

while (dr1.Read())

{

Dept1.Add(new SelectListItem { Value = dr1["CountryID"].ToString(), Text = dr1["CountryName"].ToString() });

}

return Dept1;

}

public JsonResult GetState( int Cid)

{

con.Open();

SqlCommand cmd = new SqlCommand("SP_EMPLOYEE",con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@CountryID",SqlDbType.VarChar);

cmd.Parameters["@CountryID"].Value=Cid;

cmd.Parameters.Add("@query",SqlDbType.VarChar);

cmd.Parameters["@query"].Value="SelectState";

SqlDataReader dr = cmd.ExecuteReader();

List<SelectListItem> Dept = new List<SelectListItem>();

while (dr.Read())

{

Dept.Add(new SelectListItem { Value = dr["StateID"].ToString(), Text = dr["StateName"].ToString() });

}

con.Close();

return Json(Dept, JsonRequestBehavior.AllowGet);

}

public JsonResult GetCity(int Sid)

{

con.Open();

SqlCommand cmd = new SqlCommand("SP_EMPLOYEE", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@StateID", SqlDbType.VarChar);

cmd.Parameters["@StateID"].Value = Sid;

cmd.Parameters.Add("@query", SqlDbType.VarChar);

cmd.Parameters["@query"].Value = "SelectCity";

SqlDataReader dr = cmd.ExecuteReader();

List<SelectListItem> Dept = new List<SelectListItem>();

while (dr.Read())

{

Dept.Add(new SelectListItem { Value = dr["CityID"].ToString(), Text = dr["CityName"].ToString() });

}

con.Close();

return Json(Dept, JsonRequestBehavior.AllowGet);

}

[HttpPost]

public ActionResult Index(string CountryName, string StateName, string CityName, string EName, decimal Amount, string Department, string Gender, string BDate)

{

con.Open();

SqlCommand cmd = new SqlCommand("SP_EMPLOYEE",con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@DID", SqlDbType.Int);

cmd.Parameters.Add("@CountryName",SqlDbType.VarChar);

cmd.Parameters.Add("@StateName", SqlDbType.VarChar);

cmd.Parameters.Add("@CityName", SqlDbType.VarChar);

cmd.Parameters.Add("@EName", SqlDbType.VarChar);

cmd.Parameters.Add("@Amount",SqlDbType.Decimal);

cmd.Parameters.Add("@Gender", SqlDbType.VarChar);

cmd.Parameters.Add("@DName", SqlDbType.VarChar);

cmd.Parameters.Add("@BDate", SqlDbType.DateTime);

cmd.Parameters.Add("@Query", SqlDbType.VarChar);

cmd.Parameters["@Query"].Value = "Insert";

cmd.Parameters["@DID"].Value = 0;

cmd.Parameters["@CountryName"].Value = CountryName;

cmd.Parameters["@StateName"].Value = StateName;

cmd.Parameters["@CityName"].Value = CityName;

cmd.Parameters["@EName"].Value = EName;

cmd.Parameters["@Amount"].Value = Amount;

cmd.Parameters["@Gender"].Value = Gender;

cmd.Parameters["@DName"].Value = Department;

cmd.Parameters["@BDate"].Value =BDate;

cmd.ExecuteNonQuery();

con.Close();

return RedirectToAction("Index");

}

public ActionResult Profile()

{

con.Open();

SqlCommand cmd = new SqlCommand("SP_EMPLOYEE",con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@Query", SqlDbType.VarChar);

cmd.Parameters["@Query"].Value = "SelectALL";

SqlDataReader dr = cmd.ExecuteReader();

List<EmpModel> EM = new List<EmpModel>();

while (dr.Read())

{

EmpModel emp = new EmpModel();

emp.EID =Convert.ToInt32(dr["EID"].ToString());

emp.EName = dr["EName"].ToString();

emp.DName = dr["DName"].ToString();

emp.CityName = dr["City"].ToString();

emp.StateName = dr["State"].ToString();

emp.CountryName = dr["Country"].ToString();

emp.Gender = dr["Gender"].ToString();

emp.BDate = DateTime.Parse(dr["Date"].ToString());

EM.Add(emp);

}

con.Close();

return View(EM);

}

public ActionResult Delete(int id)

{

con.Open();

SqlCommand cmd = new SqlCommand("SP_EMPLOYEE", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@EID",SqlDbType.Int);

cmd.Parameters["@EID"].Value = id;

cmd.Parameters.Add("@Query",SqlDbType.VarChar);

cmd.Parameters["@Query"].Value = "delete";

cmd.ExecuteNonQuery();

con.Close();

return RedirectToAction("Profile");

}

View : Insert

@model Mvc_SP.Models.EmpModel

ViewBag.Title = "Index";

}

@*<script src="~/Scripts/jquery-1.7.1.js"></script>*@

$(document).ready(function () {

//Country Dropdown Selectedchange event

$("#CountryID").change(function () {

$("#StateID").empty();

$.ajax({

type: 'POST',

url: '@Url.Action("GetState")', // Calling json method

dataType: 'json',

data: { Cid: $("#CountryID").val() },

// Get Selected Country ID.

success: function (states) {

$.each(states, function (i, state) {

$("#StateID").append('<option value="' + state.Value + '">' +

state.Text + '</option>');

});

error: function (ex) {

alert('Failed to retrieve states.' + ex);

}

});

return false;

})

$("#StateID").change(function () {

$("#CityID").empty();

$.ajax({

type: 'POST',

url: '@Url.Action("GetCity")', // Calling json method

dataType: 'json',

data: { Sid: $("#StateID").val() },

// Get Selected state ID.

success: function (cities) {

$.each(cities, function (i, city) {

$("#CityID").append('<option value="' + city.Value + '">' +

city.Text + '</option>');

});

error: function (ex) {

alert('Failed to retrieve states.' + ex);

}

});

return false;

})

});

function Data() {

var ctr = document.getElementById('CountryID');

var Countryname = ctr.options[ctr.selectedIndex].text;

var stt = document.getElementById('StateID');

var Statename = stt.options[stt.selectedIndex].text;

var ct = document.getElementById('CityID');

var Cityname = ct.options[ct.selectedIndex].text;

var ct1 = document.getElementById('Dept');

var Department = ct1.options[ct.selectedIndex].text;

var radio = $('[name="Gender"]:radio:checked').val();

var EName = $("#EName").val();

var Amount = $("#Amount").val();

var BDate = $("#bdate").val();

alert(BDate);

$.ajax({

url: '@Url.Action("Index", "Employee")',

data: { CountryName: Countryname, StateName: Statename, CityName: Cityname, EName: EName, Amount: Amount, Department: Department, Gender: radio, BDate: BDate },

type: 'POST',

dataType: 'json',

success: function (data) {

alert(Countryname);

}

});

}

function generate() {

var length = 10;

var chars = "abcdefghijklmnopqrstuvwxyz!@@#$%^&*()-+<>ABCDEFGHIJKLMNOP1234567890";

var pass = "";

for (var x = 0; x < length; x++) {

var i = Math.floor(Math.random() * chars.length);

pass += chars.charAt(i);

}

document.getElementById("Password").value = pass;

}

</script>

<h2>Index</h2>

<legend>EmpModel</legend>

@Html.LabelFor(model => model.DName)

</div>

@Html.EditorFor(model => model.DName)

@Html.ValidationMessageFor(model => model.DName)

</div>

@Html.LabelFor(model => model.Password)

</div>

@Html.EditorFor(model => model.Password)<a onclick="generate();"> Generate Password</a>

@Html.ValidationMessageFor(model => model.Password)

</div>

@Html.DropDownList( "Dept", ViewBag.Dept as SelectList, "-Select -", new { style = "width:70px;height:25px", @id = "Dept" } )

@Html.ValidationMessageFor(model => model.DName)

</div>

@Html.LabelFor(model => model.EName)

</div>

@Html.EditorFor(model => model.EName)

@Html.ValidationMessageFor(model => model.EName)

</div>

@Html.LabelFor(model => model.Amount)

</div>

@Html.EditorFor(model => model.Amount)

@Html.ValidationMessageFor(model => model.Amount)

</div>

@Html.LabelFor(model => model.CountryName)

</div>

@Html.DropDownList( "CountryID", ViewBag.CountryID as SelectList, "-Select -", new { style = "width:70px;height:25px", @id = "CountryID" } )

@Html.ValidationMessageFor(model => model.CountryName)

</div>

@Html.LabelFor(model => model.StateName)

</div>

@Html.DropDownList( "CountryID", new SelectList(string.Empty, "Value", "Text"),"-Select -", new { style = "width:70px;height:25px", @id = "StateID" } )

@Html.ValidationMessageFor(model => model.StateName)

</div>

@Html.LabelFor(model => model.CityName)

</div>

@Html.DropDownList( "CityID",new SelectList(string.Empty, "Value", "Text") ,"-Select -", new { style = "width:70px;height:25px", @id = "CityID" } )

@Html.ValidationMessageFor(model => model.CityName)

</div>

@Html.LabelFor(model => model.Gender)

</div>

@Html.RadioButtonFor(model => model.Gender,"Male",true)Male

@Html.RadioButtonFor(model => model.Gender,"Female",false)Female

</div>

</div>

<p>

</p>

</fieldset>

<div>

@Html.ActionLink("Back to List", "Profile")

</div>

@section Scripts {

@Scripts.Render("~/bundles/jqueryval")

}

View :Select :

@model IEnumerable<Mvc_SP.Models.EmpModel>

ViewBag.Title = "Profile";

}

jQuery(function ($) {

$('#example').DataTable({

"bsort": false,

"bFilter":true,

});

</script>

<h2>Profile</h2>

<p>

@Html.ActionLink("Create New", "Index")

</p>

<thead>

<tr>

<th>

@Html.DisplayNameFor(model => model.DName)

</th>

<th>

@Html.DisplayNameFor(model => model.EName)

</th>

<th>

@Html.DisplayNameFor(model => model.Amount)

</th>

<th>

@Html.DisplayNameFor(model => model.StateName)

</th>

<th>

@Html.DisplayNameFor(model => model.CountryName)

</th>

<th>

@Html.DisplayNameFor(model => model.CityName)

</th>

<th>

@Html.DisplayNameFor(model => model.Gender)

</th>

<th>

@Html.DisplayNameFor(model => model.BDate)

</th>

</tr>

</thead>

<tbody>

@foreach (var item in Model) {

<tr>

<td>

@Html.DisplayFor(modelItem => item.DName)

</td>

<td>

@Html.DisplayFor(modelItem => item.EName)

</td>

<td>

@Html.DisplayFor(modelItem => item.Amount)

</td>

<td>

@Html.DisplayFor(modelItem => item.StateName)

</td>

<td>

@Html.DisplayFor(modelItem => item.CountryName)

</td>

<td>

@Html.DisplayFor(modelItem => item.CityName)

</td>

<td>

@Html.DisplayFor(modelItem => item.Gender)

</td>

<td>

@Html.DisplayFor(modelItem => item.BDate)

</td>

<td>

@Html.ActionLink("Edit", "Edit", new { id=item.EID }) |

@Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |

@Html.ActionLink("Delete", "Delete", new { id=item.EID })

</td>

</tr>

}

</tbody>

</table>

See Other Tutorial :

* AngularJS CRUD Operation : Select Insert Edit Update and Delete using AngularJS in ASP.Net MVC

* AngularJS With ASP.NET MVC

* Convert Rows to columns using 'Pivot' in SQL Server

* Mvc Registration page With user exist using Ajax method

* MVC 4 How to Perform Insert Update Delete Edit Select Operation

* MVC4 Edit,update,Delete,cancel inside gridview using sql database

* MVC 4 Gridview To Display Data Using SQL Server Database With Simple code

* Login page in asp.net Mvc4 Web application

* Mvc4 How to bind Dropdown List using Sql Database

* Gridview find control in asp.net

7 comments

Write comments

Anonymous

AUTHOR

24 September 2018 at 17:24 delete

pһân phối chung cư vincity

30 November 2018 at 09:05 delete

hello there aand thank you for your info – I've definitely picked up something new from right here.
I did however expertise a few technical points
usig this web site, since I experienced to reload the weeb site many timees
previous to I could get it to load correctly. I had been wondering if your webb hosting iis OK?
Noot that I am complaining, but sluggish loading instances times will
very frequently affect your placement iin google
and can damage your quality score iif advertisiing and marketing with Adwords.
Well I'm adding this RSS to my e-mail and coluld look out for much more of
your respective exciting content. Make sure you update this
again soon.

9 December 2018 at 09:36 delete

ρhân phối chung cư vincity

22 December 2018 at 21:43 delete

Wow, marvelous blog layout! How long have you been blogging
for? you make blogging look easy. The overfall look off your website is fantastic, let alone the content!

28 January 2019 at 14:03 delete

I do not even know how I ended up here, but I thought this post was good.
I don't know who you are but definitely you are going to a famous blogger if you are not
already ;) Cheers!

22 March 2019 at 06:47 delete

An intriguing discussion is worrth comment. I do believce that you
need to publish more on this issue, it may not be a taboo subject but usuallly folks don't speak about these subjects.
To the next! All the best!!

7 April 2019 at 18:16 delete

It's truly very complex in this busy life to listen news on Television,
thus I just use world wide wweb for that purpose, and take the hottesst information.

codelearne