ASP.NET MVC 5 Stored Procedure
Tags: csharp, webdev
Published: 2020-06-08 04:11:49

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 - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/stored-procedure-in-aspnet-mvc5-00.jpg)
You can copy the code below to run the executable in SQL SERVER 2008, it's create the stored procedure
```csharp
//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
![ASP.NET MVC 5 Stored Procedure - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/stored-procedure-in-aspnet-mvc5-1.jpg)
select Data -> **ADO.NET Entity Data Model** -> Next
![ASP.NET MVC 5 Stored Procedure - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/stored-procedure-in-aspnet-mvc5-2.jpg)
You type your SQL SERVER Server, then select the Database you want to add to the project
![ASP.NET MVC 5 Stored Procedure - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/stored-procedure-in-aspnet-mvc5-3.jpg)
Continue, click Ok, after then ConnectString in the figure below, it auto add to Web.config.cs
![ASP.NET MVC 5 Stored Procedure - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/stored-procedure-in-aspnet-mvc5-4.jpg)
Okay, we need choose Table & Stored Procedure in the figure below to project
![ASP.NET MVC 5 Stored Procedure - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/stored-procedure-in-aspnet-mvc5-5.jpg)
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 - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/stored-procedure-in-aspnet-mvc5-6.jpg)
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 - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/stored-procedure-in-aspnet-mvc5-7.jpg)
The following image you can name anything to remember, so we can call it easier later.
![ASP.NET MVC 5 Stored Procedure - hoanguyenit.com](https://hoanguyenit.com/public/upload/images/stored-procedure-in-aspnet-mvc5-8.jpg)
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
```csharp
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
```csharp
var data = _db.GetById_User(1).ToList();
return Json(data3, JsonRequestBehavior.AllowGet);
```
Update the element in the Users table:
```csharp
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
```csharp
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:
```csharp
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](https://hoanguyenit.com/aspnet-mvc5-stored-procedure.html)