Published: 29 Jan 2008
By: Kent Sharkey
Download Sample Code

This article shows how you could use the SubSonic ORM to create a simple weblog site.


This article demonstrates how you can use a number of different types of queries to retrieve data for an application using the SubSonic ORM. For an introduction to SubSonic, see the previous article.

Creating the database

The first step in using SubSonic is to create the database you'll be using with it. You won't need all the tables initially, but having a few will ensure you've configured SubSonic correctly.

Create a new database (called MyBlog) using SQL Server Express (or another database supported by SubSonic, but you'll have to change the provider used). Add a new table named blog_posts to the database, as shown in Figure 1. The PostID field should be configured as an auto-increment identity field.

Figure 1: Blog Posts Table


Once the database is in place, you can add the configuration to build your data layer. Add the code in Listing 1 to your web.config file.

Listing 1: Web.config file

Build your application and confirm that everything is happy by testing using Intellisense (see Figure 2).

Figure 2: The Intellisense Test


Notice that the name of the generated object is BlogPost - the underscore has been removed and the object name has become singular. I included the blog_ in the table name, however I don't want all the objects to include this text. Fortunately, SubSonic can strip this text; add the stripTableText attribute (remember: attributes are case sensitive) to the configuration of the SubSonic provider (see Listing 2). There are similar attributes to strip out text from your stored procedures (stripSPText) and views (stripViewText) as well. Figure 3 shows the result.

Listing 2: Changing the generated table names

Figure 3: Changed generated objects


With the core table in place, you're ready to start adding blog posts.

Using the Scaffold

When building Web applications, you frequently need a fast way to edit the data in your database. With standard ASP.NET, this means you will have to build out your edit pages first, or edit the tables directly in SQL Server Management Studio (or Visual Studio). The first solution requires a lot of work before you can get to the main work on your site, and the second avoids all of the validation you may need to have in place. SubSonic once again provides: in this case using the Scaffold control.

Add a new page (ManagePosts.aspx) to your site. If you have already added the SubSonic controls to Visual Studio, add a Scaffold control to the page. If you have not added the controls to the toolbox, drag the SubSonic DLL onto the toolbox. The six SubSonic controls will be added (see Figure 4).

Figure 4: SubSonic controls


Set the TableName property of the Scaffold control and run the page. You should see the list of current posts in the blog_posts table (none, so nothing will show here initially) and a lonely Add button. Clicking the Add button will bring up an edit screen (see Figure 5) that you can use to create your blog posts. While this page can be used "as-is" or styled to make it more attractive, it is probably best to only use it during the prototyping/building phase of your site (which I will cover later in this article). Add a number of posts that can be displayed elsewhere.

Figure 5: Editing using the Scaffold


Getting the data

Displaying the posts becomes a simple matter. Add a DataList control to the default.aspx page. This will be used to display the most recent posts. Set the ItemTemplate to the code in Listing 3.

Listing 3: ItemTemplate

Of course, you will need to provide some data for the DataList to display. Listing 4 shows how little code is required.

Listing 4: Retrieving the recent posts

This code uses the Query object to perform the data retrieval. As each of the methods used returns a Query object, the resulting query can be as complex or specific as necessary.

The list page shown above directs people to a post.aspx page to display a single post. Add this new page to your project. The code shown in Listing 5 will display the blog post.

Listing 6: Displaying a single blog post (page)

Rather than using the Query object again, I'll use the Post object to retrieve the desired item. As you saw from the list page, the URL to the post page will look like post.aspx?id=# where the # is the PostID of the item to display. You could use the Request collection to retrieve this value, and cast it to an integer, but the SubSonic.Sugar namespace makes this step easy as well (see Listing 6).

Listing 7: Displaying a single blog post (code)

Adding Category support

Having the blog posts is useful, but organizing the posts into categories makes it easier for readers to follow topics of interest, so I'll add category support to the blog.

Add a new table to the database to hold the categories called blog_categories (see Figure 6). As before, the CategoryID field is an auto-increment identity field. Either add a few categories using the database, or create another scaffolding page to manage the list of categories and add a few using it.

Figure 6: Blog_Categories table


At the moment, there is no relationship between the posts and categories tables. As each post may appear in multiple categories and each category will hold multiple posts, the best relationship between the tables is a many-to-many relationship. In order to model this in the database, the standard method is to add a linking table. Add a new table (blog_post_category_map) for this relationship (see Figure 7). The two columns relate to the two key fields for the blog_posts and blog_categories tables and combine to form the primary key for the table.

Figure 7: Blog_Post_Category_Map table


In addition, you should also create the relationships between the three tables. Click the relationships button on the Table Designer toolbar. Add the two foreign key relationships between the two primary tables and the blog_post_categories mapping table (see Figure 8).

Figure 8: Adding a foreign key


The Scaffold control has built-in support for editing many-to-many relationships. Add the blog_post_category_map table to the ManyToManyTable property. Run the scaffolding page and edit an existing post (don't add a new entry, you'll see why in a moment). You should see a checkbox list of the available categories (see Figure 9). Check off the appropriate items and save the entry. You should see the added items appear in the mapping table (see Figure 10).

Figure 9: Editing an existing item using scaffolding


Figure 10: Data in the mapping table


If you attempt to add a new record, you won't see the category checkboxes. Why? Look at the steps that the scaffolding page must perform to save your new record. It must add a new entry into the blog_posts table, and to the blog_post_category_map table. While adding the data to the two tables is easy enough, the PostID must be available for saving to the mapping table. Unfortunately, SubSonic doesn't have this value yet (as it's in the process of creating that new record), so it can't show the list of categories you have available to add to the post. How can you get around this error? There are two ways, assuming you don't just edit the SubSonic code to add support for this. The first is to not use auto-incrementing keys. Instead, you could use some other value that is added by the user as a key, meaning it would be available for saving both records. The second - and in my view better - solution is to not use the ManyToMany property directly with the scaffolding when you use an auto-incrementing key. Instead, you can use the ManyToMany control after adding the new blog post to select the categories.

Replacing the Scaffolding

To do this, I'll replace the scaffold control with code, and include the ability to select the categories when adding new posts. You could now simply go and create your own page. However, I am lazy, and I'd rather have SubSonic create my edit page.

The good news is that the command-line tool accepts a parameter to generate the editor page. The bad news is that as of version 2.0.3 of SubSonic, it doesn't work. The actual code to perform the generation is AWOL (Absent WithOut Leave). The good news for lazy folks like me is that the code still works in the SubSonicCentral site that comes with SubSonic (it's in the src directory). Edit the connectionstrings.config and web.config file for that Web site to include the pointers to the MyBlog database, start the SubSonicCentral Web site, and switch to the Generators page. The scaffold generator section (see Figure 11) is on the bottom half of the page. Select the blog_posts table and generate. You should now have a PostEditor.aspx page to include in your project.

Figure 11: Generating the new edit page


The code generator used by SubSonicCentral is a little older, so you'll likely have to change a few names (it generates the class as BlogPost, for example). Once you clean this up, however, you should have a handy page that works just like the scaffolding, but that can be modified more easily (see Figure 12).

Figure 12: Generated editor page


Drag a ManyToMany control onto the editor portion (it's below the GridView portion) of the PostEditor.aspx page and set the properties as in the listing below. These properties set up the mapping between the three tables involved.

Listing 9: ManyToMany control

In addition, modify the BindAndSave method in the code behind for the editor page to set the PrimaryKeyValue property of the ManyToMany control and save the records (see Listing 8).

Listing 10: BindAndSave

Now you should be able to add and edit posts, with the selected categories. The mapping records should appear in the blog_post_category_map table as you create the new posts.

Other queries

The reader of the blog may not want to see all the records, or they may want to see older records, so I will add the ability to query the posts by category or date. Rather than create a new page, I'll use the existing default.aspx, and add support for the query URLs. The URL format for category queries will be default.aspx?category=something, where the name of the category is an URL encoded string. Date queries will include the year, month and day (or only the year, or the year and month) of the query, for example: default.aspx?year=#, default.aspx?year=#&month=#, or default.aspx?year=#&month=#&day=#. The page will determine if it is a category query, or a date query, or simply return the most recent posts. Then it will retrieve the appropriate posts and display them in reverse date order. Listing 9 shows the updated Page_Load event handler.

Listing 12: Retrieving the appropriate records

Try viewing the default.aspx page using a number of queries to view the posts in a specific category or date.

The blog now is functional, albeit very limited. You would likely want to add a Master page and/or a stylesheet, as well as extend this to include RSS and/or Atom support, and add security to the application; however as it stands, it demonstrates a number of methods you can leverage SubSonic in your applications: use the Scaffold control to enable quick addition of records if needed, and the item, collection, and Query classes to replace your existing calls to the database. When querying the database using any of the generated objects or Query, most of the methods can be chained together to create the desired SQL statement.


SubSonic provides an easy way of adding a data access layer to your applications, without forcing you to make decisions about how to retrieve your data. It gives you the flexibility to retrieve multiple or individual records, or freeform queries as needed. The generated classes work well with the built-in controls, and SubSonic also includes a few controls to speed your development. You really didn't want to write your own data access layer, did 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

Migrated from Community Server to DasBlog read more
Serialising Microsoft StreamInsight QueryTemplates read more
The Downside of Transparency read more
From DB to DataGrid So Many Choices! read more
DataBase Design & Creation read more
Next version of EF Code Only Design laid out by MS read more
Reducing the bounce rate of tech blogs with Subtext and read more
IIS Search Engine Optimization Toolkit read more
Announcing: IIS Search Engine Optimization Toolkit Beta 1 read more
Custom Route Constraint in ASP.NET MVC read more


Subject Author Date
placeholder Nice Practical Example Patrick Smith 1/31/2008 12:02 AM
I cant make it work Hakan Ozgur 2/7/2008 10:59 AM
placeholder RE: I cant make it work Kent Sharkey 2/7/2008 12:17 PM
RE: RE: I cant make it work Conor Duffy 10/18/2009 1:36 PM
placeholder it works with SubSonic 2.1 Beta 1 Hakan Ozgur 2/8/2008 5:02 AM
ORM Manuel Esteves 2/26/2008 6:22 PM
placeholder RE: ORM Kent Sharkey 2/27/2008 8:37 PM
RE: RE: ORM Manuel Esteves 3/3/2008 4:12 AM

Please login to rate or to leave a comment.