More on SubSonic and SSMS Tools Pack

I’ve publicly praised SubSonic and SSMS Tools Pack, but I haven’t posted adequate information on how one installs and uses the tools for their own development.  A colleague of mine was lucky enough to inherit my application recently and having this information documented would have come in really handy.  All the same, I gave a crash course on the two tools today. 

Here’s my quick rundown of SSMS Tools Pack and SubSonc.  Hopefully it is enough to get you (and my co-worker) started.

Reference and Installation Information

SSMS Tools Pack 1.1 was created by Mladen Prajdi?.  Here’s the feature list.  Note, I am using SQL Server Management Studio Express 2005 SP2 locally, however, SSMS Tools Pack support this as well as SQL Server Management Studio 2008 (Express).  Choose your poison here and install using the default settings.

SubSonic 2.1 was created by Rob Conery and the team includes an impressive list of coders.  Note, I am using version 2.0.3 which is the current stable release.  You can download version 2.1 although I’ve heard mixed reviews.  In either case, install using the default settings.

Using SSMS Tools Pack

I use SSMS Tool Pack to generate my CRUD stored procedures.  That’s right.  I still use stored procedures even when I access my database through a generated DAL. 

It is just a preference, but the first thing I suggest is to update the generated stored procedure naming convention by navigating to SSMS Tools > Create CRUD > Options.  Here, you update the stored procedure name prefix from “usp_” to “GSP_” on all five tabs.  “GSP” stands for Generated Stored Procedure, by the way.

Next, it’s time to define the database schema.  Be sure to include primary keys on all tables. 

Finally, generate the CRUD routines by right-clicking on the database in Object Explorer then selecting SSMS Tools > Create CRUD.  The resulting scripts will be generated to a new query window for review and execution.

Using SubSonic

I use the bare bones of what SubSonic has to offer.  I primarily use the tool to generate my DAL from which I only create, read, update and delete my data through stored procedures. I also use SubSonic to generate a set of templated classes which I include in my solution’s common class library. Believe it or not, I have written about SubSonic custom templates before.

Before going any further, I would suggest watching the “Getting Started” tutorial which can currently be found towards the bottom-right of the SubSonic Project Home Page. The tutorial covers basic SubSonic DAL concepts including configuration, a DAL class library, managing custom and generated code through use of partial classes and Visual Studio external tools.

Per the tutorial, you’re going to want to set up your Visual Studio external tool shortcuts.  This can be done by navigating to Tools > External Tools. In my case, I generate my DAL and Common library so I create two external tool references. I like including all database access and template information in the command arguments although SubCommander is smart enough to leverage the settings in your app.config file. 

Once the external tools are set up, you may now generate your DAL code by selecting the DAL project in the Solution Explorer followed by the execution of the associated external tool command. Code will be added to the configured (most likely “Generated”) folder and each file can be included/excluded from the solution as necessary.

Moment of Truth

Hold your breath.  Cross your fingers.  Hit F5.  Eat more sugar cookies.