Stored Procedures In ASP.NET MVC

Introduction

We are using Entity Framework in this application. I'll show you step-by-step operations using simple images.

Back-end

Go to SQL server database and write the following,

  1. create table emp(empno int primary key, ename varchar(20), sal int, deptno int)  
  2.     --stored procedures:  
  3.     --insert  
  4. create proc usp_addemp(@empno int, @ename varchar(20), @sal int, @deptno int)  
  5. as  
  6. begin  
  7. insert into emp(empno, ename, sal, deptno) values(@empno, @ename, @sal, @deptno)  
  8. end  
  9. --update  
  10. create proc usp_updateemp(@empno int, @ename varchar(20), @sal int, @deptno int)  
  11. as  
  12. begin  
  13. update emp set ename = @ename, sal = @sal, deptno = @deptno where empno = @empno  
  14. end  
  15. --delete  
  16. create proc usp_deleteemp(@empno int)  
  17. as  
  18. begin  
  19. delete emp where empno = @empno  
  20. end  
Now, let us move onto Visual studio:

File, New, Project and select MVC Application. Give the meaning full name (here, name is MVCSPCRUD)

MVC Application

After clicking OK, a new window opens. Choose Internet Application here so that we are not required to include extra templates and dependencies into this project from the packet manager.

Internet application

Now let's add a Model with a table and Stored Procedures for CRUD operations.

Add model

Enter name

Generate database

Click on New Connection,

new connection

Data source

Test connection

Choose data connection

Now, select the required table and stored procedures and click Finish.

Entity data model

Now, we will see the following screen,

navigation Properties

To check Stored Procedures we will check that they are included in project. Right-click on the model diagram and click on Model Browser.

Model browser

Now, we can see the stored procedures under Function Exports as given below:

Function Exports

Now, go to BUILD and Build MVCSPCRUD,

build MVCSPCRUD

Now, go to Solution Explorer, right click on Controllers, Add, then click Controller

Add new controller

Give meaningful name for the Controller (here, name is employeeController) and select other options as given below,

Add controller

After clicking Add, go to SolutionExplorer. We can find Create.cshtml, Delete.cshtml, etc under Views folder, then employee folder as given below:

Controller

Now we are all set to run the application. But before that small changes are required.

Open index.cshtml, we can see some code with comments as given below:

index.cshtml

Remove the comments and change the code as given below:

Index code

Open Details.cshtml, we can see some code with comments as given below:

Details

Remove the comments and change the code as given below:

Remove the comments

Now, run the application and enjoy the output.

run the application

Create

index

Edit

Happy coding!