Using T4 Templates to Generate multiple classes based on POCO

Question

Im looking for a way to increase productivity when I add a table to my database. Generally, when I add a new table I have to perform the following steps.

1. Add table to the database (simple)
2. Create the corresponding EF Code First class. (i dont use db migrations)
3. Create a POCO model that matches the EF class created in #2.
4. Create repository class
5. Create Commands and Handlers for CQRS pattern
6. Create AutoMapper maps for the newly created classes

I recently created a new website where the requirements were to use EF Database first and I saw how it was using the tt files to generate the classes. That got me thinking that I could somehow use those templates (new ones) to generate all the standard support items for basic CRUD operations. Trouble is I have no experience creating these templates and have no idea where to start.

Sample Code to be generated:

Repository

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

public interface IUsersRepository : IRepository<Users>
{
}

public class UsersRepository : RepositoryBase<Users>, IUsersRepository
{
    public UsersRepository(IDatabaseFactory databaseFactory)
        : base(databaseFactory)
    {
    }

}

Basic model based on entity generated from EDMX (or Code First)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

public class User 
{
    public int UserId { get; set; }
    public string UserRole { get; set; }
    public string UserName { get; set; }
}

Command

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

public class AddUpdateUserPayoutCommand : CommandBase, ICommand
{
    public int UserId { get; set; }
    public string UserRole { get; set; }
    public string UserName { get; set; }
}

Command Handler

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

public class AddUpdateUserHandler: ICommandHandler<AddUpdateUserCommand>
{
    private readonly IUsersRepository _repository;
    private readonly IUnitOfWork _unitOfWork;
    public AddUpdateUserPayoutHandler(IUsersRepository repository, IUnitOfWork unitOfWork)
    {
        _repository = repository;
        _unitOfWork = unitOfWork;
    }
    public ICommandResult Execute(AddUpdateUserCommand command)
    {
        Users entity;
        if (command.UserId == 0)
        {
            entity = AutoMapper.Mapper.Map<Users>(command);
            _repository.Add(entity);
        }
        else
        {
            entity = _repository.Get(x=>x.UserId==command.UserId);
            entity = AutoMapper.Mapper.Map<Users>(command);
            _repository.Update(entity);
        }
        _unitOfWork.Commit(command.UserId);         

        return new CommandResult(true,entity.UserId);
    }
}

Automapper Maps - Placed in app_start

Mapper.CreateMap<User, AddUpdateUserCommand>();

Answer 1

This example isn't meant to be a solution that one can cutnpaste into a project but as an example on how one could write a template that generate code from database schema.

Built a quick template to demonstrate how you might go about to generate the code artifacts.

You can find the whole project here: https://github.com/mrange/CodeStack/tree/master/q18787460/ModelGenerator

The template itself use T4Include.Schema to get the db schema. SMO is also completely legit to use as well, I just prefer T4Include.Schema because of the performance and that it only relies SqlConnection (T4Include.Schema is part of https://www.nuget.org/packages/T4IncludeTemplate/).

The basic strategy of the template is to get hold of all tables and iterate over them generating the code artifact.

<#@ include file="$(SolutionDir)\packages\T4IncludeTemplate.1.0.3\T4\Schema.ttinclude"#>

<#

    // The namespace surrounding the code
    var namespaceName               = "ModelGenerator";
    var connectionString            = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=TestDB;Integrated Security=True";
    var schema                      = GetSchema (connectionString);
    Func<string, string> justify    = s => LeftJustify (s, 40);

    var tables                      = schema
        .SchemaObjects
        .Where (so => so.Type == SchemaObject.SchemaObjectType.Table)
        .ToArray ()
        ;
#>

namespace <#=namespaceName#>
{
<#
    foreach (var table in tables)
    {
#>
    /// <summary>
    /// Repository interface for <#=table.Name#>
    /// </summary>
    partial interface I<#=table.Name#>Repository : IRepository<<#=table.Name#>>
    {
    }

    /// <summary>
    /// Repository class for <#=table.Name#>
    /// </summary>
    partial class <#=table.Name#>Repository : RepositoryBase<<#=table.Name#>>, I<#=table.Name#>Repository
    {
    }

    /// <summary>
    /// Poco class for <#=table.Name#>
    /// </summary>
    partial class <#=table.Name#>
    {
<#
    foreach (var column in table.Columns)
    {
#>
        public <#=justify (column.CsTypeName)#> <#=justify (column.Name)#>{ get; set; }
<#
    }
#>
    } 

    /// <summary>
    /// Command class for <#=table.Name#>
    /// </summary>
    partial class <#=table.Name#>Command : CommandBase, ICommand
    {
<#
    foreach (var column in table.Columns)
    {
#>
        public <#=justify (column.CsTypeName)#> <#=justify (column.Name)#> { get; set; }
<#
    }
#> 
    }

    /// <summary>
    /// Command handler class for <#=table.Name#>
    /// </summary>
    partial class <#=table.Name#>CommandHandler : ICommandHandler<<#=table.Name#>Command>
    {
        private readonly IUsersRepository _repository;
        private readonly IUnitOfWork _unitOfWork;
        public <#=table.Name#>CommandHandler(IUsersRepository repository, IUnitOfWork unitOfWork)
        {
            _repository = repository;
            _unitOfWork = unitOfWork;
        }

        public ICommandResult Execute(<#=table.Name#>Command command)
        {
            <#=table.Name#> entity;

<#
    var identityColumn = table.Columns.FirstOrDefault (c => c.IsIdentity);
    if (identityColumn == null)
    {
#>
@@@ ERROR__NO_IDENTITY_COLUMN_FOUND_FOR: <#=table.FullName#>
<#
    }
    else
    {
#>
            if (command.<#=identityColumn.Name#> == 0)
            {
                entity = AutoMapper.Mapper.Map<<#=table.Name#>>(command);
                _repository.Add(entity);
            }
            else
            {
                entity = _repository.Get(x=>x.UserId==command.<#=identityColumn.Name#>);
                entity = AutoMapper.Mapper.Map<<#=table.Name#>>(command);
                _repository.Update(entity);
            }
            _unitOfWork.Commit(command.<#=identityColumn.Name#>);         

            return new CommandResult(true,entity.<#=identityColumn.Name#>);
<#
    }
#>
        }
    }
<#
    }
#>
}

<#+

    static Schema GetSchema (string connectionString) 
    {
        using (var connection = new SqlConnection (connectionString))
        {
            connection.Open ();

            return new Schema (connection);
        }
    }


#>

Finally the generated code looks like this (for my test db that only has one table: CUS_Customer)

// ############################################################################
// #                                                                          #
// #        ---==>  T H I S  F I L E  I S   G E N E R A T E D  <==---         #
// #                                                                          #
// # This means that any edits to the .cs file will be lost when its          #
// # regenerated. Changes should instead be applied to the corresponding      #
// # template file (.tt)                                                      #
// ############################################################################











namespace ModelGenerator
{
    /// <summary>
    /// Repository interface for CUS_Customer
    /// </summary>
    partial interface ICUS_CustomerRepository : IRepository<CUS_Customer>
    {
    }

    /// <summary>
    /// Repository class for CUS_Customer
    /// </summary>
    partial class CUS_CustomerRepository : RepositoryBase<CUS_Customer>, ICUS_CustomerRepository
    {
    }

    /// <summary>
    /// Poco class for CUS_Customer
    /// </summary>
    partial class CUS_Customer
    {
        public System.Int64                             CUS_ID                                  { get; set; }
        public System.String                            CUS_FirstName                           { get; set; }
        public System.String                            CUS_LastName                            { get; set; }
        public System.DateTime                          CUS_Born                                { get; set; }
        public System.DateTime                          CUS_Created                             { get; set; }
    } 

    /// <summary>
    /// Command class for CUS_Customer
    /// </summary>
    partial class CUS_CustomerCommand : CommandBase, ICommand
    {
        public System.Int64                             CUS_ID                                   { get; set; }
        public System.String                            CUS_FirstName                            { get; set; }
        public System.String                            CUS_LastName                             { get; set; }
        public System.DateTime                          CUS_Born                                 { get; set; }
        public System.DateTime                          CUS_Created                              { get; set; }

    }

    /// <summary>
    /// Command handler class for CUS_Customer
    /// </summary>
    partial class CUS_CustomerCommandHandler : ICommandHandler<CUS_CustomerCommand>
    {
        private readonly IUsersRepository _repository;
        private readonly IUnitOfWork _unitOfWork;
        public CUS_CustomerCommandHandler(IUsersRepository repository, IUnitOfWork unitOfWork)
        {
            _repository = repository;
            _unitOfWork = unitOfWork;
        }

        public ICommandResult Execute(CUS_CustomerCommand command)
        {
            CUS_Customer entity;

            if (command.CUS_ID == 0)
            {
                entity = AutoMapper.Mapper.Map<CUS_Customer>(command);
                _repository.Add(entity);
            }
            else
            {
                entity = _repository.Get(x=>x.UserId==command.CUS_ID);
                entity = AutoMapper.Mapper.Map<CUS_Customer>(command);
                _repository.Update(entity);
            }
            _unitOfWork.Commit(command.CUS_ID);         

            return new CommandResult(true,entity.CUS_ID);
        }
    }
}

If you pull the project from github and update the connection string to something that is relevant to you it should generate the code for you. If you run into any issues just respond to this post.