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";
            }
        }
    }
}