Concepts behind CatFactory

Concepts behind CatFactory

Database Type Map
Project Selection
Event Handlers to Scaffold
Database Object Model
Import Bag

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.

This concept was created from this matrix: SQL Server Data Type Mappings.

Using this matrix as reference, now CatFactory has a class named DatabaseTypeMap. Database class contains a property with all mappings named DatebaseTypeMaps, so this property is filled by Import feature for SQL Server package.

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

DatabaseTypeMap is the class to represent database type definition, for database instance we need to create a collection of DatabaseTypeMap class to have a matrix to resolve data types.

Suppose there is a class with name DatabaseTypeMapList, this class has a property to get data types. Once we have imported an existing database we can resolve data types:

Resolve without extension methods:

// Get mappings
var dataTypes = database.DatabaseTypeMaps;

// Resolve CLR type
var mapsForString = dataTypes.Where(item => item.ClrType == typeof(string)).ToList();

// Resolve SQL Server type
var mapForVarchar = dataTypes.FirstOrDefault(item => item.DatabaseType == "varchar");

Resolve with extension methods:

// Get database type
var varcharDataType = database.ResolveType("varchar");

// Resolve CLR
var mapForVarchar = varcharDataType.GetClrType();

SQL Server allows to define data types, suppose the database instance has a data type defined by user with name Flag, Flag data type is a bit, bool in C#.

Import method retrieve user data types, so in DatabaseTypeMaps collection we can search the parent data type for Flag:

Project Selection

A project selection is a limit to apply settings for objects that match with pattern.

GlobalSelection is the default selection for project, contains a default instance of settings.

Patterns:

Pattern	Scope
Sales.OrderHeader	Applies for specific object with name Sales.OrderHeader
Sales.*	Applies for all objects inside of Sales schema
*.OrderHeader	Applies for all objects with name Order with no matter schema
*.*	Applies for all objects, this is the global selection

Sample:

// Apply settings for Project
project.GlobalSelection(settings =>
{
    settings.ForceOverwrite = true;
    settings.AuditEntity = new AuditEntity("CreationUser", "CreationDateTime", "LastUpdateUser", "LastUpdateDateTime");
    settings.ConcurrencyToken = "Timestamp";
});

// Apply settings for specific object
project.Select("Sales.OrderHeader", settings => settings.EntitiesWithDataContracts = true);

Event Handlers to Scaffold

In order to provide a more flexible way to scaffold, there are two delegates in CatFactory, one to perform an action before of scaffolding and another one to handle and action after of scaffolding.

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

Database Object Model

The most databases now have a database 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, sys.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:

Tables

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();	
}

Views

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

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

Columns

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

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

These methods are available for SQL Server Document Object Model:

GetSysSchemas
GetSysTypes
GetSysTables
GetSysViews
GetSysColumns
GetSysSequencesAsync

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.