What is SubSonic?

Introduction

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:

<configuration>

  <!-- 1 - Connection String(s) -->

  <connectionStrings>

    <add name="Northwind" 

        connectionString="Data Source=.\sqlexpress;Initial 

          Catalog=Northwind;Integrated Security=True"/>

  </connectionStrings>

  <configSections>

    <!-- 2 - SubSonic section handler -->

    <section name="SubSonicService" 

             type="SubSonic.SubSonicSection, SubSonic"/>

  </configSections>

  <!-- 3 - Point SubSonic at the appropriate data source(s) -->

  <SubSonicService defaultProvider="Northwind">

    <providers>

      <add name="Northwind" 

           type="SubSonic.SqlDataProvider, SubSonic" 

           connectionStringName="Northwind" 

           generatedNamespace="Northwind"/>

    </providers>

  </SubSonicService>

</configuration>

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:

<configuration>

  <system.web>

    <compilation>

      <buildProviders>

        <add extension=".abp" type="SubSonic.BuildProvider, SubSonic"/>

      </buildProviders>

     </compilation>

  </system.web>

</configuration>

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:

Buttfieldset31_Click

Dim posts As PostCollection = _

    New PostCollection().OrderByDesc("PostedOn").Load()

Retrieving a collection with multiple clauses

 

Northwind.CustomerCollection customers = new Northwind.CustomerCollection()

    .Where("Country", "Canada")

    .Where(Northwind.Customer.Columns.Region, "BC")

    .OrderByDesc(Northwind.Customer.Columns.PostalCode)

    .Load();

Retrieving a single item

 

If Not String.IsNullOrEmpty(Request.QueryString.Item("id")) Then

    Dim id As Integer = Int32.Parse(Request.QueryString("id"))

    post = New MyBlog.Post(id)

End If

Following a relationship

 

Northwind.Customer.FetchByID("BOTM")

    .Orders()[0]

    .OrderDetails()[0]

    .Product

    .ProductName;

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.

Query q = Product.Query()

                 .WHERE("UnitPrice > 50")

                 .AND(Product.Columns.ProductName, Comparison.Like, "S%")

                 .BETWEEN_VALUES("CategoryID", 1, 2)

                 .ORDER_BY("ProductName");

this.ProductGrid.DataSource = q.ExecuteReader();

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:

Query q = new Query("Products")

... //as before

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.

<subsonic:Scaffold ID="ProductsScaffold" 

                   runat="server" 

                   TableName="Products" />

The scaffold control will then render itself as a table (see Figure 5) with the ability to edit existing items or add new items.

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.

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.

QuickTable control

Calendar control

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.

Namespace MyBlog

    Partial Public Class Category

        Public ReadOnly Property Count() As Integer

            Get

                Return Me.GetPostCollection.Count

            End Get

        End Property

    End Class

End Namespace

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

Summary

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.

References

• http://SubSonicProject.com - the main SubSonic site. Includes documentation, forums and a growing number of screencasts showing common tasks with SubSonic.
• http://blog.wekeroad.com/2007/07/17/subsonic-mvc-templates-available-and-a-security-update/ - provides an example of using alternate templates when generating classes using SubSonic.

http://dotnetslackers.com/articles/aspnet/DigginAspnetMVC.aspx