Creating Your Own DAL

Contents

Summary

We've put a lot of effort into SubSonic 3.0 but it's silly to think we'll match your needs exactly (if we do - hurray!). To that end we've tried to make it very, very simple for you to extend things as you need - even extending our core!

We've tried to keep everything in our templates as segmented as possible so you can take the .ttinclude files and the build on top of them. That's what this page is about - building your own approach.

Concepts

T4 is code generation and runs inside Visual Studio whenever a ".tt" file is added, changed, or when you right-click and select "Run Custom Tool". You can see more on the T4 Templates page.

One main thing you should know is that the code is executing in a completely different "App Domain". What this means is that, in general, the T4 files have no idea what files are in your project - they're running within some obscure directory deep in the bowels of C:\Program Files\Visual Studio. There are some tricks to getting it to know where it's running (see below for our tricks) but in general it's important to understand that Visual Studio is doing this - not SubSonic.

Finally - Code Generation is a slippery slope and can be very intoxicating when you start using it. You should remember that it's still code and you'll need to support it - and every time you change a template you change a whole lot more than one line of code - you're changing tens or hundreds of lines of code.

Use it sparingly and wisely - debugging generated code is no fun :).

SubSonic's Core T4s

If you're an advanced T4 user, you can ignore the following sections and go dive into our AdvancedTemplates which use Oleg Sych's T4 Toolbox. Chances are you're not - so we'll stick to the two core files used by SubSonic's ActiveRecord templates.

Settings.ttinclude

This template is the most important template of the bunch, and should really be called "Core". We renamed it so people know where to set their connection strings - but in general this is where the magic happens. Specifically:

Many people ask why we don't use SubSonic's core schema bits with our templates, and the answer is that pulling a reference to SubSonic in the templates is very involved, and we would have to know the physical location of the SubSonic dll. Even if we could figure this out - VS locks the DLL when the files are run and it will cause an error in your project if you reference the same DLL.

Put simply, Settings.ttinclude handles connecting to the database and offering objects for you to work with in the next template:[DBProvider].ttinclude.

Core Template Objects

Code speaks louder than words - here is the code for the core objects we use in our ActiveRecord template:

    public class Table{
 
        public List<Column> Columns;
        public List<FKTable> FKTables;
        public string Name;
        public string CleanName;
        public string ClassName;
        public string PrimaryKey;
        public string Schema;
        public string QueryableName;
 
        public bool HasLogicalDelete(){
            return this.Columns.Any(x=>x.Name.ToLower()=="deleted" || x.Name.ToLower()=="isdeleted");
        }
        public Column DeleteColumn{
            get{
                Column result=null;
                if(this.Columns.Any(x=>x.Name.ToLower()=="deleted"))
                    result=this.Columns.Single(x=>x.Name.ToLower()=="deleted");
                if(this.Columns.Any(x=>x.Name.ToLower()=="isdeleted"))
                    result=this.Columns.Single(x=>x.Name.ToLower()=="isdeleted");
                return result;
            }
        }
        public Column PK{
            get{
                return this.Columns.SingleOrDefault(x=>x.IsPK) ?? this.Columns[0];
            }
        }
        public Column Descriptor{
            get{
                if(this.Columns.Count==1){
                    return this.Columns[0];
                }else{
                    //get the first string column
                    Column result=null;
                    result=this.Columns.FirstOrDefault(x=>x.SysType.ToLower().Trim()=="string");
                    if(result==null)
                        result=this.Columns[1];
                    return result;
                }
            }
        }
    }
 
    public class Column{
        public string Name;
        public string CleanName;
        public string SysType;
        public string DataType;
        public DbType DbType;
        public bool AutoIncrement;
        public bool IsPK;
        public int MaxLength;
        public bool IsNullable;
        public bool IsForeignKey;
    }
    public class FKTable{
        public string ThisTable;
        public string ThisColumn;
        public string OtherTable;
        public string OtherColumn;
        public string OtherClass;
        public string OtherQueryable;
    }

These objects contain all the logic you'll need to create classes of your own - extending our core as needed.

[DBProvider].ttinclude

This T4 template is a specific implementation for a given database because each database has certain ways of giving you schema information. For this discussion we'll use The SQLServer.ttinclude (though we also have SQLite.ttinclude and MySQL.ttinclude. You can offer more if you like!).

This T4 template has one job: fill up the Core objects from Settings.ttinclude (Table, FKTable, and Column) by using a single entry-point method: LoadTables() (which returns a List). In SQLServer.ttinclude, the LoadTables method looks like this:
List<Table> LoadTables(){
    var result=new List<Table>();
 
    //pull the tables in a reader
    using(IDataReader rdr=GetReader(TABLE_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;
}

To see a bit more about how this works, take a look at our SQLServer.ttinclude code (by viewing the source in our Project Source.

Tips and Tricks

It can be difficult to work up a template given that T4 editing is not part of Visual Studio (no intellisense, weird formatting, etc). To help you out, here's some tricks: