DEV Community

Cover image for ASP.NET MVC 5 Stored Procedure
Nguyễn Thanh Hòa
Nguyễn Thanh Hòa

Posted on

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
ASP.NET MVC 5 Stored Procedure -
You can copy the code below to run the executable in SQL SERVER 2008, it's create the stored procedure

    SELECT * FROM User Order By idUser DESC

    @idUser int
    SELECT * FROM Users WHERE idUser = @idUser

    @Username nvarchar(50),
    @Password nvarchar(50),
    @Lever int,
    @LastIdInsert int output 
            INSERT INTO Users(Username,Password,Lever) VALUES(@Username,@Password,@Lever)
            SET @LastIdInsert = SCOPE_IDENTITY()
            RETURN @LastIdInsert

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

After then, we have the database, import it to Project ASP.NET MVC 5
ASP.NET MVC 5 Stored Procedure -
select Data -> ADO.NET Entity Data Model -> Next
ASP.NET MVC 5 Stored Procedure -
You type your SQL SERVER Server, then select the Database you want to add to the project
ASP.NET MVC 5 Stored Procedure -
Continue, click Ok, after then ConnectString in the figure below, it auto add to Web.config.cs
ASP.NET MVC 5 Stored Procedure -
Okay, we need choose Table & Stored Procedure in the figure below to project
ASP.NET MVC 5 Stored Procedure -
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
ASP.NET MVC 5 Stored Procedure -
Continue to choose as shown below, select the add function, which means you will select a Stored Procedure to add to the project.
ASP.NET MVC 5 Stored Procedure -
The following image you can name anything to remember, so we can call it easier later.
ASP.NET MVC 5 Stored Procedure -
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:

    "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

Top comments (0)