Subsonic 3.0 and large SQL Database

Question

Is there a way to limit the code generated to specific tables in a database? My database has a few hundred tables and I only really want to use SubSonic on a handfull of them.

Answer 1

You can modify the T4 templates to just limit the function call if the table name matches one of the tables you're interested in. Right now, it just checks for ExcludedTables. It looks like it would be pretty trivial to just add a whitelist as well. Try this.

In Settings.ttinclude, copy the

string[] ExcludeTables = new string[]{
"sysdiagrams",
"BuildVersion",
};

declaration, and paste a new array declaration called "IncludeTables". Add your table names to it. Then in Structs.tt, ActiveRecord.tt and Context.tt, do a search for "ExcludeTables". Wherever you find it, add in a check for your included tables. So change,

if(!ExcludeTables.Contains(tbl.Name))

to

if(!ExcludeTables.Contains(tbl.Name) 
    && (IncludeTables.Length == 0 || IncludeTables.Contains(tbl.Name))

That should get you started.

Answer 2

Hi, Warren, I also met the same situation as you. You can try my solution. My solution is one table one T4 template.

What you should do is :

1. In SQLServer.ttinclude, replace LoadTables method with the code below:

List LoadTables(params string[] tables) { var result=new List();

string sql = TABLE_SQL;
if(tables.Length > 0)
{
  StringBuilder sb = new StringBuilder();
  foreach(string table in tables){
    sb.AppendFormat("'{0}',", table);
  }
  sql += " and TABLE_NAME in (" + sb.Remove(sb.Length - 1, 1).ToString() + ")";
}

//pull the tables in a reader
using(IDataReader rdr=GetReader(sql))
{
    while(rdr.Read())
    {
        Table tbl=new Table();
        tbl.Name=rdr["TABLE_NAME"].ToString();
        tbl.Schema=rdr["TABLE_SCHEMA"].ToString();
        tbl.Columns=LoadColumns(tbl);
        tbl.PrimaryKey=GetPK(tbl.Name);
        tbl.CleanName=CleanUp(tbl.Name);
        tbl.ClassName=Inflector.MakeSingular(tbl.CleanName);
        tbl.QueryableName=Inflector.MakePlural(tbl.ClassName);

        //set the PK for the columns
        var pkColumn=tbl.Columns.SingleOrDefault(x=>x.Name.ToLower().Trim()==tbl.PrimaryKey.ToLower().Trim());
        if(pkColumn!=null)
            pkColumn.IsPK=true;

        tbl.FKTables=LoadFKTables(tbl.Name);

        result.Add(tbl);
    }
}

foreach(Table tbl in result)
{
    //loop the FK tables and see if there's a match for our FK columns
    foreach(Column col in tbl.Columns)
    {
        col.IsForeignKey=tbl.FKTables.Any(
            x=>x.ThisColumn.Equals(col.Name,StringComparison.InvariantCultureIgnoreCase)
        );
    }
}
return result;

}

1. In Context.tt, find code var tables = LoadTables(); and replace with the code below:

   var dir = System.IO.Path.GetDirectoryName(Host.TemplateFile) + "\\Entities"; var fileNames = Directory.GetFiles(dir, "*.tt"); string[] tableNames = new string[fileNames.Length]; for(int i=0; i < fileNames.Length; i++) { tableNames[i] = System.IO.Path.GetFileName(fileNames[i]).Replace(".tt",""); } var tables = LoadTables(tableNames);

2. Create a new folder named "Entities" and copy the Settings.ttinclude and SQLServer.ttinclude to the new folder.

3. In ActiveRecord.tt, find code var tables = LoadTables(); and replace with the code below:

   var tableName = System.IO.Path.GetFileName(Host.TemplateFile).Replace(".tt",""); var tables = LoadTables(tableName);

After finish step1 to step4, when you want to generate a new ActiveRecord Class from a specified table, what you should do is:

1. Create a new T4 template that has the same name as the specified table's tablename.
2. Copy the ActiveRecord.tt code to the new T4 template
3. Generate code for the new T4 template
4. Generate code for the Context.tt template

BTW: My engilsh is not very well, so...