Setting up SubSonic3 Repository Mode with an Existing Database

Using SubSonic 3 respository mode has been a breath of fresh air for some of my projects. You can use simple POCOs (plain old CLR objects) and easily use inversion of control with your application. However, it can be a little confusing using repository mode with an existing database since SubSonic’s default schema naming may not conform to your database. By using SubSonic’s schema attributes, you can get your application up and running relatively easily; and if you have a ton of tables, I have a T4 template that generates basic domain classes against your database to get you started.

Conforming to Your Database

So first off: naming. SubSonic3 repository assumes that all your tables are pascal-cased and pluralized. So if you use a vanilla domain class against your database, it will only work with tables matching that naming pattern. Be aware that these attributes help determine the database properties; not the .NET runtime behavior of your application.  For example, a StringLength attribute will instruct SubSonic to setup a varchar column with a defined length, but your .NET runtime will still use a String type and will still accept values longer than the associated database column.  Here are the attributes you can use to tailor your classes to your database.

SubSonic.SqlGeneration.Schema including:

  • [SubSonicDefaultSetting] – set a default value
  • [SubSonicIgnore] – Ignore a property or field
  • [SubSonicLongString] – ntext or text field
  • [SubSonicNullString] – Nullable varchar or text
  • [SubSonicNumericPrecision] – decimal with specific precision
  • [SubSonicPrimaryKey] – override primary key (use for primary key properties not named “Id”)
  • [SubSonicStringLength] – varchar custom length (use when length is not 50)
  • [SubSonicTableNameOverride] – override table name mapping for class
  • [SubSonicToManyRelation] – for one to many foreign key property mappings
  • [SubSonicToOneRelation] – for one to one foreign key property mappings

 

For the most part, you will use the TableNameOverride attribute to make your classes work with their corresponding tables. However, at times you will need to use the PrimaryKey if you don’t use “ID” as your primary key. The rest are extremely handy as well especially when it comes to null strings, text fields, and properties you want to ignore completely from the SQL translation to your database. Here is a sample class from an existing application:

[SubSonicTableNameOverride("effect")]  
public partial class Effect  
{
    public int Id { get; set; }
    public int SiteId { get; set; }
    public int? EffectTypeId { get; set; }
    public bool ApplyDefault { get; set; }
    [SubSonicNullString]
    [SubSonicStringLength(100)]
    public string Name { get; set; }
    [SubSonicNullString]
    [SubSonicStringLength(500)]
    public string Description { get; set; }
}

Here, I use Id for my primary key, so it is not necessary to add the PrimaryKey attribute.  I did use a NullString operator for a couple fields here that don’t require values. On a side-note, one of the philosophies behind requiring the NullString attribute and not simply assuming all strings are nullable is that the authors believe it’s more useful to use empty strings in place of null values because it alleviates many other issues down the road.  While I don’t disagree, I still tend to use nullable strings out of habit. You can read more about their conventions from the SubSonic Tome

Domain Class Generator

To get up and running quick, I built a simple domain class generator with T4 that writes out basic structures from an existing database repository-mode-friendly format. To run it, copy the files: Settings.ttinclude, SqlServer.ttinclude (or db specific ttinclude), and this Entities_Intial.tt file into your project folder. (Don’t add them to your visual studio project just yet).  Edit the Settings.ttinclude file and customize your namespace, connection string, and database name fields. 

Then include the Entities_Initial.tt file to your project. When you include this file, it will automatically attempt to run with your settings. It should separate each class into its own file. It will also generate a file for the core Entities_Initial that you can safely delete.  (NOTE: To re-run the generation after including it to your project, right-click the Entities_Initial.tt file and select “Run Custom Tool”).

Copy these files to another folder in your project to prevent them from being accidentally overwritten with another generation. Unlike the generated partial classes of ActiveRecord and LinqTemplates where you maintain a partial extension, you want to maintain these domain classes directly because they truly should stand alone from the framework you’re using.  Managing them like this gives you direct control over the properties and attributes in your domain model.  You should also be able to use these classes with other IOC frameworks like NHibernate. (Granted… To drop the SubSonic reference, you will need to drop all the attributes at the same time).

 

Entities_Intial.tt

<#@ template language="C#v3.5" debug="True" hostspecific="True" #>  
<#@ include file="SQLServer.ttinclude" #>
<#
    var list = LoadTables();
    foreach (Table table in list)
    {
#>
using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Text;  
using System.Data;  
using System.Linq.Expressions;  
using System.Collections;  
using System.ComponentModel;  
using System.Data.Common;  
using SubSonic.SqlGeneration.Schema;

namespace <#=Namespace #>  
{
    [SubSonicTableNameOverride("<#=table.Name#>")]
    public partial class <#=table.CleanName#>
    {
<#
        foreach(Column col in table.Columns)
        {
            if (col.IsPK && col.CleanName.ToLower() != "id")
            {
#>        [SubSonicPrimaryKey]
<#            }

            var nullableNonString = string.Empty;
            var nullableString = string.Empty;
            if (col.IsNullable && col.SysType != "string")
            {
                nullableNonString = "?";
            }
            if (col.SysType == "string"){
                if (col.IsNullable)
                {
#>        [SubSonicNullString]
<#                }
                if (col.DataType == "text")
                {
#>        [SubSonicLongString]
<#                }
                else
                {
#>        [SubSonicStringLength(<#=col.MaxLength#>)]
<#                }
            }
#>        public <#=col.SysType#><#=nullableNonString#> <#=col.CleanName#> { get; set; }
<#
        }
#>
    }
}
<#    SaveOutput(table.CleanName + ".cs");
    }
#>
}

You can download the original script and its references directly or get the latest version from my snippets project on GitHub. I used the base elements of the T4 scripts from the SubSonic-Templates project for this. Currently, they only support MySql, Sql Server, and Sqlite databases.

Enjoy!