Scott On Writing.NET

IIS7, Global Themes, CSS Files and Subfolders

Scott Mitchell — Thu, 02 Aug 2012 18:12:13 GMT

Ran into an interesting and esoteric problem today when working with a client. This client had a legacy ASP.NET 2.0 application deployed on IIS6 that used global themes. First, a little background…

A theme is: “a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in a Web application, across an entire Web application, or across all Web applications on a server.” Themes commonly include images and CSS files, the latter of which are automatically added to the <head> section of an ASP.NET page that uses said theme. Typically, themes are placed in the App_Theme folder within the web application; however, it is possible to create global themes that can be used across multiple websites on a single web server. When using global themes, the themes are placed in the aspnet_client/system_web/version/Themes folder, located at the webserver’s root (typically C:\Inetpub\wwwroot).

My client had been using IIS6 and global themes. In particular, the theme contained both images and CSS files, along with skins. The CSS files were all located in a subfolder of the theme named styles and the images in a subfolder named images, resulting in a directory structure like so:

MyTheme
  |
  |-- images
  |    |
  |    |-- image1.png
  |    |-- image2.png
  |    |-- ...
  |
  |-- styles
  |    |
  |    |-- main.css
  |    |-- admin.css
  |    |-- ...
  |
  |-- GridView.skin
  |-- DetailsView.skin
  |-- ...

When configuring a page to use MyTheme, the page’s rendered markup would automatically include references to the CSS files defined in the MyTheme\styles subfolder (main.css, admin.css, etc.). This worked swimmingly when used as a global theme in IIS6 or when used directly in the App_Themes folder (on both IIS6 and IIS7). However, when my client deployed to IIS7 using global theme structure above the CSS files were not included in the web page. There were no errors when visiting the page, but it clearly wasn’t styled.

After some poking and prodding I decided to try moving the CSS files out of the styles subfolder and into the MyTheme folder (alongside the skin files) and, voila, the CSS files were automatically included in the rendered page’s output. As to why the theme structure above works as a global theme in IIS6, as to why it works when deployed to the App_Themes folder in both IIS6 and IIS7, but as to why it does not work as a global theme is IIS7 is anyone’s guess. The good news is that there is a workaround and it is pretty straightforward.

Happy Programming!

ERROR: Resource name XXX cannot be used more than once

Scott Mitchell — Wed, 02 May 2012 21:43:19 GMT

A couple weeks ago I had a client call me up with a perplexing build error. He had an ASP.NET 4 Web Application project open in Visual Studio 2010 that he was working on when, suddenly, the following error cropped up when building:

Resource name ‘xxx.resources’ cannot be used more than once.

There was no line number, just this error message. According to my client, the project built fine minutes before the error started appearing and, in the interim, he had not made any changes that he was aware of: he had not changed any code; had not created any new web pages; had not deleted anything; had not changed the project’s settings.

Unfortunately, we were unable to find much help online. While there are a lot of messageboard posts and blog entries noting this error, most of them were in the context of duplicate key names in resource files – my client’s project was not using resource files.

Anyway, we banged our heads on it for a good hour before we finally chose the nuclear option: start excluding files one by one, building after each one, and see if the error goes away. A ham-fisted approach for sure, but one that would likely identify the culprit.

Eventually we got to the obj folder – why was that part of the project? The obj folder As we started working through the files, I noticed in Solution Explorer that the obj folder holds temporary build files, including .resource files. I presume that when building the project Visual Studio was attempting to create the same files that were already part of the project, and hence the error. In any event, excluding the obj folder from the project did the trick. Once it was omitted the project built without error.

So how did this happen? My client likes having the “Show all files” option turned on in the Solution Explorer. My guess is that he inadvertently right-clicked on the excluded obj folder and clicked the “Include in project” option. Or something, because he said he did not intentionally add the folder to his project. Regardless, its presence caused the build to choke and removing it fixed everything.

Thought I would share this experience here so others would have another possible avenue to check in the event that they receive this build error.

Happy Programming!

Using Log Parser to List All Blocked IP Requests

Scott Mitchell — Sun, 08 Apr 2012 04:02:55 GMT

In a recent project we needed to block a series of IP addresses from accessing our website. IIS makes this easy with its IPv4 Address and Domain Restrictions feature, which lets the webmaster specify specific or ranges of IP addresses that are either allowed or denied access to the website. See Configure IPv4 Address and Domain Name Rules for more information.

After blocking the IP addresses of interest we wondered, how often are those blocked addresses attempting to access the website? Whenever IIS blocks an IP address it returns a particular HTTP status code - 403.6. Therefore, if we could search the IIS log files for all requests that returned a 403.6 status code we would know what banned IP addresses were attempting to access what pages and when.

Of course we weren’t at all interested in manually pouring through the log files. Fortunately, there is Log Parser. Log Parser is a free command-line tool from Microsoft for searching through IIS log files using a SQL-like syntax. We ended up using the following command, which provides the IP address, the requested URL, and the local date/time of the blocked request ordered from the most recent blocked request to the oldest. The results are outputted as a CSV file. (Note: the extra spaces and carriage returns in the below command are for readability only; remove this whitespace before attempting to run the command from the command line.)

LogParser.exe -i:W3C 
       "SELECT c-ip as IP, 
               cs-uri-stem as URL, 
               TO_LOCALTIME(TO_TIMESTAMP(date, time)) AS DateTime 
        FROM c:\inetpub\logs\LogFiles\W3SVC1\*
        WHERE TO_STRING(sc-status) = '403' 
              AND TO_STRING(sc-substatus) = '6' 
        ORDER BY TO_LOCALTIME(TO_TIMESTAMP(date, time)) DESC" 
        -o:CSV

Note the SQL-like syntax – very easy to read and understand for a DBA or developer who works regularly with SQL. Log Parser supports the standard SQL clauses, including GROUP BY clauses. Log Parser also supports a variety of output types. Above I request the data to be outputted as a CSV (see the –o:CSV switch) but I could have chosen the output as an XML file, a grid – even a chart!

For more on Log Parser, along with some common queries, check out the following resources:

There is also a Samples folder that is included when you install Log Parser with dozens of sample queries.

Happy Programming!

Uploading a Variable Number of Files from an ASP.NET Web Page

Scott Mitchell — Fri, 30 Mar 2012 23:15:00 GMT

ASP.NET has long offered the FileUpload control, which simplifies uploading a file from the client’s computer to the web server’s file system. This control provides a very simple, straight-forward user experience – the browser renders a “Browse” or “Select File” button that, when clicked, brings up a dialog box from which the user can select a file from her computer. Behind the scenes, the FileUpload control renders as an <input type=”file” /> HTML element and sets the WebForm’s encoding type to multipart/form-data. The <input type=”file” /> HTML element is what instructs the browser to render the file upload interface and the multipart/form-data encoding type instructs the browser to send the form’s contents (including the binary contents of the selected file) to the server as a multipart MIME message, which allows for transmission of binary data.

Recently, I was working on a web page where users needed the ability to upload a variable number of files. While most users would only upload a single file, some would need to upload two, while others three, four, or possibly more. Since a single FileUpload control can upload only a single file, one option was to add four (or five or six) FileUpload controls on the page, but that would crowd the page and be overkill for the majority of users who only needed to upload a single file. We considered using a third-party file upload control (such as SlickUpload) that would supports richer upload scenarios, including multifile uploads, but ended up with a simpler solution that involved a bit of HTML and jQuery.

In particular, we had the page initially contain a single <input type=”file” /> HTML element. Next, we added an “Upload another file” link that, when clicked, executed JavaScript that dynamically added another <input type=”file” /> HTML element to the DOM. Additionally, we added a “Remove” link next to each <input type=”file” /> HTML element that, when clicked, removed the associated file upload user interface from the DOM.

Show Me!

Here is a demo of this concept. First the HTML. Note that there is a form whose enctype attribute is explicitly set to multipart/form-data. Inside the form is a paragraph with an id of uploadUI and an “Upload another file” link with the id of addFileUpload. There’s also a Button Web control (btnUpload) that, when clicked, will postback the form, submitting the file contents to the server. We’ll see how to access the uploaded files from server-side code shortly.

<form id="form1" runat="server" enctype="multipart/form-data">
    <p id="uploadUI">
            
    </p>
    <p>
        [<a href="#" id="addFileUpload">Upload another file...</a>]
    </p>
    <p>
        <asp:Button ID="btnUpload" runat="server" Text="Upload Files" 
            onclick="btnUpload_Click" />
    </p>
</form>

The bulk of the content in this page is the JavaScript that creates and removes the <input type=”file” /> HTML elements in response to the user clicking the “Upload another file” and “Remove” links. Each file upload user interface is comprised of:

A <div> element with an id of fileContainerCount, where Count is a variable that starts at 0 and is incremented each time a new file upload interface is added to the DOM. This <div> element contains the following:
An <input type=”file” /> HTML element
A “Remove” link that has a class attribute value of removeUpload

The file upload user interface is added by the createFileUpload function. The <div> element (and its containing elements) are added to the end of the uploadUI paragraph.

var uploadControlCounter = 0;

function createFileUpload() {
    $("#uploadUI")
        .append(
            $("<div />")
                .attr("id", "fileContainer" + uploadControlCounter)
            .append(
                $("<input />")
                    .attr("type", "file")
                    .attr("name", "file" + uploadControlCounter)
            )
            .append(" ")
            .append(
                $("<a />")
                    .attr("href", "#")
                    .attr("class", "removeUpload")
                    .text("Remove")
            )
        );

    uploadControlCounter++;
}

In the $(document).ready event handler I wire up the click event handlers for the “Upload another file” and “Remove” links, as well as add the initial file upload user interface. Note that for the “Remove” links I use the delegate behavior for jQuery’s on function because I want this event handler to fire for not just the current “Remove” links (which there are none), but for all “Remove” links that may be added to the DOM at a later point in time.

$(document).ready(function () {
    $("#addFileUpload").click(function (e) {
        e.preventDefault();
        createFileUpload();
    });

    $("#uploadUI").on("click", "a.removeUpload", function (e) {
        e.preventDefault();
        $(this).parent().remove();
    });

    // Start by creating one file upload
    createFileUpload();
});

Onto the Server-Side…

When the user clicks the “Upload Files” button their browser will make a request back to the page sending the selected files’ contents. We can inspect the set of uploaded files on the server-side by looping through the Request object’s Files collection. The Request.Files property is a collection of HttpPostedFile objects, which have properties that provide the file name, content type, content length, and a stream to the raw binary data. In my demo I loop through the Request.Files collection and for each file save it to the ~/Uploads folder, but since you have access to the raw uploaded binary data via the HttpPostedFile object’s InputStream property you could do whatever it is you needed to do, such as save the contents to a database, inspect the contents in memory, and so on.

// Save each upload and redirect user to thank you page
for (int i = 0; i < Request.Files.Count; i++)
{
    HttpPostedFile file = Request.Files;

    string filePath = Server.MapPath("~/Uploads/" + Path.GetFileName(file.FileName));

    using (FileStream streamToDisk = File.Create(filePath))
    {
        file.InputStream.CopyTo(streamToDisk);
        streamToDisk.Close();
    }
}

That’s all there is to it! You can download the complete demo from http://scottonwriting.net/demos/MultiFileUpload.zip

Happy Programming!

Using Templates to Display Boolean Values as Yes/No Options

Scott Mitchell — Tue, 22 Nov 2011 01:11:08 GMT

One of ASP.NET MVC’s most useful features is its powerful templating system. Templates were introduced with ASP.NET MVC 2 as a way to have the view render either a display- or editing-related user interface for the entire model or for a property of the model. The Html.DisplayFor and Html.EditorFor methods provide a strongly-typed mechanism for rendering the appropriate template for a particular model property:

@Html.DisplayFor(model => model.PropertyName)

@Html.EditorFor(model => model.PropertyName)

For an in-depth overview of templates in ASP.NET MVC, see Brad Wilson’s multi-part article series, ASP.NET MVC 2 Templates. (The content in Brad’s articles apply the same to ASP.NET MVC 3.)

How Html.DisplayFor and Html.EditorFor Determine Which Template to Use

ASP.NET MVC includes a number of built-in display and editor templates. For instance, when rendering a display or editing template for a Boolean value ASP.NET MVC will render either a disabled or enabled checkbox. When rendering a display template for an email address, ASP.NET MVC will render an actual mailto: anchor tag so that the email address is clickable. The editor template for a password property renders an <input type=”password” /> textbox so that the user’s input is masked. Additionally, you can create your own custom display and editor templates.

The Html.DisplayFor and Html.EditorFor methods, when called, need to determine which template to render for the given property. To make this determination these methods examine the specified property’s metadata, which includes information like the property’s data type (bool, string, int, etc.) along with attributes decorating the model properties. For instance, in your model you can decorate a property with a DataType attribute to inform the view and the templating system the type of data this property expresses. In the following model, the property Password is decorated as a password data type, while the Bio property is decorated as a multiline text input (which will render a multiline textbox for this property’s editor template).

public class MyModel
{
    ...

    [DataType(DataType.Password)]
    public string Password { get; set; }

    [DataType(DataType.MultilineText)]
    public string Bio { get; set; }

    ...
}

Specifically, the Html.DisplayFor and Html.EditorFor methods consult the following metadata in this order:

The UIHint attribute - specifies the name of the template to use.
The DataType attribute
The data type

Creating a custom template in ASP.NET MVC is a breeze. Simply add DisplayTemplates and EditorTemplates subfolders to the Views\Shared folder. Then add a view file (e.g., .cshtml) to the subfolder with the name of the template. You can override the default templates by naming the template with the same name as the data type to override – such as String.cshtml or DateTime.cshtml – or you can give it a unique name – such as YesNo.cshtml – in which case you specify the template to use via the UIHint attribute. The custom template is a view that defines the incoming model via the @model directive and emits the desired output.

Building a Yes/No Template for Boolean Values

ASP.NET MVC’s default display template for Boolean values renders a disabled checkbox, but replacing it with the text “Yes” or “No” is quite simple to do when using templates. Start by adding a new template to the Views\Shared\DisplayTemplates folder named YesNo.cshtml with the following content:

@model bool

@if (Model)
{
    <text>Yes</text>
}
else
{
    <text>No</text>
}

The display template starts by defining the incoming model’s type (bool) via the @model directive. Next, it emits the literal string “Yes” if the incoming value (Model) is true, “No” otherwise. That’s all there is to it!

At this point we have the display template defined, but no view will use it unless we specifically instruct it to do so. (In other words, ASP.NET MVC will continue to use its default display template for Boolean values, which is the disabled checkbox.) To instruct a particular model property to use our display template rather than the default, use the UIHint attribute like so:

public class MyModel
{
    ...

    [UIHint("YesNo")]
    public bool ReceiveNewsletter { get; set; }

    ...
}

With those two pieces of the puzzle in place – the template itself and the UIHint attribute – a view will render the text “Yes” or “No” when using the following code:

@Html.DisplayFor(model => model.ReceiveNewsletter)

What about editing? Here we need to create a new template file with the same name (YesNo.cshtml) in the Views\Shared\EditorTemplates folder and then add the following content:

@model bool

@Html.DropDownList("", new SelectListItem[] { new SelectListItem() { Text = "Yes", Value = "true", Selected = Model }, new SelectListItem() { Text = "No", Value = "false", Selected = !Model }})

As with the display template, we start by defining the incoming model’s type (bool) via the @model directive. Then we render a drop-down using the Html.DropDownList method. This drop-down is composed of two select options – Yes and No with the values true and false, respectively. The first item is selected if the incoming value (Model) is true, while the second option is selected if the incoming value is false.

And we’re done! The screen shot below shows the output of a Boolean value when decorated with the [UIHint("YesNo")] attribute and displayed using the Html.EditorFor method.

Happy Programming!

Searching SQL Server Stored Procedure and Trigger Text

Scott Mitchell — Fri, 30 Sep 2011 16:16:55 GMT

While I like to consider myself a web developer, every now and then I have to put on my DBA hat to address some SQL related issue. This little script has saved my butt more than once. It searches the text of triggers, UDFs, stored procedures and views for a particular substring, returning the name and type of those database objects that match.

DECLARE @Search varchar(255)
SET @Search='text_to_search'

SELECT DISTINCT
    o.name AS Object_Name,o.type_desc
    FROM sys.sql_modules        m 
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition Like '%'+@Search+'%'
    ORDER BY 2,1

The above script is one of many in my “bag of scripts” I’ve collected over the years. This particular gem was snagged from Stackoverflow: How to find a text inside SQL Server procedures / triggers?

ASP.NET Training in San Diego on Saturday, September 24th

Scott Mitchell — Thu, 15 Sep 2011 18:19:39 GMT

On Saturday September 24th I’ll be doing two four-hour ASP.NET MVC 3 and Razor training events in San Diego:

Getting Started with ASP.NET MVC 3 and Razor – (8 AM to Noon) - This four hour morning class introduces ASP.NET MVC 3 and the new Razor view syntax, explores how to build ASP.NET MVC applications, covers key differences between Web Forms and ASP.NET MVC, and highlights the benefits of ASP.NET MVC. It is intended for developers who are interested in getting started with or learning more about ASP.NET MVC 3 and what it has to offer, as well as for developers using ASP.NET MVC 2 who want to see what's new with ASP.NET MVC 3.
Building Real-World Web Applications with ASP.NET MVC 3 – (1 to 5 PM) - This four hour afternoon class examines building real-world web applications using ASP.NET MVC 3. Learn how to craft lean controllers that comprise only a few lines of code. See how to use display and editor templates to decouple the user interface from your views. And practice creating rich and expressive view-specific models. Best practices on mapping domain models to view models, on input validation, and other topics will be explored. (This class is intended for students who attended the morning’s Getting Started with ASP.NET MVC and Razor class or who are already familiar with ASP.NET MVC.)

Both classes will be held at the University City Center office building, which is located on the 805 off the Governor St. exit (directions and map). And for both classes, students receive electronic access and printed copies of the training materials. Parking is free and coffee, snacks and lunch are provided.

To sign up, or to see a detailed outline for each course, please visit http://fuzzylogicinc.net/InDepth

Hope to see you there!

Full Day ASP.NET MVC 3 Training Event in Los Angeles on August 27th, 2011

Scott Mitchell — Thu, 18 Aug 2011 04:28:13 GMT

In conjunction with the Los Angeles .NET Developer's Group I will be presenting a full day, hands-on lab/presentation/training event on ASP.NET MVC 3 Tips, Tricks, and Best Practices on Saturday, August 27th. The event is a 8 hour training with breakfast and lunch served. Attendees are asked to bring their own laptop and participate in a hands-on session. This event is geared for developers already familiar with ASP.NET MVC.

Event: LADOTNET Master Series: ASP.NET MVC3 – Tips, Tricks, and Best Practices with Scott Mitchell
Date: Saturday, August 27th, 2011
Cost: $50.00 – signup
Location:
Outlook Amusements
2900 W. Alameda Ave, Suite 400
Burbank, CA 91505
[Map]
Abstract:

When building an ASP.NET MVC application developers are faced with a lot of questions. What comprises the Models in MVC? What techniques are available to build simple, yet powerful views? How best to keep your controllers from ballooning in size? And what sort of busywork and tedium can be offloaded to tools?

In this all-day, hands-on event, attendees start with a "baseline" ASP.NET MVC 3 application and together, we will add new features. Along the way we'll explore a variety of tips and best practices. We'll encounter pain points and discuss and implement various solutions. Topics will range from high-level, over-arching ones like options for building a domain model and persistence layer, to ones very specific to ASP.NET MVC, like creating and using custom display and editor templates in your views. And there will be plenty of hands-on experience with popular open source tools like AutoMapper.

This talk is tailored for developers who are already familiar with core ASP.NET MVC concepts and are comfortable building ASP.NET MVC applications.

Signup at: http://mastersseries004.eventbrite.com/

Creating a Currency Masked TextBox with On-the-Fly Currency Formatting

Scott Mitchell — Fri, 24 Jun 2011 23:21:54 GMT

By default, a user can enter any character into a textbox. Masked textboxes help reduce user input error by limiting what characters a user can type into a textbox. Masked textboxes have been a standard user input element in desktop applications for decades, but are less common in web applications for a variety of reasons. However, it’s not terribly difficult to implement masked textboxes. All that’s required is a touch of JavaScript and a sprinkle of jQuery.

In a recent project the client wanted a masked textbox for the textboxes on the page collecting currency information. Moreover, he wanted the user’s input to automatically be displayed as a formatted currency value in the textbox after entering their value. (Check out a live demo of my script…) But first things first, let’s see how to create a currency masked textbox.

Allowing Only Currency-Related Characters In a TextBox

There are a number of existing masked input plugins for jQuery. After trying some out I decided to roll my own JavaScript functions. I intend to come back to these and turn them into jQuery plugins, but for now they’re just JavaScript functions. As you can see in the script below, I created four functions:

numbersOnly – allows just number inputs, whether they are from the letters at the top of the keyboard or from the number pad.
numbersAndCommasOnly – allows number inputs and commas.
decimalsOnly – allows numbers, commas, and periods (either from the main keyboard or the number pad).
currenciesOnly – allows numbers, commas, periods, and the dollar sign.

In addition to the allowed characters discussed above, the functions also permit “special character key codes,” namely Delete, Backspace, left arrow, right arrow, Home, End and Tab. What keycodes are valid are listed in the variables at the top of the script; see Javascript Char Codes for a table listing the keys and their corresponding key codes.

Here is the script of interest:

// JavaScript I wrote to limit what types of input are allowed to be keyed into a textbox 
var allowedSpecialCharKeyCodes = [46,8,37,39,35,36,9];
var numberKeyCodes = [44, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105];
var commaKeyCode = [188];
var decimalKeyCode = [190,110];

function numbersOnly(event) {
    var legalKeyCode =
        (!event.shiftKey && !event.ctrlKey && !event.altKey)
            &&
        (jQuery.inArray(event.keyCode, allowedSpecialCharKeyCodes) >= 0
            ||
        jQuery.inArray(event.keyCode, numberKeyCodes) >= 0);

    if (legalKeyCode === false)
        event.preventDefault();
}

function numbersAndCommasOnly(event) {
    var legalKeyCode =
        (!event.shiftKey && !event.ctrlKey && !event.altKey)
            &&
        (jQuery.inArray(event.keyCode, allowedSpecialCharKeyCodes) >= 0
            ||
        jQuery.inArray(event.keyCode, numberKeyCodes) >= 0
            ||
        jQuery.inArray(event.keyCode, commaKeyCode) >= 0);

    if (legalKeyCode === false)
        event.preventDefault();
}

function decimalsOnly(event) {
    var legalKeyCode =
        (!event.shiftKey && !event.ctrlKey && !event.altKey)
            &&
        (jQuery.inArray(event.keyCode, allowedSpecialCharKeyCodes) >= 0
            ||
        jQuery.inArray(event.keyCode, numberKeyCodes) >= 0
            ||
        jQuery.inArray(event.keyCode, commaKeyCode) >= 0
            ||
        jQuery.inArray(event.keyCode, decimalKeyCode) >= 0);

    if (legalKeyCode === false)
        event.preventDefault();
}

function currenciesOnly(event) {
    var legalKeyCode =
        (!event.shiftKey && !event.ctrlKey && !event.altKey)
            &&
        (jQuery.inArray(event.keyCode, allowedSpecialCharKeyCodes) >= 0
            ||
        jQuery.inArray(event.keyCode, numberKeyCodes) >= 0
            ||
        jQuery.inArray(event.keyCode, commaKeyCode) >= 0
            ||
        jQuery.inArray(event.keyCode, decimalKeyCode) >= 0);

    // Allow for $
    if (!legalKeyCode && event.shiftKey && event.keyCode == 52)
        legalKeyCode = true;

    if (legalKeyCode === false)
        event.preventDefault();
}

My script is, admittedly, very US-centric. I have not tested the key codes with a non-English keyboard and for currencies I only allow a dollar sign. For the project I wrote this script for this is (currently) a non-issue since it is used within the corporate firewall and all sales are domestic, but clearly the above script would not work as well for international settings.

Applying the Currency Masking Script to a TextBox on the Page

With the above script in place you can have a textbox on the page mask its input by having the appropriate function called in response to the keydown event. The following jQuery syntax wires up this logic to all single-line textboxes that have the CSS class currenciesOnly.

$(document).ready(function () {
    $("input[type=text].currenciesOnly").live('keydown', currenciesOnly);
});

That’s it!

Formatting the Just-Entered Currency

Another requirement my client had was to format the just-entered number as a currency. That is, to change the user’s input – say, 45000 – to a formatted value like $45,000.00 immediately after their tabbed out of the textbox. To accomplish this I used Ben Dewey’s jQuery Format Currency Plugin, which you can see a demo of at http://www.bendewey.com/code/formatcurrency/demo/. This plugin adds a formatCurrency function that you can call on a set of elements returned by a jQuery selector.

To use this plugin I updated the $(document).ready event handler shown above to also call the formatCurrency function on blur:

$(document).ready(function () {
    $("input[type=text].currenciesOnly").live('keydown', currenciesOnly)
                        .live('blur', 
                                 function () { 
                                     $(this).formatCurrency(); 
                                 }
                              );
});

In short, whenever a textbox with a CSS class of currenciesOnly is blurred, the just-blurred textbox’s inputs are formatted as a currency thanks to the formatCurrency function.

And that’s all there is to it, folks.

To see a live demo of the above code, check out this jsfiddle script: http://jsfiddle.net/CBDea/1/

Happy Programming!

A Synchronized Visual Studio Crash

Scott Mitchell — Mon, 20 Jun 2011 19:52:17 GMT

About month ago at a user group meeting the guy sitting across from me shared the tale of a talk he had attended. The speaker’s laptop had downloaded a slew of Windows Updates in the background, after which the “Restart your computer to finish installing important updates” dialog box appeared. The speaker unwittingly clicked the “Restart now” button, which closed the presentation and displayed those ominous words: “Please do not power off or unplug your machine… Installing update 1 of 43.” But how many speakers have the distinction of not only crashing their own system, but in addition the laptops of dozens of others in the audience? I am now among those hallowed ranks.

On Saturday I presented another full-day ASP.NET MVC 3 training event in Los Angeles. Attendees were encouraged to bring their laptops and to follow along as I presented the material. At one point, I was creating a demo and writing some HTML in the _Layout.cshtml file. After typing in some HTML I switched from the _Layout.cshtml file to a controller at which point Visual Studio froze on me. Hard. And with vengeance. I quickly apologized and launched Windows Task Manager to kill VS and restart it.

As Visual Studio was restarting I heard a rising murmur from the crowd – many other people just had Visual Studio crash on them, too! Several people were following along with me key stroke for key stroke and had experienced the same crash. Lovely. I asked the audience to humor me and I tried to reproduce the crash again, but had no luck. But clearly whatever sequence of events caused Visual Studio to crash was deterministic seeing that several other people had the same experience when following the same steps.

So the next time you are at a conference or user group and are swapping stories of speaker flubs or miscues, feel free to tell them about this one guy who not only crashed his own demo, but was so bad that also he crashed dozens of other laptops in the audience.

Export an ADO.NET DataTable to Excel using NPOI

Scott Mitchell — Tue, 07 Jun 2011 23:10:41 GMT

My latest article on DotNetSlackers looks at how to create Excel spreadsheets using NPOI. NPOI is a free, open-source .NET library for creating and reading Excel spreadsheets and is a port of the Java POI library. In the article I show how to use NPOI to programmatically export data into a spreadsheet with multiple sheets, formatting, and so on. Specifically, my demos look at having a set of objects to export – for example, a set of Linq-to-Sql entity objects – and then crafting an Excel spreadsheet by enumerating those objects and adding applicable rows and columns to the spreadsheet.

Recently, I needed the ability to allow for more generic exports to Excel. In one of the web applications I work on there is an Excel Export page that offers a number of links that, when clicked, populate an ADO.NET DataTable with the results of a particular database view, generate a CSV file, and then stream that file down to the client with a Content-Type of application/vnd.ms-excel, which prompts the browser to display the CSV content in Excel. This has worked well enough over the years, but unfortunately such data cannot be viewed from the iPad; however, the iPad can display a native Excel file (.xls). The solution, then, was to update the code to use NPOI to return an actual Excel spreadsheet rather than a CSV file.

To accomplish this I wrote a bit of code that exports the contents of any ol’ DataTable into an Excel spreadsheet using NPOI. It’s pretty straightforward, looping through the rows of the DataTable and adding each as a row to the Excel spreadsheet. There were, however, a couple of gotcha points:

Excel 2003 limits a sheet inside a workbook to a maximum of 65,535 rows. To export more rows than this you need to use multiple sheets. Zach Hunter’s blog entry, NPOI and the Excel 2003 Row Limit, provided a simple approach to avoiding this problem. In short, I keep track of how many rows I’ve added to the current sheet and once it exceeds a certain threshold I create a new sheet and start from the top.
Excel has limits and restrictions on the length of sheet names and what characters can appear in a sheet name. I have a method named EscapeSheetName that ensures the sheet name is of a valid length and does not contain any offending characters.
When exporting very large Excel spreadsheets you may bump into OutOfMemoryExceptions if you are developing on a 32-bit system and are trying to dump the Excel spreadsheet into a MemoryStream object, which is a common technique for streaming the data to the client. See this Stackoverflow discussion for more information and possible workarounds: OutOfMemoryException When Generating a Large Excel Spreadsheet.

To demonstrate exporting a DataTable to Excel using NPOI, I augmented the code demo available for download from my DotNetSlackers article to include a new class in the App_Code folder named NPoiExport, which you can download from http://scottonwriting.net/demos/ExcelExportToDataTable.zip. This class offers an ExportDataTableToWorkbook method that takes as input a DataTable and the sheet name to use for the Excel workbook. (If there are multiple sheets needed, the second sheet is named “sheetName – 2,” the third, “sheetName – 3,” and so forth.)

The ExportDataTableToWorkbook method follows:

public void ExportDataTableToWorkbook(DataTable exportData, string sheetName)
{
    // Create the header row cell style
    var headerLabelCellStyle = this.Workbook.CreateCellStyle();
    headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
    var headerLabelFont = this.Workbook.CreateFont();
    headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD;
    headerLabelCellStyle.SetFont(headerLabelFont);

    var sheet = CreateExportDataTableSheetAndHeaderRow(exportData, sheetName, headerLabelCellStyle);
    var currentNPOIRowIndex = 1;
    var sheetCount = 1;

    for (var rowIndex = 0; rowIndex < exportData.Rows.Count; rowIndex++)
    {
        if (currentNPOIRowIndex >= MaximumNumberOfRowsPerSheet)
        {
            sheetCount++;
            currentNPOIRowIndex = 1;

            sheet = CreateExportDataTableSheetAndHeaderRow(exportData, 
                                                            sheetName + " - " + sheetCount, 
                                                            headerLabelCellStyle);
        }

        var row = sheet.CreateRow(currentNPOIRowIndex++);

        for (var colIndex = 0; colIndex < exportData.Columns.Count; colIndex++)
        {
            var cell = row.CreateCell(colIndex);
            cell.SetCellValue(exportData.Rows[rowIndex][colIndex].ToString());
        }
    }
}

Whenever a new sheet needs to be generated – either when starting or when the maximum number of rows per sheet is exceeded – the CreateExportDataTableSheetAndHeaderRow method is called. This method creates a header row, listing the name of each column in the DataTable.

protected Sheet CreateExportDataTableSheetAndHeaderRow(DataTable exportData, string sheetName, CellStyle headerRowStyle)
{
    var sheet = this.Workbook.CreateSheet(EscapeSheetName(sheetName));

    // Create the header row
    var row = sheet.CreateRow(0);

    for (var colIndex = 0; colIndex < exportData.Columns.Count; colIndex++)
    {
        var cell = row.CreateCell(colIndex);
        cell.SetCellValue(exportData.Columns[colIndex].ColumnName);

        if (headerRowStyle != null)
            cell.CellStyle = headerRowStyle;
    }

    return sheet;
}

Here’s how you would go about using the NpoiExport class to export a DataTable and then stream it down to the client:

Create and populate the DataTable with the data to export. Remember, the DataTable’s column names are what will appear in the header row so use aliases in the SQL query to provide more description/formatted column names, if you prefer.
Create an instance of the NpoiExport class.
Call the object’s ExportDataTableToWorkbook method passing in the DataTable from step 1 (along with a sheet name of your choice).
Set the Content-Type and Content-Disposition response headers appropriately and then stream down the contents of the Excel document, which is accessible via the NpoiExport object’s GetBytes method.

The following code snippet illustrates the above four steps.

// Populate the DataTable
var myDataTable = new DataTable();
using (var myConnection = new SqlConnection(connectionString)
{
    using (var myCommand = new SqlCommand())
    {
        myCommand.Connection = myConnection;
        myCommand.CommandText = sqlQuery;

        using (var myAdapter = new SqlDataAdapter(myCommand))
        {
            myAdapter.Fill(myDataTable);
        }
    }
}


// Creat the NpoiExport object
using (var exporter = new NpoiExport())
{
    exporter.ExportDataTableToWorkbook(myDataTable, "Results");

    string saveAsFileName = string.Format("Results-{0:d}.xls", DateTime.Now);

    Response.ContentType = "application/vnd.ms-excel";
    Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
    Response.Clear();
    Response.BinaryWrite(exporter.GetBytes());
    Response.End();
}

That’s all there is to it. The screen shot below shows an example of the exported report. Note that it lacks the nice formatting, auto-sized columns, and other bells and whistles that are possible with NPOI when constructing a report by hand (as I showed in Create Excel Spreadsheets Using NPOI), but it does make exporting data to Excel an exercise of just a few lines of code. And its exported data that can be opened and viewed from an iPad.

The above Excel spreadsheet was created using the ad-hoc query:

SELECT CategoryName AS [Category], 
       Description, 
       (SELECT COUNT(*) 
        FROM Products 
        WHERE Products.CategoryID = Categories.CategoryID) 
            AS [Product Count]
FROM Categories
WHERE CategoryID >= 3

Happy Programming!

Download: http://scottonwriting.net/demos/ExcelExportToDataTable.zip

Full Day ASP.NET MVC 3 Training Event in Los Angeles on May 21st, 2011

Scott Mitchell — Thu, 12 May 2011 16:57:09 GMT

In conjunction with the Los Angeles .NET Developer's Group I will be presenting a full day, hands-on lab and training event on ASP.NET MVC 3 on Saturday, May 21st. The event is a 8 hour training with breakfast and lunch served. Attendees are asked to bring their own laptop and participate in a hands-on session. This event is geared for intermediate to experienced web developers new to ASP.NET MVC (or just new to ASP.NET MVC 3 and the Razor syntax).

Over the course of the day we’ll explore MVC fundamentals, step through the process of building an ASP.NET MVC application, and implement common web application scenarios, such as: creating master/detail reports; working with forms; and building Create/Read/Update/Delete screens (CRUD). We’ll also explore interesting and powerful ASP.NET MVC tools and features, including scaffolding, partial views, layouts and layout sections, and more.

Event: LADOTNET Master Series: ASP.NET MVC3 with Scott Mitchell
Date: Saturday, May 21st, 2011
Cost: $50.00 – signup
Location:
Outlook Amusements
2900 W. Alameda Ave, Suite 400
Burbank, CA 91505
[Map]
Abstract: In this all-day, hands-on event attendees will build a complete, functional and interesting data-driven website over the course of the day using Microsoft's ASP.NET MVC 3 framework and the new Razor syntax.
Signup at: http://www.eventbrite.com/event/1553162551?utm_source=eb_email&utm_medium=email&utm_campaign=new_eventv2&utm_term=eventurl_text

Hope to see you there!

Terse Markup and CSS for Aligned Form Labels and Inputs

Scott Mitchell — Mon, 18 Apr 2011 17:46:39 GMT

Like many ASP.NET developers, I am most comfortable working with C# and VB. I know just enough HTML and CSS to be dangerous. I know enough to implement the overarching page layout without using <table>s and instead to use CSS to position, size, and float the elements on the page, but when it comes to certain user interface designs within a page I’m quick to use <table>s. For instance, if asked to create a contact form like the one pictured below, my first inclination would be to use a trusty <table>.

Recently, my friend and colleague Dave Yates showed me a website he had helped design and implement, StonehengeStyle. I was particularly interested when Dave showed me the contact page, which contained very clean, terse, readable markup without the use of <table>s.

First, each right-aligned text label – Name, Phone, and so on - is implemented as a <label> element. The input elements for collecting the input are simply <input>, <textarea> or <select> elements. For example, the markup for the Name, Phone, and Email Address inputs follows. (Note: I removed the required indicator for brevity; view the contact form’s markup in your browser for the complete markup.)

<label for="name">Name</label> 
<input name="name" type="text" id="name" class="textfield" /> 

<label for="phone">Phone</label> 
<input name="phone" type="text" id="phone" class="textfield" /> 

<label for="email">Email Address</label> 
<input name="email" type="text" id="email" class="textfield" />

Couldn’t be simpler, right? No filler <br /> or <p> elements, no <table>s cluttering things up. Heck, no <div>s, even.

The layout of the <label> and <input> elements is handled in the CSS via the following rules. First, all <label> elements are styled such that they are 180 pixels wide with 3 pixel padding on the top, 10 pixel padding on the right, and 2 pixel padding on the bottom. Their text is right-aligned and they clear left floating elements, which is why each <label> appears beneath the one above it.

label {
    clear: left;
    float: left;
    padding: 3px 10px 2px;
    text-align: right;
    width: 180px;
}

The textfield, textarea, and selectlist CSS classes, which assigned to <input>, <textarea>, and <select> elements in this contact form, specify a width of 250 pixels with a bottom margin of 8 pixels.

.textfield, .textarea, .selectlist {
    font-family: Arial,Helvetica,sans-serif;
    font-size: 12px;
    margin: 0 0 8px;
    width: 250px;
}

And that’s all there is to it! Pretty simple and straightforward.

This may not be terribly exciting for seasoned web developers or designers, but for someone like me, with just a working knowledge of HTML and CSS, this markup/CSS pattern is a gem and is how I’ve started doing my contact forms and other similar in-page layouts.

Happy Programming!

I’ve Written My Last Article for 4GuysFromRolla

Scott Mitchell — Tue, 29 Mar 2011 19:32:48 GMT

Warning! This blog post is long and rife with navel-gazing.

In 1998 I started an ASP resource site, 4GuysFromRolla.com. Toward the tail end of the dotcom boom I sold 4Guys to Internet.com, but continued working as the editor and primary contributor for the site, writing a new article each week. This arrangement continued until just recently. My last article for 4Guys has been written – Use MvcContrib Grid to Display a Grid of Data in ASP.NET MVC.

The Beginnings

My first exposure to web programming came in 1998 working at Empower Trainers and Consultants, a mid-sized consulting and training firm with locations in Kansas City, St. Louis, and Nashville. At the time I was an inexperienced, nervous, 19 year old sophomore at the University of Missouri-Rolla (UMR) who had landed an 8-month internship with Empower at their Kansas City location. My first assignment was to add some new features to the internal timekeeping tool, a custom-build data-driven web application powered by SQL Server and ASP. At the time I had done some rudimentary HTML development, but had zero experience with JavaScript, ASP, and SQL.

Needless to say, I found ASP enthralling. The ability to quickly create an application that could be shared with the world amazed me then as it continues to amaze me to this day. At the time there weren’t many online resources for learning more about ASP. As my internship drew to an end I decided that once I got back to school I would start my own site rich with ASP information.

Upon returning to university I cajoled three good friends into starting a website, 4GuysFromRolla.com. The idea was that the site would boast four sections:

ASP Information
Programming Information
Linux Information
Humor

If you couldn’t guess, we were four witty computer nerds (with an emphasis on the nerd part).

In September 1998 4GuysFromRolla.com went live. Over time, the other three guys lost interest and moved onto other projects. By the time I graduated in May 2000, 4GuysFromRolla.com was run by one guy from Rolla and focused strictly on ASP.

Sale to Internet.com

The dotcom boom reached its fever pitch in 2000. Companies were paying $5,000 a month for a little 125x125 banner to appear on the 4Guys homepage and $500 for a two sentence text ad to appear in the weekly newsletter, not to mention the thousands of dollars per month companies were dropping to have their animated 468x60 banners in the rotation to appear at the top of each article. The spending frenzy also extended to the acquisitions side, as numerous ASP resource sites were gobbled up by larger players.

In late 2000 I decided to “cash out.” 4Guys was sold to Internet.com.

I wrestled with the decision on whether to sell the site or not for a long time. On one hand, 4Guys was my baby and I had poured uncounted hours into it over the previous three years. Having seen how sites like 15Seconds.com fared after their acquisition, I knew that selling 4Guys would be akin to signing its death warrant. When a larger company buys a smaller site it’s not uncommon for the original founders to exit stage right, either immediately or in the very near term. When that happens, and when the acquirer starts to turn the screws in an attempt to better monetize their purchase, the inevitable happens – the site withers on the vine, traffic languishes, and the death knell is sounded. On the other hand, by late 2000 I think it was pretty apparent to everyone that the dotcom boom was coming to an end.

In the end, I decided to sell. The sales price reflected more than five years of dotcom boom revenue, which I deduced would be more like ten or more years of revenue once the boom ended. At age 22, five to ten years is an unimaginable window of time. I wondered, Would I be interested in writing about ASP ten years hence? Would I even be using ASP or web-based technologies? Since the answers to those questions were “maybe,” I decided to take the bird in the hand over the two in the bush.

Of course, here we are, 11 years later, and I am still actively involved in ASP.NET and the ASP.NET community and, until recently, was still writing for 4Guys. If I had it to do over again (and knowing what I know now), I would not have sold the site. Hindsight is 20/20. But that’s not to say that I regret the decision to sell the site – I don’t. In fact, I still hold that it was the right decision at the time given the unknowns.

The Buying Eyeballs Business Model

The dotcom boom heralded an interesting time in the history of the web. At its peak, billions of dollars were spent buying traffic, or “eyeballs,” as it was commonly referred to back then. In 2000, companies like Internet.com and DevX (among many others) were buying technology resource sites not for their content or talent, but for their existing traffic. This was a workable business model at the time due to the high rates advertisers were paying. Unfortunately, it was not sustainable once the bottom dropped out of advertising.

In 2009, Internet.com and its hundreds of technology-focused websites were sold to QuinStreet for $18 million. I continued working on 4Guys for QuinStreet (until recently). Unfortunately, QuinStreet’s purchase was a continuation of the buying eyeballs business model as evidenced by the lack of investment in the purchased web properties. 4Guys retained its dated look and feel as even more ads were squeezed onto the page.

Sites like 4Guys were sold by Internet.com to QuinStreet for pennies on the dollar. Even at such a steep discount, the question remains: did QuinStreet overpay? Time will tell.

Withering On the Vine

After the sale of 4Guys to Internet.com in 2000 I continued on as the site’s editor and primary contributor, authoring an article each week. Despite my continued work on the site, 4Guys started to lose prevalence in the ASP.NET community. There were many times I talked to a developer at a User Group or at a conference who would say something nice like, “I taught myself classic ASP from your articles on 4GuysFromRolla.com - I used to go there all the time.” The message was always the same – a meaningful compliment that had embedded in it a reflection on the current state of the site - I used to visit 4Guys.

There are probably a lot of different reasons why the importance and relevance of 4GuysFromRolla diminished over the years. Some of the reasons I’ve arrived at include:

My predominant use of VB code samples (rather than C#). In recent years, I started writing more C#-focused articles, as well as articles with code samples in both VB and C#, but the majority of articles on 4Guys are VB-only. And my switch to a more C#-friendly style came long after C# had become the de facto .NET language.
Increased attempts at monetization. More ads, bigger ads, flashier ads, and more annoying ads all made the site more difficult and less enjoyable to use.
A dated look and feel. If you couldn’t guess, the 4GuysFromRolla.com website hasn’t had a site redesign since 2002. It just looks old and dated. I’d like to think that the quality and quantity of content can make up for such aesthetic issues, but I understand why visitors would find the site appearance off-putting and why that might make them less likely to return, especially if there was similar content to be found elsewhere, which brings me to the next three factors…
The Google. Google turned the Internet upside down. Prior to Google, when faced with a particular problem people would go to a particular site and start hunting (or searching) for a solution. Once Google made search quick, fast, easy, and accurate – something I think happened in the early 2000s – user behavior shifted radically. Now Google was where people went to find answers to their questions. Just ask Jeff Atwood, who notes that: “Currently, 83% of our total traffic [to Stackoverflow] is from search engines, or rather, one particular search engine.” And that search engine, if you couldn’t guess, is Google.
A stronger online presence from Microsoft. In the late 90s and early 2000s, Microsoft offered a substandard web presence for their web technologies. There was technical documentation buried somewhere on Microsoft’s website, some articles on their MSDN site here and there, as well as articles from MSDN Magazine that were available online. But everything was scattered and hard to find. Microsoft finally got it right in the mid-2000s when they made MSDN easier and quicker to search and separated out their core technologies into standalone sites – www.asp.net, www.iis.net, etc. This move sucked an appreciable amount of traffic from community-founded sites like 4GuysFromRolla.
The proliferation of blogs. Blogs are another technology that made resource sites like 4GuysFromRolla.com less relevant. Intelligent developers with something interesting or useful to share didn’t need to get their thoughts published on your site – instead, they could start their own blog. The explosion of blogs outpaced the demand for information, cutting into everyone’s traffic and relevance.

Of all of the reasons listed above only one of them falls on my shoulders, namely my slow move away from VB to C#. But perhaps there are other factors that are my fault that my ego is blinding me from. I do believe that the quality of writing that has appeared on 4Guys has improved over the years. When I read some of the articles I wrote while I was still in school (1998-2000) I cringe. Also, I posit that the articles’ topics are (relatively) timely and of interest to ASP.NET developers. (To be fair, I was a bit late to jQuery and ASP.NET MVC, but once I jumped on that bandwagon I wrote quite a bit on said topics.)

The increased attempts and monetization and dated look and feel falls on Internet.com and QuinStreet’s shoulders. The last three factors were out of everyone’s control and affected all websites, not just those in my little corner of the web. And those macro changes, while perhaps detrimental to the growth of a site like 4Guys, are net gains for the Internet (and humanity) as a whole.

Neither QuinStreet nor Internet.com has ever provided me with traffic numbers so I don’t have any hard data to back up my thoughts on this, but my presumption is that 4Guys is still used by hundreds of thousands of developers around the world each month, but that it’s become less and less relevant as time has gone on. Today, I imagine that most people reach 4Guys from a Google search or from a link posted on an old messageboard or newsgroup thread. Few visit the site to see what new content is available or because a coworker told them that it’s a great website for ASP.NET developers of any stripe.

Yes, there are still many who find a solution to their problem on 4Guys, but few say, “How do I do X? I bet 4Guys has the answer!”

Some Fun Facts

Is it just me, or is this blog post getting a little depressing? How about some fun 4Guys trivia.

First online article I ever wrote was published on 4Guys on September 16th, 1998 – Using ActiveX Controls on Your Web Page
First article on 4Guys about ASP.NET was published on July 15th, 2000 – ASP.NET: An Introduction and My Views on the Matter
Rolla is a small town in Missouri that is home to the University of Missouri – Rolla, now named Missouri University of Science and Technology. Rolla is pronounced Rawl-ah. It’s 4GuysFromRawl-ah, not 4GuysFromRoll-ah.

For those who have never been to Rolla, it is about an hour and a half west of St. Louis, located square in the middle of nowhere. The university in Rolla focuses on engineering and the sciences and the student body is predominantly male. Many people wonder how I had the time to write nearly 750 articles while a student at UMR. The answer is that I went to school in the middle of nowhere with no girls - free time was not something that was hard to find!

When we started 4Guys, one of the other 4Guys created the site design. It had a black background with gray text and these bubbles that spanned the top and right of each page with links to each of the four sections. Together, we redesigned the site in 1999 to give it a more professional look. It was at this time that 4Guys adopted teal as its primary color. After acquiring the site, Internet.com did a resign in 2002. The redesign made the site a bit more graphics heavy and added some curved doodads here and there. I always found the 4Guys logo that Internet.com’s design team created to be hilarious.

The guy on the left looks depressed and ostracized from the group. The guy on the right wants nothing more than a big group hug. And those two guys in the middle? They look like a couple of real a-holes. Too cocky and arrogant to console their melancholy friend on the left, and too cool for school to hug the guy on the right. Jerks.

So Farewell…

My time with 4Guys has now come to an end. It was a fun and unforgettable run. I fondly remember huddled around a computer monitor with the other three guys from Rolla as we tried to decide on a domain name. I remember the excitement of landing my first advertiser and of depositing that first check. And I won’t forget the many emails from fellow developers who wrote in to thank me for an article that helped them solve a vexing problem. But most of all, my memories will center around writing the 4Guys article each week – drumming up a topic, banging out some code, and then putting that code into prose.

Having written a 4Guys article each of the preceding 650 or so weeks, it will be odd not to do so this week. Or next week. Or ever again.

Farewell, old girl, it was a good run.

Just to be clear, I am not retiring! I am a writer, that’s what I do. You’ll continue to see articles from me on this blog and on sites like DotNetSlackers.com and ASPAlliance.com. And I am always looking for additional engagements – if you have a need for a technical writer or prolific ASP.NET author, please don’t hesitate to check out my resume and drop me a line.

The Average Number of Words and Points in Boggle

Scott Mitchell — Fri, 04 Mar 2011 00:21:46 GMT

Boggle is a word game trademarked by Parker Brothers and Hasbro that involves several players trying to find as many words as they can in a 4x4 grid of letters. At the end of the game, players compare the words they found. During this comparison I've always wondered what about missed words. Was there some elusive 10-letter word that no one unearthed? Did we only discover 25 solutions when there were 200 or more?

To answer these questions I created a Boggle solver web application (back in 2008) that prompts a user for the letters in the Boggle board and then recursively explores the board to locate (and display) all available solutions. This Boggle solver is available online - fuzzylogicinc.net/Boggle. My family uses it every time we get together and play Boggle. For more information on how it works and to get your hands on the code, check out my article, Creating an Online Boggle Solver. In November 2010, I updated the code to make it more Ajax-friendly; see Updating My Online Boggle Solver Using jQuery Templates and WCF for details.

While playing a game of Boggle I found myself wondering how many total available words are present in a typical game of Boggle, and how many total points are available. It soon dawned on me that I could answer this question using my Boggle solver application and a the Monte Carlo method. My Boggle solving engine has a GenerateBoard method that randomly assembles a legal Boggle board. By generating tens of thousands of random Boggle boards, running them through my solver, and recording the number of words and total points available I could arrive at a good approximation for the average number of words and points in a given game of Boggle. (Scroll to the bottom of this blog entry if all you care about is the average number of words and points per game.)

I started by creating a new class in my Boggle solving Class Library, which I named GameLogger. This class has a single method, LogGame, which takes an inputs the BoggleBoard object that was used to find the solutions and the BoggleWorldList object that comprises the set of solutions to the accompanying BoggleBoard. This method then inserts a record into a database table (boggle_Boards) that logs:

The BoardID, which is a 16-character string that uniquely identifies the board. Namely, it contains one character for each letter in the board.
The NumberOfSolutions, which is the number of words on the for the board.
The Score, which is the cummulative score of all of the words on the board. In Boggle, 3 and 4 letter words score 1 point, 5 letter words score 2, six letter words score 3, seven letter words score 5 and words eight letters or longer score 11.
The MinimumWordLength, which specifies the minimum number of letters needed to form a valid solution. Boggle’s rules permit words three or more letters in length, but my family often plays a variation that permits only four letter or longer words.

The code for my Monte Carlo simulator is brain-dead simple – create a new Boggle game, solve it, then log it, and do this until I tell you to stop.

while (true)
{
    var gt = GameTiles.OfficialBoggleGameTiles();
    var board = new BoggleBoard(3, gt.GenerateBoard());

    var solutions = board.Solve();

    GameLogger.LogGame(board, solutions);
}

Let the above code run for 5 minutes and you’ve got tens of thousands of solved, random Boggle boards in the database from which you can now ascertain average number of words and score.

The following query returns the average number of solutions and score for games allowing words with 3 of more letters:

SELECT AVG(CAST(NumberOfSolutions AS decimal)), AVG(CAST(Score AS decimal))
FROM dbo.boggle_Boards
WHERE MinimumWordLength = 3

Which comes out to:

Average # of words: 66.82
Average score: 93.25

If we compute the average number of words and points for games requiring four or more letters we get, expectedly, lower results. For such games you can expect, on average, 42.12 words and 68.30 points.

So, next time you break out Boggle, keep in mind that, on average, there are nearly 67 words hiding there, ready for you to find. And after time expires, be sure to use my Boggle solver to see all the words that were present!

Note: Of course, these results are based on the dictionary that my Boggle solver uses. My dictionary may permit or deny certain words that you deny or allow, in which case these averages would be skewed. Unfortunately, I am unaware of where I got my dictionary file. I downloaded it from some website many years ago. I know many word-based games use the Enhanced North American Benchmark Lexicon as their dictionary. I am not using this but would like to integrate it at some point in the future…