Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
 
(untagged)

Scaffolding Dapper with CatFactory

0.00/5 (No votes)
8 Dec 2019 1  
Scaffolding Dapper with CatFactory

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:

  1. Create Database Factory
  2. Import Database
  3. Create instance of Project (Entity Framework Core, Dapper, etc.)
  4. Build Features (One feature per schema)
  5. 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 the 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.GetClrType() == 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 OrderHeader 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.Selection("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 scaffolding and another one to handle an action after 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
};

Document Object Model

The most databases now have a document object model, this model provides a simple way to know the structure for database and objects.

In SQL Server there are views like sys.tables, sys.views, ys.columns that contain all information about these objects.

Also there are stored procedures and functions like sp_help and dm_exec_describe_first_result_set_for_object that retrieve information about a database object and columns for the result of execute an object.

In CatFactory, this model is available through extension methods for DbConnection class:

using (var connection = new SqlConnection("server=(local);database=OnlineStore;integrated security=yes;"))
{
	connection.Open();

	// Retrieve all tables defined in database
	var tables = connection.GetSysTables().ToList();	
}

These methods are available for SQL Server Document Object Model:

  • GetSysSchemas
  • GetSysTypes
  • GetSysTables
  • GetSysViews
  • GetSysColumns
  • GetSysSequences

Import Bag

Based on ViewBag concept from ASP MVC, this concept allows to add specific data for different databases providers.

ImportBag is a dynamic property, the CatFactory engine saves specific information in this property, for SQL Server saves extended properties, scalar functions, table functions, stored procedures and sequences.

For Postgre SQL saves sequences, in the future versions it will save another information related to database objects like stored procedures*.

Workshop

One of the 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 want 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 not 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 am wrong about this point, please let me know in the comments below.

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 having 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 solved 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 cleaner 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.

Background

Generate code is a common task in software developer, most developers write a "code generator" in their lives.

Using Entity Framework 6.x, I worked with EF wizard and it's a great tool even with limitations like:

  • Not scaffolding for Fluent API
  • Not scaffolding for Repositories
  • Not scaffolding for Unit of Work
  • Custom scaffolding is so complex or in some cases impossible

With Entity Framework Core, I worked with command line to scaffold from existing database, EF Core team provided a great tool with command line but there are still the same limitations above.

So, CatFactory pretends to solve those limitations and provide a simple way to scaffold Entity Framework Core.

StringBuilder was used to scaffold a class or interface in older versions of CatFactory but some years ago, there was a change about how to scaffold a definition (class or interface), CatFactory allows to define the structure for class or interface in a simple and clear way, then use an instance of CodeBuilder to scaffold in C#.

Let's start with scaffold a class in C#:

var definition = new CSharpClassDefinition
{
    Namespace = "OnlineStore.DomainDrivenDesign",
    AccessModifier = AccessModifier.Public,
    Name = "StockItem",
    Properties =
    {
        new PropertyDefinition(AccessModifier.Public, "string", "GivenName")
        {
            IsAutomatic = true
        },
        new PropertyDefinition(AccessModifier.Public, "string", "MiddleName")
        {
            IsAutomatic = true
        },
        new PropertyDefinition(AccessModifier.Public, "string", "Surname")
        {
            IsAutomatic = true
        },
        new PropertyDefinition(AccessModifier.Public, "string", "FullName")
        {
            IsReadOnly = true,
            GetBody =
            {
                new CodeLine(" return GivenName + (string.IsNullOrEmpty
                  (MiddleName) ? \"\" : \" \" + MiddleName) + \" \" + Surname)")
            }
        }
    }
};

CSharpCodeBuilder.CreateFiles("C:\\Temp", string.Empty, true, definition);

This is the output code:

namespace OnlineStore.DomainDrivenDesign
{
	public class StockItem
	{
		public string GivenName { get; set; }

		public string MiddleName { get; set; }

		public string Surname { get; set; }

		public string FullName
			=> GivenName + (string.IsNullOrEmpty(MiddleName) ? "" : " " + 
                            MiddleName) + " " + Surname;

	}
}

To create an object definition like class or interface, these types can be used:

  • EventDefinition
  • FieldDefinition
  • ClassConstructorDefinition
  • FinalizerDefinition
  • IndexerDefinition
  • PropertyDefinition
  • MethodDefinition

Types like ClassConstructorDefinition, FinalizerDefinition, IndexerDefinition, PropertyDefinition and MethodDefinition can have code blocks, these blocks are arrays of ILine.

ILine interface allows to represent a code line inside of code block, there are different types for lines:

  1. CodeLine
  2. CommentLine
  3. EmptyLine
  4. PreprocessorDirectiveLine
  5. ReturnLine
  6. TodoLine

Let's create a class with methods:

var classDefinition = new CSharpClassDefinition
{
 Namespace = "OnlineStore.BusinessLayer",
 AccessModifier = AccessModifier.Public,
 Name = "WarehouseService",
 Fields =
 {
  new FieldDefinition("OnlineStoreDbContext", "DbContext")
  {
   IsReadOnly = true
  }
 },
 Constructors =
 {
  new ClassConstructorDefinition
  {
   AccessModifier = AccessModifier.Public,
   Parameters =
   {
    new ParameterDefinition("OnlineStoreDbContext", "dbContext")
   },
   Lines =
   {
    new CodeLine("DbContext = dbContext;")
   }
  }
 },
 Methods =
 {
  new MethodDefinition
  {
   AccessModifier = AccessModifier.Public,
   Type = "IListResponse<StockItem>",
   Name = "GetStockItems",
   Lines =
   {
    new TodoLine(" Add filters"),
    new CodeLine("return DbContext.StockItems.ToList();")
   }
  }
 }
};

CSharpCodeBuilder.CreateFiles("C:\\Temp", string.Empty, true, definition);

This is the output code:

namespace OnlineStore.BusinessLayer
{
 public class WarehouseService
 {
  private readonly OnlineStoreDbContext DbContext;

  public WarehouseService(OnlineStoreDbContext dbContext)
  {
   DbContext = dbContext;
  }

  public IListResponse<StockItem> GetStockItems()
  {
   // todo:  Add filters
   return DbContext.StockItems.ToList();
  }
 }
}

Now let's refact an interface from class:

var interfaceDefinition = classDefinition.RefactInterface();

CSharpCodeBuilder.CreateFiles(@"C:\Temp", string.Empty, true, interfaceDefinition);

This is the output code:

public interface IWarehouseService
{
	IListResponse<StockItem> GetStockItems();
}

I know some developers can reject this design alleging there is a lot of code to scaffold a simple class with 4 properties but keep in mind CatFactory's way looks like a "clear" transcription of definitions.

CatFactory.NetCore uses the model from CatFactory to allow scaffold C# code, so the question is: What is CatFactory.Dapper package?

Is a package that allows to scaffold Dapper using scaffolding engine provided by CatFactory.

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

Take a look for sample database to understand each component in architecture. In this database, there are 4 schemas: Dbo, HumanResources, Warehouse and Sales.

Each schema represents a division on store company, keep this in mind because all code is designed following this aspect; at this moment, this code only implements features for Production and Sales schemas.

All tables have a primary key with one column and have columns for creation, last update and concurrency token.

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 find the scripts for database in this link: Online Store Database Scripts on GitHub.

Please remember: This is a sample database, only for demonstration of concepts.

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 suffix that indicates it's a project to scaffold, for example: OnLineStore.CatFactory.Dapper.

Add the following packages for your project:

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

Save all changes and build the project.

Step 03 - Add Code To Scaffold

Add this code in Main method:

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

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

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

// 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.Selection("Sales.OrderHeader", 
                   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 at 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 contain 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

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 other 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 choice.

Get All

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

Get by Key

// Create connection for SQL Server
using (var connection = new SqlConnection("server=(local);
       database=OnlineStore;integrated security=yes;"))
{
 // Create repository instance
 var repository = new SalesRepository(connection);
 
 // Get entity by id
 var entity = await repository.GetOrderHeaderAsync(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=OnlineStore;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=OnlineStore;integrated security=yes;"))
{
 // Create repository instance
 var repository = new SalesRepository(connection);
 
 // Create instance for entity
 var entity = new OrderHeader();
  
 // Set values for properties
 // e.g. entity.Total = 29.99m;
  
 // Add entity in database
 await repository.AddOrderHeaderAsync(entity);
}

Update:

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

Remove:

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

Table Function:

// Create connection for SQL Server
using (var connection = new SqlConnection("server=(local);
       database=OnlineStore;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 Description
Dapper Latest version Provides helper methods for database operations

How All Code Works 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) that 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 definitions.

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

Code Review

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

Code for OrderHeader class:

using System;

namespace OnlineStore.Core.EntityLayer.Sales
{
 public class OrderHeader : IEntity
 {
  public OrderHeader()
  {
  }

  public OrderHeader(long? orderHeaderID)
  {
   OrderHeaderID = orderHeaderID;
  }

  public long? OrderHeaderID { get; set; }

  public short? OrderStatusID { get; set; }

  public int? CustomerID { get; set; }

  public int? EmployeeID { get; set; }

  public int? ShipperID { get; set; }

  public DateTime? OrderDate { get; set; }

  public decimal? Total { get; set; }

  public string CurrencyID { get; set; }

  public Guid? PaymentMethodID { get; set; }

  public int? DetailsCount { get; set; }

  public long? ReferenceOrderID { 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 OnlineStore.Core.EntityLayer;
using OnlineStore.Core.DataLayer.Contracts;
using OnlineStore.Core.EntityLayer.HumanResources;
using OnlineStore.Core.EntityLayer.Sales;
using OnlineStore.Core.EntityLayer.Warehouse;

namespace OnlineStore.Core.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(Int32? employeeID = null);
 }
}

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 OnlineStore.Core.EntityLayer;
using OnlineStore.Core.DataLayer.Contracts;
using OnlineStore.Core.EntityLayer.HumanResources;
using OnlineStore.Core.EntityLayer.Sales;
using OnlineStore.Core.EntityLayer.Warehouse;

namespace OnlineStore.Core.DataLayer.Contracts
{
 public interface ISalesRepository : IRepository
 {
  Task<IEnumerable<Customer>> GetCustomersAsync(int pageSize = 10, int pageNumber = 1);

  Task<Customer> GetCustomerAsync(Customer entity);

  Task<Int32> AddCustomerAsync(Customer entity);

  Task<Int32> UpdateCustomerAsync(Customer entity);

  Task<Int32> RemoveCustomerAsync(Customer entity);

  Task<IEnumerable<CustomerAddress>> GetCustomerAddressesAsync
      (int pageSize = 10, int pageNumber = 1, 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
      (int pageSize = 10, int pageNumber = 1, 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<OrderDetail>> GetOrderDetailsAsync
        (int pageSize = 10, int pageNumber = 1, Int64? orderHeaderID = null, 
         Int32? productID = null);

  Task<OrderDetail> GetOrderDetailAsync(OrderDetail entity);

  Task<OrderDetail> GetOrderDetailByOrderHeaderIDAndProductIDAsync(OrderDetail entity);

  Task<Int32> AddOrderDetailAsync(OrderDetail entity);

  Task<Int32> UpdateOrderDetailAsync(OrderDetail entity);

  Task<Int32> RemoveOrderDetailAsync(OrderDetail entity);

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

  Task<OrderHeader> GetOrderHeaderAsync(OrderHeader entity);

  Task<Int32> AddOrderHeaderAsync(OrderHeader entity);

  Task<Int32> UpdateOrderHeaderAsync(OrderHeader entity);

  Task<Int32> RemoveOrderHeaderAsync(OrderHeader entity);

  Task<IEnumerable<OrderStatus>> 
       GetOrderStatusesAsync(int pageSize = 10, int pageNumber = 1);

  Task<OrderStatus> GetOrderStatusAsync(OrderStatus entity);

  Task<Int32> AddOrderStatusAsync(OrderStatus entity);

  Task<Int32> UpdateOrderStatusAsync(OrderStatus entity);

  Task<Int32> RemoveOrderStatusAsync(OrderStatus entity);

  Task<IEnumerable<PaymentMethod>> 
          GetPaymentMethodsAsync(int pageSize = 10, int pageNumber = 1);

  Task<PaymentMethod> GetPaymentMethodAsync(PaymentMethod entity);

  Task<Int32> AddPaymentMethodAsync(PaymentMethod entity);

  Task<Int32> UpdatePaymentMethodAsync(PaymentMethod entity);

  Task<Int32> RemovePaymentMethodAsync(PaymentMethod entity);

  Task<IEnumerable<Shipper>> GetShippersAsync(int pageSize = 10, int pageNumber = 1);

  Task<Shipper> GetShipperAsync(Shipper entity);

  Task<Int32> AddShipperAsync(Shipper entity);

  Task<Int32> UpdateShipperAsync(Shipper entity);

  Task<Int32> RemoveShipperAsync(Shipper entity);

  Task<IEnumerable<OrderSummary>> 
          GetOrderSummariesAsync(int pageSize = 10, int pageNumber = 1, 
          Int64? orderHeaderID = null, Int16? orderStatusID = null, 
          Int32? customerID = null, Int32? employeeID = null, Int32? shipperID = null);

  Task<IEnumerable<UfnGetCustomerContactResult>> 
               GetUfnGetCustomerContactsAsync(Int32? customerID);
 }
}

Code for Repository class:

using System.Data;
using OnlineStore.Core.EntityLayer;

namespace OnlineStore.Core.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 OnlineStore.Core.EntityLayer;
using OnlineStore.Core.DataLayer.Contracts;
using OnlineStore.Core.EntityLayer.HumanResources;
using OnlineStore.Core.EntityLayer.Sales;
using OnlineStore.Core.EntityLayer.Warehouse;

namespace OnlineStore.Core.DataLayer.Repositories
{
 public class HumanResourcesRepository : Repository, IHumanResourcesRepository
 {
  public HumanResourcesRepository(IDbConnection connection)
   : base(connection)
  {
  }
  
  // Another operations

  public async Task<IEnumerable<EmployeeInfo>> 
            GetEmployeeInfosAsync(Int32? employeeID = null)
  {
   // Create string builder for query
   var query = new StringBuilder();
   
   // Create sql statement
   query.Append(" select ");
   query.Append("  [EmployeeID], ");
   query.Append("  [EmployeeName], ");
   query.Append("  [EmployeeAddresses], ");
   query.Append("  [EmployeeEmails] ");
   query.Append(" from ");
   query.Append("  [HumanResources].[EmployeeInfo] ");
   query.Append(" where ");
   query.Append("  (@employeeID is null or [EmployeeID] = @employeeID) and ");
   
   // Create parameters collection
   var parameters = new DynamicParameters();
   
   // Add parameters to collection
   parameters.Add("@employeeID", employeeID);
   
   // Retrieve result from database and convert to typed list
   return await Connection.QueryAsync<EmployeeInfo>(query.ToString());
  }
 }
}

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 OnlineStore.Core.EntityLayer;
using OnlineStore.Core.DataLayer.Contracts;
using OnlineStore.Core.EntityLayer.HumanResources;
using OnlineStore.Core.EntityLayer.Sales;
using OnlineStore.Core.EntityLayer.Warehouse;

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

  // Another operations

  public async Task<IEnumerable<OrderHeader>> 
           GetOrderHeadersAsync(int pageSize = 10, int pageNumber = 1, 
           String 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("  [OrderHeaderID], ");
   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("  [DetailsCount], ");
   query.Append("  [ReferenceOrderID], ");
   query.Append("  [Comments], ");
   query.Append("  [CreationUser], ");
   query.Append("  [CreationDateTime], ");
   query.Append("  [LastUpdateUser], ");
   query.Append("  [LastUpdateDateTime], ");
   query.Append("  [Timestamp] ");
   query.Append(" from ");
   query.Append("  [Sales].[OrderHeader] ");
   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(" [OrderHeaderID] ");
   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<OrderHeader>
            (new CommandDefinition(query.ToString(), parameters));
  }

  public async Task<OrderHeader> GetOrderHeaderAsync(OrderHeader entity)
  {
   // Create string builder for query
   var query = new StringBuilder();
   
   // Create sql statement
   query.Append(" select ");
   query.Append("  [OrderHeaderID], ");
   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("  [DetailsCount], ");
   query.Append("  [ReferenceOrderID], ");
   query.Append("  [Comments], ");
   query.Append("  [CreationUser], ");
   query.Append("  [CreationDateTime], ");
   query.Append("  [LastUpdateUser], ");
   query.Append("  [LastUpdateDateTime], ");
   query.Append("  [Timestamp] ");
   query.Append(" from ");
   query.Append("  [Sales].[OrderHeader] ");
   query.Append(" where ");
   query.Append("  [OrderHeaderID] = @orderHeaderID ");
   
   // Create parameters collection
   var parameters = new DynamicParameters();
   
   // Add parameters to collection
   parameters.Add("orderHeaderID", entity.OrderHeaderID);
   
   // Retrieve result from database and convert to entity class
   return await Connection.QueryFirstOrDefaultAsync<OrderHeader>
                 (query.ToString(), parameters);
  }

  public async Task<Int32> AddOrderHeaderAsync(OrderHeader entity)
  {
   // Create string builder for query
   var query = new StringBuilder();
   
   // Create sql statement
   query.Append(" insert into ");
   query.Append("  [Sales].[OrderHeader] ");
   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("   [DetailsCount], ");
   query.Append("   [ReferenceOrderID], ");
   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("  @detailsCount, ");
   query.Append("  @referenceOrderID, ");
   query.Append("  @comments, ");
   query.Append("  @creationUser, ");
   query.Append("  @creationDateTime ");
   query.Append(" ) ");
   query.Append("  select @orderHeaderID = @@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("detailsCount", entity.DetailsCount);
   parameters.Add("referenceOrderID", entity.ReferenceOrderID);
   parameters.Add("comments", entity.Comments);
   parameters.Add("creationUser", entity.CreationUser);
   parameters.Add("creationDateTime", entity.CreationDateTime);
   parameters.Add("orderHeaderID", 
                   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.OrderHeaderID = parameters.Get<Int64?>("orderHeaderID");
   
   return affectedRows;
  }

  public async Task<Int32> UpdateOrderHeaderAsync(OrderHeader entity)
  {
   // Create string builder for query
   var query = new StringBuilder();
   
   // Create sql statement
   query.Append(" update ");
   query.Append("  [Sales].[OrderHeader] ");
   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("  [DetailsCount] = @detailsCount, ");
   query.Append("  [ReferenceOrderID] = @referenceOrderID, ");
   query.Append("  [Comments] = @comments, ");
   query.Append("  [LastUpdateUser] = @lastUpdateUser, ");
   query.Append("  [LastUpdateDateTime] = @lastUpdateDateTime ");
   query.Append(" where ");
   query.Append("  [OrderHeaderID] = @orderHeaderID ");
   
   // 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("detailsCount", entity.DetailsCount);
   parameters.Add("referenceOrderID", entity.ReferenceOrderID);
   parameters.Add("comments", entity.Comments);
   parameters.Add("lastUpdateUser", entity.LastUpdateUser);
   parameters.Add("lastUpdateDateTime", entity.LastUpdateDateTime);
   parameters.Add("orderHeaderID", entity.OrderHeaderID);
   
   // Execute query in database
   return await Connection.ExecuteAsync
           (new CommandDefinition(query.ToString(), parameters));
  }

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

  public async Task<IEnumerable<OrderSummary>> 
               GetOrderSummariesAsync(int pageSize = 10, int pageNumber = 1, 
               Int64? orderHeaderID = null, Int16? orderStatusID = null, 
               Int32? customerID = null, Int32? employeeID = null, Int32? shipperID = null)
  {
   // Create string builder for query
   var query = new StringBuilder();
   
   // Create sql statement
   query.Append(" select ");
   query.Append("  [OrderHeaderID], ");
   query.Append("  [OrderStatusID], ");
   query.Append("  [CustomerID], ");
   query.Append("  [CustomerName], ");
   query.Append("  [EmployeeID], ");
   query.Append("  [EmployeeName], ");
   query.Append("  [ShipperID], ");
   query.Append("  [ShipperName], ");
   query.Append("  [OrderDate], ");
   query.Append("  [Total], ");
   query.Append("  [CurrencyName], ");
   query.Append("  [PaymentMethodName], ");
   query.Append("  [DetailsCount] ");
   query.Append(" from ");
   query.Append("  [Sales].[OrderSummary] ");
   query.Append(" where ");
   query.Append("  (@orderHeaderID is null or [OrderHeaderID] = @orderHeaderID) and ");
   query.Append("  (@orderStatusID is null or [OrderStatusID] = @orderStatusID) and ");
   query.Append("  (@customerID is null or [CustomerID] = @customerID) and ");
   query.Append("  (@employeeID is null or [EmployeeID] = @employeeID) and ");
   query.Append("  (@shipperID is null or [ShipperID] = @shipperID) and ");
   query.Append(" order by ");
   query.Append(" [OrderHeaderID], _
                  [OrderStatusID], [CustomerID], [EmployeeID], [ShipperID] ");
   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("@orderHeaderID", orderHeaderID);
   parameters.Add("@orderStatusID", orderStatusID);
   parameters.Add("@customerID", customerID);
   parameters.Add("@employeeID", employeeID);
   parameters.Add("@shipperID", shipperID);
   
   // Retrieve result from database and convert to typed list
   return await Connection.QueryAsync<OrderSummary>(query.ToString());
  }

  public async Task<IEnumerable<UfnGetCustomerContactResult>> 
                     GetUfnGetCustomerContactsAsync(Int32? 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
             <UfnGetCustomerContactResult>(query.ToString(), parameters);
  }
 }
}

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 to set values for all settings because we have a lot of settings for DapperProjectSettings. I think at this moment, it is simpler to create a console project to generate the code and then developer moves generated files for existing project and makes a code refactor if applies.
  • CatFactory doesn't have UI now because at the beginning of this project, .NET Core had no 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 on continuous updates to provide better help for users.

Related Links

Code Improvements

  • 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 the 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 handlers 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
  • 23th December, 2018: Database Update
  • 12th May, 2019: Expand Scaffolding Engine

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here



Permalink

Privacy
Cookies
Terms of Use
Layout: fixed | fluid

Article Copyright 2017 by HHerzl
Everything else Copyright © CodeProject, 1999-2024

Web01 2.8:2024-10-20:1