Published: 28 Dec 2007
By: Kent Sharkey

An introduction to SubSonic, a data-layer builder.


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 build itself."

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.

Configuring SubSonic

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 classes.

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 .abp file 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
  • MySQL
  • Oracle

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

The 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 items.

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.

Extending SubSonic

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 below).


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.


<<  Previous Article Continue reading and see our next or previous articles Next Article >>

About Kent Sharkey

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.

Other articles in this category

Code First Approach using Entity Framework 4.1, Inversion of Control, Unity Framework, Repository and Unit of Work Patterns, and MVC3 Razor View
A detailed introduction about the code first approach using Entity Framework 4.1, Inversion of Contr...
Exception Handling and .Net (A practical approach)
Error Handling has always been crucial for an application in a number of ways. It may affect the exe...
jQuery Mobile ListView
In this article, we're going to look at what JQuery Mobile uses to represent lists, and how capable ...
Book Review: SignalR: Real-time Application Development
A book review of SignalR by Simone.
JQuery Mobile Widgets Overview
An overview of widgets in jQuery Mobile.

You might also be interested in the following related blog posts

New article: How to detect and avoid memory and resources leaks in .NET applications read more
Time Flys Like An Arrow; Fruit Flies Like A Banana read more
Developing aN Astoria data provider for SubSonic read more
Back to Basics: Delegates, Anonymous Methods and Lambda Expressions read more
.NET Coding Guidelines read more
SubSonic article on DotNetSlackers read more
DevConnections and OpenForce read more
Growing the family read more
Review: Professional ASP.NET 2.0 Ajax read more
Strongly Typed Stored Procedures Using Subsonic read more


Subject Author Date
placeholder About time... Granville Barnett 12/30/2007 7:52 AM
RE: About time... Sonu Kapoor 12/30/2007 11:11 AM
placeholder Kool Kazi Manzur Rashid 1/3/2008 9:22 AM
Yes, Subsonic is cool Simone Busoli 1/7/2008 2:51 AM
placeholder Class library Patrick Smith 1/15/2008 4:20 PM
RE: Class library Kent Sharkey 1/15/2008 4:49 PM
placeholder RE: RE: Class library Patrick Smith 1/16/2008 10:09 AM
SPROCs Pond Fish 3/12/2008 9:58 AM
placeholder RE: SPROCs Kent Sharkey 3/12/2008 7:04 PM
About subsonic Artical shahid shaikh 6/9/2008 9:28 AM
placeholder Access the data access layer shyakaster 6/10/2008 12:57 PM

Please login to rate or to leave a comment.