Sometimes, it's better to roll your own ...
Programming is more than click and drag.  

Revised January 2011

Even though the site is still heavily focused on Microsoft Access, I've been working and playing with other tools for a few years now.  It just took me longer to get around to writing about them here. 

After I got serious about sharpening my skills with ASP.NET, Microsoft's web building technology that blends HTML with .NET code to deliver dynamic web pages, it didn't take me long to think of ways that I could use it with this site. Drewslair.com has gone through a mini-evolution over the years, starting out in Microsoft Frontpage and then shedding all the FrontPage-specific features after Microsoft dropped support for the FrontPage extensions on Linux and my hosting service raised the possibility of dropping them in the future. This prompted me to learn about Cascading Style Sheets, Server-Side Includes and other fun stuff so I could keep the same look and feel. 

This time around, switching to ASP.NET meant moving away from FrontPage entirely and redoing the site in Microsoft Visual Studio.  It took awhile but it was a fun project and I learned a lot in the process.

I didn't see myself needing to add user logins to the site but I did have fun playing around with ASP.NET's navigation features and master pages that I could use to replace the Server-Side Includes that I use to share borders across the site. The tutorial that I was using advised using tables to setup the format for the master page that would be inherited by other pages but I quickly verified that the style sheet formatting worked just as well and I was able to keep the same look.

What I thought would be an interesting touch would be to display random quotes in the top border of the site next to the site logo. It looked kind of empty as I'd never come up with something to put there.

I initially decided to store the quotes in a SQL Server 2005 database, pull them at random when the page loaded and display them using a Literal control which accepts literal text, including HTML tags, and inserts it into the page. By feeding the right HTML code to the control, I could make it display the quote text with a carriage return between the quote and the author's name and position itself at exactly the spot on the page I wanted. Making these functions part of the site's master page would cause all pages based on it to inherit the functions.

First, though, I had to decide how I was going to get the data that I needed from the database. Here are the steps I decided to follow.

1. Count the number of records in the quotes table.
2. The quote IDs are sequential, so generate a random number between 1 and the number of quotes available.
3. Go back to the database and get the record with a quote ID matching the number generated.
4. If there's any error in retrieving a quote, manually display a quote that's hard-coded into the site but not present in the database.  This keeps the site working and lets me know there's a problem when I view the site.

Ideally, step #4 would then have the site send me a notification by e-mail of the problem and maybe write the details of the error to a logfile but that can come later. 

Visual Studio features a SQLDataSource control which makes binding data to a collection of page controls a breeze. You can throw this control on a page, configure it through dialog settings and have the controls reference it to display data. Saving and updating data back to the database is also ridiculously easy. The problem was that in order to get the steps above to happen in the right order, I needed to code them in Visual Basic rather than plot them out through controls on the page. When I was working on the site redesign, I wasn't able to find a way to do this with the SQLDataSource control. While the page-bound controls could access the SQLDataSource's data, I had trouble getting at that data through code.  After reviewing the documentation further, I found a simple way to do it but at that time, I didn't see it. 

That's when I decided to go back to being a programmer and write my own class to manage the connection to the database and accept SQL commands to return the data I needed in the form of string values and data tables. (Click here to see the code.) It took me longer than using the SQLDataSource but was much more satisfying and gave me the control I needed. Then I wrote the code in the Page_Load event of the master page to pass the necessary SQL commands to the data class, read the data coming back and display it on the page. (Click to view the code.) Any page that inherits the master page would then display a quote as shown below.

Simpler Storage

After playing around with this for awhile and seeing it work, I started thinking that an entire SQL Server database was a little much for a read-only collection of quotes.  It also required me to either login to the site control panel and go through a number of screens to add and edit quotes or setup a new page on the website just for that purpose and arrange the security so that nobody else could hack into it.  That's when I decided to change to an XML quotes file instead.  XML is a flat-file text format that uses custom tags very similar to HTML to format data in a way that can be easily translated to and from other database formats.  Here's a sample of the quote file in XML:

<Quote> 
<QuoteText>I love deadlines. I like the whooshing sound they make as they fly by.</QuoteText>
<Author>Douglas Adams</Author>
</Quote>

<Quote>
<QuoteText>Every composer knows the anguish and despair occasioned by forgetting ideas which one had no time to write down.</QuoteText>
<Author>Hector Berlinoz</Author>
</Quote>

<Quote>
<QuoteText>For disappearing acts, it's hard to beat what happens to the eight hours supposedly left after eight of sleep and eight of work.</QuoteText>
<Author>Doug Larson</Author>
</Quote>

As you can see, each quote block is marked by tags defining the data.  The names of these tags are completely at my discretion as long as they're consistent.  This results in a simple text file that I can easily update as needed.  The program can then import the data from this file into a table, using the tags as column names and follow roughly the same steps to select a random quote; count the number available, generate a random number between 1 and the number available and then display the quote corresponding with that number in the list. (Click to view the code.) There are fewer things that can go wrong since the program is dealing with a text file rather than going through another piece of software to access a proprietary database format.  It's probably faster, too.

Back to SQL

Earlier, I mentioned that I did eventually find a way to tap into the data coming from the SQLSourceControl and, rather than taking up design space on the page, I declared it through code. (Click to view the code.)  The Select command of the SQLDataSource creates a DataView object, the DataTable of which can be read through code.  With a few extra statements, the quotes can be retrieved into this table and a random quote can be selected as easily as from the XML file.

Going Live

I couldn't offer this on the Web until I switched to a hosting service that supported ASP.NET.  I hated leaving my old service but in addition to the support for the new features, I gained some extra web resources and was able to setup two sites on the same hosting account.  I finally decided to have one for Drewslair.com and one for AndrewComeau.com (.net, .org ...).  AndrewComeau.com, which was online during 2010, hosted the ASP.NET pages and Drewslair.com remained the same to preserve the links on Google and other search engines. 

Sidetracked by other priorities, I didn't do much else with the ASP.NET site and I decided to return to the previous host and maintain a single site for the time being. Since that means going back to Linux hosting, I'll probably have to figure out how to program the features in PHP if I want to display the quotes again. It was a valuable experiment, however, and I'm glad I took the time to try it out.

 

See more articles on Drewslair.com