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
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
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
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_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
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
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
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.
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=#&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?
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.