Last week while conducting a .NET Core Full Stack training, my client asked me to explain the mechanism of accessing SQL Server Stored Procedures using Entity Framework (EF) Core. They were creating an ASP.NET Core 2.2 WEB API app.
I have explained the entire process with examples and demonstrations. My attendees even requested me to show an example to access a stored procedure that accepts a User Defined Table (UDT) Type as input parameter.
I have explained the entire process with examples and demonstrations. My attendees even requested me to show an example to access a stored procedure that accepts a User Defined Table (UDT) Type as input parameter.
Entity Framework Core (EF Core) is an ORM for building the data access layer of modern applications.
EF Core provides Database First and Code First approaches to generate entity classes from database and generate Db Tables from entity classes respectively. The object model of EF Core provides methods to connect to a relational database and perform database transactions.
In most of the cases, we use Entity classes to perform all CRUD operations with the database. In this case, we will write all the logic for database operations in the .NET application by creating various repositories.
But what if that the database logic is already written in various Stored Procedures?
Stored Procedures are compiled and executed on Database Engine and hence result in better performance. So here the question is how can we use EF Core for performing database operations by making call to stored procedures? How can we work with Database-First approach and use Stored Procedures?
EF Core provides Database First and Code First approaches to generate entity classes from database and generate Db Tables from entity classes respectively. The object model of EF Core provides methods to connect to a relational database and perform database transactions.
In most of the cases, we use Entity classes to perform all CRUD operations with the database. In this case, we will write all the logic for database operations in the .NET application by creating various repositories.
But what if that the database logic is already written in various Stored Procedures?
Stored Procedures are compiled and executed on Database Engine and hence result in better performance. So here the question is how can we use EF Core for performing database operations by making call to stored procedures? How can we work with Database-First approach and use Stored Procedures?
Thankfully, EF Core already has support for accessing Stored Procedures and passing Input and Output parameters, but what if that the Stored Procedure has an User Defined Table Type as input parameter. User Defined Table Types can be used as a parameter to Stored Procedures and User-Defined functions to pass Tabular data to them. Hence it provides re-usability of data that is to be passed to Stored Procedures. Unlike tables, we cannot map with the User-Defined Table Types using EF Core and scaffold it. So if we have Database Stored Procedures with User-Defined Table Types as parameters, then its a bit tricky to access them using EF Core and execute them.
In this article, we will use a SQL Server Database. We will create database, tables, stored procedures and user-defined table types. We will create ASP.NET Core 2.2 WEB API application with EF Core and then access stored procedures and pass parameters to it.
We will implement the application as shown in the following figure:
We will implement the application as shown in the following figure:
Figure 1: The application structure
Step 1: Open SQL Server management Studio (SSMS), log-in with your credentials (Windows Authentication or SQL Server Authentication). If you are using SQL Server Authentication then make sure that you have rights to create database, tables, stored procedures, etc.
Run the following command in the Query Window:
CREATE DATABASE Application
Run the following commands to create ProductMaster and Person Tables
CREATE TABLE [dbo].[ProductMaster](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ProductId] [varchar](10) NOT NULL,
[ProductName] [varchar](50) NOT NULL,
[CategoryName] [varchar](50) NOT NULL,
[Price] [int] NOT NULL,
CONSTRAINT [PK_ProductMaster] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Person](
[Id] [int] IDENTITY(1,1) NOT NULL,
[PersonId] [varchar](10) NOT NULL,
[PersonName] [varchar](100) NOT NULL,
[ResidenceNo] [varchar](10) NOT NULL,
[ResidenceName] [varchar](30) NOT NULL,
[Street] [varchar](50) NOT NULL,
[City] [varchar](50) NOT NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Listing 1: Creating tables
Lets create stored procedures to read all the products from ProductMaster table and insert a new product into the same table:
Lets create stored procedures to read all the products from ProductMaster table and insert a new product into the same table:
CREATE PROCEDURE [dbo].[spGetProducts]
AS
BEGIN
SET NOCOUNT ON;
select * from ProductMaster
END
GO
CREATE PROCEDURE [dbo].[spInsertProduct]
@Id int OUT,
@ProductId varchar(10),
@ProductName varchar(50),
@CategoryName varchar(50),
@Price int
AS
BEGIN
SET NOCOUNT ON;
Insert into ProductMaster values (@ProductId,@ProductName,@CategoryName,@Price)
SET @Id = SCOPE_IDENTITY()
END
GO
Listing 2: Creating Stored Procedures
Lets add a new User-Defined Table type. This will have the same columns as that of the Person table.
CREATE TYPE [dbo].[PersonTableType] AS TABLE(
[PersonId] [varchar](10) NULL,
[PersonName] [varchar](100) NULL,
[ResidenceNo] [varchar](10) NULL,
[ResidenceName] [varchar](30) NULL,
[Street] [varchar](50) NULL,
[City] [varchar](50) NULL
)
GO
Listing 3: Creating User-Defined Table Type
Listing 3: Creating User-Defined Table Type
Now we will create a stored procedure to insert a new Person record into the table as shown in the following listing:
Create Procedure [dbo].[spInsertPerson]
@PersonAddress PersonTableType READONLY,
@Id int OUT
As
Begin
Insert into [dbo].[Person]
select * from @PersonAddress
SET @Id = SCOPE_IDENTITY()
End
GO
Listing 4: Stored Procedure with User-Define Table Type as Input Parameter
Listing 4: Stored Procedure with User-Define Table Type as Input Parameter
Step 2: Open Visual Studio 2017 or 2019 and create a new ASP.NET Core 2.2 project. Name this project as StoredProcEFCore. Select the API project. The first step in the project is that we need to scaffold Model classes from the database using Database First approach. Open the Command prompt and navigate to the folder where this project is created. Run the following command from the command prompt:
dotnet ef dbcontext scaffold "Data Source=.;Initial Catalog=Application;Integrated Security=SSPI" Microsoft.EntityFrameworkCore.SqlServer -o Models
This command adds a Models folder into the project and adds ApplicationContext, Person and ProductMaster classes in it. Note that, the ApplicationContext.cs class file contains the Database connection string in it. As a recommended practice, we need to have the Database connection string in the appSettings.json file.
Open the ApplicatoinContext.cs file and comment the code inside the OnConfiguring() method. Modify the appSettings.json by adding the Connection string as shown in the following listing:
dotnet ef dbcontext scaffold "Data Source=.;Initial Catalog=Application;Integrated Security=SSPI" Microsoft.EntityFrameworkCore.SqlServer -o Models
This command adds a Models folder into the project and adds ApplicationContext, Person and ProductMaster classes in it. Note that, the ApplicationContext.cs class file contains the Database connection string in it. As a recommended practice, we need to have the Database connection string in the appSettings.json file.
Open the ApplicatoinContext.cs file and comment the code inside the OnConfiguring() method. Modify the appSettings.json by adding the Connection string as shown in the following listing:
"ConnectionStrings": {
"AppConnStr": "Data Source=.;Initial Catalog=Application;
Integrated Security=SSPI"
}
Listing 5: Connection String in appsettings.json
Step 3: Since the Microsoft.EntityFrameworkCore package does not have any default support for working with User-Defined Table type, we need to use the EntityFrameworkExtras.EF7 package in the project. This package provides some extra features such as executing Stored Procedures with User-Defined Table Types as Input and Output Parameters. Figure 2 shows the NuGet Package reference for the EntityFrameworkExtras.EF7
Step 3: Since the Microsoft.EntityFrameworkCore package does not have any default support for working with User-Defined Table type, we need to use the EntityFrameworkExtras.EF7 package in the project. This package provides some extra features such as executing Stored Procedures with User-Defined Table Types as Input and Output Parameters. Figure 2 shows the NuGet Package reference for the EntityFrameworkExtras.EF7
Figure 2: Adding NuGet Package for EntityFrameworkExtras.EF7
Step 7: In the project, add a new folder and name it as Services. In this folder add a new class file and name it as ProductMasterService.cs. Add the code in this file as shown in the following listing
The above code contains The IProductMasterService interface. This interface contains asynchronous methods for reading all products (GetAsync()) and creating a new product (CreateAsync()). This interface is implemented by the ProductMasterService class.
The GetAsync() method uses FromSql() method that accepts spGetProducts stored procedure as an input parameter. This stored procedure returns all products from the ProductMaster table from the database. The FromSql() method returns List of data returned from the stored procedure. The CreateAsync() method accepts ProductMaster as input parameter. This method declares SqlParameters using the SqlParameter class.
These parameters will be passed as object array to the ExecuteSqlCommandAsync() method. The ExecuteSqlCommandAsync() method accepts parameters as the spInsertProduct stored procedure and its input and output parameters list. In our case @Id is an output parameter. When ExecuteSqlCommandAsync() completes its execution the @Id will contain the return value from the stored procedure.
Step 9: In the Services folder, add a new class file of name PersonService.cs. Add the code in this file as shown in the following listing:
The PersonAppService class implements the IPersonAppService interface. This interface declares the InsertPerson() method having ClsPersonTableType as input parameter.
In the implementation of the InsertPerson() method, an instance of the InsertPersonStoredProcedure class is declared. This class is mapped with the spInsertPerson stored procedure created in the Listing 4. An instance of the InsertPersonStoredProcedure class initialize Id property (this is output parameter) and PersonAddress as List of ClsPersonTableType. This is the mapped parameter to the User-Defined Table type to the stored procedure.
The ExecuteStoredProcedure() is an extension method provided by EntityFrameworkExtras.EF7 to access and execute stored procedure that is having User-Defined Table Type as input parameter. After the execution, we will receive the return value using Id output property.
Step 10: In the ConfigureServices() method of the Startup.cs, add the following code for registering ApplicationContext and Service classes in dependency injection container as shown in the following listing:
Listing 10: The PersonAppService class
Step 11: In the Controllers folder, add an Empty API controller and name it as ProductController.cs. This controller will use IProductMaster service as dependency to access method to Get all products and create a new product as shown in the following listing
Listing 11: The ProductController class
Step 4: Since the spInsertPerson stored procedure accepts User-Define Table type, we need to add a new class in Models folder that maps with the PersonTableType. In the Model folder, add a new class file and name it as ClsPersonTableType.cs add the code from the following listing in it:
using EntityFrameworkExtras.EF7;
namespace StoredProcEFCore.Models
{
[UserDefinedTableType("PersonTableType")]
public class ClsPersonTableType
{
[UserDefinedTableTypeColumn(1)]
public string PersonId { get; set; }
[UserDefinedTableTypeColumn(2)]
public string PersonName { get; set; }
[UserDefinedTableTypeColumn(3)]
public string ResidenceNo { get; set; }
[UserDefinedTableTypeColumn(4)]
public string ResidenceName { get; set; }
[UserDefinedTableTypeColumn(5)]
public string Street { get; set; }
[UserDefinedTableTypeColumn(6)]
public string City { get; set; }
}
}Listing 6: PersonTable type mapping class
The above class contains UserDefinedTableType attribute applied on it. This attribute class is provided in the EntityFrameworkExtras.EF7 namespace. The PersonTableType value is passed to the attribute. This means that the class is mapped to the table type. The class contains properties mapped with the columns from the PersonTableType. This mapping is defined using UserDefinedTableTypeColumn attribute. This class will be used as the stored procedure parameter while we will be making call to it using EF Core ApplicaitonContext class.
Step 6: The EntityFrameworkExtras.EF7 provides StoredProcedureAttribute and StoredProcedureParameter classes. These are used to define class mapping with stored procedure and its parameters respectively. With the help of these classes, we can manage to define a CLR class with properties mapped with Stored Procedure and its parameters. In the Models folder, add a new class file and name it as InsertPersonStoredProcedure.cs. Add the code in this file as shown in the following listing:
using EntityFrameworkExtras.EF7;
using System.Collections.Generic;
namespace StoredProcEFCore.Models
{
[StoredProcedure("spInsertPerson")]
public class InsertPersonStoredProcedure
{
[StoredProcedureParameter(System.Data.SqlDbType.Int, ParameterName = "Id",
Direction = System.Data.ParameterDirection.Output)]
public int Id { get; set; }
[StoredProcedureParameter(System.Data.SqlDbType.Structured,
ParameterName ="PersonAddress")]
public List PersonAddress { get; set; }
}
}
Listing 7: Class that maps with the Stored Procedure
Its mandatory that you must define the StoredProcedureParameter as IEnumerable type that is mapped with the User-Defined Table type. In our case, the PersonAddress is the parameter mapped with the User-Defined table type that's the reason we are defining it as List.Step 7: In the project, add a new folder and name it as Services. In this folder add a new class file and name it as ProductMasterService.cs. Add the code in this file as shown in the following listing
using Microsoft.EntityFrameworkCore;
using StoredProcEFCore.Models;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Threading.Tasks;
namespace StoredProcEFCore.Services
{
public interface IProductMasterService
{
Task CreateAsync(ProductMaster prd);
Task> GetAsync();
}
public class ProductMasterService : IProductMasterService
{
private readonly ApplicationContext context;
public ProductMasterService(ApplicationContext context)
{
this.context = context;
}
public async Task CreateAsync(ProductMaster prd)
{
SqlParameter pId = new SqlParameter();
pId.ParameterName = "@Id";
pId.SqlDbType = System.Data.SqlDbType.Int;
pId.Direction = System.Data.ParameterDirection.Output;
SqlParameter pPrdId = new SqlParameter();
pPrdId.ParameterName = "@ProductId";
pPrdId.SqlDbType = System.Data.SqlDbType.Text;
pPrdId.Size = 10;
pPrdId.Value = prd.ProductId;
pPrdId.Direction = System.Data.ParameterDirection.Input;
SqlParameter pPrdName = new SqlParameter();
pPrdName.ParameterName = "@ProductName";
pPrdName.SqlDbType = System.Data.SqlDbType.Text;
pPrdName.Size = 50;
pPrdName.Value = prd.ProductName;
pPrdName.Direction = System.Data.ParameterDirection.Input;
SqlParameter pCatName = new SqlParameter();
pCatName.ParameterName = "@CategoryName";
pCatName.SqlDbType = System.Data.SqlDbType.Text;
pCatName.Size = 50;
pCatName.Value = prd.CategoryName;
pCatName.Direction = System.Data.ParameterDirection.Input;
SqlParameter pPrice = new SqlParameter();
pPrice.ParameterName = "@Price";
pPrice.SqlDbType = System.Data.SqlDbType.Int;
pPrice.Value = prd.Price;
pPrice.Direction = System.Data.ParameterDirection.Input;
var result = await context.Database.ExecuteSqlCommandAsync(
"spInsertProduct @Id OUT, @ProductId, @ProductName,
@CategoryName, @Price", new object[] {
pId, pPrdId,pPrdName,pCatName,pPrice });
var res = pId.Value;
return (int)res;
}
public async Task> GetAsync()
{
try
{
var result = await context.ProductMaster.FromSql("spGetProducts")
.ToListAsync();
return result;
}
catch (Exception ex)
{
throw ex;
}
}
}
}
Listing 8: The ProductMasterService classThe above code contains The IProductMasterService interface. This interface contains asynchronous methods for reading all products (GetAsync()) and creating a new product (CreateAsync()). This interface is implemented by the ProductMasterService class.
The GetAsync() method uses FromSql() method that accepts spGetProducts stored procedure as an input parameter. This stored procedure returns all products from the ProductMaster table from the database. The FromSql() method returns List of data returned from the stored procedure. The CreateAsync() method accepts ProductMaster as input parameter. This method declares SqlParameters using the SqlParameter class.
These parameters will be passed as object array to the ExecuteSqlCommandAsync() method. The ExecuteSqlCommandAsync() method accepts parameters as the spInsertProduct stored procedure and its input and output parameters list. In our case @Id is an output parameter. When ExecuteSqlCommandAsync() completes its execution the @Id will contain the return value from the stored procedure.
Step 9: In the Services folder, add a new class file of name PersonService.cs. Add the code in this file as shown in the following listing:
using EntityFrameworkExtras.EF7;
using StoredProcEFCore.Models;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
namespace StoredProcEFCore.Services
{
public interface IPersonAppService
{
int InsertPerson(ClsPersonTableType person);
}
public class PersonAppService : IPersonAppService
{
private readonly ApplicationContext context;
public PersonAppService(ApplicationContext context)
{
this.context = context;
}
public int InsertPerson(ClsPersonTableType person)
{
try
{
SqlParameter pId = new SqlParameter();
pId.ParameterName = "@Id";
pId.SqlDbType = System.Data.SqlDbType.Int;
pId.Direction = System.Data.ParameterDirection.Output;
var proc = new InsertPersonStoredProcedure()
{
Id = 0,
PersonAddress = new List() { person }
};
var res = context.Database.ExecuteStoredProcedure(proc);
var resultId = proc.Id;
return resultId;
}
catch (Exception ex)
{
throw ex;
}
}
}
}
Listing 9: The PersonAppService classThe PersonAppService class implements the IPersonAppService interface. This interface declares the InsertPerson() method having ClsPersonTableType as input parameter.
In the implementation of the InsertPerson() method, an instance of the InsertPersonStoredProcedure class is declared. This class is mapped with the spInsertPerson stored procedure created in the Listing 4. An instance of the InsertPersonStoredProcedure class initialize Id property (this is output parameter) and PersonAddress as List of ClsPersonTableType. This is the mapped parameter to the User-Defined Table type to the stored procedure.
The ExecuteStoredProcedure() is an extension method provided by EntityFrameworkExtras.EF7 to access and execute stored procedure that is having User-Defined Table Type as input parameter. After the execution, we will receive the return value using Id output property.
Step 10: In the ConfigureServices() method of the Startup.cs, add the following code for registering ApplicationContext and Service classes in dependency injection container as shown in the following listing:
public void ConfigureServices(IServiceCollection services)
{
services.AddDbContext(options => {
options.UseSqlServer(Configuration.GetConnectionString("AppConnStr"));
});
services.AddScoped();
services.AddScoped();
services.AddMvc()
.AddJsonOptions(options =>
options.SerializerSettings.ContractResolver
= new DefaultContractResolver()
)
.SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
}
Listing 10: The PersonAppService class
Step 11: In the Controllers folder, add an Empty API controller and name it as ProductController.cs. This controller will use IProductMaster service as dependency to access method to Get all products and create a new product as shown in the following listing
[Route("api/[controller]")]
[ApiController]
public class ProductController : ControllerBase
{
private readonly IProductMasterService serv;
public ProductController(IProductMasterService serv)
{
this.serv = serv;
}
[HttpGet]
public IActionResult Get()
{
var res = serv.GetAsync().Result;
return Ok(res);
}
[HttpPost]
public IActionResult Post(ProductMaster product)
{
try
{
var res = serv.CreateAsync(product).Result;
return Ok(res);
}
catch (Exception ex)
{
return BadRequest(ex.Message);
}
}
}
Listing 11: The ProductController class
Add one more empty API and name it as PersonContoller with the following code:
So now we have the APIs ready. We can test these APIs using Fildder or Postman
Run the application and enter the following URL in Postman for making a POST request.
http://localhost:20088/api/Product
Enter the following data in the request body
Make the post request. Open the table in the database, you will see a new Product record created in the table. You can test it using Get request for the same URL.
To test the Person API enter the following URL in Postman.
http://localhost:20088/api/Person
Make the post request with the following data:
Open the table in the database, you will see a new Person record is added in to the table.
The person record is created using the stored procedure that is accepting User-Defined Table Type as input parameter. We can implement it using EntityFrameworkExtras.EF 7 package.
Conclusion:
As we saw, using EF Core we can effectively work with Stored Procedures using Database First approach.
[Route("api/[controller]")]
[ApiController]
public class PersonController : ControllerBase
{
private readonly IPersonAppService service;
public PersonController(IPersonAppService service)
{
this.service = service;
}
[HttpPost]
public IActionResult Post(ClsPersonTableType person)
{
try
{
var res = service.InsertPerson(person);
return Ok(res);
}
catch (Exception ex)
{
return BadRequest(ex.Message);
}
}
}
Listing 12: The PersonController classSo now we have the APIs ready. We can test these APIs using Fildder or Postman
Run the application and enter the following URL in Postman for making a POST request.
http://localhost:20088/api/Product
Enter the following data in the request body
{
"ProductId": "Prd0001",
"ProductName": "Laptop",
"CategoryName": "Electronice",
"Price": 200000
}
Make the post request. Open the table in the database, you will see a new Product record created in the table. You can test it using Get request for the same URL.
To test the Person API enter the following URL in Postman.
http://localhost:20088/api/Person
Make the post request with the following data:
{
"PersonId":"Per001",
"PersonName":"Mahesh",
"ResidenceNo":"HN-99",
"ResidenceName":"SINH RD",
"Street":"76 plt",
"City":"PNQ"
}
Open the table in the database, you will see a new Person record is added in to the table.
The person record is created using the stored procedure that is accepting User-Defined Table Type as input parameter. We can implement it using EntityFrameworkExtras.EF 7 package.
Conclusion:
As we saw, using EF Core we can effectively work with Stored Procedures using Database First approach.
Tweet
About The Author
No comments:
Post a Comment