Store procedures crude operation using mvc4

Introduction : Insert update delete using Store Procedure in mvc4

SnapShot:

Step 1:Create two table with folowing

Step 2 : Create Store procedure for crude operation

ALTER PROCEDURE[dbo].[SP_Join]

@Emp_id int=null,

@Emp_Name varchar(50)=null,

@Emp_Dob datetime=null,

@Emp_City varchar(50)=null,

@id int=null,

@Amount decimal=null,

@Name nvarchar(50)=null,

@Adress nvarchar(50)=null,

@City nvarchar(50)=null,

@query varchar(50)=null

if(@query='E_Insert')

BEGIN

insert into tb_Employee values(@Emp_Name,@Emp_Dob,@Emp_City);

insert into tb_Salary values(@Amount,SCOPE_IDENTITY());

insert into tb_user values(@Name,@Adress,@City,SCOPE_IDENTITY());

END

else if(@query='Select')

BEGIN

select e.Emp_id,e.Emp_Name,e.Emp_City,e.Emp_Dob,tb_Salary.Amount from tb_Employee as e inner join tb_Salary on e.Emp_id=tb_Salary.Emp_id;

END

else if(@query='Edit')

BEGIN

select tb_Employee.Emp_id,tb_Employee.Emp_Name,tb_Employee.Emp_Dob,tb_Employee.Emp_City,tb_Salary.Amount from tb_Employee,tb_Salary where tb_Employee.Emp_id=@Emp_id and tb_Salary.Emp_id=@Emp_id;

END

else if(@query='Update')

BEGIN

update tb_Employee set Emp_Name=@Emp_Name,Emp_Dob=@Emp_Dob,Emp_City=@Emp_City where Emp_id=@Emp_id

update tb_Salary set Amount=@Amount where Emp_id=@Emp_id;

END

else if(@query='Delete')

BEGIN

delete from tb_Employee where tb_Employee.Emp_id=@Emp_id;

delete from tb_Salary where tb_Salary.Emp_id=@Emp_id;

END

Step 3 : Crate view for Crude as following

@model IEnumerable<Mvc_SP.Models.Home>

<p>
@Html.ActionLink("Create New", "Insert_Employee")
</p>

@using (@Html.BeginForm())
{
<table>
<tr>
<td>
@Html.TextBox("SearchKey", "")

</td>
<td>
<input type="submit" value="Search" />
</td>
</tr>
</table>
<table>
<tr>
<th>
@Html.DisplayNameFor(model => model.Emp_Id)
</th>
<th>
@Html.DisplayNameFor(model => model.Emp_Dob)
</th>
<th>
@Html.DisplayNameFor(model => model.Emp_City)
</th>
<th>
@Html.DisplayNameFor(model => model.Emp_Name)
</th>
<th>
@Html.DisplayNameFor(model => model.Amount)
</th>

<th></th>
</tr>

@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.Emp_Id)
</td>
<td>
@Html.DisplayFor(modelItem => item.Emp_Dob)
</td>
<td>
@Html.DisplayFor(modelItem => item.Emp_City)
</td>

<td>
@Html.DisplayFor(modelItem => item.Emp_Name)
</td>

<td>
@Html.DisplayFor(modelItem => item.Amount)
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id=item.Emp_Id }) |
@Html.ActionLink("Details", "Details", new { id=item.Emp_Id }) |
@Html.ActionLink("Delete", "Delete", new {id=item.Emp_Id })
</td>
</tr>
}

</table>

}

Step 4: create model as following

public class Home
{
public int Emp_Id { get; set; }
public DateTime Emp_Dob { get; set; }
public string Emp_City { get; set; }
public int Salary_Id { get; set; }
public string Emp_Name { get; set; }
public string Mobile { get; set; }
public decimal Amount { get; set; }

}

Step 5:Create Controller code

public class HomeController : Controller
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);

[HttpPost]
public ActionResult Insert_Employee(Home hm)
{
con.Open();
SqlCommand cmd = new SqlCommand("SP_Join", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Emp_id", SqlDbType.Int);
cmd.Parameters.Add("@Emp_Name", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@Emp_Dob", SqlDbType.DateTime, 50);
cmd.Parameters.Add("@Emp_City", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@Amount", SqlDbType.VarChar, 50);

cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@Adress", SqlDbType.NVarChar, 50);
cmd.Parameters.Add("@City", SqlDbType.NVarChar, 50);

cmd.Parameters["@Emp_id"].Value = 0;
cmd.Parameters["@Emp_Name"].Value = hm.Emp_Name;
cmd.Parameters["@Emp_Dob"].Value = hm.Emp_Dob;
cmd.Parameters["@Emp_City"].Value = hm.Emp_City;
cmd.Parameters["@Amount"].Value = hm.Amount;

cmd.Parameters["@Name"].Value = hm.Name;
cmd.Parameters["@Adress"].Value = hm.Adress;
cmd.Parameters["@City"].Value = hm.City;

cmd.Parameters.Add("@query", SqlDbType.VarChar);
cmd.Parameters["@query"].Value = "E_Insert";
cmd.ExecuteNonQuery();
con.Close();
return View();
}

public List<Home> Selected_Data()
{
List<Home> D = new List<Home>();
con.Open();
SqlCommand cmd = new SqlCommand("SP_Join", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@query", SqlDbType.VarChar);
cmd.Parameters["@query"].Value = "Select";

SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Home hm = new Home();
hm.Emp_Id = Convert.ToInt32(dr["Emp_id"].ToString());
hm.Emp_City = dr["Emp_City"].ToString();
hm.Emp_Dob = DateTime.Parse(dr["Emp_Dob"].ToString());
hm.Emp_Name = dr["Emp_Name"].ToString();
hm.Amount = Decimal.Parse(dr["Amount"].ToString());
D.Add(hm);
}
con.Close();
return (D);
}

[HttpGet]
public ActionResult Select()
{
return View(Selected_Data());
}

[HttpPost]
public ActionResult Select(string SearchKey)
{
List<Home> D = new List<Home>();
Home da = new Home();
var V = (from m in Selected_Data() where m.Emp_Name.StartsWith(SearchKey, StringComparison.OrdinalIgnoreCase) select new { m.Emp_Id, m.Emp_Name, m.Emp_City,m.Amount,m.Emp_Dob }).ToList();
foreach (var w in V)
{
D.Add(new Home { Emp_Id = w.Emp_Id, Emp_Name = w.Emp_Name, Emp_City = w.Emp_City,Emp_Dob=w.Emp_Dob,Amount=w.Amount });
}
return View(D);
}

[HttpGet]
public ActionResult Edit(int id)
{
List<Home> D = new List<Home>();
con.Open();
SqlCommand cmd = new SqlCommand("SP_Join", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Emp_id", SqlDbType.Int);
cmd.Parameters["@Emp_id"].Value = id; ;
cmd.Parameters.Add("@query", SqlDbType.VarChar);
cmd.Parameters["@query"].Value = "Edit";

SqlDataReader dr = cmd.ExecuteReader();
Home hm = new Home();
while (dr.Read())
{
//Home hm = new Home();
hm.Emp_Id = Convert.ToInt32(dr["Emp_id"].ToString());
hm.Emp_City = dr["Emp_City"].ToString();
hm.Emp_Dob = DateTime.Parse(dr["Emp_Dob"].ToString());
hm.Emp_Name = dr["Emp_Name"].ToString();
hm.Amount = Decimal.Parse(dr["Amount"].ToString());
}
con.Close();
return View(hm);
}
[HttpPost]
public ActionResult Edit(Home hm)
{
con.Open();
SqlCommand cmd = new SqlCommand("SP_Join", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Emp_id", SqlDbType.Int);
cmd.Parameters.Add("@Emp_Name", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@Emp_Dob", SqlDbType.DateTime, 50);
cmd.Parameters.Add("@Emp_City", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@Amount", SqlDbType.VarChar, 50);

cmd.Parameters["@Emp_id"].Value = hm.Emp_Id;
cmd.Parameters["@Emp_Name"].Value = hm.Emp_Name;
cmd.Parameters["@Emp_Dob"].Value = hm.Emp_Dob;
cmd.Parameters["@Emp_City"].Value = hm.Emp_City;
cmd.Parameters["@Amount"].Value = hm.Amount;

cmd.Parameters.Add("@query", SqlDbType.VarChar);
cmd.Parameters["@query"].Value = "Update";
cmd.ExecuteNonQuery();
con.Close();
return RedirectToAction("Select");
}

public ActionResult Delete(int id)
{
con.Open();
SqlCommand cmd = new SqlCommand("SP_Join", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@Emp_id", SqlDbType.Int);
cmd.Parameters["@Emp_id"].Value = id;
cmd.Parameters.Add("@query", SqlDbType.VarChar);
cmd.Parameters["@query"].Value = "Delete";
cmd.ExecuteNonQuery();
con.Close();
return RedirectToAction("Select");
}

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

codelearne

Store procedures crude operation using mvc4