Subsonic : Specify the tables you really need !

With the Subsonic’s T4 Templates you can specify the tables you don’t want the access code to be generated.

But what if you want to choose the tables for which the mapping classes will be created ?

Actually you can have many reasons to do that : having an application using only specific tables from a big system or having the ability to separate distinct parts of a system by example.

In my case I wanted to use only three tables from my database to build an ASP.NET membership provider. This is the example I’ll use for this article.

So, in my database I have at least three tables :

  • Users
  • Roles
  • UsersInRoles

Now I want to generate the DAL layer only for these tables, so I create a new code library project, a add an app.config file containing the connection string and add the Subsonic.Core reference.

The I have to add my T4 template, but they are not ready. I have to modify them to allow the selection of my tables.

To do that, I go to the T4 template directory and edit the “Settings.ttinclude” and the “Context.tt” files :

T4TemplatesDirectory

First, check how it works

In the “Settings.ttinclude” file, line #30 we see the definition of the tables to be excluded:


//this is a list of tables you don't want generated
string[] ExcludeTables = new string[]{
"sysdiagrams",
"BuildVersion"
};

In the “Context.tt” file, il we search for “ExcludeTables”, we find three part of code using it in the same way:

// line 135

<#  foreach(Table tbl in tables){
if(!ExcludeTables.Contains(tbl.Name))
{

// ...

// line 251

foreach(Table tbl in tables)
{
if(!ExcludeTables.Contains(tbl.Name))
{

// ...

// line 271

foreach(Table tbl in tables)
{
if(!ExcludeTables.Contains(tbl.Name))
{

// ....

Now that we know how it’s working, we can modify the T4 templates to add the ability to choose the tables we want

The simplest option would be to replace the “ExcludeTables” array by “IncludeTables” and to remove the logical NOT operator (!) in the conditions and the job will be done.

But if we think a bit, we realize that there is many ways to choose the tables we want.
We can decide to choose :

  • All tables
  • All tables except the specified ones
  • Only the specified tables
  • All tables matching a pattern (example : starting by “MyApplication_”)
  • An other choice method

My solution :

In the “Settings.ttinclude” file:

Add a string array named “IncludeTables” containing the tables you want :


// this is the list of the tables you want to be generated
string[] IncludeTables = new string[]{
"Users",
"Roles",
"UsersInRoles"
};

Add a string variable named TablePrefix

 // the prefix of the table you want to be generated
 string TablePrefix="";

Add an enumeration named “TableChoiceMethod” and containing the different ways to choose the tables:


enum TableChoiceMethod
 {
SpecifiedExcludes,
SpecifiedIncludes,
SpecifiedPrefix,
All
 }

Add a variable for this enum, set to the value you want


// define the table choice method
 TableChoiceMethod tableChoiceMethod = TableChoiceMethod.SpecifiedIncludes;

Create  a ““IsTableOkToBeIncluded” method taking care of the choice we made in the activerecord file and returning whether or not the table is ok to be generated.


bool IsTableOkToBeIncluded(string tableName)
{
 switch(tableChoiceMethod)
 {
  case TableChoiceMethod.SpecifiedIncludes:
  {
   return IncludeTables.Contains(tableName);
  }
  case TableChoiceMethod.SpecifiedExcludes:
  {
   return !ExcludeTables.Contains(tableName);
  }
  case TableChoiceMethod.SpecifiedPrefix:
  {
  return tableName.StartsWith(TablePrefix, StringComparison.InvariantCultureIgnoreCase);
  }
  default: // assume TableChoiceMethod.All
  {
   return true;
  }
 }
}

In the Context.tt, ActiveRecord.tt and Struct.tt files :

Replace the condition”if(!ExcludeTables.Contains(tbl.Name))” by this method


<#  foreach(Table tbl in tables){
if(IsTableOkToBeIncluded(tbl.Name))
{

// ...

Now specify the Namespace, ConnectionStringName and DatabaseName in the Settings.ttinclude file.

Add the template to your DAL project,

Generate

You are done !

You can download my templates here.

Remarks :

  • The Subsonic version I use is 3.0.0.4.
  • I’m running Windows 7 64 bits and I had  unblock the files in windows explorer.
  • I had to run Visual Studio as an administrator.
  • If you are not familiar with subsonic, I invite you to watch the 5 minute demo and read the startup tutorial from the subsonicproject website.

Incoming search terms:

  • subsonic 3 0 tablename s

About Sam Beauvois

Application Developer, .NET enthusiast since 2004, I'm interested in technology watch, usability, code quality, patterns & practices, UX, ...

3 comments

  1. For now I never really played with subsonic and the stored procs

    I think that adding the same logic than for tables for the stored procedures in the Settings.ttinclude file

    I mean creating a method

    bool IsStoredProcOkToBeIncluded(string storedProcName)
    {
    // include your check logic
    }

    Then in the StoredProcedures.tt file adding a condition
    if(IsStoredProcOkToBeIncluded(sp.Name))

    so :

    < # foreach(var sp in sps){
    if(IsStoredProcOkToBeIncluded(sp.Name)) { #>
    // …
    < # }
    }#>

  2. Darrin says:

    This is great and exactly what I was looking for. Thanks!!

    Do you know if this is as easy to implement for Stored Procedure code generation also?

    I’m sure I can figure it out but I thought if you had already dealt with this it would be great too!

    Thanks again!

  3. [...] we are on, we check if the access code to the table has to be generated (see my previous article : Subsonic : Specify the tables you really need ! [...]

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>