Last month, I wrote Manual Code Generation, an article which shared, in my view, the pros and the cons of code generators. In this same article, I took sides and expressed that “I am not a fan of code generation.” My stance came as no surprise to some — particularly my buddy (read archenemy) Jon Galloway. We’ve gone back and forth on this topic for a few years now so, of course, Jon replied to my post with arguments in favor of code generation. Along with arguments, he also offered a few suggestions; the best of which was to check out SubSonic. Actually, Jon simply asked, “Have you checked out SubSonic?” but I know this was just his polite way of asking me to please check it out before I continued to publicly embarrass myself by making bold statements about things which I have fairly little exposure simply because I effectively dismissed them years ago.
I have now checked out SubSonic. In an effort to get in the last word, here’s my review:
First Impressions
The SubSonic project is hosted on CodePlex. If you are not familiar, CodePlex is Microsoft’s open source project hosting web site. You can use CodePlex to create new projects to share with the world, join others who have already started their own projects, or use the applications on the site and provide feedback. Through CodePlex your project team may manage news feeds, releases, discussions, issues, source code and licensing. I know this is a review of SubSonic, but CodePlex is worth talking about (especially since it helped make a good first impression.)
Nearly Everything You Need To Know
The project’s main page has nearly everything one needs to get started:
Product Overview
The overview provides a very quick description of the product’s core feature list. To be honest, I wish more detailed information was provided here, but I think I got the gist. At its essence, SubSonic includes:
- A Data Access Layer (DAL) Builder which generates CRUD-enabled objects (which, in turn, represent selected database tables) and complementary strongly-typed collections.
- A complete utility toolset complete with Rails-like scaffolding which effortlessly builds admin-like web pages, migrations or DB versioning and code generators.
- A dynamic query tool that lets one use SQL Server, mySQL and/or Enterprise Library without having to know the underlying SQL syntax.
- An object-relational (OR) mapper which extends to stored procedures and views.
My one recommendation would be to elaborate on the feature set and tell a little more about the product in the overview. Answer questions like “Why was this effort undertaken?” and ”What problem does SubSonic solve?” That sort of thing. I will get into this a bit more in the Webcast section, but I think the “Welcome to the ActionPack for .NET!” content found on the sample website default page would be a nice addition to the project main page.
Source Code Download
SubSonic 1.0.5 was recently released on 10/24/2006. It’s a 1.3 MB .zip file download which includes the complete SubSonic Actionpack, Sample Website and Dependencies. A double-click on the unzipped solution file, a quick update to the Northwind connection string and I was up and running. You can’t beat that.
Starter Site Download
Latest version of the starter site project was released on 10/24/2006 as well. It came packaged up as a .vsi (Visual Studio Content Installer) file which, unfortunately, failed when I attempted to run it [1]. As I mentioned previously, the Sample Website source code was provided which I’m guessing is what would come along with the Starter Site so I was still in business.
Webcast
This demo was a treat. I am so pleased I spent the time to watch it. Not only did the webcast walk through highlighted functionality, but, in my opinion, it gave the product a “story.” You see, when I am at a gallery or museum, I tend to spend more time reading about the art/artist then actually admiring the work itself. I have a hard time appreciating a painting until I understand its background. I really like to know what the piece represents and what drove the artist to create it. That’s what the 20-minute webcast gave me. It gave SubSonic a personality. It also more deeply shared the philosophy behind the product which is Rails. Though the written overview touched upon elements of Rails (scaffolding), the demo touched upon one of its guiding principles (Convention Over Configuration) and base architecture (MVC, model-view-controller) in the words of the author. If you are interested in trying out the SubSonic ActionPack you should absolutely watch the webcast.
Playtime
Now that SubSonic was running on my machine, it was time to start playing around. I started with the basics which was basically everything which was discussed in the demo.
BuildProvider Class
If it hasn’t been mentioned already, SubSonic takes advantage of a new feature in .NET 2.0, the System.Web.Compilation.BuildProvider Class, to generate the DAL at compile time. There’s good reason why I’m placing a lot of emphasis on the use of the BuildProvider: First, you are generating and working your code within the same tool – Visual Studio. Also, your code is generated automatically when you build your application. This means your application code and generated code will never be out of synch again. Finally, I wasn’t previously familiar with the BuildProvider class, thus it made SubSonic a great learning tool.
I should mention that the generator is wired up really nicely and you don’t actually need to know a thing about the BuildProvider. All you need to know is classes/collections will be generated for tables listed in the App_Code/builder.abp file. You can even keep the default (not touch a line of code) and generate classes for all tables.
Additional Documentation
I built the project and clicked through the Sample Website. Again, I really like the content on the default page so be sure to spend some time here. I then breezed through the short tutorials found in the “Setup” and “CRUD” sections since the WebCast had already provided me with the basics.
Data Access Layer
My main interest was working with the generated DAL. Therefore, I spent the majority of my time in the “Query the DB” section. I did encounter an issue with the “Use a Collection” example, but otherwise the samples provided for a great testing grounds [2].
At first glance, I personally thought the provided examples were dumbed down a little too much. Sure, they were enough to get one started, but they didn’t go beyond the bare essentials.
For example, what if one wishes to filter their result set by more than one criterion. How is this done? After fiddling around a bit, I discovered this is as easy as making more than one call to the Query object’s AddWhere() function, but I wouldn’t have known without some experimentation.
Actually this is a nice segue.
Knowing SubSonic
I’ve commented in the past that generated code is difficult to write and maintain unless you have a firm, working knowledge of the code generation tool itself. What I mean by this is the generated code (in this case the exposed functions and methods available in the SubSonic DAL) uses custom syntax which one can only learn through using the tool and generated objects.
The Query object’s AddWhere() function acts a good example. Though it’s easy to query on multi-parameters, you have to learn SubSonic’s language before knowing how to do so. This makes for an interesting point. The documentation states that SubSonic “lets you use SQL Server and the Enterprise Library without having to know SQL.” Though this is true, instead of learning SQL, you need to learn SubSonic’s language.
Sticking to Conventions
To the creator’s credit, it appears that time was dedicated to making the tool and, in turn, the DAL consistent and intuitive. SubSonic is a working example of “Convention Over Configuration” which makes learning how to use the product a lot easier. There are numerous examples of this throughout the code, but most glaring is the decision to use singular, rather than plural, forms of object names. Even if the underlying database table is named Products (plural), SubSonic is configured to generate a ProductCollection rather than a ProductsCollection. Once you start understanding SubSonic’s conventions and patterns, you can start to navigate around the codebase with more confidence.
(Of course, I had to look at the PluralToSingular utility function. It will work in most cases, but the ever-present Octopi table is going to cause problems.)
Writing SubSonic
One big code generation con, in my opinion, is that someone has to spend time writing the code. Though my point holds water, Jon argued that someone has already written SubSonic so this shouldn’t be a problem for me. Well, maybe I’m too picky, but I’ve found that most software doesn’t do everything that I want or need and I wind up at least tweaking the generated classes/collections.
For example, I like to be able to access collection items by key as well as by index. The CodeSmith implementation, which I mentioned in my earlier write-up, didn’t provide this functionality. Additionally, functions like Exists() weren’t available either. If I recall, these custom collections were inheriting from System.Collections.CollectionBase which isn’t fall that full-featured. SubSonic’s generated collections are a bit more robust (they inherit from System.Collections.Generic.List ) but I still can’t access, add or remove an item from the collection by key. In the end, even though someone has already written the code, I will also need to spend time writing code if I would like this functionality.
Code Regeneration
With this said, I wouldn’t necessarily have to modify the code generator itself. Since SubSonic takes advantage of partial classes, I could easily maintain app-specific collection code with no concern that it would be over-written with the code generated by the next compile. Along these lines, as Jon pointed out, SubSonic does support handcrafted code very well. Subsonic also supports database changes as the DAL is safely regenerated when the project built.
DAL Focused
If you use the provided scaffolding model you will find it can quickly generate admin pages based on a database table and the associated DAL objects. In the example project, scaffolding is in place to administer the Products table. What one gets out-of-the-box with this example is a list of all products along with a link add or edit. On the edit page, one may modify any of the field values (with the exception of an identity column. Item deletion is done on the edit page as well.
Though scaffolding is in place to quickly generate admin pages, the presentation layer is not the ActionPack’s focus. SubSonic’s bread and butter truly is the DAL which I consider a very wise approach to code generation. In fact, in the demo, it even suggests that CRUD scaffolding should be considered for developer use only as it is presumably non-performant. I have to give SubSonic’s team a lot of credit for making scaffolding available yet sharing the limitations openly and not pushing this feature down the throats of end users. I also really respect the decision to focus heavily in one area (the DAL), making it really solid, and not get distracted by other not-as-important features.
Keep It Simple, Stupid
SubSonic’s DAL architecture follows the KISS principle which I admire. For example, though the Northwind EmployeeTerritories table establishes an obvious relationship between Employees and Territories through foreign key constraints, the DAL doesn’t. One might assume the Employee object would own an internal Territories Collection, but it doesn’t. SubSonic generates separate objects for each database table and they are managed completely separate in the DAL. Period.
I may be wrong, but I believe this means one would have to do the following in order to display the names of all Territories for Employee with the last name Griswold.
- Fill an EmployeeCollection where last name = Griswold
- Fill a EmployeeTerritoryCollection reference for each Employee.EmployeeID in the EmployeeCollection.
- Fill a TerritoryCollection for each EmployeeTerritory.TerritoryID in the EmployeeTerritoryCollection.
You may be thinking, “That’s not exactly simple.” Well, you are right and you’re wrong. The object model is simple and for 90% of what needs to be coded in a given application this is more than adequate. Again, SubSonic keeps it simple and it’s objects are generated to meet the most-likely conditions — not the exception.
That being said, one might consider using SubSonic’s Query Object to build the necessary SQL to populate the TerritoryCollection right off the bat, I’m not sure this is possible since the query would require joining at least two tables.
On the surface this appears that we’re ina quite the predicament about 10% of the time, but SubSonic has an answer. Which bring us to potentially my favorite feature…
Using Stored Procedures
In the cases where you aren’t able to use Query Object and you aren’t able to leverage the Fill methods exposed in the collections, you can fall back to using stored procedures. This will help you do whatever is needed.
I thoroughly appreciate SubSonic’s stance on the use of stored procedures. Basically, if you want to use them, the DAL provides ample support. (Each stored procedure in the database is wrapped in their own custom, publicly available function.) If you don’t want to use SPs, that’s fine too. Make no mistake about it, however. This product’s concentration is on building dynamic queries which is smart for a performance and usability stand point.
I’ve used code generation tools which attempt to generate a stored procedure for every CRUD condition which could be dreamed up. I found that most of the routines performed just fine. After all, it is difficult to slow down a query which pulls back a single row filtered on a clustered index. However, other stored procedures tried to do too much. For example, the routine which accommodated for filtering results on one or many fields through the use of “like” and “coalesce()” abused the SQL Server. It is this type of performance nightmare which SubSonic cleverly avoids by, first and foremost, generating SQL and but also by making existing SPs available.
One comment about the generated SQL: Dynamic SQL has raised a lot of red flags because of SQL injection concerns, but SubSonic is actually leveraging parameterized queries which are a safe “dynamic” SQL implementation. For example, ”select productname from products where productid = @productid;” is generated. Prior to execution, the @productid parameter value is provided to the Command object. This technique is as safe from SQL injection as a properly coded stored procedure simply because there is no room for injection. Try to maliciously hack code into the @productid parameter and the command will fail entirely.
I had a chance to take a look at the algorithm to build SQL strings. It is far less code than I expected and, as a matter of fact, it is here where I stumbled across a couple lines of code which made this entire review worth while. Per the comment, the author really liked it too.
1: //the following line is my favorite… Moe, Larry, Curly…
2: foreach (Where wWhere in qry.wheres) {
3: …
4: }
Wrap Up
After spending a few hours with the SubSonic, I don’t think its name does it justice. I’m not saying the tool deserves to be called SuperSonic just yet, but Sonic seems perfectly reasonable. If one were to draw a line in the sand, you still won’t find me on the side with the code generator advocates. Maybe I’m straddling the line? After all, I’d have to be a fool not to at least keep my eye on a tool which could potentially decrease my coding (and troubleshooting) time. For now, let’s say that SubSonic has definitely gotten my attention.
Footnotes
1: Installation of StarterKit Failed: Installation stopped because the directory for the ProjectType value did not exist. The project type is invalid for your installation of Visual Studio.
2: Query the DB > Use a Collection Error: Disallowed implicit conversion from data type varchar to data type money, table ‘Northwind.dbo.Products’, column ‘UnitPrice’. Use the CONVERT function to run this query.
