Simple T4 generation for Simple POCO [closed]

Question

I'm looking for a tutorial, guidance or software that can generate simple POCO's for some SQL Server tables for use in ASP.NET MVC. Something like this:

1) Keep a list of the table names in the SQL Server database that should have a POCO generated

2) Feed the list to some program

3) The program generates a simple POCO (more like DTO) in a single .cs file, or appends to a Poco.cs. Either way, it doesn't matter.

For example:

public class MyDto
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool? IsMale {get; set;}
}

4) Run the program whenever I want to re-generate the POCO's

The program could be WinForm, commandline, or something else. It doesn't matter.

Suzette

Answer 1

I have wanted to answer this but been busy.

I have created a simple example on how to read a database schema and generate classes and properties from this.

Basically you should be able to cutnpaste this into a TT file (see Oleg Sychs blog on how to get started), update the connection string and save to execute the template.

I don't claim that this is a complete sample but it could serve as a starting point for you:

  <#@ template   language    = "C#"                           #>
  <#@ assembly   name        = "Microsoft.CSharp"             #>
  <#@ assembly   name        = "System.Core"                  #>
  <#@ assembly   name        = "System.Data"                  #>
  <#@ import     namespace   = "System.Collections.Generic"   #>
  <#@ import     namespace   = "System.Dynamic"               #>
  <#@ import     namespace   = "System.Linq"                  #>
  <#@ import     namespace   = "System.Data.SqlClient"        #>

  <#
     var namespaceName    = "Poco2";
     // Update the connection string to something appropriate
     var connectionString = @"Data Source=localhost\SQLExpress;Initial Catalog=MyTest;Integrated Security=True";
  #>

  <#
     using (var db = new SqlConnection (connectionString))
     using (var cmd = db.CreateCommand ())
     {
        db.Open();
        var tables              = ReadRows (cmd, "SELECT * FROM sys.tables").ToArray ();

        var columns             = ReadRows (cmd, "SELECT * FROM sys.columns").ToLookup (k => k.object_id);

        var indexes             = ReadRows (cmd, "SELECT * FROM sys.indexes").ToLookup (k => k.object_id);
        var indexColumns        = ReadRows (cmd, "SELECT * FROM sys.index_columns").ToLookup (k => k.object_id);

        var foreignKeys         = ReadRows (cmd, "SELECT * FROM sys.foreign_keys").ToArray ();
        var foreignKeyColumns   = ReadRows (cmd, "SELECT * FROM sys.foreign_key_columns").ToArray ();
  #>
  namespace <#=namespaceName#>
  {
     using System;
     using System.Data.Linq.Mapping;

  <#
        foreach (var table in tables)
        {         
  #>
     [Table]
     partial class <#=table.name#>
     {
  <#
           IEnumerable<dynamic> tc = columns[table.object_id];
           var tableColumns = tc.OrderBy (r => r.column_id).ToArray ();          

           IEnumerable<dynamic> ti = indexes[table.object_id];
           var tableIndexes = ti.ToArray ();          

           var primaryKeyIndex = tableIndexes.FirstOrDefault (i => i.is_primary_key);
           var primaryKeyColumns = new Dictionary<dynamic, dynamic> ();
           if (primaryKeyIndex != null)
           {
              IEnumerable<dynamic> pc = indexColumns[table.object_id];
              primaryKeyColumns = pc
                 .Where (c => c.index_id == primaryKeyIndex.index_id)
                 .ToDictionary (c => c.column_id, c => c.key_ordinal)
                 ;
           }

           foreach (var tableColumn in tableColumns)
           {
              var type = MapToType (tableColumn.user_type_id, tableColumn.max_length, tableColumn.is_nullable);

  #>
        [Column (IsPrimaryKey = <#=primaryKeyColumns.ContainsKey (tableColumn.column_id) ? "true" : "false"#>)]
        public <#=type#> <#=tableColumn.name#> {get;set;}

  <#
           }
  #>

     }
  <#
        }
  #>
  }
  <#
     }
  #>

  <#+

     struct DataType
     {     
        public readonly int     SizeOf;
        public readonly string  SingularType;
        public readonly string  PluralType;

        public DataType (
           int sizeOf,
           string singularType,
           string pluralType = null
           )
        {
           SizeOf         = sizeOf;
           SingularType   = singularType;
           PluralType     = pluralType ?? (singularType + "[]");
        }

     }
     static Dictionary<int, DataType> dataTypes = new Dictionary<int, DataType>
        {
           {61   , new DataType (8,  "DateTime"            )},
           {127  , new DataType (8,  "long"                )},
           {165  , new DataType (1,  "byte"                )},
           {231  , new DataType (2,  "char"    ,  "string" )},
        };

     static string MapToType (int typeId, int maxLength, bool isNullable)
     {
        DataType dataType;

        if (dataTypes.TryGetValue (typeId, out dataType))
        {
           var length = maxLength > 0 ? (maxLength / dataType.SizeOf) : int.MaxValue;
           if (length > 1)
           {
              return dataType.PluralType;
           }
           else
           {
              return dataType.SingularType + (isNullable ? "?" : "");
           }
        }
        else
        {
           return "UnknownType_"+ typeId;
        }
     }

     static IEnumerable<dynamic> ReadRows (SqlCommand command, string sql)
     {
        command.CommandText = sql ?? "";

        using (var reader = command.ExecuteReader())
        {
           while (reader.Read())
           {
              var dyn = new ExpandoObject ();
              IDictionary<string, object> dic = dyn;

              for (var iter = 0; iter < reader.FieldCount; ++iter)
              {
                    dic[reader.GetName(iter) ?? ""] = reader.GetValue(iter);
              }

              yield return dyn;
           }

        }
     }


  #>

Formulaic

Worked for me after I added the missing data types I needed. Thanks. – Langill

Answer 2

I built a similar custom generation tool about 1 year ago, and I did not find a unified, comprehensive resource for accomplishing this goal.

Oleg Sych provides a number of blog posts about using T4 at his blog and maintains a library to help with T4 generation, T4 Toolbox. I relied heavily on both during development.

For database schema discovery in the T4 templates, I used SQL Server Management Objects.

I run my T4 templates from within Visual Studio.

Adorno

Answer 3

You can use the classes.tt template (in linqtemplates, plus SQLServer.ttinclude) in subsonic 3 (run in visual studio). http://subsonicproject.com/ The generated properties have backing variables, and some extra lines to support INotifyPropertyChanging and INotifyPropertyChanged, but you can edit the template to take out those lines.

Here is sample output and template:

public partial class Order
{
    public int OrderID { get; set; }
    public string CustomerID { get; set; }
    public int? EmployeeID { get; set; }
    public DateTime? OrderDate { get; set; }
    public DateTime? RequiredDate { get; set; }
    public DateTime? ShippedDate { get; set; }
    public int? ShipVia { get; set; }
    public decimal? Freight { get; set; }
    public string ShipName { get; set; }
    public string ShipAddress { get; set; }
    public string ShipCity { get; set; }
    public string ShipRegion { get; set; }
    public string ShipPostalCode { get; set; }
    public string ShipCountry { get; set; }
}


<#@ template language="C#" debug="False" hostspecific="True"  #>
<#@ output extension=".cs" #>
<#@ include file="SQLServer.ttinclude" #>
<#
    var tables = LoadTables();
#>
using System;
using System.ComponentModel;
using System.Linq;

namespace <#=Namespace#>
{


<#  foreach(Table tbl in tables){#>    

    /// <summary>
    /// A class which represents the <#=tbl.Name#> table in the <#=DatabaseName#> Database.
    /// This class is queryable through <#=DatabaseName#>DB.<#=tbl.ClassName#> 
    /// </summary>

    public partial class <#=tbl.ClassName#>
    {
        #region Properties

<#      foreach(var col in tbl.Columns){
            if (tbl.ClassName == col.CleanName)
            {
                col.CleanName += "X";
            }
#>
        public <#=col.SysType#><#=CheckNullable(col)#> <#=col.CleanName#> { get; set; }
<#      }#>

        #endregion

    }

<#}#>
}

Manana

Answer 4

St4bby at github. Open source. T4. Quite easy to read and manipulate as you wish.

Boarer