Scaffolding Stored Procedures with CatFactory.SqlServer
Scaffolding Stored Procedures with CatFactory.SqlServer
Introduction
What is CatFactory?
CatFactory is a scaffolding engine for .NET Core built with C#.
How does it Works?
The concept behind CatFactory is to import an existing database from SQL Server instance and then to scaffold a target technology.
We can also replace the database from SQL Server instance with an in-memory database.
The flow to import an existing database is:
- Create Database Factory
- Import Database
- Create instance of Project (Entity Framework Core, Dapper, etc)
- Build Features (One feature per schema)
- Scaffold objects, these methods read all objects from database and create instances for code builders
Currently, the following technologies are supported:
This package is the core for child packages, additional packages have created with this naming convention: CatFactory.PackageName.
- CatFactory.SqlServer
- CatFactory.NetCore
- CatFactory.EntityFrameworkCore
- CatFactory.AspNetCore
- CatFactory.Dapper
Concepts Behind CatFactory
Database Type Map
One of things I don't like to get equivalent between SQL data type for CLR is use magic strings, after of review the more "fancy" way to resolve a type equivalence is to have a class that allows to know the equivalence between SQL data type and CLR type.
Using this table as reference, now CatFactory has a class with name DatabaseTypeMap
. Database
class contains a property with all mappings with name Mappings
, so this property is filled by Import
feature for SQL Server package.
Class Definition:
namespace CatFactory.Mapping
{
public class DatabaseTypeMap
{
public string DatabaseType { get; set; }
public bool AllowsLengthInDeclaration { get; set; }
public bool AllowsPrecInDeclaration { get; set; }
public bool AllowsScaleInDeclaration { get; set; }
public string ClrFullNameType { get; set; }
public bool HasClrFullNameType { get; }
public string ClrAliasType { get; set; }
public bool HasClrAliasType { get; }
public bool AllowClrNullable { get; set; }
public DbType DbTypeEnum { get; set; }
public bool IsUserDefined { get; set; }
public string ParentDatabaseType { get; set; }
public string Collation { get; set; }
}
}
Code Sample:
// Get mappings
var mappings = database.DatabaseTypeMaps;
// Resolve CLR type
var mapsForString = mappings.Where(item => item.ClrType == typeof(string)).ToList();
// Resolve SQL Server type
var mapForVarchar = mappings.FirstOrDefault(item => item.DatabaseType == "varchar");
Project Selection
A project selection is a limit to apply settings for objects match with pattern.
GlobalSelection is the default selection for project, contains a default instance of settings.
Patterns:
Pattern | Scope |
---|---|
Sales.OrderHeader | Applies for specific object with name Sales.OrderHeader |
Sales.* | Applies for all objects inside of Sales schema |
*.OrderHeader | Applies for all objects with name Order with no matter schema |
*.* | Applies for all objects, this is the global selection |
Code Sample:
// Apply settings for Project
project.GlobalSelection(settings =>
{
settings.ForceOverwrite = true;
settings.AuditEntity = new AuditEntity("CreationUser", "CreationDateTime", "LastUpdateUser", "LastUpdateDateTime");
settings.ConcurrencyToken = "Timestamp";
});
// Apply settings for specific object
project.Select("Sales.OrderHeader", settings =>
{
settings.EntitiesWithDataContracts = true;
});
Event Handlers to Scaffold
In order to provide a more flexible way in scaffolding, there are two delegates in CatFactory, one to perform an action before of scaffolding and another one to handle and action after of scaffolding.
Code Sample:
// Add event handlers to before and after of scaffold project.ScaffoldingDefinition += (source, args) => { // Add code to perform operations with code builder instance before to create code file }; project.ScaffoldedDefinition += (source, args) => { // Add code to perform operations after of create code file };
Workshop
One of things I don't like about dapper is to have all definitions for queries in strings or string builders... I prefer to have an object that builds queries and in that way reduce code lines quantity but I don't know if that concept breaks Dapper philosophy, I really weant to know about that because scaffold high quality code is fundamental in CatFactory, so I don't want to add an implementation that breaks the main concept behind an ORM...
Anyway I have invested some time to research about how can I solve query building for repositories and I have no found any object that allows to build a query from object's definition, there are frameworks that provide CRUD functions but something like LINQ there isn't, as I know of course if I wrong about this point please let me know in comments.
So I'll provide a draft for query building and take your time to let me know your feedback and then please answer 2 questions:
- This implementation breaks Dapper concept?
- What do you think about to have metadata for entities in Dapper?
Query Builder Draft
Select all
var query = QueryBuilder
.Select<Shipper>();
// Output:
// select [ShipperID], [CompanyName], [Phone] from [dbo].[Shipper]
Select by key
var query = QueryBuilder
.Select<Shipper>()
.Where("ShipperID", QueryOperator.Equals, 1);
// Output:
// select [ShipperID], [CompanyName], [Phone] from [dbo].[Shipper] where [ShipperID] = 1
Insert
var query = QueryBuilder
.Insert<Shipper>(identity: "ShipperID");
// Output:
// insert into [dbo].[Shipper] ([CompanyName], [Phone]) values (@companyName, @phone)
// select @shipperID = @@identity
Update
var query = QueryBuilder
.Update<Shipper>(key: new string[] { "ShipperID" });
// Output:
// update [dbo].[Shipper] set [CompanyName] = @companyName, [Phone] = @phone where [ShipperID] = @shipperID
Delete
var query = QueryBuilder
.Delete<Shipper>(key: new string[] { "ShipperID" });
// Output:
// delete from [dbo].[Shipper] where [ShipperID] = @shipperID
Select by
// Search by
var query = QueryBuilder
.Select<Shipper>()
.Where("CompanyName", QueryOperator.Like, "%a%")
.And("Phone", QueryOperator.Like, "%a%");
// Output:
// select [ShipperID], [CompanyName], [Phone] from [Shipper] where [CompanyName] like '%a%' and [Phone] like '%a%'
Shipper
is an entity for this example, I have found the following issues with this solution:
- There isn't information for schema (e.g. dbo, Production, Purchasing, Sales)
- There isn't a way to know if one table with name "Order Details" is mapped to entity with Name
OrderDetail
The above points can be solve if there is any information for table and entity (C# class), something like metadata, we can have an interface with name IEntity
like this:
public interface IEntity
{
Table ToTable();
}
Then create a class with name Shipper
and implement interface:
public class Shipper : IEntity
{
public int? ShipperID { get; set; }
public string CompanyName { get; set; }
public string Phone { get; set; }
public Table ToTable()
=> new Table
{
Schema = "dbo",
Name = "Shipper",
Identity = new Identity("ShipperID", 1, 1),
PrimaryKey = new PrimaryKey("ShipperID")
Columns = new List<Column>
{
new Column
{
Name = "ShipperID",
Type = "int"
},
new Column
{
Name = "CompanyName",
Type = "varchar",
Lenght = 50
},
new Column
{
Name = "Phone",
Type = "varchar",
Length = 25
}
}
};
}
}
In that way we can have all "metadata" for all entities and get that definitions to build queries in dynamic way, so we can reduce code lines in our repositories.
The definition for Table
, Columns
, Identity
and PrimaryKey
already exists in CatFactory, so we can reuse those definitions for this purpose :)
Please let me know what do you think about this implementation, make sense?
According to feedback from developers and to provide a better experience for users, I'm working on some improvements to get a more clean way to work with CatFactory:
Working with database
// Import from existing database
var database = SqlServerDatabaseFactory.Import("YourConnectionStringHere");
// Read all tables
foreach (var table in database.Tables)
{
// Check primary key on table's definition
if (table.PrimaryKey == null)
{
continue;
}
// Check identity on table's definition
if (table.Identity != null)
{
var identityName = table.Identity.Name;
}
// Read all columns
foreach (var column in table.Columns)
{
// Get equivalent CLR type from column type
var clrType = database.ResolveType(column).GetClrType();
}
}
Packages
CatFactory
CatFactory.SqlServer
CatFactory.NetCore
CatFactory.EntityFrameworkCore
CatFactory.AspNetCore
CatFactory.Dapper
CatFactory.TypeScript
You can check the download statistics for CatFactory packages in NuGet Gallery.
CatFactory
This package provides all definitions for CatFactory engine, this is the core for child packages.
Namespaces:
- CodeFactory: Contains objects to perform code generation.
- Diagnostics: Contains objects for diagnostics.
- Markup: Contains objects for markup languages.
- ObjectOrientedProgramming: Contains objects to modeling definitions: classes, interfaces and enums.
- ObjectRelationalMapping: Contains objects for ORM: database, tables, views, scalar functions, table functions and stored procedures.
CatFactory.SqlServer
This packages contains logic to import existing databases from SQL Server instances.
Object | Supported |
---|---|
Tables | Yes |
Views | Yes |
Scalar Functions | Yes |
Table Functions | Yes |
Stored Procedures | Yes |
Sequences | Not yet |
Extended Properties | Yes |
Data types | Yes |
CatFactory.NetCore
This package contains code builders and definitions for .NET Core (C#).
Object | Feature | Supported |
---|---|---|
Interface | Inheritance | Yes |
Interface | Events | Yes |
Interface | Properties | Yes |
Interface | Methods | Yes |
Class | Inheritance | Yes |
Class | Events | Yes |
Class | Fields | Yes |
Class | Constructors | Yes |
Class | Properties | Yes |
Class | Methods | Yes |
Enum | Options | Yes |
Struct | All | Not yet |
CatFactory.EntityFrameworkCore
This package provides scaffolding for Entity Framework Core.
Object | Supported |
---|---|
Class for entity | Yes |
Class for view | Yes |
Class for table function result | Not yet |
Class for stored procedure result | Not yet |
Class for DbContext | Yes |
Class for entity configuration (table) | Yes |
Class for entity configuration (view) | Yes |
Interface for Repository | Yes |
Class for Repository | Yes |
Method for scalar function invocation | Yes |
Method for table function invocation | Not yet |
Method for stored procedure invocation | Not yet |
Entity Framework Core 2 Feature Compatibility Chart
Category | Feature | Supported |
---|---|---|
Modeling | Table splitting | Not yet |
Modeling | Owned types | Not yet |
Modeling | Model-level query filters | Not yet |
Modeling | Database scalar function mapping | Not yet |
High Performance | DbContext pooling | Not yet |
High Performance | Explicitly compiled queries | Not yet |
Read more here: New features in EF Core 2.0
CatFactory.AspNetCore
This package provides scaffolding for Asp .NET Core.
Feature | Supported |
---|---|
Controllers | Yes |
Requests | Yes |
Responses | Yes |
Client Scaffolding | Not yet |
Help Page for Web API | Not yet |
Unit Tests | Not yet |
Not yet |
CatFactory.Dapper
This package provides scaffolding for Dapper.
Object | Supported |
---|---|
Class for entity | Yes |
Class for view | Yes |
Class for table function result | Yes |
Class for stored procedure result | Not yet |
Interface for Repository | Yes |
Class for Repository | Yes |
Method for scalar function invocation | Yes |
Method for table function invocation | Yes |
Method for stored procedure invocation | Not yet |
CatFactory.TypeScript
This package provides scaffolding for Type Script.
Object | Feature | Supported |
---|---|---|
Interface | Inheritance | Yes |
Interface | Fields | Yes |
Interface | Properties | Yes |
Interface | Methods | Yes |
Class | Inheritance | Yes |
Class | Fields | Yes |
Class | Constructor | Yes |
Class | Properties | Yes |
Class | Methods | Yes |
Module | All | Not yet |
Trivia
- The name for this framework it was F4N1 before than CatFactory
- Framework's name is related to kitties
- Import logic uses sp_help stored procedure to retrieve the database object's definition, I learned that in my database course at college
- Load mapping for entities with MEF, it's inspired in "OdeToCode" (Scott Allen) article for Entity Framework 6.x
- Expose all settings in one class inside of project's definition is inspired on DevExpress settings for Web controls (Web Forms)
- This implementation exists thanks to Edson Ferreira and my person
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:
Tables
Schema | Name |
---|---|
dbo | ChangeLog |
dbo | ChangeLogExclusion |
dbo | Country |
dbo | CountryCurrency |
dbo | Currency |
dbo | EventLog |
HumanResources | Employee |
HumanResources | EmployeeAddress |
HumanResources | EmployeeEmail |
Sales | Customer |
Sales | OrderDetail |
Sales | OrderHeader |
Sales | OrderStatus |
Sales | PaymentMethod |
Sales | Shipper |
Warehouse | Location |
Warehouse | Product |
Warehouse | ProductCategory |
Warehouse | ProductInventory |
You can found the scripts for database in this link: OnLine Store Database Scripts on GitHub.
Step 02 - Create Console Project
Create a console project with dotnet core wherever you want, and add these packages to your project.json
file:
Name | Version | Description |
---|---|---|
CatFactory.SqlServer | 1.0.0-beta-sun-build26 | Provides import database feature for SQL Server |
Save changes and build project.
Step 03 - Add Code
Now let's modify the Program.cs file as follows:
var database = SqlServerDatabaseFactory.Import("server=(local);database=OnLineStore;integrated security=yes;");
foreach (var table in database.Tables)
{
var codeBuilder = new SqlStoredProcedureCodeBuilder
{
Database = database,
Table = table,
OutputDirectory = "C:\\Temp\\CatFactory.SqlServer\\StoredProceduresFromExistingDatabase",
ForceOverwrite = true
};
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.OrderHeaderGetAll', 'P') is not null
drop procedure [Sales].[OrderHeaderGetAll]
go
create procedure [Sales].[OrderHeaderGetAll]
@currencyID smallint = null,
@customerID int = null,
@employeeID int = null,
@orderStatusID smallint = null,
@paymentMethodID uniqueidentifier = null,
@shipperID int = null
as
select
[OrderHeaderID],
[OrderStatusID],
[CustomerID],
[EmployeeID],
[ShipperID],
[OrderDate],
[Total],
[CurrencyID],
[PaymentMethodID],
[DetailsCount],
[ReferenceOrderID],
[Comments],
[CreationUser],
[CreationDateTime],
[LastUpdateUser],
[LastUpdateDateTime],
[Timestamp]
from
[Sales].[OrderHeader]
where
(@currencyID is null or @currencyID = @currencyID) and
(@customerID is null or @customerID = @customerID) and
(@employeeID is null or @employeeID = @employeeID) and
(@orderStatusID is null or @orderStatusID = @orderStatusID) and
(@paymentMethodID is null or @paymentMethodID = @paymentMethodID) and
(@shipperID is null or @shipperID = @shipperID)
go
if object_id('Sales.OrderHeaderGet', 'P') is not null
drop procedure [Sales].[OrderHeaderGet]
go
create procedure [Sales].[OrderHeaderGet]
@orderHeaderID bigint
as
select
[OrderHeaderID],
[OrderStatusID],
[CustomerID],
[EmployeeID],
[ShipperID],
[OrderDate],
[Total],
[CurrencyID],
[PaymentMethodID],
[DetailsCount],
[ReferenceOrderID],
[Comments],
[CreationUser],
[CreationDateTime],
[LastUpdateUser],
[LastUpdateDateTime],
[Timestamp]
from
[Sales].[OrderHeader]
where
[OrderHeaderID] = @orderHeaderID
go
if object_id('Sales.OrderHeaderAdd', 'P') is not null
drop procedure [Sales].[OrderHeaderAdd]
go
create procedure [Sales].[OrderHeaderAdd]
@orderHeaderID bigint output,
@orderStatusID smallint,
@customerID int,
@employeeID int,
@shipperID int,
@orderDate datetime,
@total decimal(12, 4),
@currencyID smallint,
@paymentMethodID uniqueidentifier,
@detailsCount int,
@referenceOrderID bigint,
@comments varchar(-1),
@creationUser varchar(25),
@creationDateTime datetime,
@lastUpdateUser varchar(25),
@lastUpdateDateTime datetime,
@timestamp timestamp
as
insert into [Sales].[OrderHeader]
(
[OrderStatusID],
[CustomerID],
[EmployeeID],
[ShipperID],
[OrderDate],
[Total],
[CurrencyID],
[PaymentMethodID],
[DetailsCount],
[ReferenceOrderID],
[Comments],
[CreationUser],
[CreationDateTime],
[LastUpdateUser],
[LastUpdateDateTime],
[Timestamp]
)
values
(
@orderStatusID,
@customerID,
@employeeID,
@shipperID,
@orderDate,
@total,
@currencyID,
@paymentMethodID,
@detailsCount,
@referenceOrderID,
@comments,
@creationUser,
@creationDateTime,
@lastUpdateUser,
@lastUpdateDateTime,
@timestamp
)
select @orderHeaderID = @@identity
go
if object_id('Sales.OrderHeaderUpdate', 'P') is not null
drop procedure [Sales].[OrderHeaderUpdate]
go
create procedure [Sales].[OrderHeaderUpdate]
@orderHeaderID bigint,
@orderStatusID smallint,
@customerID int,
@employeeID int,
@shipperID int,
@orderDate datetime,
@total decimal(12, 4),
@currencyID smallint,
@paymentMethodID uniqueidentifier,
@detailsCount int,
@referenceOrderID bigint,
@comments varchar(-1),
@creationUser varchar(25),
@creationDateTime datetime,
@lastUpdateUser varchar(25),
@lastUpdateDateTime datetime,
@timestamp timestamp
as
update
[Sales].[OrderHeader]
set
[OrderHeaderID] = @orderHeaderID
where
[OrderHeaderID] = @orderHeaderID
go
if object_id('Sales.OrderHeaderDelete', 'P') is not null
drop procedure [Sales].[OrderHeaderDelete]
go
create procedure [Sales].[OrderHeaderDelete]
@orderHeaderID bigint
as
delete from
[Sales].[OrderHeader]
where
[OrderHeaderID] = @orderHeaderID
go
As we can see, there are 5 stored procedures inside of SQL output file:
Name | Description |
---|---|
[Sales].[OrderHeaderGetAll] | Retrieves all rows from Sales.OrderHeader table |
[Sales].[OrderHeaderGet] | Retrieves one row from Sales.OrderHeader table by key |
[Sales].[OrderHeaderAdd] | Inserts new row in Sales.OrderHeader table |
[Sales].[OrderHeaderUpdate] | Updates one row in Sales.OrderHeader table by key |
[Sales].[OrderHeaderDelete] | Delete one row in Sales.OrderHeader table by key |
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.
Code Improvements
Bugs?
If you get any exception with CatFactory packages, please use these links:
I'll appreciate your feedback to improve CatFactory.
Source picture for "CatFactory" concept =^^=
History
- 23th December, 2016: Initial version
- 29th November, 2018: Upgrade package to beta version