CRUD Operations Using Stored Procedure and Entity Framework in ASP.Net MVC

Introduction

This article will explore how to perform CRUD (Create, Read, Update, and Delete) operations using stored procedures and the Entity Framework in an ASP.NET MVC application. Stored procedures can offer several benefits when working with a database, including improved performance and increased security. By utilizing the power of the Entity Framework, we can easily map our stored procedures to classes and objects in our application, making it simple to interact with our data. We are about to build an MVC application to implement the CRUD operations.

Let's use the following procedure to implement it.

I have a database, DBEngine, with a table, LoginDetails.

CREATE TABLE [dbo].[LoginDetails](  
    [UserId] [int] IDENTITY(1,1) NOT NULL,  
    [UserName] [varchar](20) NOT NULL,  
    [Password] [varchar](20) NOT NULL,  
    [FirstName] [varchar](20) NOT NULL,  
    [LastName] [varchar](20) NOT NULL,  
 CONSTRAINT [PK_LoginDetails] PRIMARY KEY CLUSTERED   
(  
    [UserId] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  

The above SQL Statement creates a table named "LoginDetails" in a database with the following columns:

• UserId - an integer column that is set as the primary key for the table and is set to auto-incrementing by one and starting from 1.
• UserName - a varchar(20) column that cannot be null.
• Password - a varchar(20) column that cannot be null.
• FirstName - varchar(20) column that cannot be null.
• LastName - a varchar(20) column that cannot be null.

Additionally, it creates a primary key constraint on the UserId column, which enforces that the UserId column is unique and not null across all table rows. The primary key also creates a clustered index on the UserId column, which improves the performance of queries that filter or sort data based on the UserId column. It also specifies that the table will be created on the primary filegroup of the database.

Also, you can create by using Designer, as shown in the below snapshot.

Stored Procedures in SQL Server

INSERT

CREATE PROC [dbo].[INSERT_SP]  
   (  
    @Username varchar(20),  
    @Password varchar(20),  
    @FirstName varchar(20),  
    @LastName varchar (20)  
    )  
    AS  
    BEGIN  
    INSERT INTO [dbo].[LoginDetails](UserName,Password,FirstName,LastName) values (@Username,@Password,@FirstName,@LastName);  
    RETURN  
    END 

The SQL mentioned above creates a stored procedure used to insert data into a table called "LoginDetails" in a database. The stored procedure is named "INSERT_SP." It takes four parameters as input – @Username, @Password, @FirstName, and @LastName. These parameters are used to insert data into the corresponding columns of the "LoginDetails" table.

UPDATE

CREATE PROC [dbo].[UPDATE_SP]  
   (  
    @UserId int,  
    @Username varchar(20),  
    @Password varchar(20),  
    @FirstName varchar(20),  
    @LastName varchar (20)  
    )  
    AS  
    BEGIN  
    UPDATE [dbo].[LoginDetails] SET UserName=@Username,Password=@Password,FirstName=@FirstName,LastName=@LastName WHERE UserId=UserId;  
    RETURN  
    END  

The SQL mentioned above statement is used to update data in a table called "LoginDetails" in a database. The stored procedure is named "UPDATE_SP." It takes five parameters as input – @UserId, @Username, @Password, @FirstName, and @LastName. These parameters update specific data in the "LoginDetails" table.

DELETE

CREATE PROC [dbo].[DELETE_SP]  
(  
  @UserId int  
  )  
    AS  
    BEGIN  
    DELETE FROM [dbo].[LoginDetails] WHERE UserId=@UserId  
    RETURN  
    END  

The SQL mentioned above statement is used to delete data from a table called "LoginDetails" in a database. It takes one parameter as input, the @UserId, which is used to specify which rows should be deleted from the table. The stored procedure is named "DELETE_SP."

Now, let us move into Visual Studio. Select New Project→ Add MVC Application.

A new window opens. Choose Internet Application here so that we are not required to include different templates and dependencies from the packet manager in this project.

Here we have our MVC application with default folders for internet applications. Now let's add a Model with a table and Stored Procedures for CRUD operations.

Please provide the name of our Model as CRUD.

Since we have the database in SQL Server, we also have a table named LoginDetails. In the next window, click on Generate From Database.

We now have a window with Connection Properties and configure all the properties. Create a new connection with the server name and all.

Test the connection to ensure that we have configured the suitable connection properties.

Now a window with the Entity Connection string with the name opens.

Now choose the required database objects. In this project, we are implementing the CRUD operations using Stored procedures; therefore, check all the Stored Procedures and the specific table in which these operations are to be applied.

Here is how our Model diagram looks.

To check the Stored Procedures, we check that they are included in projects. Right-click on the model diagram, then click on Model Browser.

The browser shows the Stored Procedures and function imports that MVC will automatically detect for our CRUD operations.

Let's add a Controller for the CRUD model.

If we add a Controller without building the project (model class), an error says no model class is available.

Let's build the project and add a Controller. Now we can add all the available model classes.

We added the Data Context Class as DBEngineEntitites3 in the preceding step. We can confirm this Data Context Class from the CRUD. Context. cs file of our model.

We are set to run the application to implement the CRUD operations in MVC using Entity Framework. Run the application.

Here we can create (insert) new records into the table.

Enter new records.

Enter more records.

Update (edit) existing records.

We can see that updated record.

Delete an existing record.

We can see that the record is deleted from the view.

See the detailed view.

Conclusion

In this article, we performed CRUD operations using Stored Procedures and Entity Framework in ASP.Net MVC with a simple procedure. I hope you all enjoyed it. Comments and Complements are always welcome.

Happy Coding!