Sometimes, it's better to roll your own ...
Programming is more than click and drag.
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. I just never got
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
and 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 don't see myself needing to add user logins to the site anytime soon but I did
have fun playing around with the navigation features and master pages that I could
use to replace the Server-Side Includes that I've been using to share borders across
the site for the past year or so. 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.
That's a big empty space.
(Click to view full size.)
I initially decided to store the quotes in a SQL Server 2005 database, pull them at random during the
load event of the master page 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.
Before that, I had to decide how I was going to
get the data that I needed
from the database. Here are the steps I was going to follow.
- Count the number of records in the quotes table, indicating the number of quotes
available.
- The quote IDs are sequential, so generate a random number between 1 and the number
of quotes available.
- Go back to the database and get the record with a quote ID matching the number generated.
- 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 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 the steps listed above in Visual
Basic rather than plot them out through controls on the page and from what I can
tell so far, the SQLDataSource was never really meant to be used from code. I was
hopeful at first, seeing easy ways to change the control's SQL command but I never
did find a way to get the data that it was retrieving.
That's when I decided to go back to being a programmer and write my own code
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. 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. Any page that inherits the master page would then display a quote as
shown below.
That's much better.
(Click to view full size.)
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 collection of quotes
that was, for the most part, read-only. 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. 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.
I couldn't put this out
live 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 can host the
new ASP.NET pages and Drewslair.com can remain the same for now to preserve the links on Google and other search engines.