John Sheehan : Blog

How I Use SubSonic, Part 1

I started heavily using SubSonic right around the time Rob Conery released his poorly-named ‘MVC Templates’. I was never a fan of the ActiveRecord pattern, but I really liked the Repository pattern used in the MVC Templates. The controllers generated by these templates were partial classes that inherited a base class that offered some common methods like List, Get, Delete, Save, etc. For each project, I would build up a pretty substantial set of controllers and I would have all my data access code in a nice centralized library.

The only place I made any real changes was with the templates themselves. Rob’s shipping version didn’t include lazy-loaded properties or methods for foreign keys like ActiveRecord. I really liked this feature, so I added them back to the templates using the controllers’ Get methods to call the correct item.

This worked great for awhile until I wanted to extend the base class. Despite repeatedly asking Rob for the source to his unofficial branch (and never getting an email back), I was stuck with what was there for awhile. Then along came SubSonic 2.1 and the new RepositoryRecord base class.

I really wanted to use the new SqlQuery capabilities so off I went to migrate my existing Controllers. In Part 1, I’ll cover recreating the ControllerBase class. In Part 2, I’ll outline my changes to the code gen templates. In Part 3, I’ll cover some caching and query metrics this architecture let me easily add.

This is the class definition I needed to replicate:

The methods I used most frequently were:

• Delete(object keyID): In my controllers, I would add a Delete(ItemType item) method, handle any pre-delete logic there, and then call Delete(keyID) within my method.
• Get(object key): This method was called extensively in my generated objects when loading foreign key properties. Also used whenever I need a single item.
• List(): Rarely used since it’s the equivalent of "SELECT * FROM Table" which isn’t very useful except for in my utility classes. Still used however.
• List(IDataReader rdr): I used this with my StoredProcedures to get a strongly-typed collection from the results of calling the sprocs.
• List(Query q): All other non-sproc collection loading was done here by passing a Query object from my Controllers.
• Save(ItemType item, string, userName): In my controllers, I would add a Save(ItemType item) method overload, handle all the saving logic and validation, then call this method when I needed to finalize the changes. (Yes, this ended up with a lot of business-logic leaking into my DAL, but that was my fault, not SubSonic’s and it is currently being remedied).

My new ControllerBase needed to support all of the above scenarios and work with the new SqlQuery object.

I started with the easiest methods to replicate:

   1:  public class ControllerBase<ItemType, ListType>

   2:      where ItemType : RepositoryRecord<ItemType>, new()

   3:      where ListType : RepositoryList<ItemType, ListType>, new()

   4:  {

   5:      public static ListType List()

   6:      {

   7:          return DB.Select().From<ItemType>().ExecuteAsCollection<ListType>();

   8:      }

   9:   

  10:      public static void Delete(int id)

  11:      {

  12:          DB.Delete<ItemType>(id);

  13:      }

  14:   

  15:      public static void Save(ItemType item, string username)

  16:      {

  17:          DB.Save(item, username);

  18:      }

  19:  }

SubSonic pros will know there’s no DB.Delete(int id) method in SubSonicRepository. You’ll have to download the source and add it yourself if you want to replicate this solution exactly. Here’s the code to add to SubSonicRepository.cs (you’ll also need to update ISubSonicRepository):

   1:  public void Delete<T>(int itemId) where T : RepositoryRecord<T>, new()

   2:  {

   3:      T item = new T();

   4:      TableSchema.Table tbl = item.GetSchema();

   5:      string pkColumn = tbl.PrimaryKey.ColumnName;

   6:      Delete<T>(pkColumn, itemId);

   7:  }

OK, back to ControllerBase. Next up was replicating the List overloads for building a collection from a Query object and a Stored Procedure.

   1:  public static ListType List(Query query)

   2:  {

   3:      ListType coll = GetCollectionFromReader(query.ExecuteReader());

   4:      return coll;

   5:  }

   6:   

   7:  public static ListType List(StoredProcedure s)

   8:  {

   9:      ListType coll = GetCollectionFromReader(s.GetReader());

  10:      return coll;

  11:  }

  12:   

  13:  protected static ListType GetCollectionFromReader(IDataReader rdr)

  14:  {

  15:      ListType coll = new ListType();

  16:      coll.LoadAndCloseReader(rdr);

  17:      return coll;

  18:  }

And the Get method:

   1:  public static ItemType Get(object id)

   2:  {

   3:      if (id == null)

   4:          return default(ItemType);

   5:   

   6:      ItemType item = DB.Get<ItemType>(id);

   7:   

   8:      return item;

   9:  }

Supporting the new SqlQuery object was trivial:

   1:  public static ListType List(SqlQuery q)

   2:  {

   3:      ListType coll = q.ExecuteAsCollection<ListType>();

   4:      return coll;

   5:  }

That was all I needed for backwards compatibility. I then added a new method with overloads for Query, SqlQuery and StoredProcedure to return a single item (thanks to Rob for responding to my feature request in the forums and implementing ExecuteSingle<T>()):

   1:  public static ItemType GetFirst(SqlQuery q)

   2:  {

   3:      return q.ExecuteSingle<ItemType>();

   4:  }

   5:   

   6:  public static ItemType GetFirst(Query q) // also created an overload for StoredProcedure

   7:  {

   8:      ListType coll = List(q);

   9:   

  10:      if (coll.Count == 0)

  11:      {

  12:          return default(ItemType);

  13:      }

  14:      else

  15:      {

  16:          return coll[0];

  17:      }

  18:  }

And there you have it. A backwards-compatible controller base class that lets you use the new features of SubSonic 2.1. You can download the complete class for use in your own projects. Part 2 (hopefully tomorrow) will cover my SubSonic template customizations.