Using SubSonic (to create a simple blog engine)

By: Kent Sharkey
Download Sample Code

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

Introduction

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

1. <?xml version="1.0"?>  
2. <configuration>  
3.     <configSections>  
4.       <section name="SubSonicService" type="SubSonic.SubSonicSection, SubSonic" />  
5.     </configSections>    
6.     <appSettings/>  
7.   <connectionStrings>  
8.     <add name="MyBlog"   
9.   connectionString="server=.\sqlexpress;integrated security=true;database=myblog;"/>  
10.   </connectionStrings>  
11.   <SubSonicService defaultProvider="MyBlog">  
12.     <providers>  
13.       <add name="MyBlog" type="SubSonic.SqlDataProvider, SubSonic"  
14.           connectionStringName="MyBlog" generatedNamespace="MyBlog" />  
15.     </providers>  
16.   </SubSonicService>  
17.   <system.web>  
18.     <compilation>  
19.       <buildProviders>  
20.         <add extension=".abp" type="SubSonic.BuildProvider, SubSonic" />  
21.       </buildProviders>  
22.     </compilation>  
23.   </system.web>  
24. </configuration>  

<?xml version="1.0"?> <configuration> <configSections> <section name="SubSonicService" type="SubSonic.SubSonicSection, SubSonic" /> </configSections> <appSettings/> <connectionStrings> <add name="MyBlog" connectionString="server=.\sqlexpress;integrated security=true;database=myblog;"/> </connectionStrings> <SubSonicService defaultProvider="MyBlog"> <providers> <add name="MyBlog" type="SubSonic.SqlDataProvider, SubSonic" connectionStringName="MyBlog" generatedNamespace="MyBlog" /> </providers> </SubSonicService> <system.web> <compilation> <buildProviders> <add extension=".abp" type="SubSonic.BuildProvider, SubSonic" /> </buildProviders> </compilation> </system.web> </configuration>

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

1. <SubSonicService defaultProvider="MyBlog">  
2.   <providers>  
3.     <add name="MyBlog" type="SubSonic.SqlDataProvider, SubSonic"  
4.         connectionStringName="MyBlog" generatedNamespace="MyBlog"  
5.         stripTableText="blog_"/>  
6.   </providers>  
7. </SubSonicService>  

<SubSonicService defaultProvider="MyBlog"> <providers> <add name="MyBlog" type="SubSonic.SqlDataProvider, SubSonic" connectionStringName="MyBlog" generatedNamespace="MyBlog" stripTableText="blog_"/> </providers> </SubSonicService>

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

1. <asp:DataList ID="PostList" runat="server">  
2.     <ItemTemplate>  
3.         <h3 class="postHeader">  
4.             <a href="post.aspx?id=<%#Eval("PostID")%>">  
5.                 <%# Eval("Title") %></a></h3>  
6.             <div class="postBody">  
7.                 <%# Eval("Body") %></div>  
8.             <div class="postDate">  
9.                 posted on  
10.                 <%# Eval("PostedOn") %></div>  
11.     </ItemTemplate>  
12. </asp:DataList>  

<asp:DataList ID="PostList" runat="server"> <ItemTemplate> <h3 class="postHeader"> <a href="post.aspx?id=<%#Eval("PostID")%>"> <%# Eval("Title") %></a></h3> <div class="postBody"> <%# Eval("Body") %></div> <div class="postDate"> posted on <%# Eval("PostedOn") %></div> </ItemTemplate> </asp:DataList>

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

1. Query q = new Query(MyBlog.Post.Schema)  
2.     .ORDER_BY(MyBlog.Post.Columns.PostedOn, "DESC");  
3. q.Top = "5";  
4. this.PostList.DataSource = q.ExecuteReader();  
5. this.DataBind();  

Query q = new Query(MyBlog.Post.Schema) .ORDER_BY(MyBlog.Post.Columns.PostedOn, "DESC"); q.Top = "5"; this.PostList.DataSource = q.ExecuteReader(); this.DataBind();

1. Dim q As New Query(MyBlog.Post.Schema) _  
2.     .ORDER_BY(MyBlog.Post.Columns.PostedOn, "DESC")  
3. q.Top = "5"  
4. this.PostList.DataSource = q.ExecuteReader()  
5. this.DataBind()  

Dim q As New Query(MyBlog.Post.Schema) _ .ORDER_BY(MyBlog.Post.Columns.PostedOn, "DESC") q.Top = "5" this.PostList.DataSource = q.ExecuteReader() this.DataBind()

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)

1. <div>  
2.     <h3 class="postHeader">  
3.         <%# p.Title %></h3>  
4.     <div class="postBody">  
5.         <%# p.Body %></div>  
6.     <div class="postDate">  
7.         posted on:<%# p.PostedOn %></div>  
8. </div>  

<div> <h3 class="postHeader"> <%# p.Title %></h3> <div class="postBody"> <%# p.Body %></div> <div class="postDate"> posted on:<%# p.PostedOn %></div> </div>

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)

1. protected MyBlog.Post p = null;  
2.   
3. protected void Page_Load(object sender, EventArgs e) {  
4.     int id = SubSonic.Sugar.Web.QueryString<int>("id");  
5.     if (id > 0) {  
6.         p = new MyBlog.Post(id);  
7.         this.DataBind();  
8.     } else {  
9.         Response.Redirect("default.aspx");  
10.     }  
11. }  

protected MyBlog.Post p = null; protected void Page_Load(object sender, EventArgs e) { int id = SubSonic.Sugar.Web.QueryString<int>("id"); if (id > 0) { p = new MyBlog.Post(id); this.DataBind(); } else { Response.Redirect("default.aspx"); } }

1. Protected p As MyBlog.Post  
2.   
3. Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)  
4.     Dim id As Integer = SubSonic.Sugar.Web.QueryString(Of Integer)("id")  
5.     If id > 0 Then  
6.         p = New MyBlog.Post(id)  
7.         Me.DataBind()  
8.     Else  
9.         Response.Redirect("default.aspx")  
10.     End If  
11. End Sub  

Protected p As MyBlog.Post Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Dim id As Integer = SubSonic.Sugar.Web.QueryString(Of Integer)("id") If id > 0 Then p = New MyBlog.Post(id) Me.DataBind() Else Response.Redirect("default.aspx") End If End Sub

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

1. <sub:ManyManyList ID="PostCategories" runat="server"   
2.                   MapTableName="blog_post_category_map"  
3.                   ForeignTableName="blog_categories" ForeignTextField="Name"   
4.                   PrimaryTableName="blog_posts" />  

<sub:ManyManyList ID="PostCategories" runat="server" MapTableName="blog_post_category_map" ForeignTableName="blog_categories" ForeignTextField="Name" PrimaryTableName="blog_posts" />

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

1. void BindAndSave(string id) {  
2.   
3.     Post item;  
4.     if (!String.IsNullOrEmpty(id) && id != "0") {  
5.         //it's an edit  
6.         item = new Post(id);  
7.     } else {  
8.         //add  
9.         item = new Post();  
10.     }  
11.       
12.     object valctrlTitle =   
13.         Utility.GetDefaultControlValue(Post.Schema.GetColumn("Title"),   
14.         ctrlTitle, isAdd, false);  
15.   
16.     item.Title = Convert.ToString(valctrlTitle);  
17.   
18.     object valctrlPostedOn =   
19.         Utility.GetDefaultControlValue(Post.Schema.GetColumn("PostedOn"),   
20.         ctrlPostedOn, isAdd, false);  
21.   
22.     item.PostedOn = Convert.ToDateTime(valctrlPostedOn);  
23.   
24.     object valctrlBody =   
25.         Utility.GetDefaultControlValue(Post.Schema.GetColumn("Body"),   
26.         ctrlBody, isAdd, false);  
27.   
28.     item.Body = Convert.ToString(valctrlBody);  
29.   
30.     //bind it  
31.     item.Save(User.Identity.Name);  
32.   
33.     //now save the ManyToMany control's data  
34.     this.PostCategories.PrimaryKeyValue = item.PostID.ToString();  
35.     this.PostCategories.Save();  
36. }  

void BindAndSave(string id) { Post item; if (!String.IsNullOrEmpty(id) && id != "0") { //it's an edit item = new Post(id); } else { //add item = new Post(); } object valctrlTitle = Utility.GetDefaultControlValue(Post.Schema.GetColumn("Title"), ctrlTitle, isAdd, false); item.Title = Convert.ToString(valctrlTitle); object valctrlPostedOn = Utility.GetDefaultControlValue(Post.Schema.GetColumn("PostedOn"), ctrlPostedOn, isAdd, false); item.PostedOn = Convert.ToDateTime(valctrlPostedOn); object valctrlBody = Utility.GetDefaultControlValue(Post.Schema.GetColumn("Body"), ctrlBody, isAdd, false); item.Body = Convert.ToString(valctrlBody); //bind it item.Save(<a href="http://User.Identity.Name">User.Identity.Name</a>); //now save the ManyToMany control's data this.PostCategories.PrimaryKeyValue = item.PostID.ToString(); this.PostCategories.Save(); }

1. Private Sub BindAndSave(ByVal id As String)  
2.   
3.     Dim item As Post  
4.     If Not String.IsNullOrEmpty(id) AndAlso id <> "0" Then  
5.         'it's an edit  
6.         item = New Post(id)  
7.     Else  
8.         'add  
9.         item = New Post()  
10.     End If  
11.   
12.     Dim valctrlTitle As Object = _  
13.       Utility.GetDefaultControlValue(Post.Schema.GetColumn("Title"), _  
14.       ctrlTitle, isAdd, False)  
15.   
16.     item.Title = Convert.ToString(valctrlTitle)  
17.   
18.     Dim valctrlPostedOn As Object = _  
19.       Utility.GetDefaultControlValue(Post.Schema.GetColumn("PostedOn"), _  
20.       ctrlPostedOn, isAdd, False)  
21.   
22.     item.PostedOn = Convert.ToDateTime(valctrlPostedOn)  
23.   
24.     Dim valctrlBody As Object = _  
25.       Utility.GetDefaultControlValue(Post.Schema.GetColumn("Body"), _  
26.       ctrlBody, isAdd, False)  
27.   
28.     item.Body = Convert.ToString(valctrlBody)  
29.   
30.     'bind it  
31.     item.Save(User.Identity.Name)  
32.     'now save the ManyToMany control's data  
33.     Me.PostCategories.PrimaryKeyValue = item.PostID.ToString()  
34.     Me.PostCategories.Save()  
35. End Sub  

Private Sub BindAndSave(ByVal id As String) Dim item As Post If Not String.IsNullOrEmpty(id) AndAlso id <> "0" Then 'it's an edit item = New Post(id) Else 'add item = New Post() End If Dim valctrlTitle As Object = _ Utility.GetDefaultControlValue(Post.Schema.GetColumn("Title"), _ ctrlTitle, isAdd, False) item.Title = Convert.ToString(valctrlTitle) Dim valctrlPostedOn As Object = _ Utility.GetDefaultControlValue(Post.Schema.GetColumn("PostedOn"), _ ctrlPostedOn, isAdd, False) item.PostedOn = Convert.ToDateTime(valctrlPostedOn) Dim valctrlBody As Object = _ Utility.GetDefaultControlValue(Post.Schema.GetColumn("Body"), _ ctrlBody, isAdd, False) item.Body = Convert.ToString(valctrlBody) 'bind it item.Save(<a href="http://User.Identity.Name">User.Identity.Name</a>) 'now save the ManyToMany control's data Me.PostCategories.PrimaryKeyValue = item.PostID.ToString() Me.PostCategories.Save() End Sub

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

1. protected void Page_Load(object sender, EventArgs e) {  
2.     //set up possible querystrings  
3.     String cat = SubSonic.Sugar.Web.QueryString<String>("category");  
4.     Int32 yr = 0;  
5.     Int32 mnth = 0;  
6.     Int32 dy = 0;  
7.       
8.     Int32.TryParse(Request.QueryString["year"], out yr);  
9.     Int32.TryParse(Request.QueryString["month"], out mnth);  
10.     Int32.TryParse(Request.QueryString["day"], out dy);  
11.   
12.     if (!String.IsNullOrEmpty(cat)) {  
13.         MyBlog.Category selectedCategory = new MyBlog.Category("Name", cat);  
14.         this.PostList.DataSource = selectedCategory  
15.             .GetPostCollection()  
16.             .OrderByDesc("PostedOn");  
17.     } else if (yr > 0) {  
18.         DateTime startDate, endDate;  
19.   
20.         if (mnth > 0) {  
21.             if (dy > 0) {  
22.                 //search by day, month, year  
23.                 startDate = new DateTime(yr, mnth, dy, 0, 0, 0);  
24.                 endDate = new DateTime(yr, mnth, dy, 23, 59, 59);  
25.             } else {  
26.                 //search by month, year  
27.                 startDate = new DateTime(yr, mnth, 1, 0, 0, 0);  
28.                 endDate = new DateTime(yr, mnth + 1, 1, 23, 59, 59).AddDays(-1);  
29.             }  
30.         } else {  
31.             //search only by year  
32.             startDate = new DateTime(yr, 1, 1, 0, 0, 0);  
33.             endDate = new DateTime(yr + 1, 1, 1, 23, 59, 59).AddDays(-1);  
34.         }  
35.         MyBlog.PostCollection posts = new MyBlog.PostCollection()  
36.             .BetweenAnd(MyBlog.Post.Columns.PostedOn, startDate, endDate)  
37.             .OrderByDesc(MyBlog.Post.Columns.PostedOn)  
38.             .Load();  
39.         this.PostList.DataSource = posts;  
40.     } else {  
41.         //no querystring request  
42.         Query q = new Query(MyBlog.Post.Schema);  
43.         q.ORDER_BY(MyBlog.Post.Columns.PostedOn, "DESC");  
44.         q.Top = "5";  
45.         this.PostList.DataSource = q.ExecuteReader();  
46.     }  
47.     this.DataBind();  
48. }  

protected void Page_Load(object sender, EventArgs e) { //set up possible querystrings String cat = SubSonic.Sugar.Web.QueryString<String>("category"); Int32 yr = 0; Int32 mnth = 0; Int32 dy = 0; Int32.TryParse(Request.QueryString["year"], out yr); Int32.TryParse(Request.QueryString["month"], out mnth); Int32.TryParse(Request.QueryString["day"], out dy); if (!String.IsNullOrEmpty(cat)) { MyBlog.Category selectedCategory = new MyBlog.Category("Name", cat); this.PostList.DataSource = selectedCategory .GetPostCollection() .OrderByDesc("PostedOn"); } else if (yr > 0) { DateTime startDate, endDate; if (mnth > 0) { if (dy > 0) { //search by day, month, year startDate = new DateTime(yr, mnth, dy, 0, 0, 0); endDate = new DateTime(yr, mnth, dy, 23, 59, 59); } else { //search by month, year startDate = new DateTime(yr, mnth, 1, 0, 0, 0); endDate = new DateTime(yr, mnth + 1, 1, 23, 59, 59).AddDays(-1); } } else { //search only by year startDate = new DateTime(yr, 1, 1, 0, 0, 0); endDate = new DateTime(yr + 1, 1, 1, 23, 59, 59).AddDays(-1); } MyBlog.PostCollection posts = new MyBlog.PostCollection() .BetweenAnd(MyBlog.Post.Columns.PostedOn, startDate, endDate) .OrderByDesc(MyBlog.Post.Columns.PostedOn) .Load(); this.PostList.DataSource = posts; } else { //no querystring request Query q = new Query(MyBlog.Post.Schema); q.ORDER_BY(MyBlog.Post.Columns.PostedOn, "DESC"); q.Top = "5"; this.PostList.DataSource = q.ExecuteReader(); } this.DataBind(); }

1. Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)  
2.     'set up possible querystrings  
3.     Dim cat As String = Sugar.Web.QueryString(Of String)("category")  
4.     Dim yr As Integer = 0  
5.     Dim mnth As Integer = 0  
6.     Dim dy As Integer = 0  
7.   
8.     Int32.TryParse(Request.QueryString("year"), yr)  
9.     Int32.TryParse(Request.QueryString("month"), mnth)  
10.     Int32.TryParse(Request.QueryString("day"), dy)  
11.   
12.     If Not String.IsNullOrEmpty(cat) Then  
13.         Dim selectedCategory As New MyBlog.Category("Name", cat)  
14.         Me.PostList.DataSource = selectedCategory _  
15.             .GetPostCollection() _  
16.             .OrderByDesc("PostedOn")  
17.     ElseIf yr > 0 Then  
18.         Dim startDate, endDate As DateTime  
19.   
20.         If (mnth > 0) Then  
21.             If (dy > 0) Then  
22.                 'search by day, month, year  
23.                 startDate = New DateTime(yr, mnth, dy, 0, 0, 0)  
24.                 endDate = New DateTime(yr, mnth, dy, 23, 59, 59)  
25.             Else  
26.                 'search by month, year  
27.                 startDate = New DateTime(yr, mnth, 1, 0, 0, 0)  
28.                 endDate = New DateTime(yr, mnth + 1, 1, 23, 59, 59).AddDays(-1)  
29.             End If  
30.   
31.         Else  
32.             'search only by year  
33.             startDate = New DateTime(yr, 1, 1, 0, 0, 0)  
34.             endDate = New DateTime(yr + 1, 1, 1, 23, 59, 59).AddDays(-1)  
35.   
36.             Dim posts As New MyBlog.PostCollection()  
37.             posts.BetweenAnd(MyBlog.Post.Columns.PostedOn, startDate, endDate) _  
38.                 .OrderByDesc(MyBlog.Post.Columns.PostedOn) _  
39.                 .Load()  
40.             Me.PostList.DataSource = posts  
41.         End If  
42.     Else  
43.         'no querystring request  
44.         Dim q As New Query(MyBlog.Post.Schema)  
45.         q.ORDER_BY(MyBlog.Post.Columns.PostedOn, "DESC")  
46.         q.Top = "5"  
47.         Me.PostList.DataSource = q.ExecuteReader()  
48.     End If  
49.   
50.     Me.DataBind()  
51. End Sub  

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) 'set up possible querystrings Dim cat As String = Sugar.Web.QueryString(Of String)("category") Dim yr As Integer = 0 Dim mnth As Integer = 0 Dim dy As Integer = 0 Int32.TryParse(Request.QueryString("year"), yr) Int32.TryParse(Request.QueryString("month"), mnth) Int32.TryParse(Request.QueryString("day"), dy) If Not String.IsNullOrEmpty(cat) Then Dim selectedCategory As New MyBlog.Category("Name", cat) Me.PostList.DataSource = selectedCategory _ .GetPostCollection() _ .OrderByDesc("PostedOn") ElseIf yr > 0 Then Dim startDate, endDate As DateTime If (mnth > 0) Then If (dy > 0) Then 'search by day, month, year startDate = New DateTime(yr, mnth, dy, 0, 0, 0) endDate = New DateTime(yr, mnth, dy, 23, 59, 59) Else 'search by month, year startDate = New DateTime(yr, mnth, 1, 0, 0, 0) endDate = New DateTime(yr, mnth + 1, 1, 23, 59, 59).AddDays(-1) End If Else 'search only by year startDate = New DateTime(yr, 1, 1, 0, 0, 0) endDate = New DateTime(yr + 1, 1, 1, 23, 59, 59).AddDays(-1) Dim posts As New MyBlog.PostCollection() posts.BetweenAnd(MyBlog.Post.Columns.PostedOn, startDate, endDate) _ .OrderByDesc(MyBlog.Post.Columns.PostedOn) _ .Load() Me.PostList.DataSource = posts End If Else 'no querystring request Dim q As New Query(MyBlog.Post.Schema) q.ORDER_BY(MyBlog.Post.Columns.PostedOn, "DESC") q.Top = "5" Me.PostList.DataSource = q.ExecuteReader() End If Me.DataBind() End Sub

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.

Summary

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?

Source: http://dotnetslackers.com/articles/aspnet/usingsubsonictocreateasimplebogengine.aspx