Introduction to Entity Framework Stored Procedure

Entity Framework Stored Procedure performs the pre-defined logic on the database. Therefore, most organizations must use the necessary stored procedures regarding policies. It also denotes that the Entity Framework must use the Stored Procedures to use Inserting, Deleting, and Update entities.

Overview of Entity Framework Stored Procedure

Entity Framework can automate the creation of commands for the database based on the Entity SQL queries or the LINQ-to-Entities, which create the commands for deleting, updating, and inserting the data. For overriding the steps to make use of the pre-defined stored procedures. Using the Stored Procedures, we can either retrieve the data or add, delete and update the records for multiple database tables. Entity Framework builds the functions instead of the entity in EDM for the stored procedure and User-Defined Function in the database.

Entity Framework enables the Stored Procedures in the Entity Data Model instead of the automated command creation.

• To use Stored Procedures to perform the logic of pre-defined tables of the database, the organizations have the policies that need Stored Procedures’ use.
• To denote the Entity Framework, use the stored procedures for deleting, inserting, or updating the entities.
• Dynamically created commands are inefficient and secure manner.
• Explicitly control what is to execute on the store and select to create the stored procedure.

Create a New Project Stored Procedure

Entity Framework can automate the creation of commands for the database based on the Entity SQL queries or the LINQ-to-Entities, which create the commands for deleting, updating, and inserting the data. The stored procedure helps in pre-defined logic on the tables of the database. Also, it denotes that the Entity Framework must use the Stored Procedures to use Inserting, Deleting, and Update entities.

Let’s create the new application project using Entity Framework with Stored Procedure as follows:

Initially, to create the procedure for that, we have to make a database (here, my database name is EmployeeDB); in that database, create a new table; here, I’m creating a new table with the name “EmployeeDetails” with the attributes of ID, name, address, age, salary, designation and department ID of the employee.

See below the table creation as follows:

Code:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[EmployeeDetails]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].EmployeeDetails(
EmpID [int] IDENTITY(1001,1) NOT NULL,
EmpName VARCHAR(50) NOT NULL,
EmpAddress VARCHAR(50) NOT NULL,
EmpAge INT NOT NULL,
EmpSalary VARCHAR(50) NOT NULL,
EmpDesig VARCHAR(50) NOT NULL,
Emp_DeptID INT Not NULL,
)
END
GO
--select * from EmployeeDetails

Once creating the table next to creating the Stored Procedure, here im creating the Procedure “GetEmployee.” This procedure retrieves the table records based on the Employee ID; let’s see the following procedure.

Code:

use EmployeeDB
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[GetEmployee]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[GetEmployee]
@EmpId int
AS
SELECT EmpID, EmpName, EmpAddress, EmpAge, EmpSalary, EmpDesig FROM dbo.EmployeeDetails
WHERE EmpId = @EmpId
'
END
GO

Next, create a new project for that. Go to File – New – Project and then select the Console Application as shown and give the suitable name to the project here. I have given “Console_EF_SProcedure.”

It was once creating the above things, next to adding a new item, Entity Data Model. Right-click on the Project in Solution Explorer and select Add – New Item; a new dialog appears from that. Select – ADO.NET Entity Data Model, give the appropriate name, and click Add.

Once adding the model in the following dialog box, we must choose the Model Content in that Entity Data Model Wizard. For example, select EF Designer from Database and then click Next.

In the following dialog, set your database connectivity, select your desired database from the dropdown, and click the Next button.

In the following dialog, choose your database objects like tables and create stored procedures; here, my table name is “EmployeeDetails,” and the Procedure name is “GetEmployee.” Select your desired database objects, and finally, click on Finish, as shown below.

I was once doing all for the Entity Data Model.

Next, select View – Other Windows – Entity Data Model Browser in the Project menu.

In the right-side explorer, you can see the Model Browser; right-click on your Stored Procedure under Function Imports and click on Edit, as shown in the image.

It will bring out the following dialog containing the name and returns.

From that, click on the radio button Entities, select the table’s name from the ComboBox as the Stored Procedure return type, and finally, click on OK.

In the above dialog, “EmployeeDetails” is the name of my table, and GetEmployee is the procedure we apply in the following program.

Let’s see the following program, which retrieves the details of a particular employee from the table EmployeeDetails by passing the ID of Employee as a parameter in the Stored Procedure GetEmployee.

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace Console_EF_SProcedure
{
class Program
{
static void Main(string[] args)
{
using (var context = new EmployeeDBEntities())
{
int EmpId = 1001;
var getDetails = context.GetEmployee(EmpId);
Console.WriteLine("Emp_ID Employee_Name Address Age Designation Salary Department ID\n");
Console.WriteLine("-----------------------------------------------------------------\n");
foreach (var res in getDetails)
{
// Console.WriteLine("Employee ID: {0}, Employee Name: {1}, Address: {2} ,Employee Age: {3}, Designation: {4}, Salary: {5}, Department ID:{6}",
// res.EmpId, res.EmpName, res.EmpAddress, res.EmpAge, res.EmpDesig, res.EmpSalary, res.Emp_DeptID);
Console.WriteLine(res.EmpId+ "\t"+res.EmpName +"\t"+res.EmpAddress+"\t\t"+ res.EmpAge +"\t"+ res.EmpDesig +"\t"+res.EmpSalary+"\t"+res.Emp_DeptID+"\n");
}
Console.WriteLine("\t---------------------------------------------------------------------------------------------\n");
Console.ReadKey();
}
}
}
}

Once the above coding is complied with and executed, we retrieve the following output as follows:

Output:

Conclusion

In this article, we have seen the “Entity Framework Stored Procedure” with how to create the procedures and how to implement them on the application, for example, using ASP.NET Console Application.

Recommended Articles

This is a guide to Entity Framework Stored Procedure. Here we discuss the introduction and create a new project stored procedure, respectively. You may also have a look at the following articles to learn more –

1. Entity Framework Core
2. ASP.NET Core Entity Framework
3. Entity Framework NuGet
4. Entity Framework Join

The post Entity Framework Stored Procedure appeared first on EDUCBA.