Stored procedure in ASP.NET Core

Table of Content

What is stored procedure?

Understand it using a small example, Let’s assume we are creating one application for any company in that company have Admin, Employee, Helper, etc. So, we can make a design for all to registration in the application and also need to write code for every web-page for registration in other things need to write insert coding for every type of profile registration for individual pages.

Just to avoid insert code write again and again for each and every page to insert statement so we can write code one time and store it in the database and use it for every page’s insert statement for this purpose, we are using the Stored Procedure. We are store code in a database which is used many times in project when we need that code call it and use it as requirements. In short use a single part of the code on each page by using a stored procedure.

A Stored Procedure can contain SELECT, INSERT, UPDATE, and DELETE statements, in other words, we can have a set of Data Manipulation Language and Data Definition Language statements in a single Stored Procedure. They are used whenever we need a single statement or set of statements multiple times in the application.

Types of Stored Procedure

The stored procedure has four types, these are:

System Defined Stored Procedure

System Defined Stored Procedure are already defined in SQL Server by the system.

Extended Stored Procedure

Stored procedures which are call functions from DLL files that are called Extended Stored Procedure..

User-Defined Stored Procedure

A stored Procedure which is created by the user is called a user-defined stored procedure. It contains one or more SQL statements for Insert, Update, Delete, Select, this type of stored procedure also takes input parameters and output parameters.

CLR Stored Procedure

Stored Procedure which has managed codes is called CLR stored procedure. It has more safety and memory management. It also provides object-orientated programming capability so we can also use encapsulation, polymorphism, inheritance and is supported to write code in c#, Vb.Net.

Difference between Stored Procedure and Function

How to create Stored Procedure?

We think you may know about the structure of Asp.Net core and entity framework core so let's start with how to create a stored procedure. Syntax of create stored procedure is below:

Syntax:

CREATE PROCEDURE ProcedureName (Declare Filed What you want like @Column name Data type)
                      AS
                      BEGIN
                      Write SQL query 
                     END

As u see above syntax of creating a stored procedure is write in SQL to createand you can also edit stored procedure by just writing ALTER replace to CREATEand also it allows to declare more fields by using the colon”,”. Below created a stored procedure for select employee from employee table:

Create a stored procedure for Get_Employee

CREATE PROCEDURE Get_Employee(@Name VARCHAR(100), 
                          @City VARCHAR(100))
                          AS
                          BEGIN
                          SELECT * FROM Employee WHERE @Id=id;
                          END

Above is create a stored procedure and its name is Get_Employee and some fields are declared and also write SQL syntax after BEGIN you can also retrieve data according to your requirement by using WHERE condition. You can ignore the typical syntax of creating Stored Procedure in PL/SQL you can see and observes which we are pass parameters Name and City and see what has to be done in a Stored Procedure when SELECT query returns same rows for passed id is same if it has no found any data then it shall be no return any rows from the database.

Stored Procedure is created now let's see how to call it in Core Application:

You can call a stored procedure when you want data and according to your application structure.

Calling a Stored Procedure that executes a query. In this case, we're invoking on the Employee entity and so we shall run it on the same.

var Id= new SqlParameter("@Id", Model.Id);
                      var users = context
                      .Employee
                      .FromSqlRaw("exec Get_Employee @Id,Id)
                      .ToList();

Entity Frame Work Core provides two methods for executing a stored procedure which is listed below:

This Query for records from a database - - FromSqlRaw()

Execute a command (typically Data Manipulation Command) on the database - ExecuteSqlRaw() or the awaitable ExecuteSqlRawAsync()

In our case we are executing the stored procedure for those queries which gives us results so we use the “FromSqlRaw()” method which is tack one parameter.

An SQL string:

In our case, it shall be the "exec Get_Employee” which is along with parameter, SQL statement to execute the Stored Procedure.

If you want to pass a series of the parameters with this statement you need to pass the object of it which type is SqlParameter() which do map between your parameter names which are defined in SQL.

Remark: You take care of your passing parameters name and its data types are the same as you write in SQL.