TRUNGTQ

Sources: 

1. CatFactory repository
2. CatFactory.SqlServer repository

Introduction

CatFactory is a code generation engine and we can use to generate code in different ways, now we'll generate stored procedures from existing database.

Background

Usually, there are companies that are required to use stored procedures for read and write data in their databases, write the code for all stored procedures. It's a task that takes a long time, we find to reduce the time using a code generation for this issue, it's true Catfactory does not resolve the issue in all cases but at least provides a useful draft.

Using the Code

Step 01 - Create a Sample Database

Let's start creating a sample database, execute the following script on your SQL Server instance:

create schema HumanResources
go

create schema Production
go

create schema Sales
go

create table [EventLog]
(
	[EventLogID] int not null identity(1, 1),
	[EventType] int not null,
	[Key] varchar(255) not null,
	[Message] varchar not null,
	[EntryDate] datetime not null
)

create table [HumanResources].[Employee]
(
	[EmployeeID] int not null identity(1, 1),
	[FirstName] varchar(25) not null,
	[MiddleName] varchar(25) null,
	[LastName] varchar(25) not null,
	[BirthDate] datetime not null
)

create table [Production].[ProductCategory]
(
	[ProductCategoryID] int not null identity(1, 1),
	[ProductCategoryName] varchar(100) not null
)

create table [Production].[Product]
(
	[ProductID] int not null identity(1, 1),
	[ProductName] varchar(100) not null,
	[ProductCategoryID] int not null,
	[Description] varchar(255) null
)

create table [Production].[ProductInventory]
(
	[ProductInventoryID] int not null identity(1, 1),
	[ProductID] int not null,
	[EntryDate] datetime not null,
	[Quantity] int not null
)

create table [Sales].[Customer]
(
	[CustomerID] int not null identity(1, 1),
	[CompanyName] varchar(100) null,
	[ContactName] varchar(100) null
)

create table [Sales].[Shipper]
(
	[ShipperID] int not null identity(1, 1),
	[CompanyName] varchar(100) null,
	[ContactName] varchar(100) null
)

create table [Sales].[Order]
(
	[OrderID] int not null identity(1, 1),
	[OrderDate] datetime not null,
	[CustomerID] int not null,
	[EmployeeID] int not null,
	[ShipperID] int not null,
	[Comments] varchar(255) null
)

create table [Sales].[OrderDetail]
(
	[OrderID] int not null,
	[ProductID] int not null,
	[ProductName] varchar(255) not null,
	[UnitPrice] decimal(8, 4) not null,
	[Quantity] int not null,
	[Total] decimal(8, 4) not null
)

alter table [EventLog]
    add constraint EventLog_PK primary key (EventLogID)
go

alter table [HumanResources].[Employee]
    add constraint HumanResources_Employee_PK primary key (EmployeeID)
go

alter table [Production].[ProductCategory]
    add constraint Production_ProductCategory_PK primary key (ProductCategoryID)
go

alter table [Production].[Product]
    add constraint Production_Product_PK primary key (ProductID)
go

alter table [Production].[ProductInventory]
    add constraint Production_ProductInventory_PK primary key (ProductInventoryID)
go

alter table [Sales].[Customer]
    add constraint Sales_Customer_PK primary key (CustomerID)
go

alter table [Sales].[Shipper]
    add constraint Sales_Shipper_PK primary key (ShipperID)
go

alter table [Sales].[Order]
    add constraint Sales_Order_PK primary key (OrderID)
go

alter table [Sales].[OrderDetail]
    add constraint Sales_OrderDetail_PK primary key (OrderID, ProductID)
go

Step 02 - Create Console Project

Create a console project with dotnet core wherever you want, and add these packages to your project.jsonfile:

Save changes and build project.

Step 03 - Add Code

Now let's modify the Program.cs file as follows:

using System;
using CatFactory.SqlServer;

namespace ConsoleApp1
{
    public class Program
    {
        public static void Main(String[] args)
        {
            var connectionString = "server=(local);database=Store;integrated security=yes;";

            var dbFactory = new SqlServerDatabaseFactory()
            {
                ConnectionString = connectionString
            };

            var db = dbFactory.Import();
            
            foreach (var table in db.Tables)
            {
                var codeBuilder = new SqlStoredProcedureCodeBuilder
                {
                    Table = table,
                    OutputDirectory = "C:\\Temp\\StoredProcedures"
                };

                codeBuilder.CreateFile();
            }
        }
    }
}

Please don't forget we're using a sample database, you can change your connection string for target another database.

Please make sure you have the rights to access output directory and database. Please make sure you avoid common errors.

Once we have run our program, we can check the output directory:

In this case, we'll review Sales.Order.sql file:

if object_id('Sales.OrderGetAll', 'P') is not null
	drop procedure [Sales].[OrderGetAll]
go

create procedure [Sales].[OrderGetAll]
as
	select
		[OrderID],
		[OrderDate],
		[CustomerID],
		[EmployeeID],
		[ShipperID],
		[Comments]
	from
		[Sales].[Order]
go

if object_id('Sales.OrderGet', 'P') is not null
	drop procedure [Sales].[OrderGet]
go

create procedure [Sales].[OrderGet]
	@orderID int
as
	select
		[OrderID],
		[OrderDate],
		[CustomerID],
		[EmployeeID],
		[ShipperID],
		[Comments]
	from
		[Sales].[Order]
	where
		[OrderID] = @orderID
go

if object_id('Sales.OrderAdd', 'P') is not null
	drop procedure [Sales].[OrderAdd]
go

create procedure [Sales].[OrderAdd]
	@orderID int output,
	@orderDate datetime,
	@customerID int,
	@employeeID int,
	@shipperID int,
	@comments varchar(255)
as
	insert into [Sales].[Order]
	(
		[OrderDate],
		[CustomerID],
		[EmployeeID],
		[ShipperID],
		[Comments]
	)
	values
	(
		@orderDate,
		@customerID,
		@employeeID,
		@shipperID,
		@comments
	)

	select @orderID = @@identity
go

if object_id('Sales.OrderUpdate', 'P') is not null
	drop procedure [Sales].[OrderUpdate]
go

create procedure [Sales].[OrderUpdate]
	@orderID int,
	@orderDate datetime,
	@customerID int,
	@employeeID int,
	@shipperID int,
	@comments varchar(255)
as
	update
		[Sales].[Order]
	set
		[OrderDate] = @orderDate,
		[CustomerID] = @customerID,
		[EmployeeID] = @employeeID,
		[ShipperID] = @shipperID,
		[Comments] = @comments
	where
		[OrderID] = @orderID
go

if object_id('Sales.OrderDelete', 'P') is not null
	drop procedure [Sales].[OrderDelete]
go

create procedure [Sales].[OrderDelete]
	@orderID int
as
	delete from
		[Sales].[Order]
	where
		[OrderID] = @orderID
go

As we can see, there are 5 stored procedures inside of SQL output file:

This apply to all tables from database.

Points of Interest

• If the target table contains identity, procedure has an output parameter and sets the value for generated identity to output parameter.

Related Links

1. Generating Code for EF Core with CatFactory
2. EF Core for Enterprise

Bugs

Please let me know if you have troubles with this package in comments. :)

LINK: https://www.codeproject.com/Tips/1162346/Generating-Stored-Procedures-with-CatFactory-SqlSe