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:

USE [DB_WITH_SP]
GO
/****** Object:  Table [dbo].[anMaterial]    Script Date: 12/05/2020 16:57:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[anMaterial](
    [idMaterial] [int] IDENTITY(1,1) NOT NULL,
    [Code] [varchar](50) NULL,
    [Material] [varchar](255) NULL,
    [Cost] [decimal](12, 2) NULL,
    [idMaterialType] [int] NULL,
 CONSTRAINT [PK_anMaterial] PRIMARY KEY CLUSTERED
(
    [idMaterial] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[mdMaterialType]    Script Date: 12/05/2020 16:57:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[mdMaterialType](
    [idMaterialType] [int] NOT NULL,
    [Code] [varchar](50) NULL,
    [MaterialType] [varchar](255) NULL,
 CONSTRAINT [PK_mdMaterialType] PRIMARY KEY CLUSTERED
(
    [idMaterialType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  View [dbo].[vw_anMaterial]    Script Date: 12/05/2020 16:57:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[vw_anMaterial]
AS

SELECT m.idMaterial,
       m.Code,
       m.Material,
       m.Cost,
       m.idMaterialType,
       MaterialTypeCode = t.Code,
       t.MaterialType
FROM anMaterial m LEFT JOIN mdMaterialType t ON t.idMaterialType = m.idMaterialType
GO
SET IDENTITY_INSERT [dbo].[anMaterial] ON
GO
INSERT [dbo].[anMaterial] ([idMaterial], [Code], [Material], [Cost], [idMaterialType]) VALUES (1, N'MAT001', N'Cement', CAST(1000.00 AS Decimal(12, 2)), 1)
GO
INSERT [dbo].[anMaterial] ([idMaterial], [Code], [Material], [Cost], [idMaterialType]) VALUES (2, N'MAT002', N'Raw Meal', CAST(500.00 AS Decimal(12, 2)), 3)
GO
INSERT [dbo].[anMaterial] ([idMaterial], [Code], [Material], [Cost], [idMaterialType]) VALUES (3, N'MAT003', N'Clinker', CAST(750.00 AS Decimal(12, 2)), 2)
GO
INSERT [dbo].[anMaterial] ([idMaterial], [Code], [Material], [Cost], [idMaterialType]) VALUES (4, N'MAT004', N'Liquid Additive', CAST(50.00 AS Decimal(12, 2)), 3)
GO
INSERT [dbo].[anMaterial] ([idMaterial], [Code], [Material], [Cost], [idMaterialType]) VALUES (5, N'MAT005', N'Intermediate Binder', CAST(350.00 AS Decimal(12, 2)), 2)
GO
INSERT [dbo].[anMaterial] ([idMaterial], [Code], [Material], [Cost], [idMaterialType]) VALUES (6, N'MAT005', N'Clay', CAST(200.00 AS Decimal(12, 2)), 3)
GO
INSERT [dbo].[anMaterial] ([idMaterial], [Code], [Material], [Cost], [idMaterialType]) VALUES (7, N'MAT007', N'C4', CAST(100.00 AS Decimal(12, 2)), 3)
GO
SET IDENTITY_INSERT [dbo].[anMaterial] OFF
GO
INSERT [dbo].[mdMaterialType] ([idMaterialType], [Code], [MaterialType]) VALUES (1, N'FIN_PRO', N'Finish Product')
GO
INSERT [dbo].[mdMaterialType] ([idMaterialType], [Code], [MaterialType]) VALUES (2, N'INT_PRO', N'Intermediate Product')
GO
INSERT [dbo].[mdMaterialType] ([idMaterialType], [Code], [MaterialType]) VALUES (3, N'RAW_MAT', N'Raw Material')
GO
/****** Object:  StoredProcedure [dbo].[Material_GetByCode]    Script Date: 12/05/2020 16:57:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[Material_GetByCode](
    @Code VARCHAR(50)
)
AS

SELECT m.idMaterial,
       m.Code,
       m.Material,
       m.Cost,
       m.idMaterialType,
       m.MaterialTypeCode,
       m.MaterialType
FROM vw_anMaterial m WHERE m.Code = @Code
GO
/****** Object:  StoredProcedure [dbo].[Material_GetById]    Script Date: 12/05/2020 16:57:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[Material_GetById](
    @idMaterial INT
)
AS

SELECT m.idMaterial,
       m.Code,
       m.Material,
       m.Cost,
       m.idMaterialType,
       m.MaterialTypeCode,
       m.MaterialType
FROM vw_anMaterial m WHERE m.idMaterial = @idMaterial
GO
/****** Object:  StoredProcedure [dbo].[Material_NEW]    Script Date: 12/05/2020 16:57:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Material_NEW]
(
    @Code VARCHAR(50),
    @Material VARCHAR(255),
    @Cost DECIMAL(12,2) NULL,
    @idMaterialType INT,
    @idMaterial INT OUT
)
AS

INSERT INTO anMaterial
(
    Code,
    Material,
    Cost,
    idMaterialType
)
VALUES ( @Code, @Material, @Cost, @idMaterialType )

SET @idMaterial = @@IDENTITY
GO
/****** Object:  StoredProcedure [dbo].[Material_SEARCH]    Script Date: 12/05/2020 16:57:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Material_SEARCH]
(
    @Code VARCHAR(50) NULL,
    @Material VARCHAR(255) NULL,
    @idMaterialType INT NULL
)
AS

SELECT m.idMaterial,
       m.Code,
       m.Material,
       m.Cost,
       m.idMaterialType,
       m.MaterialTypeCode,
       m.MaterialType
FROM vw_anMaterial m
WHERE m.Code = ISNULL(@Code,m.Code)
AND m.Material LIKE '%' + ISNULL(@Material,m.Material) + '%'
AND m.idMaterialType = ISNULL(@idMaterialType, m.idMaterialType)
GO
/****** Object:  StoredProcedure [dbo].[Material_UPD]    Script Date: 12/05/2020 16:57:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Material_UPD]
(
    @idMaterial INT,
    @Material VARCHAR(255),
    @Cost DECIMAL(12,2) NULL,
    @idMaterialType INT NULL
)
AS

UPDATE anMaterial
    SET Material = @Material,
        Cost = @Cost,
        idMaterialType = @idMaterialType
WHERE idMaterial = @idMaterial
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:

 

 

 

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:

 

 

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:

 

 

You will find a new file in the solution:

 

 

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.

static void Main(string[] args)
{
    Console.WriteLine("Hello, I'm SPToCore!");

    using (SPToCoreContext db = new SPToCoreContext()) {

        var mat = db.Material_GetByIdAsync(1).Result.FirstOrDefault();

        Console.WriteLine($"TEST1 : The material with id 1 is: {mat.Material}");

        var mat2 = db.Material_GetByCodeAsync("MAT002").Result.FirstOrDefault();

        Console.WriteLine($"TEST2 : The material with code MAT001 is: {mat2.Material}");

        var allMaterials = db.Material_SEARCHAsync(null, null, null);
        foreach (var m in allMaterials.Result) {
            Console.WriteLine($"TEST3 : {m.Code} {m.Material}");
        }

        //now lets create new material

        int? idMaterialNew = null;

        Random random = new Random();
        int randomNumber = random.Next(0, 1000);

        db.Material_NEW($"TESTNEW{randomNumber}", $"Material Test {randomNumber}", 0, 1, ref idMaterialNew);

        var mat4 = db.Material_GetByIdAsync(idMaterialNew).Result.FirstOrDefault();

        Console.WriteLine($"TEST4 : The new material is: {mat4.Code} {mat4.Material}");

        //now update the material by setting a new cost

        db.Material_UPD(idMaterialNew, mat4.Material, 100, mat4.idMaterialType);

        mat4 = db.Material_GetByIdAsync(idMaterialNew).Result.FirstOrDefault();

        Console.WriteLine($"TEST5 : The new material cost for {mat4.Code} {mat4.Material} is {mat4.Cost}");
        Console.WriteLine();

        Console.ReadLine();
    }
}

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:

 

 

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.