Click here to Skip to main content
13,767,544 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

18.6K views
30 bookmarked
Posted 1 Nov 2017
Licenced CPOL

Scaffolding Dapper with CatFactory

, 25 Oct 2018
Rate this:
Please Sign up or sign in to vote.
Scaffolding Dapper with CatFactory

Introduction

What is CatFactory?

CatFactory it's a scaffolding engine for .NET Core built in C#.

How it Works?

The key thing in CatFactory it's import an existing database from a SQL Server instance, then scaffold code for specific technology. In some cases we can replace the database from SQL Server instance with a in memory database.

To understand the scope for CatFactory, in few words CatFactory is the core, to have more packages we can create them with this naming convention: CatFactory.PackageName.

In the sample code, the basic flow for 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

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.Order Applies for specific object with name Sales.Order
Sales.* Applies for all objects inside of Sales schema
*.Order 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.Order", settings =>
{
    settings.ForceOverwrite = true;
    settings.AuditEntity = new AuditEntity("CreationUser", "CreationDateTime", "LastUpdateUser", "LastUpdateDateTime");
    settings.ConcurrencyToken = "Timestamp";
    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:

  1. This implementation breaks Dapper concept?
  2. 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

Code generation it's a common technique developers use to reduce time in code writing, I know the most programmers build a code generator in their professional lifes.

EF 6.x had a wizard for code generation, that tool generates DbContext and POCOs but there isn't code for Fluent API, Repositories and other things like those; with .NET Core there is a command line tool for code generation but we have the same scenario, there is generation only for DbContext and Entities; with CatFactory we're looking for a simple way to generate code with enterprise patterns, please don't forget this is an alpha version of CatFactory, don't pretend to have in this date a full version of code generation engine.

Why don't use code CodeDOM? CodeDOM it's a complex code generation engine, I don't saying CodeDOM sucks or something like that, but at this moment we're focus on generate code in the more simple way, maybe in the incoming versions we'll add some integration with CodeDOM.

Prerequisites

Skills

  • OOP
  • AOP
  • ORM
  • C#
  • Design Patterns (Repository and Unit of Work)

Software Prerequisites

  • .NET Core
  • Visual Studio 2017 or VS Code
  • Access to existing SQL Server instance

Using the code

Please follow these steps to scaffold Dapper with CatFactory:

Step 01 - Create sample database

This is a sample database, Keep in mind you'll change the connection string for your context, also you have to change the name for project and output directory.

Please make sure you have permissions to write in output directory, please make sure about this point that point to avoid common errors.

Sample SQL script:

use master
go

create database Store
go

use Store
go

create schema HumanResources
go

create schema Production
go

create schema Sales
go

create table [dbo].[EventLog]
(
	[EventLogID] uniqueidentifier not null,
	[EventType] int not null,
	[Key] varchar(255) not null,
	[Message] varchar(max) not null,
	[EntryDate] datetime not null
)

create table [dbo].[ChangeLog]
(
	[ChangeLogID] int not null identity(1, 1),
	[ClassName] varchar(255) not null,
	[PropertyName] varchar(255) not null,
	[Key] varchar(255) not null,
	[OriginalValue] varchar(max) null,
	[CurrentValue] varchar(max) null,
	[UserName] varchar(25) not null,
	[ChangeDate] datetime not null
)

create table [dbo].[ChangeLogExclusion]
(
	[ChangeLogExclusionID] int not null identity(1, 1),
	[EntityName] varchar(128) not null,
	[PropertyName] varchar(128) not null
)

create table [dbo].[Country]
(
	[CountryID] int not null,
	[CountryName] varchar(100) not null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [dbo].[Currency]
(
	[CurrencyID] smallint not null identity(1000, 1000),
	[CurrencyName] varchar(50) not null,
	[CurrencySymbol] varchar(1) not null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [dbo].[CountryCurrency]
(
	[CountryCurrencyID] int not null identity(1, 1),
	[CountryID] int not null,
	[CurrencyID] smallint not null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion 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,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [HumanResources].[EmployeeAddress]
(
	[EmployeeAddressID] int not null identity(1, 1),
	[EmployeeID] int not null,
	[AddressLine1] varchar(50) not null,
	[AddressLine2] varchar(50) null,
	[City] varchar(25) not null,
	[State] varchar(25) not null,
	[ZipCode] varchar(5) null,
	[CountryID] int not null,
	[PhoneNumber] varchar(25) null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [HumanResources].[EmployeeEmail]
(
	[EmployeeEmailID] int not null identity(1, 1),
	[EmployeeID] int not null,
	[Email] varchar(50) not null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [Production].[ProductCategory]
(
	[ProductCategoryID] int not null identity(1, 1),
	[ProductCategoryName] varchar(100) not null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [Production].[Product]
(
	[ProductID] int not null identity(1, 1),
	[ProductName] varchar(100) not null,
	[ProductCategoryID] int not null,
	[UnitPrice] decimal(8, 4) not null,
	[Description] varchar(255) null,
	[Discontinued] bit not null,
	[Stocks] int not null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [Production].[Warehouse]
(
	[WarehouseID] varchar(5) not null,
	[WarehouseName] varchar(100) not null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [Production].[ProductInventory]
(
	[ProductInventoryID] int not null identity(1, 1),
	[ProductID] int not null,
	[WarehouseID] varchar(5) not null,
	[Quantity] int not null,
	[Stocks] int not null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [Sales].[OrderStatus]
(
	[OrderStatusID] smallint not null,
	[Description] varchar(100) not null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [Sales].[PaymentMethod]
(
	[PaymentMethodID] uniqueidentifier not null,
	[PaymentMethodName] varchar(50) not null,
	[PaymentMethodDescription] varchar(255) not null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [Sales].[Customer]
(
	[CustomerID] int not null identity(1, 1),
	[CompanyName] varchar(100) null,
	[ContactName] varchar(100) null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [Sales].[CustomerAddress]
(
	[CustomerAddressID] int not null identity(1, 1),
	[CustomerID] int not null,
	[AddressLine1] varchar(50) not null,
	[AddressLine2] varchar(50) null,
	[City] varchar(25) not null,
	[State] varchar(25) not null,
	[ZipCode] varchar(5) null,
	[CountryID] int not null,
	[PhoneNumber] varchar(25) null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [Sales].[CustomerEmail]
(
	[CustomerEmailID] int not null identity(1, 1),
	[CustomerID] int not null,
	[Email] varchar(50) not null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [Sales].[Shipper]
(
	[ShipperID] int not null identity(1, 1),
	[CompanyName] varchar(100) null,
	[ContactName] varchar(100) null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [Sales].[Order]
(
	[OrderID] bigint not null identity(1, 1),
	[OrderStatusID] smallint not null,
	[CustomerID] int not null,
	[EmployeeID] int null,
	[ShipperID] int null,
	[OrderDate] datetime not null,
	[Total] decimal(12, 4) not null,
	[CurrencyID] smallint null,
	[PaymentMethodID] uniqueidentifier null,
	[Comments] varchar(max) null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [Sales].[OrderDetail]
(
	[OrderDetailID] bigint not null identity(1, 1),
	[OrderID] bigint 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,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)
go

alter table [dbo].[EventLog]
	add constraint [PK_EventLog] primary key (EventLogID)
go

alter table [dbo].[ChangeLog]
	add constraint [PK_ChangeLog] primary key (ChangeLogID)
go

alter table [dbo].[ChangeLogExclusion]
	add constraint [PK_ChangeLogExclusion] primary key(ChangeLogExclusionID)

alter table [dbo].[Country]
	add constraint [PK_Country] primary key([CountryID])
go

alter table [dbo].[Currency]
	add constraint [PK_Currency] primary key([CurrencyID])
go

alter table [dbo].[CountryCurrency]
	add constraint [PK_CountryCurrency] primary key([CountryCurrencyID])
go

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

alter table [HumanResources].[EmployeeAddress]
	add constraint [PK_HumanResources_EmployeeAddress] primary key (EmployeeAddressID)
go

alter table [HumanResources].[EmployeeEmail]
	add constraint [PK_HumanResources_EmployeeEmail] primary key (EmployeeEmailID)
go

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

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

alter table [Production].[Product]
	add constraint [U_Production_Product_ProductName] unique (ProductName)
go

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

alter table [Production].[Warehouse]
	add constraint [PK_Production_Warehouse] primary key (WarehouseID)
go

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

alter table [Sales].[CustomerAddress]
	add constraint [PK_Sales_CustomerAddress] primary key (CustomerAddressID)
go

alter table [Sales].[CustomerEmail]
	add constraint [PK_Sales_CustomerEmail] primary key (CustomerEmailID)
go

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

alter table [Sales].[OrderStatus]
	add constraint [PK_Sales_OrderStatus] primary key (OrderStatusID)
go

alter table [Sales].[PaymentMethod]
	add constraint [PK_Sales_PaymentMethod] primary key (PaymentMethodID)
go

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

alter table [Sales].[OrderDetail]
	add constraint [PK_Sales_OrderDetail] primary key (OrderDetailID)
go

alter table [dbo].[CountryCurrency]
	add constraint [U_CountryCurrency] unique (CountryID, CurrencyID)
go

alter table [Sales].[OrderDetail]
	add constraint [U_Sales_OrderDetail] unique (OrderID, ProductID)
go

alter table [ChangeLogExclusion]
	add constraint [U_ChangeLogExclusion] unique(EntityName, PropertyName)
go

alter table [dbo].[CountryCurrency]
	add constraint [FK_CountryCurrency_Country] foreign key (CountryID)
		references [dbo].[Country]
go

alter table [dbo].[CountryCurrency]
	add constraint [FK_CountryCurrency_Currency] foreign key (CurrencyID)
		references [dbo].[Currency]
go

alter table [Production].[Product]
	add constraint [FK_Production_Product_ProductCategory] foreign key (ProductCategoryID)
		references [Production].[ProductCategory]
go

alter table [Production].[ProductInventory]
	add constraint [FK_Production_ProductInventory_Product] foreign key (ProductID)
		references [Production].[Product]
go

alter table [Production].[ProductInventory]
	add constraint [FK_Production_ProductInventory_Warehouse] foreign key (WarehouseID)
		references [Production].[Warehouse]
go

alter table [HumanResources].[EmployeeAddress]
	add constraint [FK_HumanResources_EmployeeAddress] foreign key (EmployeeID)
		references [HumanResources].[Employee]
go

alter table [HumanResources].[EmployeeEmail]
	add constraint [FK_HumanResources_EmployeeEmail] foreign key (EmployeeID)
		references [HumanResources].[Employee]
go

alter table [Sales].[CustomerAddress]
	add constraint [FK_Sales_CustomerAddress] foreign key (CustomerID)
		references [Sales].[Customer]
go

alter table [Sales].[CustomerEmail]
	add constraint [FK_Sales_CustomerEmail] foreign key (CustomerID)
		references [Sales].[Customer]
go

alter table [Sales].[Order]
	add constraint [FK_Sales_Order_OrderStatus] foreign key (OrderStatusID)
		references [Sales].[OrderStatus]
go

alter table [Sales].[Order]
	add constraint [FK_Sales_Order_Customer] foreign key (CustomerID)
		references [Sales].[Customer]
go

alter table [Sales].[Order]
	add constraint [FK_Sales_Order_Employee] foreign key (EmployeeID)
		references [HumanResources].[Employee]
go

alter table [Sales].[Order]
	add constraint [FK_Sales_Order_Shipper] foreign key (ShipperID)
		references [Sales].[Shipper]
go

alter table [Sales].[Order]
	add constraint [FK_Sales_Order_Currency] foreign key (CurrencyID)
		references [dbo].[Currency]
go

alter table [Sales].[Order]
	add constraint [FK_Sales_Order_PaymentMethod] foreign key (PaymentMethodID)
		references [Sales].[PaymentMethod]
go

alter table [Sales].[OrderDetail]
	add constraint [FK_Sales_OrderDetail_Order] foreign key (OrderID)
		references [Sales].[Order]
go

alter table [Sales].[OrderDetail]
	add constraint [FK_Sales_OrderDetail_Product] foreign key (ProductID)
		references [Production].[Product]
go

create function [Production].[ufnGetStock](@productID int)
    returns int
as
    begin
        declare @value int
        select @value = sum([Stocks]) from [Production].[ProductInventory] where [ProductID] = @productID
        if (@value is null)
            set @value = 0
        
        return @value
    end
go

create function [HumanResources].[ufnGetEmployeeFullName](@employeeID int)
    returns varchar(75)
as
    begin
        declare @value varchar(75)

        select
            @value = [FirstName] + isnull(MiddleName + ' ', '') + [LastName]
        from
            [HumanResources].[Employee]
        where
            [employeeID] = @employeeID

        return @value
    end
go

create function [Sales].[ufnGetCustomerContact](@customerID int)
    returns @retCustomerContactInformation table
    (
        [CustomerID] int not null,
        [AddressLine1] varchar(50) not null,
	    [AddressLine2] varchar(50) null,
        [City] varchar(25) not null,
	    [State] varchar(25) not null,
        [ZipCode] varchar(5) null,
	    [PhoneNumber] varchar(25) null
    )
    as
        begin
            insert into
                @retCustomerContactInformation
            select
                [SalesCustomerAddress].[CustomerID],
                [SalesCustomerAddress].[AddressLine1],
                [SalesCustomerAddress].[AddressLine2],
                [SalesCustomerAddress].[City],
                [SalesCustomerAddress].[State],
                [SalesCustomerAddress].[ZipCode],
                [SalesCustomerAddress].[PhoneNumber]
            from
                [Sales].[CustomerAddress] [SalesCustomerAddress]
            where
                [SalesCustomerAddress].[CustomerID] = @customerID
			return
        end
go

create view [HumanResources].[EmployeeInfo]
as
	select
		Employee.FirstName + ' ' + isnull(Employee.MiddleName + ' ', '') + Employee.LastName as EmployeeName,
		(select count(EmployeeID) from HumanResources.EmployeeAddress where EmployeeID = Employee.EmployeeID) as EmployeeAddresses,
		(select count(EmployeeID) from HumanResources.EmployeeEmail where EmployeeID = Employee.EmployeeID) as EmployeeEmails
	from
		HumanResources.Employee Employee
go

create view [Sales].[OrderSummary]
as
	select
	OrderHeader.OrderID,
	Customer.CompanyName as CustomerName,
	Employee.FirstName + ' ' + isnull(Employee.MiddleName + ' ', '') + Employee.LastName as EmployeeName,
	Shipper.CompanyName as ShipperName,
	OrderHeader.OrderDate,
	OrderHeader.Total,
	Currency.CurrencyName,
	PaymentMethod.PaymentMethodName,
	(select count(OrderDetailID) from Sales.OrderDetail where OrderID = OrderHeader.OrderID) as OrderLines
from
	Sales.[Order] OrderHeader
	inner join Sales.Customer Customer
		on OrderHeader.CustomerID = Customer.CustomerID
	inner join HumanResources.Employee Employee
		on OrderHeader.EmployeeID = Employee.EmployeeID
	inner join Sales.Shipper Shipper
		on OrderHeader.ShipperID = Shipper.ShipperID
	inner join dbo.Currency
		on OrderHeader.CurrencyID = Currency.CurrencyID
	inner join Sales.PaymentMethod PaymentMethod
		on OrderHeader.PaymentMethodID = PaymentMethod.PaymentMethodID
go

Step 02 - Create Project

Create a console application for .NET Core, in some cases you can add one project to your existing solution but with some name or sufix that indicates it's a project to scaffold, for example: Store.CatFactory.Dapper.

Add the following packages for your project:

Name Version Description
CatFactory.SqlServer 1.0.0-beta-sun-build21 Provides import feature for SQL Server databases
CatFactory.Dapper 1.0.0-beta-sun-build19 Provides scaffold for Dapper

Save all changes and build the project.

Step 03 - Add Code To Scaffold

Once we have the packages installed on our project, we can add code in Main method:

// Create database factory
var databaseFactory = new SqlServerDatabaseFactory
{
    DatabaseImportSettings = new DatabaseImportSettings
    {
        ConnectionString = "server=(local);database=Store;integrated security=yes;",
        ImportScalarFunctions = true,
        ImportTableFunctions = true,
        Exclusions =
        {
            "dbo.sysdiagrams",
            "dbo.fn_diagramobjects"
        }
    }
};

// Import database
var database = databaseFactory.Import();

// Create instance of Dapper Project
var project = new DapperProject
{
    Name = "Store",
    Database = database,
    OutputDirectory = @"C:\Projects\Store\Store.Dapper.API"
};

// Apply settings for project
project.GlobalSelection(settings =>
{
    settings.ForceOverwrite = true;
    settings.UpdateExclusions = new List<string> { "CreationUser", "CreationDateTime", "Timestamp" };
    settings.InsertExclusions = new List<string> { "LastUpdateUser", "LastUpdateDateTime", "Timestamp" };
});

project.Select("Production.*", settings =>
{
    settings.DeclareConnectionAsParameter = true;
    settings.UseStringBuilderForQueries = false;
    settings.AddPagingForGetAllOperation = true;
});

project.Select("Sales.Order", settings => settings.AddPagingForGetAllOperation = true);

// Build features for project, group all entities by schema into a feature
project.BuildFeatures();

// 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
};

// Scaffolding =^^=
project
    .ScaffoldEntityLayer()
    .ScaffoldDataLayer();

If you want to know more about import databases with CatFactory.SqlServer package, take a look on wiki: CatFactory.SqlServer Wiki.

Description for methods:

Extension Methods for DapperProject Instance
Name Description
ScaffoldEntityLayer Scaffold entities from Tables, Views, Scalar Functions and Table Functions
ScaffoldDataLayer Scaffold Contracts and Repositories

Setting Up CatFactory for Dapper

Additionally, there are settings for DapperProject instance, we'll take a look on those settings:

Name Default Value Description
ForceOverwrite false Indicates if code builder must overwrite files if they already exist
SimplifyDataTypes false Indicates if code builder must change from CLR types to native types (e.g. Int32 => int)
UseAutomaticPropertiesForEntities true Indicates if entities classes will use automatic properties or not, if value is false, the entity will contains private fields
EnableDataBindings false Implements INotifyPropertyChanged property and add properties with fields for class definition
UseStringBuilderForQueries true Indicates if queries will construct with StringBuilder
InsertExclusions   Sets the columns list to exclude in insert method
UpdateExclusions   Sets the columns list to exclude in update method
AddPagingForGetAllOperation false Add SQL code for GetAll methods to allow paging
DeclareConnectionAsParameter false Obsolete: Connection is not a parameter for repository methods anymore, set connection instance in repository constructor

Also we can change the namespaces, set the values for output namespaces inside of project instance:

Namespace Default Value Description
Entity Layer EntityLayer Gets or sets the namespace for entity layer
Data Layer DataLayer Gets or sets the namespace for data layer
Contracts Contracts Gets or sets the namespace for contracts in data layer
Repositories Repositories Gets or sets the namespace for repositories in data layer

Step 04 - Create Console Project

Obviously we add this simple code to test output code doesn't have errors, if we use the generated code in Web API project, we'll need to add dependency injection and anothers things, please check in links list at the end of this post for more information.

Now we can go to output directory and create a console project for .NET Core and add code according to your choose:

Get All:

// Create connection for SQL Server
using (var connection = new SqlConnection("server=(local);database=Store;integrated security=yes;"))
{
 // Create repository instance
 var repository = new SalesRepository(connection);
 
 // Retrieve orders
 var orders = await repository.GetOrdersAsync();
}

Get by Key:

// Create connection for SQL Server
using (var connection = new SqlConnection("server=(local);database=Store;integrated security=yes;"))
{
 // Create repository instance
 var repository = new SalesRepository(connection);
 
 // Get entity by id
 var entity = await repository.GetOrderAsync(new Order(1));
}

Get by Unique (If entity has an unique constraint):

// Create connection for SQL Server
using (var connection = new SqlConnection("server=(local);database=Store;integrated security=yes;"))
{
 // Create repository instance
 var repository = new SalesRepository(connection);
 
 // Get entity by id
 var entity = await repository.GetProductByProductNameAsync(new Product { ProductName = "The King of Fighters XIV"});
}

Add:

// Create connection for SQL Server
using (var connection = new SqlConnection("server=(local);database=Store;integrated security=yes;"))
{
 // Create repository instance
 var repository = new SalesRepository(connection);
 
 // Create instance for entity
 var entity = new Order();
  
 // Set values for properties
 // e.g. entity.Total = 29.99m;
  
 // Add entity in database
 await repository.AddOrderAsync(entity);
}

Update:

// Create connection for SQL Server
using (var connection = new SqlConnection("server=(local);database=Store;integrated security=yes;"))
{
 // Create repository instance
 var repository = new SalesRepository(connection);
 
 // Retrieve entity by id
 var entity = await repository.GetOrderAsync(new Order(1));
  
 // Set values for properties
 // e.g. entity.Total = 29.99m;
  
 // Add entity in database
 await repository.UpdateOrderAsync(entity);
}

Remove:

// Create connection for SQL Server
using (var connection = new SqlConnection("server=(local);database=Store;integrated security=yes;"))
{
 // Create repository instance
 var repository = new SalesRepository(connection);
 
 // Retrieve entity by id instance for entity
 var entity = await repository.GetOrderAsync(new Order(1));
  
 // Add entity in database
 await repository.RemoveOrderAsync(entity);
}

Table Function:

// Create connection for SQL Server
using (var connection = new SqlConnection("server=(local);database=Store;integrated security=yes;"))
{
 // Create repository instance
 var repository = new SalesRepository(connection);
 
 // Retrieve contacts for customer
 var customerContacts = await repository.GetUfnGetCustomerContactsAsync(1);
}

The previous code samples do not include error handling, these are samples to show how to work with generated code.

Name Version  
Dapper Latest version Provides helper methods for database operations

How works all code together?

We need to create the options for connection, then we proceed to create the instance for repository and finally we invoke the method from repository instance.

For this architecture implementation we are using the .NET naming conventions: PascalCase for classes, interfaces and methods; camelCase for parameters.

Namespaces for generated code:

  1. EntityLayer
  2. DataLayer
  3. DataLayer\Contracts
  4. DataLayer\Repositories

Inside of EntityLayer we'll place all entities, in this context entity means a class that represents a table or view from database, sometimes entity is named POCO (Plain Old Common language runtime Object) than means a class with only properties not methods nor other things (events)

Inside of DataLayer we'll place DbContext and AppSettings because they're common classes for DataLayer

Inside of DataLayer\Contracts we'll place all interfaces that represent operations catalog, we're focusing on schemas and we'll create one interface per schema and Store contract for default schema (dbo)

Inside of DataLayer\Repositories we'll place the implementations for Contracts definitons

We can review the link about Entity Framework Core for Enterprise, and we can understand this guide allow to us generate all of that code to reduce time in code writing.

Code Review

We'll review some the generated code for one entity to understand this design:

Code for Order class:

using System;

namespace Store.EntityLayer.Sales
{
 public class Order : IEntity
 {
  public Order()
  {
  }

  public Order(Int64? orderID)
  {
   OrderID = orderID;
  }

  public Int64? OrderID { get; set; }

  public Int16? OrderStatusID { get; set; }

  public Int32? CustomerID { get; set; }

  public Int32? EmployeeID { get; set; }

  public Int32? ShipperID { get; set; }

  public DateTime? OrderDate { get; set; }

  public Decimal? Total { get; set; }

  public Int16? CurrencyID { get; set; }

  public Guid? PaymentMethodID { get; set; }

  public String Comments { get; set; }

  public String CreationUser { get; set; }

  public DateTime? CreationDateTime { get; set; }

  public String LastUpdateUser { get; set; }

  public DateTime? LastUpdateDateTime { get; set; }

  public Byte[] Timestamp { get; set; }
 }
}

Code for IHumanResourcesRepository interface:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using Microsoft.Extensions.Options;
using Store.EntityLayer;
using Store.DataLayer.Contracts;
using Store.EntityLayer.HumanResources;
using Store.EntityLayer.Production;
using Store.EntityLayer.Sales;

namespace Store.DataLayer.Contracts
{
 public interface IHumanResourcesRepository : IRepository
 {
  Task<IEnumerable<Employee>> GetEmployeesAsync();

  Task<Employee> GetEmployeeAsync(Employee entity);

  Task<Int32> AddEmployeeAsync(Employee entity);

  Task<Int32> UpdateEmployeeAsync(Employee entity);

  Task<Int32> RemoveEmployeeAsync(Employee entity);

  Task<IEnumerable<EmployeeAddress>> GetEmployeeAddressesAsync(Int32? employeeID = null);

  Task<EmployeeAddress> GetEmployeeAddressAsync(EmployeeAddress entity);

  Task<Int32> AddEmployeeAddressAsync(EmployeeAddress entity);

  Task<Int32> UpdateEmployeeAddressAsync(EmployeeAddress entity);

  Task<Int32> RemoveEmployeeAddressAsync(EmployeeAddress entity);

  Task<IEnumerable<EmployeeEmail>> GetEmployeeEmailsAsync(Int32? employeeID = null);

  Task<EmployeeEmail> GetEmployeeEmailAsync(EmployeeEmail entity);

  Task<Int32> AddEmployeeEmailAsync(EmployeeEmail entity);

  Task<Int32> UpdateEmployeeEmailAsync(EmployeeEmail entity);

  Task<Int32> RemoveEmployeeEmailAsync(EmployeeEmail entity);

  Task<IEnumerable<EmployeeInfo>> GetEmployeeInfosAsync();

  Task<String> GetUfnGetEmployeeFullNamesAsync(Int32 employeeID);
 }
}

Code for ISalesRepository interface:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using Microsoft.Extensions.Options;
using Store.EntityLayer;
using Store.DataLayer.Contracts;
using Store.EntityLayer.HumanResources;
using Store.EntityLayer.Production;
using Store.EntityLayer.Sales;

namespace Store.DataLayer.Contracts
{
 public interface ISalesRepository : IRepository
 {
  Task<IEnumerable<Customer>> GetCustomersAsync();

  Task<Customer> GetCustomerAsync(Customer entity);

  Task<Int32> AddCustomerAsync(Customer entity);

  Task<Int32> UpdateCustomerAsync(Customer entity);

  Task<Int32> RemoveCustomerAsync(Customer entity);

  Task<IEnumerable<CustomerAddress>> GetCustomerAddressesAsync(Int32? customerID = null);

  Task<CustomerAddress> GetCustomerAddressAsync(CustomerAddress entity);

  Task<Int32> AddCustomerAddressAsync(CustomerAddress entity);

  Task<Int32> UpdateCustomerAddressAsync(CustomerAddress entity);

  Task<Int32> RemoveCustomerAddressAsync(CustomerAddress entity);

  Task<IEnumerable<CustomerEmail>> GetCustomerEmailsAsync(Int32? customerID = null);

  Task<CustomerEmail> GetCustomerEmailAsync(CustomerEmail entity);

  Task<Int32> AddCustomerEmailAsync(CustomerEmail entity);

  Task<Int32> UpdateCustomerEmailAsync(CustomerEmail entity);

  Task<Int32> RemoveCustomerEmailAsync(CustomerEmail entity);

  Task<IEnumerable<Order>> GetOrdersAsync(Int32 pageSize = 10, Int32 pageNumber = 1, Int16? currencyID = null, Int32? customerID = null, Int32? employeeID = null, Int16? orderStatusID = null, Guid? paymentMethodID = null, Int32? shipperID = null);

  Task<Order> GetOrderAsync(Order entity);

  Task<Int32> AddOrderAsync(Order entity);

  Task<Int32> UpdateOrderAsync(Order entity);

  Task<Int32> RemoveOrderAsync(Order entity);

  Task<IEnumerable<OrderDetail>> GetOrderDetailsAsync(Int64? orderID = null, Int32? productID = null);

  Task<OrderDetail> GetOrderDetailAsync(OrderDetail entity);

  Task<OrderDetail> GetOrderDetailByOrderIDAndProductIDAsync(OrderDetail entity);

  Task<Int32> AddOrderDetailAsync(OrderDetail entity);

  Task<Int32> UpdateOrderDetailAsync(OrderDetail entity);

  Task<Int32> RemoveOrderDetailAsync(OrderDetail entity);

  Task<IEnumerable<OrderStatus>> GetOrderStatusesAsync();

  Task<OrderStatus> GetOrderStatusAsync(OrderStatus entity);

  Task<Int32> AddOrderStatusAsync(OrderStatus entity);

  Task<Int32> UpdateOrderStatusAsync(OrderStatus entity);

  Task<Int32> RemoveOrderStatusAsync(OrderStatus entity);

  Task<IEnumerable<PaymentMethod>> GetPaymentMethodsAsync();

  Task<PaymentMethod> GetPaymentMethodAsync(PaymentMethod entity);

  Task<Int32> AddPaymentMethodAsync(PaymentMethod entity);

  Task<Int32> UpdatePaymentMethodAsync(PaymentMethod entity);

  Task<Int32> RemovePaymentMethodAsync(PaymentMethod entity);

  Task<IEnumerable<Shipper>> GetShippersAsync();

  Task<Shipper> GetShipperAsync(Shipper entity);

  Task<Int32> AddShipperAsync(Shipper entity);

  Task<Int32> UpdateShipperAsync(Shipper entity);

  Task<Int32> RemoveShipperAsync(Shipper entity);

  Task<IEnumerable<OrderSummary>> GetOrderSummariesAsync();

  Task<IEnumerable<UfnGetCustomerContact>> GetUfnGetCustomerContactsAsync(int customerID);
 }
}

Code for Repository class:

using System.Data;
using Store.EntityLayer;

namespace Store.DataLayer.Contracts
{
 public class Repository
 {
  public Repository(IDbConnection connection)
  {
   Connection = connection;
  }

  protected IDbConnection Connection { get; }
 }
}

Code for HumanResourcesRepository

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using Microsoft.Extensions.Options;
using Store.EntityLayer;
using Store.DataLayer.Contracts;
using Store.EntityLayer.HumanResources;
using Store.EntityLayer.Production;
using Store.EntityLayer.Sales;

namespace Store.DataLayer.Repositories
{
 public class HumanResourcesRepository : Repository, IHumanResourcesRepository
 {
  public HumanResourcesRepository(IDbConnection connection)
   : base(connection)
  {
  }

  // Methods for another entities operations

  public async Task<String> GetUfnGetEmployeeFullNamesAsync(Int32 employeeID)
  {
   // Create string builder for query
   var query = new StringBuilder();
   
   // Create sql statement
   query.Append(" select ");
   query.Append("  [HumanResources].[ufnGetEmployeeFullName](@employeeID) ");
   
   // Create parameters collection
   var parameters = new DynamicParameters();
   
   // Add parameters to collection
   parameters.Add("@employeeID", employeeID);
   
   // Retrieve scalar from database and cast to specific CLR type
   var scalar = await Connection.ExecuteScalarAsync(query.ToString(), parameters);
   return (String)scalar;
  }
 }
}

Code for SalesRepository class:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using Microsoft.Extensions.Options;
using Store.EntityLayer;
using Store.DataLayer.Contracts;
using Store.EntityLayer.HumanResources;
using Store.EntityLayer.Production;
using Store.EntityLayer.Sales;

namespace Store.DataLayer.Repositories
{
 public class SalesRepository : Repository, ISalesRepository
 {
  public SalesRepository(IDbConnection connection)
   : base(connection)
  {
  }

  // Methods for another entities operations

  public async Task<IEnumerable<Order>> GetOrdersAsync(Int32 pageSize = 10, Int32 pageNumber = 1, Int16? currencyID = null, Int32? customerID = null, Int32? employeeID = null, Int16? orderStatusID = null, Guid? paymentMethodID = null, Int32? shipperID = null)
  {
   // Create string builder for query
   var query = new StringBuilder();
   
   // Create sql statement
   query.Append(" select ");
   query.Append("  [OrderID], ");
   query.Append("  [OrderStatusID], ");
   query.Append("  [CustomerID], ");
   query.Append("  [EmployeeID], ");
   query.Append("  [ShipperID], ");
   query.Append("  [OrderDate], ");
   query.Append("  [Total], ");
   query.Append("  [CurrencyID], ");
   query.Append("  [PaymentMethodID], ");
   query.Append("  [Comments], ");
   query.Append("  [CreationUser], ");
   query.Append("  [CreationDateTime], ");
   query.Append("  [LastUpdateUser], ");
   query.Append("  [LastUpdateDateTime], ");
   query.Append("  [Timestamp] ");
   query.Append(" from ");
   query.Append("  [Sales].[Order] ");
   query.Append(" where ");
   query.Append("  (@currencyID is null or [CurrencyID] = @currencyID) and ");
   query.Append("  (@customerID is null or [CustomerID] = @customerID) and ");
   query.Append("  (@employeeID is null or [EmployeeID] = @employeeID) and ");
   query.Append("  (@orderStatusID is null or [OrderStatusID] = @orderStatusID) and ");
   query.Append("  (@paymentMethodID is null or [PaymentMethodID] = @paymentMethodID) and ");
   query.Append("  (@shipperID is null or [ShipperID] = @shipperID)  ");
   query.Append(" order by ");
   query.Append(" [OrderID] ");
   query.Append(" offset @pageSize * (@pageNumber - 1) rows ");
   query.Append(" fetch next @pageSize rows only ");
   
   // Create parameters collection
   var parameters = new DynamicParameters();
   
   // Add parameters to collection
   parameters.Add("@pageSize", pageSize);
   parameters.Add("@pageNumber", pageNumber);
   parameters.Add("@currencyID", currencyID);
   parameters.Add("@customerID", customerID);
   parameters.Add("@employeeID", employeeID);
   parameters.Add("@orderStatusID", orderStatusID);
   parameters.Add("@paymentMethodID", paymentMethodID);
   parameters.Add("@shipperID", shipperID);
   
   // Retrieve result from database and convert to typed list
   return await Connection.QueryAsync<Order>(new CommandDefinition(query.ToString(), parameters));
  }

  public async Task<Order> GetOrderAsync(Order entity)
  {
   // Create string builder for query
   var query = new StringBuilder();
   
   // Create sql statement
   query.Append(" select ");
   query.Append("  [OrderID], ");
   query.Append("  [OrderStatusID], ");
   query.Append("  [CustomerID], ");
   query.Append("  [EmployeeID], ");
   query.Append("  [ShipperID], ");
   query.Append("  [OrderDate], ");
   query.Append("  [Total], ");
   query.Append("  [CurrencyID], ");
   query.Append("  [PaymentMethodID], ");
   query.Append("  [Comments], ");
   query.Append("  [CreationUser], ");
   query.Append("  [CreationDateTime], ");
   query.Append("  [LastUpdateUser], ");
   query.Append("  [LastUpdateDateTime], ");
   query.Append("  [Timestamp] ");
   query.Append(" from ");
   query.Append("  [Sales].[Order] ");
   query.Append(" where ");
   query.Append("  [OrderID] = @orderID ");
   
   // Create parameters collection
   var parameters = new DynamicParameters();
   
   // Add parameters to collection
   parameters.Add("orderID", entity.OrderID);
   
   // Retrieve result from database and convert to entity class
   return await Connection.QueryFirstOrDefaultAsync<Order>(query.ToString(), parameters);
  }

  public async Task<Int32> AddOrderAsync(Order entity)
  {
   // Create string builder for query
   var query = new StringBuilder();
   
   // Create sql statement
   query.Append(" insert into ");
   query.Append("  [Sales].[Order] ");
   query.Append("  ( ");
   query.Append("   [OrderStatusID], ");
   query.Append("   [CustomerID], ");
   query.Append("   [EmployeeID], ");
   query.Append("   [ShipperID], ");
   query.Append("   [OrderDate], ");
   query.Append("   [Total], ");
   query.Append("   [CurrencyID], ");
   query.Append("   [PaymentMethodID], ");
   query.Append("   [Comments], ");
   query.Append("   [CreationUser], ");
   query.Append("   [CreationDateTime] ");
   query.Append("  ) ");
   query.Append(" values ");
   query.Append(" ( ");
   query.Append("  @orderStatusID, ");
   query.Append("  @customerID, ");
   query.Append("  @employeeID, ");
   query.Append("  @shipperID, ");
   query.Append("  @orderDate, ");
   query.Append("  @total, ");
   query.Append("  @currencyID, ");
   query.Append("  @paymentMethodID, ");
   query.Append("  @comments, ");
   query.Append("  @creationUser, ");
   query.Append("  @creationDateTime ");
   query.Append(" ) ");
   query.Append("  select @orderID = @@identity ");
   
   // Create parameters collection
   var parameters = new DynamicParameters();
   
   // Add parameters to collection
   parameters.Add("orderStatusID", entity.OrderStatusID);
   parameters.Add("customerID", entity.CustomerID);
   parameters.Add("employeeID", entity.EmployeeID);
   parameters.Add("shipperID", entity.ShipperID);
   parameters.Add("orderDate", entity.OrderDate);
   parameters.Add("total", entity.Total);
   parameters.Add("currencyID", entity.CurrencyID);
   parameters.Add("paymentMethodID", entity.PaymentMethodID);
   parameters.Add("comments", entity.Comments);
   parameters.Add("creationUser", entity.CreationUser);
   parameters.Add("creationDateTime", entity.CreationDateTime);
   parameters.Add("orderID", dbType: DbType.Int64, direction: ParameterDirection.Output);
   
   // Execute query in database
   var affectedRows = await Connection.ExecuteAsync(new CommandDefinition(query.ToString(), parameters));
   
   // Retrieve value for output parameters
   entity.OrderID = parameters.Get<Int64?>("orderID");
   
   return affectedRows;
  }

  public async Task<Int32> UpdateOrderAsync(Order entity)
  {
   // Create string builder for query
   var query = new StringBuilder();
   
   // Create sql statement
   query.Append(" update ");
   query.Append("  [Sales].[Order] ");
   query.Append(" set ");
   query.Append("  [OrderStatusID] = @orderStatusID, ");
   query.Append("  [CustomerID] = @customerID, ");
   query.Append("  [EmployeeID] = @employeeID, ");
   query.Append("  [ShipperID] = @shipperID, ");
   query.Append("  [OrderDate] = @orderDate, ");
   query.Append("  [Total] = @total, ");
   query.Append("  [CurrencyID] = @currencyID, ");
   query.Append("  [PaymentMethodID] = @paymentMethodID, ");
   query.Append("  [Comments] = @comments, ");
   query.Append("  [LastUpdateUser] = @lastUpdateUser, ");
   query.Append("  [LastUpdateDateTime] = @lastUpdateDateTime ");
   query.Append(" where ");
   query.Append("  [OrderID] = @orderID ");
   
   // Create parameters collection
   var parameters = new DynamicParameters();
   
   // Add parameters to collection
   parameters.Add("orderStatusID", entity.OrderStatusID);
   parameters.Add("customerID", entity.CustomerID);
   parameters.Add("employeeID", entity.EmployeeID);
   parameters.Add("shipperID", entity.ShipperID);
   parameters.Add("orderDate", entity.OrderDate);
   parameters.Add("total", entity.Total);
   parameters.Add("currencyID", entity.CurrencyID);
   parameters.Add("paymentMethodID", entity.PaymentMethodID);
   parameters.Add("comments", entity.Comments);
   parameters.Add("lastUpdateUser", entity.LastUpdateUser);
   parameters.Add("lastUpdateDateTime", entity.LastUpdateDateTime);
   parameters.Add("orderID", entity.OrderID);
   
   // Execute query in database
   return await Connection.ExecuteAsync(new CommandDefinition(query.ToString(), parameters));
  }

  public async Task<Int32> RemoveOrderAsync(Order entity)
  {
   // Create string builder for query
   var query = new StringBuilder();
   
   // Create sql statement
   query.Append(" delete from ");
   query.Append("  [Sales].[Order] ");
   query.Append(" where ");
   query.Append("  [OrderID] = @orderID ");
   
   // Create parameters collection
   var parameters = new DynamicParameters();
   
   // Add parameters to collection
   parameters.Add("orderID", entity.OrderID);
   
   // Execute query in database
   return await Connection.ExecuteAsync(new CommandDefinition(query.ToString(), parameters));
  }
  
  // Methods for another entities operations

  public async Task<IEnumerable<UfnGetCustomerContact>> GetUfnGetCustomerContactsAsync(int customerID)
  {
   // Create string builder for query
   var query = new StringBuilder();
   
   // Create sql statement
   query.Append(" select ");
   query.Append("  [CustomerID], ");
   query.Append("  [AddressLine1], ");
   query.Append("  [AddressLine2], ");
   query.Append("  [City], ");
   query.Append("  [State], ");
   query.Append("  [ZipCode], ");
   query.Append("  [PhoneNumber] ");
   query.Append(" from ");
   query.Append("  [Sales].[ufnGetCustomerContact](@customerID) ");
   
   // Create parameters collection
   var parameters = new DynamicParameters();
   
   // Add parameters to collection
   parameters.Add("@customerID", customerID);
   
   // Retrieve result from database and convert to typed list
   return await Connection.QueryAsync<UfnGetCustomerContact>(query.ToString(), parameters);
  }
 }
}

Please take a look on all operations in repositories, all are async operations.

Points of Interest

  • CatFactory doesn't have command line for nuget because from my point of view it will be a big trouble to allow set values for all settings because we have a lot of settings for DapperProjectSettings, I think at this moment is more simple to create a console project to generate the code and then developer move generated files for existing project and make a code refactor if applies
  • CatFactory doesn't have UI now because at the beginning of this project .NET Core had no an standard UI, but we're working on UI for CatFactory maybe we'll choose Angular =^^=
  • Now we are focused on Entity Framework Core and Dapper but in future there will be Web API, Unit Tests and other things :)
  • We're working in continuous updates to provide better help for user

Related Links

Code Improvements

  • Enable use of stored procedures for repository
  • Add children for entity in get by key method (Order -> OrderDetail)
  • Handling concurrency for update and delete operations
  • Something else? Let me know your feedback in comments :)

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 =^^=

Behind CatFactory's Concept

History

  • 1st November, 2017: Initial version
  • 27th November, 2017: Addition of trivia and workshop
  • 8th February, 2018: Addition of project selections, database type map and event hanlders in workshop
  • 2nd May, 2018: Upgrade package version to beta
  • 11th July, 2018: Update on scaffolding code
  • 25th September, 2018: Update for code samples (CRUD) and Dapper project settings
  • 11th October, 2018: Addition of Scalar Functions and refactor for Article
  • 25th October, 2018: Addition of Packages list

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

HHerzl
Software Developer
United States United States
Full Stack Developer with Experience in ASP.NET Core and Angular

You may also be interested in...

Comments and Discussions

 
QuestionDepper is really old now, take time to set upp and really noe flexible Pin
Alen Toma20-Sep-18 17:59
memberAlen Toma20-Sep-18 17:59 
AnswerRe: Depper is really old now, take time to set upp and really noe flexible Pin
HHerzl23-Sep-18 21:02
memberHHerzl23-Sep-18 21:02 
QuestionScaffolding code Pin
Member 1300571731-May-18 2:45
memberMember 1300571731-May-18 2:45 
AnswerRe: Scaffolding code Pin
HHerzl18-Jun-18 5:13
memberHHerzl18-Jun-18 5:13 
GeneralRe: Scaffolding code Pin
Member 1300571718-Jun-18 19:43
memberMember 1300571718-Jun-18 19:43 
GeneralRe: Scaffolding code Pin
HHerzl18-Jun-18 21:20
memberHHerzl18-Jun-18 21:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web05-2016 | 2.8.181116.1 | Last Updated 25 Oct 2018
Article Copyright 2017 by HHerzl
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid