Creating Your Own DAL

edit

Creating Your Own DAL

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 .

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: *Connection string setting *Core classes for dealing with database schema: Table, Column, and FKTable *Namespace setting (for generated classes) *Table naming (using the CleanUp() method) *Pulling the connection string from your project using ENVDTE *An Inflector class for pluralizing/singularizing names 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 Columns; public List 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

LoadTables(){ var result=new List
(); //pull the tables in a reader using(IDataReader rdr=GetReader(TABLESQL)){ while(rdr.Read()){ Table tbl=new Table(); tbl.Name=rdr["TABLENAME"].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 .

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: *Use - this will allow you to do all kinds of trickery, but be careful, you can blow up VS pretty easily.