CAPTCHASP - Defend Your ASP.NET Web Sites against Evil Bots

By Steve C. Orr

Robots are taking control of the Internet! Don't let them overwhelm your Web site with their unrelenting, self-serving probes. Now you can fight back with this free control that allows you to discriminate between human and computer visitors.

While this might sound like a sci-fi promotion for the next Terminator or Transformers movie, in a way, that ominous sci-fi future is already here. But don't be too afraid — just like in the movies, there are robots here to help us, too.

Not All Robots Are Bad

Robots are automated software systems that perform functions normally expected to be done by people. They send e-mail, surf the Web, send instant messages, etc. Such bots can be used for good. For example, Google's multitude of bots surf virtually all public Web sites and collect bits of information that it uses to help people search and find those Web sites. Google's bots are generally considered to be respected and responsible members of the Internet, because they abide by requests for privacy and, in exchange for the small amount of shared Internet resources they consume, provide a useful service that's valuable to nearly everybody.

The problem is that bad people can make bots, too — and their bots may do bad things. At their worst, bots have been used to take control of unsuspecting victims' computers. When a hacker "owns" enough computers through such a process, their army of zombie PCs can march across the Internet doing evil deeds, such as swiping credit card numbers, breaking into more computer systems, and taking down major Web sites by overwhelming them with phony Web page requests.

Then there are the bots in the middle ground. Maybe their creators didn't intend for them to do bad things, but, nevertheless, many people consider them to be nuisances — or worse. In the early days of the World Wide Web, the idea of bots caught fire, and this borderline kind of bot ran rampant and unhindered. This caused problems for many pioneers of the Internet.

Companies such as eBay and Ticketmaster were dominant on the Web in their respective industries; their tiny competitors were struggling, in comparison. They couldn't compete with Ticketmaster if they didn't have tickets to sell, and they couldn't compete with eBay without plenty of auctions to attract members. So they made bots that would simply use these big-name sites in the background. If you bought a ticket from their budding Web site, their bots would actually go to Ticketmaster's site and buy the ticket and then pass it on to you. This might not have worked out so badly if it weren't for the fact that these bots had to constantly probe the sites they were using to keep their lists of data fresh and in sync. In time, and as such tactics caught on, the number of bots increased, and their constant automated requests started to take a heavy toll on the servers they were accessing. Ticketmaster and eBay were getting irked that their competitors were making money by consuming their expensive data, resources, and computer systems without permission.

Friendly tactics were tried at first, such as asking these little companies to go away and buy their own computers. That didn't work — mostly because these little companies couldn't afford to compete with the big boys. Then legal actions were tried. But even today there are precious few laws regarding such matters, and back then there were virtually no laws covering such newfangled concepts. So what was the solution to be?

Yahoo had a bot problem, too. Its free e-mail system was being abused by spam bots that were automatically signing up for thousands of e-mail accounts and then exploiting them to send junk e-mail to people all over the world. Yahoo enlisted the help of a Carnegie Mellon University team that came up with a brilliant technological solution.

CAPTCHA to the Rescue

CAPTCHA effectively immunizes a Web site against bots. It stands for "Completely Automated Public Turing Test To Tell Computers and Humans Apart". Its foundation lays in the fact that while computers are brilliantly useful at some kinds of things (like calculating equations and tracking data), there are still many tasks that the human brain is much better equipped to handle.

For example, a human can glance at a painting such as the Mona Lisa and see in an instant — without even thinking about it — that it's a picture of a beautiful woman sitting down with a demure smile. On the other hand, even today's most cutting-edge optical recognition computer systems would be hard-pressed to even be able to tell you definitively that there is a human being in such a picture.

Optical character recognition systems (which "read" text from an image) fair a little better — primarily because there are a finite number of characters in the alphabet. However, even these programs work only semi-reliably when under optimal conditions: clear black print on a plain white background. Colors, blurriness, fancy fonts, handwriting, symbols, embedded pictures, and crooked text are just a few of the common conditions that tend to confuse optical character recognition systems, making them unable to recognize the writing contained in a scanned image.

So these days, Web sites that wish to defend themselves against bots use the CAPTCHA concept to display a picture that contains crooked, colorful, blurry text with varying fonts and asks the user to type in what they see (see Figure 1). It is a simple task for any legitimate human user, but a virtually insurmountable chore for bots. Therefore, a Web site can be reasonably sure that any user who makes it through their CAPTCHA gateway is a person, not a computer.

Figure 1: Ticketmaster implements an advanced CAPTCHA system that lets users in and keeps bots out.

Using CAPTCHASP

CAPTCHASP is a custom Web control I created to easily add CAPTCHA verification to any ASP.NET Web site (see Figure 2). Simply drag the CAPTCHASP.DLL onto your Visual Studio toolbox, then drag it from there onto any Web form and you've got instant CAPTCHA (see end of article for download details). Unlike most controls that generate images, there is no dependency on outside pages, resources, HTTP Handlers, or web.config settings. This is because of a novel development technique I used that will be detailed in next month's follow-up article that delves into CAPTCHASP's source code. Simple, standard xcopy deployment is all that's needed — and it's virtually impossible to mess up.

Figure 2: The CAPTCHASP control can be dragged onto any ASP.NET Web form to provide instant, highly customizable CAPTCHA verification.

After the control's been dropped on your Web form, the only other thing that's vitally important for you to know is that the control will raise its UserVerified event when the user has entered the correct codeword and therefore been proven to be a real human. From that event you can then choose to let the user in and perhaps set some kind of flag to remember that the user has successfully been verified.

This is all you really need to know to use CAPTCHASP, but I suggest you keep reading to learn how to take advantage of the many optional features the control offers (see Figure 3 for the complete list of CAPTCHASP events).

CAPTCHASP Events	Parameter	Description
UserVerified	n/a	This event is raised when the user has entered the correct code and therefore been proven to be human. Alternatively, you could ignore this event and instead call the Validate method followed by a check of the IsValid property.
VerificationFailure	FailCount (Integer) ByVal (in)	This event is raised when the user has entered an incorrect code. The FailCount parameter specifies how many consecutive times they've failed to enter the correct code. An exception will be thrown after 15 invalid attempts, so you may want to handle this exception or deal with the suspected bot in some other way before that happens.
CodeWordSelection	CodeWord (String) ByRef (in/out)	This event is raised when it's time to choose a codeword for display in the image portion of the control. The control's suggested codeword will be provided by the modifiable CodeWord parameter, unless the CodeWordType property is set to Custom, in which case you'll be required to provide your own codeword via the CodeWord parameter.

Figure 3: CAPTCHASP has three events that supply potentially useful information to the page.

Choosing CodeWords

The "CodeWord" is the CAPTCHA characters the user sees in the image and types in to be validated. The CodeWord may be a series of random characters, an actual word, or some combination thereof, depending on how CAPTCHASP has been configured.

By default, CAPTCHASP's CodeWordType property is set to its RandomCharacters enumeration value. This will cause the control to generate a random series of lowercase characters. The AddSymbols property, when set to its default value of True, will mix in some symbol characters, as well. The number of characters generated for each CodeWord is determined by the NumberOfCharacters property, which has a default value of 5, a minimum value of 3, and a maximum value of 10.

If the CodeWordType property is set to the UseWordList enumeration value, CAPTCHASP will randomly choose a CodeWord from the comma-separated list of words in the WordList property. The WordList property comes pre-populated with a list of more than 150 English words that are well thought out to be clear to humans, but confusingly similar to bots. Of course, you can add to this list, modify it, replace it with your own list, or customize it in any way you wish.

In all cases, the control's randomly chosen CodeWord is sent as a parameter through the CodeWordSelection event. This gives the page code a chance to observe the value or request a different CodeWord be randomly generated by calling the GenerateNewCodeWord method. The CodeWord parameter is also modifiable so you can optionally replace the CodeWord with one of your own choosing.

However, if the CodeWordType property is set to the Custom enumeration value, the CodeWordSelection event's CodeWord parameter becomes required; the CAPTCHASP control will not take the time to randomly select a CodeWord — instead, the page will be expected to supply it with one. This option is nice for situations where you want to always use your own function to generate a custom CodeWord or dynamically grab one from a data source of your choosing (see Figure 4).

CodeWord-related Properties	Property Type	Description
CodeWordType	Enumeration	Specifies whether the CAPTCHASP control should automatically generate a random series of letters for the CodeWord, whether it should randomly choose a word from the WordList property, or whether you prefer to supply it with a custom CodeWord.
AddSymbols	Boolean	When set to its default property of True and the CodeWordType property is set to its default of RandomLetters, symbol characters will be randomly mixed in with lowercase letters to create the CodeWord.
NumberOfCharacters	Byte	When the CodeWordType property is set to its default of RandomLetters, this property specifies how many randomly generated characters each CodeWord should contain.
WordList	String	A comma-separated list of words from which the control will randomly pick a CodeWord when the CodeWordType property is set to UseWordList. There are more than 150 pre-populated default words.

Figure 4: The CAPTCHASP control provides a variety of ways to customize the CodeWords that are displayed to the user.

For security reasons, CodeWords of at least three characters are required. For clarity and usability reasons, you should avoid supplying CodeWords of more than 10 characters. It's also good to be aware that the letter "l" and number "1" look confusingly similar; therefore, you may want to avoid using one or both of them. Likewise, the number zero ("0") is often confused with the letter "o". The CAPTCHASP control takes into consideration these issues when generating its random CodeWords. One way it does this is by not using any numbers. Additionally, user input is case-insensitive, so users need not worry about accidentally entering an uppercase "O" where a similar looking lowercase "o" was expected. And the default WordList contains no numbers nor the letter "l".

Cosmetic Customizations

The look and feel of CAPTCHASP can be configured in a variety of ways. Virtually every aspect of the control's appearance can be altered via properties and styles. Figure 5 demonstrates many of the control's optional user interface elements and customizations. I don't necessarily recommend altering the control's appearance to this much of an extreme, but it's nice to know you can.

Figure 5: Virtually every aspect of CAPTCHASP's appearance (including several optional elements) can be altered via properties and styles — even to ugly extremes such as this!

The optional title area (at the top of Figure 5 in green) can be shown by setting the TitleText property to the text you'd like to appear there. You can adjust the TitleStyle property elements to change how it looks in a variety of standard ways.

The InstructionText property can be used to change the text that is displayed above the textbox. Its InstructionStyle property elements can be used to adjust its look in many ways, and has been used in Figure 5 to apply a fancy italic font.

A hyperlink can be displayed to explain in more detail why the user must go through this process. This "Why?" element (shown in blue in Figure 5) pops up a customizable message when clicked. The WhyStyle property elements can be used to adjust the look and feel of this hyperlink. The CodeWord entry textbox can also be adjusted in a variety of ways via the TextEntryStyle property elements. Figure 5 demonstrates this with purple text.

The Submit button (shown in orange in Figure 5) has ButtonStyle property elements associated with it to adjust its appearance. The ButtonText property can be used to change the button text from its default of Submit. The ShowSubmitButton Boolean property can be changed to False to make the button invisible, in case you'd like to implement your own submit button (or link) elsewhere on the page. Such a custom submit element would need to call the CAPTCHASP control's validate method to trigger the control to check if the user's entry is correct or not.

If the user enters the wrong code, the FailMessage will appear, as shown in Figure 5 in red. The FailMessageStyle property elements can be used to adjust visual aspects, and the FailMessageText property can be used to change what it says.

Finally, there is an optional ChangeCodeWord hyperlink that can be shown at the bottom of the control. Figure 5 displays this link highlighted in yellow via the ChangeCodeWordStyle property elements. The ChangeCodeWordText property can be used to change what the link says. The ShowChangeCodeWordLink property can be changed to True (from its default of False) to get this link to appear. When the user clicks this link the control will generate and display a new CodeWord. The FailCount property will be incremented each time this happens to help prevent abuse by any cherry-picking bots that feel brave enough to attempt to decode a CAPTCHA image. Instead of displaying this built-in link, you could implement your own link elsewhere on the page to change the CodeWord. It would simply need to call CAPTCHASP's server-side GenerateNewCodeWord method.

Conclusion

You should now understand what CAPTCHA is, as well as how and why it came to be. With the CAPTCHASP control you can now easily immunize your ASP.NET Web site to keep bots out and let legitimate users in.

The CAPTCHASP control is freely downloadable to everyone. You can download it or try the live demo right now.

References

• Live Demo
• Control Download (Free!)

The original version of this article was published in the September 2007 issue of ASP.NET Pro Magazine. 

 

Sourc: http://www.steveorr.net/articles/CAPTCHASP.aspx

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