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

Introduction

We are about to build a MVC application in which we will implement the basic operations of Create, Retrieve, Delete and Update data in the database. In other words, 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]

Stored Procedures

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

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

DELETE

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

Now, let us move into Visual Studio. Seelct New Project-> Add MVC Application.

A new window opens. Choose Internet Application here so that we are not required to include extra templates and dependencies into this peoject from the packet manger.

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

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, configure all the properties. Create a new connection with server name and all.

Test the connection just to ensure that we configured the right 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 Procedues, 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 is showing the Stored Procedures and function imports that will be automatically detected by MVC for our CRUD operations.

Let's add a Controller for the CRUD model.

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

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

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.

Now we are all 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.

Closure

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!