Stored procedure in ASP.NET Core


Isn't it fun,

to instantly listen to a blog on the go? PLAY !

 
 

Stored-procedure-in

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

Stored Procedure

Function

Stored Procedure is to return multiple values or zero to retrieve values returned we use an output parameter.

The function is return values which are scalar or table type.

Stored Procedure allows to pass two types of parameters one is for input parameter and the second one is for output parameter.

The function allows only one input parameter.

Stored Procedure allows maintaining a transaction

Function did not allow to maintain transaction.

We can only call a Stored Procedure in an execution statement Example EXEC statement, which means we can't call a Stored Procedure in a SELECT statement

Function is call in a SELECT/ WHERE/ HAVING section

In Stored Procedure, we can do exception handling by using tr and catch block.

In function, we can’t do exception handling.

We can call a Function in the Stored Procedure.

We can't call a Stored Procedure in the Function.

Stored Procedure allows to use INSERT, UPDATE, and DELETE statements in SP.

The Function is to allow only the SELECT statement.

How tocreate 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.


Looking to hire ASP.Net Development Company in USA ?

Your Search ends here.


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.

Conclusion

We hope after reading this blog you must know about what is stored procedure and why we use it and how can you create your custom stored procedure and how to call it in ASP.Net core with Core Entity Frame Work using Stored procedure. We can save our time and make the application more efficient and write less code by storing reusable code in the database and call it when we need it.

Stored-procedure-in

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

Stored Procedure

Function

Stored Procedure is to return multiple values or zero to retrieve values returned we use an output parameter.

The function is return values which are scalar or table type.

Stored Procedure allows to pass two types of parameters one is for input parameter and the second one is for output parameter.

The function allows only one input parameter.

Stored Procedure allows maintaining a transaction

Function did not allow to maintain transaction.

We can only call a Stored Procedure in an execution statement Example EXEC statement, which means we can't call a Stored Procedure in a SELECT statement

Function is call in a SELECT/ WHERE/ HAVING section

In Stored Procedure, we can do exception handling by using tr and catch block.

In function, we can’t do exception handling.

We can call a Function in the Stored Procedure.

We can't call a Stored Procedure in the Function.

Stored Procedure allows to use INSERT, UPDATE, and DELETE statements in SP.

The Function is to allow only the SELECT statement.

How tocreate 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.


Looking to hire ASP.Net Development Company in USA ?

Your Search ends here.


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.

Conclusion

We hope after reading this blog you must know about what is stored procedure and why we use it and how can you create your custom stored procedure and how to call it in ASP.Net core with Core Entity Frame Work using Stored procedure. We can save our time and make the application more efficient and write less code by storing reusable code in the database and call it when we need it.