One of the signs of an easily maintained application is a division of labor between the classes, usually resulting in a user interface
layer, a business layer and a data access layer. While this technique helps to better organize the application, many developers shy away from
creating them. Creating a reliable and fast data access layer requires some planning, and once you've created one, they all start to look
similar. SubSonic helps you by automatically creating a data access layer based on your database.
What is SubSonic?
SubSonic is a data-layer builder. More than that, it's an auto-magic object-relational mapping (ORM) tool that "Helps a Web site
Beyond the hype, though, just what does SubSonic do, and how can it help you build your applications faster? SubSonic reads the structure
of your database, and builds classes to provide you with a fast and flexible data access layer. It requires minimal configuration to set up,
provides you with a number of different methods for retrieving and saving data, and includes methods to customize the classes to fit with
your own development style. SubSonic was inspired by the ActiveRecord classes in Ruby on Rails. However, SubSonic is pure .NET and fits with
the .NET methods of development.
Compared with other ORMs, SubSonic requires remarkably little configuration. At a minimum, you will need to add the following to your
web.config (or app.config) file:
- One or more connection strings
- The SubSonic section handler
- A link between the appropriate connection string(s) and the SubSonic classes
That's it - no need to identify the tables you want, or perform any mapping between the tables and objects. In practice, the
configuration looks like the following:
Once you have configured your application, you can generate your data access layer using the SubSonic command-line tool.
Alternately, if you are using ASP.NET, you can use the build provider to dynamically generate the data layer.
You can use the command-line tool from (surprisingly enough) the command-line, but it is easier to create a new External Tool (see Figure
1) to create the classes. Figure 2 shows the generated classes.
Figure 1: Configuring the command-line tool
Figure 2: Generated classes
Generating the classes gives you a
set of physical files you can look at, admire, and learn from, but you shouldn't edit these files. Any changes you make will be overridden if
you regenerate the data layer. Instead, as all the classes are defined as partial, you can override them in your own files (see "Extending
the generated classes" below). To avoid the temptation of editing the files, you can use the build provider to automatically generate the
In order to use the build provider, you must add a few more entries into the web.config file:
The above setting assigns the build provider to any files ending in
.abp in the App_Code subdirectory. This is
similar to the way that adding an XML schema to the App_Code directory creates a typed DataSet. You only need a single
for the build provider, and it does not need any text in it, it only needs to be present. When the application is built, the presence of the
file triggers the generation of the classes, just as with the command-line tool (see Figure 3). The only difference is that now you don't see
them in your project.
Figure 3: Using the generated classes
Getting the data
Once you have created your DAL with SubSonic, either manually or using the build provider, you are ready to begin to query your database.
At this writing, there are providers for a number of databases:
- Microsoft SQL Server 2000 or 2005, including Express
SubSonic generates three classes for each table in your database:
- A singular version of the table name. For example, if you have the table Products, the class will be named Product. This is a
strongly-typed class representing a single row in the table. Each column in the table will be represented by a strongly-typed property of the
class. Fields that accept null values are defined as nullable.
- A collection, for example for the table Products, you will get a ProductCollection class. This class provides methods for dealing
with a (surprisingly enough) collection of rows in the database.
- A controller (e.g. ProductController). This provides the basic CRUD (Create, Retrieve, Update and Delete) functionality. While
the Product and ProductCollection are capable of loading themselves, the ProductController is designed for these tasks. In addition, you can
use the Controller class as the data source for your page using the ObjectDataSource control (see Figure 4).
Figure 4: Connecting the ObjectDataSource to a controller
strongly-typed classes enable you to query the database using a variety of methods, depending on the needs of the situation. Below are just a
few possible types of queries you could create using the generated classes:
Listing 3: Simple retrieval of a collection
Listing 4: Retrieving a collection with multiple clauses
Listing 5: Retrieving a single item
Listing 6: Following a relationship
The retrieval methods provided by the generated classes give you a full range of options for building the queries, from where
clauses, order by clauses, in clauses and even Boolean operations. You can retrieve the strongly-typed objects or collections, a DataSet, or
an IDataReader, depending on your needs.
In addition to the strongly-typed classes, SubSonic also includes a generic query tool, creatively called Query. You can use this to
perform ad hoc queries of your database.
The above example uses the Product class to identify the table to query. You could have also done this by creating the Query
using the following code:
Each of the methods in the query returns a Query object. This enables you to string them together as necessary to retrieve just
the data you need.
Performance of the SubSonic generated objects is good - generally faster than using typed DataSets. Therefore, you are not sacrificing
performance to make use of SubSonic.
But wait, there's more...
In addition to the data access functionality, SubSonic also includes a few controls that make working with data easier.
The most dramatic of the controls that ships with SubSonic is the scaffold control. Like a scaffold on a construction site, or the
scaffolding in Ruby on Rails, it is intended to provide you with an easy way to create something, with the intention that eventually the
scaffolding will be removed. In the case of SubSonic, the scaffolding makes it easy to edit a table.
The scaffold control will then render itself as a table (see Figure 5) with the ability to edit existing items or add new
Figure 5: Viewing the current items
Selecting an item to edit
renders a form with controls appropriate for the various controls. As you can see from the Figure 6, the Posted On field creates a Calendar
control, and the Body field gets a multi-line Textbox.
Figure 6: Editing an item
In addition to the scaffold control,
SubSonic also includes the QuickTable control (see Figure 7), which displays data in a grid format, an updated Calendar control that also
allows for entering in a time (see Figure 8), and a ManyToMany control (see Figure 9) that helps you edit related columns.
Figure 7: QuickTable control
Figure 8: Calendar control
Figure 9: Many to Many control
Each of the controls looks fine
out of the box, but also includes a number of properties for setting the CSS styles for the elements of the control.
And it also comes with steak knives...
It would be easy to overlook the Sugar namespace when using SubSonic, however if you do, you'd miss some useful functionality.
SubSonic.Sugar doesn't include a lot of glamorous functions, but instead it provides the "miscellaneous" functions part of the API, and
includes functions for:
- File operations, such as opening and reading a file in one request.
- Date operations, such as determining the number of work days between two dates.
- String operations, such as conversions to proper case or stripping HTML.
- Numeric operations, such as determining if a number is whole, or even.
- Web operations, such as opening and reading a Web page, or performing a DNS lookup.
- Validation operations, including the common Visa, MasterCard and other common credit card validations.
No application can provide the solution for all needs, and so the default classes built by SubSonic may not be enough. You may want to add
methods to the generated classes, or you may want to change the way the classes themselves are generated. SubSonic provides for both needs.
All of the classes generated by SubSonic are partial classes. This means that you can add functionality to them by creating another class
with the same name, just as you do when creating Windows Forms applications. For example, the following code would add a new method to the
Category class to calculate the number of posts in that category.
If you don't like the way the generated classes are constructed, you can also override the templates used. The templates used
are similar to templated controls in ASP.NET, where you provide a set of markup that is repeated for each row. In the case of the SubSonic
templates, they are repeated for each table you generate. You can create your own set of templates, and have SubSonic use them by including
the TemplateDirectory attribute. For an example of doing this, see Rob Conery's post on using his MVC templates (see the References section
You know you have a database you need to connect to an application. You know that putting the SQL requests in the page is a bad
architectural decision. You know you should write a data layer to manage the CRUD for your application. You really want to get it all done
now so that you can go home to loved ones. SubSonic helps you solve all of these in a single stroke, without sacrificing performance,
maintainability or flexibility. Just download it and give it a try. Your applications will thank you.
Just another geek, but this one is me. Kent used to work at Microsoft, but is now an independent, working out of Comox, BC (Don't worry, no one knows where it is. Except Shane.)
This author has published 2 articles on DotNetSlackers. View other articles or the complete profile here.
Please login to rate or to leave a comment.