Hi All,
This is just the extension of CRUD operation in Asp.net MVC using AngularJs
In the previous post we show that we are doing database operation using Entity Framework with LINQ query. But if we have some complex logic which require multiple tables to be join then we write the logic in store procedure. So entity framework also provide the feature to map the SP in edmx file.
Now we will go by step wise for this task
Step 1: Write the SP in Database like this
CREATE PROCEDURE DeleteDept
@Id int
AS
Begin
Delete from tblDept where Id=@Id
END
RETURN
---------------------------------------------
CREATE PROCEDURE FetchDept_OnId
@Id int
AS
Begin
Select * from tblDept where Id=@Id
END
RETURN
---------------------------------------------
CREATE PROCEDURE FetchDeptDetails
AS
SELECT * from tblDept
RETURN
-----------------------------------------
CREATE PROCEDURE InsertDept
@DeptName Varchar(250),
@DeptDesc Varchar(500)
AS
Begin
Insert into tblDept(DeptName,DeptDesc) Values(@DeptName,@DeptDesc)
END
RETURN
------------------------------------------
CREATE PROCEDURE UpdateDept
@Id int,
@DeptName Varchar(250),
@DeptDesc Varchar(500)
AS
Begin
Update tblDept Set DeptName=@DeptName, DeptDesc= @DeptDesc where Id=@Id
END
RETURN
Step 2:
Do like my previous article How to fetch data from SP in EF
Step 3: Now select the Context.tt and Model.tt file of Edmx file and run the “Run Custom Tool” like this
This will generate the DAL layer code with SP for Insert/Update/Read/Delete functionality.
Step 4: Now go to the Dept Controller and write the code for calling SP like this
using AngularCRUD.Models;
using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;
namespace AngularCRUD.Controllers
{
public class DeptController : Controller
{
// GET: /Dept/
string msg = string.Empty;
public ActionResult Index()
{
return View();
}
public JsonResult Get_AllDepts()
{
using (Database1Entities obj = new Database1Entities())
{
var objDept = obj.FetchDeptDetails().ToList();
return Json(objDept, JsonRequestBehavior.AllowGet);
}
}
public JsonResult Get_DeptById(string Id)
{
using (Database1Entities obj = new Database1Entities())
{
int DeptId = int.Parse(Id);
return Json(obj.FetchDept_OnId(DeptId), JsonRequestBehavior.AllowGet);
}
}
public string Insert_Dept(tblDept dept)
{
if (dept != null)
{
using (Database1Entities obj = new Database1Entities())
{
int flag= obj.InsertDept(dept.DeptName,dept.DeptDesc);
if (flag==1)
{
msg= "Dept details Added Successfully";
}
return msg;
}
}
else
{
return "Dept Details Not Inserted! Try Again";
}
}
public string Update_Dept(tblDept Dept)
{
if (Dept != null)
{
using (Database1Entities Obj = new Database1Entities())
{
int flag = Obj.UpdateDept(Dept.Id, Dept.DeptName, Dept.DeptDesc);
if (flag==1)
{
msg = "Dept details Updated Successfully";
}
return msg;
}
}
else
{
return "Dept Details Not Updated! Try Again";
}
}
public string Delete_Dept(tblDept dept)
{
if (dept != null)
{
using (Database1Entities obj = new Database1Entities())
{
int flag= obj.DeleteDept(dept.Id);
if (flag==1)
{
msg= "Dept details deleted Successfully";
}
return msg;
}
}
else
{
return "Dept Details Not Deleted! Try Again";
}
}
}
}
Chandradev's Blog 