ASP.NET MVC 5 Stored Procedure

Continue, the Article shared everyone,Today, I'm using Stored Procedure in ASP.NET MVC 5. The data to retrieve, Update, Add,...We usually have many way get data, after then add data to database
Create Project ASP.NET MVC 5

- Create database in SQL SERVER
- Create Project ASP.NET MVC 5: File -> New project -> Chọn ASP.NET MVC 5
- Install Entity Framework: Click right project -> Manager Nutget Packages -> Search Entity Framework -> Installing

Okay, the figure below in the database in SQL SERVER 2008, I have create tables and Stored Procedures available

You can copy the code below to run the executable in SQL SERVER 2008, it's create the stored procedure

//GetAll_User
 CREATE PROC GetAll_User
 AS
    SELECT * FROM User Order By idUser DESC
 GO

 //GetById_User
 CREATE PROC GetById_User
    @idUser int
 AS
    SELECT * FROM Users WHERE idUser = @idUser
 GO

//Insert_User
CREATE PROC Insert_User
    @Username nvarchar(50),
    @Password nvarchar(50),
    @Lever int,
    @LastIdInsert int output 
    AS 
        BEGIN
            INSERT INTO Users(Username,Password,Lever) VALUES(@Username,@Password,@Lever)
            SET @LastIdInsert = SCOPE_IDENTITY()
            RETURN @LastIdInsert
        END
    GO

//Update user
 CREATE PROC Update_User
    @idUser int,
    @Username nvarchar(50)
AS
    UPDATE Users SET Username = @Username WHERE idUser = @idUser
    SELECT * FROM Users WHERE idUser = @idUser
GO

After then, we have the database, import it to Project ASP.NET MVC 5

select Data -> ADO.NET Entity Data Model -> Next

You type your SQL SERVER Server, then select the Database you want to add to the project

Continue, click Ok, after then ConnectString in the figure below, it auto add to Web.config.cs

Okay, we need choose Table & Stored Procedure in the figure below to project

After performing the above steps, finally we have imported the database to Project, you will see the picture below, do you see the Update Model from Database , later if there is anything new, click Right -> Model -> Update Model from Database to add or update the Model
Right Click User Model-> select as shown below

Continue to choose as shown below, select the add function, which means you will select a Stored Procedure to add to the project.

The following image you can name anything to remember, so we can call it easier later.

Ok that's it, now we just need to create a control file called Stored procedure out and use it.
If you have successfully imported the database into the project, next we create a File HomeController.cs in the Controllers folder

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Core.Objects;
using System.Data.Entity.Core.Objects.DataClasses;
using StoredProcedureMVC5.Models;
namespace StoredProcedureMVC5.Controllers
{
    public class HomeController : Controller
    {
        private demoASPEntities _db = new demoASPEntities();
        // GET: Home
        public ActionResult Index()
        {
            var data = _db.GetAll_User_PK().ToList();
            return Json(data3, JsonRequestBehavior.AllowGet);

        }
    }
}

The code on the user call the Stored Procudure name = " GetAll_User_PK " or name = " GetAll_User " which I set in the steps above image, the command will execute and return the data table Users in the database
To search for an element and Retrieving the specified data of that element is as follows, we will call the stored procedure name = " GetById_User", then insert the parameter

var data = _db.GetById_User(1).ToList();
return Json(data3, JsonRequestBehavior.AllowGet);

Update the element in the Users table:

var data = _db.Update_User(1,"ABC-XYZ").ToList();
return Json(data3, JsonRequestBehavior.AllowGet);

Add an element to the Users table , then return the idUser just added

System.Data.Entity.Core.Objects.ObjectParameter returnId = new System.Data.Entity.Core.Objects.ObjectParameter("LastIdInsert", typeof(int));
             _db.Insert_User("Skipperhoa2019", "123445555", 1, returnId);
            var data3 = _db.GetById_User(returnId).ToList();
            return Json(data3, JsonRequestBehavior.AllowGet);

In the code above, you notice " LastIdInsert " which is the parameter we declared in our Stored Procedure , used to Output the parameter.
In half, you can use the Stored Procedure call as follows:

context.Database.SqlQuery<EntityType>(
    "EXEC ProcName @param1, @param2",
    new SqlParameter("param1", param1),
    new SqlParameter("param2", param2)
);

EntityType: is your model name
ProcName: name of Stored Procedure
SqlParameter: use parameter insertion for Stored Procedure
You can find out more, the above way is great because it has no EXEC like we are running the SQL SERVER statement so!
The Article: ASP.NET MVC 5 Stored Procedure