Musings on technical writing...

While working on a legacy ASP.NET WebForms application I stumbled into the following error: I had a paged GridView with a LinkButton in a TemplateField that, when clicked, accessed the GridView’s DataKeys collection. When clicking one of the LinkButton’s on the first page everything worked beautifully, but as soon as a user navigated to any page other than the first clicking on the LinkButton resulted in the following exception when attempting to access the DataKeys collection: Index was out of range. Must be non-negative and less than the size of the collection.

Note that the LinkButton’s CommandArgument property is set to the index of the current row being bound to the grid via the Container.DataItemIndex syntax.

Whenever the LinkButton is clicked there is a postback and the GridView’s RowCommand event handler fires. From here I was reading the CommandArgument value to get the row index and then used that to access the DataKeys collection.

This code works great if the GridView is not paged or if viewing the first page of a paged GridView. It blows up, however, when clicking the LinkButton in a paged GridView when viewing any page other than the first. Specifically, it blows up because the value of rowIndex exceeds the bounds of the DataKeys collection.

Container.DataItemIndex returns the index of the data item whereas the DataKeys collection only contains entries for the current page of data being displayed. To put it in simple terms, if you have a paged GridView that displays 10 records per page and you bind 100 records to it, the Container.DataItemIndex value will return a value between 0 and 99 no matter what page you are viewing; the DataKeys collection, on the other hand, will only have 10 records at most (since only 10 records are being displayed per page). So if you are viewing page 2 of the GridView the Container.DataItemIndex values will range from 10..19, but the DataKeys collection is still only going to have 10 records (0..9). So when you go to page 3, say, and click the LinkButton for the first row in that page you are actually running code like:

object value = gvProducts.DataKeys[20].Value;

But the DataKeys collection only has 10 items (0..9). The result is a System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection.

The Solution

There are two workarounds here. The first is to set the LinkButton’s CommandArgument property to the value you actually care about. If you want to read the ProductID value for the row just send it on in (rather than sending in the row index and reading the value from the DataKeys collection), like so:

<asp:LinkButton ID="lb" CommandArgument='Eval("ProductID")' ... />

The other option is to do a little modular arithmetic in the RowCommand event handler. Taking the row index mod the number of records per page will give you the appropriate value by which to access the DataKeys collection. GridViewID.PageSize will return the number of records per page so all you need to do is do rowIndex mod pageSize, as the following code snippet shows:

ASP.NET 4.5 introduced a nifty feature for script and stylesheet bundling and minification, a technique that can drastically reduce the size of your script and stylesheet files and, more importantly, reduce the number of round-trips the browser must make to the server to fully render a web page. Today I was working with a client who had been using bundling and minification with great success for sometime but after a recent site update his script bundle, while being rendered in the web page, was not returning the bundled, minified script content as expected, but rather was returning a 404 error (File Not Found).

I’m going to provide a more in-depth discussion on the issue at hand, but let me state the exact problem and solution in short – the problem was that the name of the script bundle – e.g., ~/bundles/MyBundle – did not match the bundle name referenced in the Scripts.Render statement – e.g., Scripts.Render(“~/bundles/MyBundleTypo”). Unfortunately, this mismatch didn’t produce any sort of runtime error that would alert us to the crux of the problem, but instead “failed silently” and generated a 404 for the requested bundle (“~/bundles/MyBundleTypo”). I was able to narrow down the problem due to the resulting <script> element that was rendered (and generated a 404). Instead of seeing a <script> element like:

Step (1) involves creating one or more ScriptBundle or StyleBundle classes and adding them to a BundleCollection. The following snippet shows the bundling added for the ~/bundles/MsAjaxJs bundle (which is included automatically when creating a new ASP.NET WebForms project in Visual Studio):

My client, however, was getting a different looking <script> element generated, one without the querystring like so:

<script src="http://nbaweblog.com/bundles/MsAjaxJs"></script>

Moreover, the URL, when visited, was returning File Not Found (404), rather than the bundled and minified script content.

The problem, it turned out, was a typo, a transposition of two characters in the Scripts.Render markup. Instead of Scripts.Render(“~/bundles/MsAjaxJs”) my client had inadvertently typed in Scripts.Render(“~/bundles/MsAjxaJs”). Of course, there was no bundle named ~/bundles/MsAjxaJs, but instead of generating a runtime exception (which would have been helpful) we instead just got a 404 for the <script> element.

I actually discovered the typo by using Reflector to scour the code of the System.Web.Optimization assembly in an attempt to ascertain why the rendered <script> element lacked a querystring value. Scripts.Render does not include a querystring under two conditions:

The bundled scripts are registered using an absolute path (e.g., http://www.example.com/js/MyScript.js) rather than a virtual path (e.g., ~/js/MyScript.js), which was not the case here, or

The named bundle could not be found in the bundles table (DING DING DING!!)

And that’s what alerted me to very carefully check to ensure that the bundle names and names specified in Scripts.Render matched precisely, which led me to the typo.

It’s never a comforting sign when you need to dig out Reflector and start parsing through disassembled source code in order to unearth the cause of an error. In my opinion there should be some sort of exception that is raised when requesting a non-existent bundled or minified script from Scripts.Render.

Today’s blog post is going to be a bit obscure, but I stumbled across this problem recently and found a workaround that I thought would be worth sharing for anyone else who found themselves in my place.

iText is an open-source library for programmatically creating PDF documents (iTextSharp is the .NET port of iText). I’ve written before on using iTextSharp to create PDF documents from an ASP.NET page, see:

I recently started work on a project that makes heavy use of iTextSharp to create rather complex PDF documents on the fly. One feature of iText is the ability to lay out text into columns. There are two flavors of columns available:

Simple columns – you create simple columns by defining a bounded rectangle, e.g., the lower left and upper right coordinates of the rectangle. The text then flows into the bounds of the column.

Irregular columns – with irregular columns you specify an array of coordinates for the left margin(s) and another array of coordinates for the right margin(s). With this approach you can define irregularly shaped columns in order to have the text flow around images and other document elements.

One challenge I bumped into is how iText lays out the elements added to a column. There are two layout modes: composite modeand text mode. To be honest, I am not 100% clear on the fine differences between the two modes, but from my understanding the differences are as follows:

Composite mode allows you to add all sorts of elements and the layout defined by those added elements is what dictates the rendered layout. For example, you can add lists and tables paragraphs and other such elements to a column when using composite mode and the layout directives for the lists, tables and paragraphs are respected when the column is rendered.

With text mode, on the other hand, you are limited to adding only phrases to the column and any layout directives defined in the phrase(s) are ignored. Consequently, if you add three paragraphs in text mode they all run together, one after another, without space between each paragraph, without indentation, and so on.

Unfortunately, text mode and irregular columns are intertwined, meaning that if you are adding irregular columns you cannot use composite mode for those columns. The downside is that you cannot add lists, tables, or other richer document elements to an irregular column. Additionally, you have to add your own line breaks between paragraphs. For instance, if you examine the code in Mike’s Page Layout with Columns post you’ll see that in his irregular column example he adds a series of Phrases and ends the content of each phrase with two newline characters (\n) to create the whitespace between each paragraph.

So what if you need to add a bulleted or numbered list to an irregular columns, are you out of luck? Well, you’re out of luck if you want to add a List element and have it do the rendering but if you don’t mind doing to rendering yourself you can add your own list-like content. The following code snippet shows how to add both ordered and unordered lists.

First, let’s setup the irregular columns. The following code was lifted directly from Mike’s blog post:

This code starts be creating a Document and outputting the PDF to a file named MyIrregularColumnsExample.pdf on the file system. Next, the irregular column is defined through a series of left and right coordinates. These coordinates are such that the column will run on the left half of the page and wrap around a rectangle in the upper left corner where an image can be placed. If the intent isn’t clear, the screen shot below should help clarify things; also, you can read Mike’s blog post for details.

Next, the elements for the list are defined as a List<string>:

List<string> items = new List<string>();
items.Add("This is the first item");
items.Add("This is the next item");
items.Add("And here be the last item");

Next we’re ready to add the list! Here’s how you add an ordered list – simply loop through each item in the list and add some space and the appropriate number (1, 2, 3, …).

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:

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.

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.

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.)

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:

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.

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.

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.

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.

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:

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).

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:

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.

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

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.

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.

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).

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.

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.

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:

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.

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.)

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.

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.

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.

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.