How to Call Stored Procedures with Entity Framework Core in .NET 8
Prerequisites:
- Knowledge of ASP.NET Core MVC.
- Knowledge of Dependency Injection Design Pattern.
- Knowledge of HTML.
- Knowledge of JavaScript.
- Knowledge of Bootstrap5.
- Knowledge of JQuery.
- Knowledge of C# Programming.
Download Now!
2) To connect the project with SQL server database, following list of packages are required to be installed using nuget package manager within visual studio in the below listed order i.e.
- Microsoft.EntityFrameworkCore.SqlServer
- Microsoft.EntityFrameworkCore.Tools
- Microsoft.EntityFrameworkCore.Design
You also need to install 'Newtonsoft.Json' package if it is not already installed in your project.
3) Build the project and make sure that there are no errors in the build at this stage.
4) Now, next step is to design the database with the require tables and store procedures I have taken sample products list data from Adventure Works and have created my designated store procedures to get product details and products list.
5) After, creating require store procedures within the database, its time to connect the SQL server database with asp.net8 core mvc project using entity framework core database first approach. To do this as per asp.net core rules, a DbContext class needs to be created, which will be the data access layer for the application. To generate the DbContext file according to the targeted SQL server database, open the Tools -> NuGet Package Manager -> Package Manager Console as shown below i.e.
6) Now, I will use a "Scaffold-DbContext" command to generate the require DbContext file. There are two options in which you can use the Scaffold-DbContext command to generate the require DbContext file i.e.
- Directly using SQL connection string within the command.
- Define SQL connection string within appsettings.json file.
1. Directly using SQL connection string within the command.
In first option, type the below command inside Package Manager Console window by directly using SQL connection string within Scaffold-DbContext command, make sure that you update the below command with your SQL server credentials i.e.
Scaffold-DbContext "Server=SQL_SERVER_NAME;Database=SQL_DATABASE_NAME;user id=SQL_USERNAME;password=SQL_PASSWORD;Trusted_Connection=True;TrustServerCertificate=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models/DB
2. Define SQL connection string within appsettings.json file.
For second option, first define the SQL server connection string within "appsettings.json" file and then type the below Scaffold-DbContext command inside Package Manager Console window to generate the DbContext file. Make sure
that you have updated the SQL server connection string within the appsettings.json file with your SQL server credentials and update the below command connection string name with your appsettings.json file i.e.
Scaffold-DbContext "Name=ConnectionStrings:YOUR_CONNECTION_STRING_NAME" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models/DB
7) The targeted DbContext file has been generated as shown below.
8) Next step is to create the targeted store procedures within DbContext file and also create the targeted custom object mapper classes for these store procedures inside Models/DB folder. You need to make sure that the custom
object mapper properties that you are creating must exactly match the
fields defined in the database store procedure along with the data type.
If in case you are generating an integer value dynamically which is not
part of the table then its type must be set to long instead of integer
within your object mapper property. I have created two store procedures along with their custom object mappers. One store procedure will display the list of all products whose price is greater than 1,000 and second store procedure will get product details using product ID.
9) My two custom object mappers names are "SpGetProductByID" & "SpGetProductByPriceGreaterThan1000". So, open the DbContext file and add below lines of code at the end of the "protected override void OnModelCreating(ModelBuilder modelBuilder){...} " method in order to register these store procedures custom object mappers with the DbContext i.e.
...
// Regster store procedure custom objects.
modelBuilder.Entity<SpGetProductByID>().HasNoKey();
modelBuilder.Entity<SpGetProductByPriceGreaterThan1000>().HasNoKey();
...
10) Now, Implement the designated store procedures method within the DbContext file. For example, below is the implementation of my targeted "GetProductByPriceGreaterThan1000Async" store procedure i.e.
... public async Task<List<SpGetProductByPriceGreaterThan1000>> GetProductByPriceGreaterThan1000Async() { // Initialization. List<SpGetProductByPriceGreaterThan1000> lst = new List<SpGetProductByPriceGreaterThan1000>(); // Processing. string sqlQuery = "EXEC [dbo].[GetProductByPriceGreaterThan1000]"; lst = await this.Set<SpGetProductByPriceGreaterThan1000>().FromSqlRaw(sqlQuery).ToListAsync(); // Info. return lst; } ...
11) Now, next step is to register the created DbContext file as service within asp.net core platform, so, that by using the dependency injection design pattern the database layer can be accessed within the controllers. To do this open "Program.cs" file and paste below line of code to register the generated DbContext as service i.e.
... // Register SQL database configuration context as services. builder.Services.AddDbContext<DbCoreSpCallContext>(options => options.UseSqlServer(builder.Configuration.GetConnectionString("YOUR_CONNECTION_STRING_NAME"))); ...
In the above command, make sure to use the connection string name that you have defined within appsettings.json file.
12) Now, for accessing the database layer services with the help of dependency injection design pattern, first create a read only private property of the DbContext database type and then create the overload constructor with DbContext database as constructor's passing parameter within "Controllers -> HomeController.cs" file as shown in the below lines of code i.e.
... private readonly DbCoreSpCallContext databaseManager; ... public HomeController(DbCoreSpCallContext databaseManagerContext) { ... // Settings. this.databaseManager = databaseManagerContext; ... } ...
13) Create a suitable default layout for your project and include
relevant JavaScripts and CSS styles libraries references if applicable.
14) Next, I have created an index page which will display a form to get product details by providing a product ID, a complete details of the product and a list view of all the products whose prices are higher than 1,000.
15) Now, when you execute project, you will be able to view the data from SQL server database via store procedure calls and you can also get the details of the product by providing a product ID as shown below i.e.
Conclusion
In this article, you will learn to to call a SQL server database store procedure using entity framework core from within ASP.NET 8 Core MVC platform web application. You will learn two options to auto generate the DbContext file using the "Scaffold-DbContext" command and appsettings.js file. You will learn to register store procedure custom object mappers within the DbContext file. You will also learn to create store procedure call method within the DbContext file. Finally, you will learn to register the DbContext object as ASP.NET core service in order to access the database layer by adding DbContect object as a parameter of the overloaded constructor of the target controller file with the help of dependency injection design pattern.
Video Demo
Post a Comment