Learn About Stored Procedure Scaffolding Utillity For .NET Core 3

Introduction 

 
In enterprise applications, especially in ERP / CRM, all business logic is often demanded to Stored Procedures.
 
Stored procedures are a powerful tool for developers whose know T-SQL to elaborate complex query or to implement core business logic with an eye on database performance.
 
Unluckily, in the new .NET Core 3, stored procedure support is not complete.
  1. You have to use FromSqlRaw ( or Query ) and compose your own SQL Command
  2. The resulting structure of the Stored Procedure must have an Entity Structure
  3. There is not a parameter mapping so each time you modify a stored procedure by adding a new parameter you have to manually fix the SQL Command
  4. There is not a scaffolding function that imports all the stored procedure
(Please refer to this article if you want to go deep on how to call stored procedure)
 
In today's article, I will show you how SPToCore utility can help you to manage a lot of stored procedures.
 

What is SPToCore ???

 
SPToCore is a utility that scaffolds all database stored procedures and creates a new dbContext that implements stored procedure methods, results, and parameter mappings.
 
You can find it on GitHub.
 
Step 1
 
First, create your existing SQL server database named "DB_WITH_SP" by executing the following SQL script:
  1. USE [DB_WITH_SP]  
  2. GO  
  3. /****** Object:  Table [dbo].[anMaterial]    Script Date: 12/05/2020 16:57:53 ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. CREATE TABLE [dbo].[anMaterial](  
  9.     [idMaterial] [int] IDENTITY(1,1) NOT NULL,  
  10.     [Code] [varchar](50) NULL,  
  11.     [Material] [varchar](255) NULL,  
  12.     [Cost] [decimal](12, 2) NULL,  
  13.     [idMaterialType] [intNULL,  
  14.  CONSTRAINT [PK_anMaterial] PRIMARY KEY CLUSTERED   
  15. (  
  16.     [idMaterial] ASC  
  17. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  18. ON [PRIMARY]  
  19. GO  
  20. /****** Object:  Table [dbo].[mdMaterialType]    Script Date: 12/05/2020 16:57:53 ******/  
  21. SET ANSI_NULLS ON  
  22. GO  
  23. SET QUOTED_IDENTIFIER ON  
  24. GO  
  25. CREATE TABLE [dbo].[mdMaterialType](  
  26.     [idMaterialType] [intNOT NULL,  
  27.     [Code] [varchar](50) NULL,  
  28.     [MaterialType] [varchar](255) NULL,  
  29.  CONSTRAINT [PK_mdMaterialType] PRIMARY KEY CLUSTERED   
  30. (  
  31.     [idMaterialType] ASC  
  32. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  33. ON [PRIMARY]  
  34. GO  
  35. /****** Object:  View [dbo].[vw_anMaterial]    Script Date: 12/05/2020 16:57:53 ******/  
  36. SET ANSI_NULLS ON  
  37. GO  
  38. SET QUOTED_IDENTIFIER ON  
  39. GO  
  40.   
  41.   
  42. CREATE VIEW [dbo].[vw_anMaterial]  
  43. AS  
  44.   
  45. SELECT m.idMaterial,  
  46.        m.Code,  
  47.        m.Material,  
  48.        m.Cost,  
  49.        m.idMaterialType,  
  50.        MaterialTypeCode = t.Code,  
  51.        t.MaterialType  
  52. FROM anMaterial m LEFT JOIN mdMaterialType t ON t.idMaterialType = m.idMaterialType  
  53. GO  
  54. SET IDENTITY_INSERT [dbo].[anMaterial] ON   
  55. GO  
  56. INSERT [dbo].[anMaterial] ([idMaterial], [Code], [Material], [Cost], [idMaterialType]) VALUES (1, N'MAT001', N'Cement'CAST(1000.00 AS Decimal(12, 2)), 1)  
  57. GO  
  58. INSERT [dbo].[anMaterial] ([idMaterial], [Code], [Material], [Cost], [idMaterialType]) VALUES (2, N'MAT002', N'Raw Meal'CAST(500.00 AS Decimal(12, 2)), 3)  
  59. GO  
  60. INSERT [dbo].[anMaterial] ([idMaterial], [Code], [Material], [Cost], [idMaterialType]) VALUES (3, N'MAT003', N'Clinker'CAST(750.00 AS Decimal(12, 2)), 2)  
  61. GO  
  62. INSERT [dbo].[anMaterial] ([idMaterial], [Code], [Material], [Cost], [idMaterialType]) VALUES (4, N'MAT004', N'Liquid Additive'CAST(50.00 AS Decimal(12, 2)), 3)  
  63. GO  
  64. INSERT [dbo].[anMaterial] ([idMaterial], [Code], [Material], [Cost], [idMaterialType]) VALUES (5, N'MAT005', N'Intermediate Binder'CAST(350.00 AS Decimal(12, 2)), 2)  
  65. GO  
  66. INSERT [dbo].[anMaterial] ([idMaterial], [Code], [Material], [Cost], [idMaterialType]) VALUES (6, N'MAT005', N'Clay'CAST(200.00 AS Decimal(12, 2)), 3)  
  67. GO  
  68. INSERT [dbo].[anMaterial] ([idMaterial], [Code], [Material], [Cost], [idMaterialType]) VALUES (7, N'MAT007', N'C4'CAST(100.00 AS Decimal(12, 2)), 3)  
  69. GO  
  70. SET IDENTITY_INSERT [dbo].[anMaterial] OFF  
  71. GO  
  72. INSERT [dbo].[mdMaterialType] ([idMaterialType], [Code], [MaterialType]) VALUES (1, N'FIN_PRO', N'Finish Product')  
  73. GO  
  74. INSERT [dbo].[mdMaterialType] ([idMaterialType], [Code], [MaterialType]) VALUES (2, N'INT_PRO', N'Intermediate Product')  
  75. GO  
  76. INSERT [dbo].[mdMaterialType] ([idMaterialType], [Code], [MaterialType]) VALUES (3, N'RAW_MAT', N'Raw Material')  
  77. GO  
  78. /****** Object:  StoredProcedure [dbo].[Material_GetByCode]    Script Date: 12/05/2020 16:57:53 ******/  
  79. SET ANSI_NULLS ON  
  80. GO  
  81. SET QUOTED_IDENTIFIER ON  
  82. GO  
  83.   
  84.   
  85. CREATE PROC [dbo].[Material_GetByCode](  
  86.     @Code VARCHAR(50)  
  87. )  
  88. AS  
  89.   
  90. SELECT m.idMaterial,  
  91.        m.Code,  
  92.        m.Material,  
  93.        m.Cost,  
  94.        m.idMaterialType,  
  95.        m.MaterialTypeCode,  
  96.        m.MaterialType  
  97. FROM vw_anMaterial m WHERE m.Code = @Code  
  98. GO  
  99. /****** Object:  StoredProcedure [dbo].[Material_GetById]    Script Date: 12/05/2020 16:57:53 ******/  
  100. SET ANSI_NULLS ON  
  101. GO  
  102. SET QUOTED_IDENTIFIER ON  
  103. GO  
  104.   
  105.   
  106. CREATE PROC [dbo].[Material_GetById](  
  107.     @idMaterial INT  
  108. )  
  109. AS  
  110.   
  111. SELECT m.idMaterial,  
  112.        m.Code,  
  113.        m.Material,  
  114.        m.Cost,  
  115.        m.idMaterialType,  
  116.        m.MaterialTypeCode,  
  117.        m.MaterialType  
  118. FROM vw_anMaterial m WHERE m.idMaterial = @idMaterial  
  119. GO  
  120. /****** Object:  StoredProcedure [dbo].[Material_NEW]    Script Date: 12/05/2020 16:57:53 ******/  
  121. SET ANSI_NULLS ON  
  122. GO  
  123. SET QUOTED_IDENTIFIER ON  
  124. GO  
  125.   
  126. CREATE PROC [dbo].[Material_NEW]  
  127. (  
  128.     @Code VARCHAR(50),  
  129.     @Material VARCHAR(255),  
  130.     @Cost DECIMAL(12,2) NULL,  
  131.     @idMaterialType INT,  
  132.     @idMaterial INT OUT  
  133. )  
  134. AS  
  135.   
  136. INSERT INTO anMaterial  
  137. (  
  138.     Code,  
  139.     Material,  
  140.     Cost,  
  141.     idMaterialType  
  142. )  
  143. VALUES ( @Code, @Material, @Cost, @idMaterialType )  
  144.   
  145. SET @idMaterial = @@IDENTITY  
  146. GO  
  147. /****** Object:  StoredProcedure [dbo].[Material_SEARCH]    Script Date: 12/05/2020 16:57:53 ******/  
  148. SET ANSI_NULLS ON  
  149. GO  
  150. SET QUOTED_IDENTIFIER ON  
  151. GO  
  152.   
  153. CREATE PROC [dbo].[Material_SEARCH]  
  154. (  
  155.     @Code VARCHAR(50) NULL,  
  156.     @Material VARCHAR(255) NULL,  
  157.     @idMaterialType INT NULL  
  158. )  
  159. AS  
  160.   
  161. SELECT m.idMaterial,  
  162.        m.Code,  
  163.        m.Material,  
  164.        m.Cost,  
  165.        m.idMaterialType,  
  166.        m.MaterialTypeCode,  
  167.        m.MaterialType  
  168. FROM vw_anMaterial m    
  169. WHERE m.Code = ISNULL(@Code,m.Code)  
  170. AND m.Material LIKE '%' + ISNULL(@Material,m.Material) + '%'  
  171. AND m.idMaterialType = ISNULL(@idMaterialType, m.idMaterialType)  
  172. GO  
  173. /****** Object:  StoredProcedure [dbo].[Material_UPD]    Script Date: 12/05/2020 16:57:53 ******/  
  174. SET ANSI_NULLS ON  
  175. GO  
  176. SET QUOTED_IDENTIFIER ON  
  177. GO  
  178.   
  179. CREATE PROC [dbo].[Material_UPD]  
  180. (  
  181.     @idMaterial INT,  
  182.     @Material VARCHAR(255),  
  183.     @Cost DECIMAL(12,2) NULL,  
  184.     @idMaterialType INT NULL  
  185. )  
  186. AS  
  187.   
  188. UPDATE anMaterial  
  189.     SET Material = @Material,  
  190.         Cost = @Cost,  
  191.         idMaterialType = @idMaterialType  
  192. WHERE idMaterial = @idMaterial  
  193. GO  
In the above script, I have created two simple tables ( anMaterial, mdMaterialType ), one view ( vw_anMaterial ) with five stored procedures:
  1. Material_NEW: create a new material
  2. Material_UPD: update an existing material 
  3. Material_GetById: get the material by his id
  4. Material_GetByCode: get the material by his code
  5. Material_SEARCH: search for material by his description ( Material column )
Step 2
 
Now, create a new .NET Core console application project and name it "myStoredProcedureCalls" as shown below:
 
Learn About Stored Procedure Scaffolding Utillity For .NET Core 3
 
Learn About Stored Procedure Scaffolding Utillity For .NET Core 3
 
Note
I will put the console application in "C:\Temp" folder, just to simplify the example.
 
Step 3
 
Build the solution and ensure that the build is successful.
 
Step 4
 
Add a new folder to the solution called "Model" and add all the libraries necessaries to scaffold the database:
  1. Right click on Solution --> Add new Folder ( call it "Model" )
  2. Open Package Manager console and run,

    Install-Package Microsoft.EntityFrameworkCore.Tools
    Install-Package Microsoft.EntityFrameworkCore.SqlServer


  3. Scaffold the DB_WITH_SP database by using the following command (review connectionstring!!!),

    Scaffold-DbContext "Data Source=DN1;Initial Catalog=DB_WITH_SP;Persist Security Info=True;User ID=sa;Password=secret" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Model
If everything is ok, your solution will look like this:
 
Learn About Stored Procedure Scaffolding Utillity For .NET Core 3
 
Step 4
 
Now it's time to implement the stored procedure methods by using the SPToCore utility!
 
First of all download SPToCore.exe from the link below (sorry for the file size, it's a single file compiled .NET Core application )
https://github.com/DarioN1/SPToCore/tree/master/SPToCore/dist
 
Then open a command prompt and run the following command from the SPToCore.exe folder.
 
SPToCore.exe scan -cnn "Data Source=DN1;Initial Catalog=DB_WITH_SP;Persist Security Info=True;User ID=sa;Password=password;" -sch * -nsp myStoredProcedureCalls -ctx DB_WITH_SPContext -sf Model -pf C:\Temp\myStoredProcedureCalls\myStoredProcedureCalls\Model\ -f SPToCoreContext.cs 
 
If everything is OK, you will see these console logs:
 
Learn About Stored Procedure Scaffolding Utillity For .NET Core 3
 
You will find a new file in the solution:
 
Learn About Stored Procedure Scaffolding Utillity For .NET Core 3
 
This SPToCoreContext.cs is a new dbContext that implements all the stored procedures!
 
Step 4
 
Its time to test the stored procedures.
 
Now open your Program.cs and add the following code and run the program.
  1. static void Main(string[] args)  
  2. {  
  3.     Console.WriteLine("Hello, I'm SPToCore!");  
  4.   
  5.     using (SPToCoreContext db = new SPToCoreContext()) {  
  6.   
  7.         var mat = db.Material_GetByIdAsync(1).Result.FirstOrDefault();  
  8.   
  9.         Console.WriteLine($"TEST1 : The material with id 1 is: {mat.Material}");  
  10.   
  11.         var mat2 = db.Material_GetByCodeAsync("MAT002").Result.FirstOrDefault();  
  12.   
  13.         Console.WriteLine($"TEST2 : The material with code MAT001 is: {mat2.Material}");  
  14.   
  15.         var allMaterials = db.Material_SEARCHAsync(nullnullnull);  
  16.         foreach (var m in allMaterials.Result) {  
  17.             Console.WriteLine($"TEST3 : {m.Code} {m.Material}");  
  18.         }  
  19.   
  20.         //now lets create new material  
  21.   
  22.         int? idMaterialNew = null;  
  23.   
  24.         Random random = new Random();  
  25.         int randomNumber = random.Next(0, 1000);  
  26.   
  27.         db.Material_NEW($"TESTNEW{randomNumber}", $"Material Test {randomNumber}", 0, 1, ref idMaterialNew);  
  28.   
  29.         var mat4 = db.Material_GetByIdAsync(idMaterialNew).Result.FirstOrDefault();  
  30.   
  31.         Console.WriteLine($"TEST4 : The new material is: {mat4.Code} {mat4.Material}");  
  32.   
  33.         //now update the material by setting a new cost  
  34.   
  35.         db.Material_UPD(idMaterialNew, mat4.Material, 100, mat4.idMaterialType);  
  36.   
  37.         mat4 = db.Material_GetByIdAsync(idMaterialNew).Result.FirstOrDefault();  
  38.   
  39.         Console.WriteLine($"TEST5 : The new material cost for {mat4.Code} {mat4.Material} is {mat4.Cost}");  
  40.         Console.WriteLine();  
  41.   
  42.         Console.ReadLine();  
  43.     }  
  44. }  
As you can see, the program calls the five stored procedures that were just imported...
 
If everything is OK, you should see these logs in your console:
 
Learn About Stored Procedure Scaffolding Utillity For .NET Core 3
 

Conclusion

 
In this article, I showed you a useful utility to extend your dbContext by adding a coder-friendly stored procedure support for efcore.
 
The utility must be strongly tested but its a good starting point to bring a little sp support in efcore environment.
 
I'm personally using this utility to migrate a .Net 4.5 web API project who implements more than 300 stored procedures to a new .NET Core 3.1 project, and I can say that it works well.


Similar Articles