RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


The Baker's Dozen: 13 Steps for Building an ASP.NET Database Lookup Page

Build a database search Web page using new features in ASP.NET 2.0, SQL Server 2005, and .NET generics in C#.

ookup and navigation screens initially seem like no-brainers, when compared to other parts of an application—yet by the time a developer has met all the user requirements and requests, he/she has expended much more time than expected.

This issue of The Baker's Dozen will build a lookup Web page using ASP.NET 2.0, SQL Server 2005, and C# 2.0. The lookup and results page will contain optional lookup criteria and custom paging of the result set. The solution will utilize new language features in SQL 2005 for providing ranking numbers to correlate with custom paging, and new capabilities in .NET generics to pump the results of a stored procedure into a custom collection. Just like Mr. Mayagi taught Daniel the martial arts by doing exercises, the example in this article will demonstrate some common design patterns, such as factory creation patterns, using .NET generics. The article also subtly presents a general methodology for building database Web pages.

Beginning with the End in Mind
Let's get down to business. This article will build a lookup and results/navigation screen (see Figures 1, 2, and 3).

Figure 1: First page of results, user can make various selections and page through results
Figure 2: Default sort/page on Last Name, jump to letter "K."
Figure 3: Sort/Page on address, then jump to page 5.
I'll build the end result in thirteen steps, as follows:

  • Establishing all the requirements for the lookup screen, and how the software will address them.
  • Defining all the session variables and stored procedure parameters.
  • Writing the stored procedure code to handle all of the optional lookup parameters.
  • Writing the stored procedure code to integrate the ranking with the paging using the new SQL 2005 ranking functions.
  • Implementing SQL 2005 Common Table Expressions to tie the query together.
  • Building the Data Access Layer (DAL) in C#.
  • Building the basic ASP.NET 2.0 Web page.
  • Building the ASP.NET 2.0 code to handle the quick lookup navigation and the paging.
  • Building the page to handle sorting on any column.
  • Create an environment to build and test your stored procedures independently of your application.
  • Designing the grid result set and results info.
  • Setting up the page to set up a column as a link to another page.
  • Modifying the application for users to make a variable number of selections against a list.
  • Modifying the DAL to pump in a custom object.
As a bonus at the end, I'll show some code using .NET generics to handle sorting with anonymous methods.

Less Talk, More Code
I have a New Year's resolution for the Baker's Dozen. (As I type this, there are still a few days remaining in 2006): short on talk and long on code. When I began the .NET learning curve years ago, I got the most value out of books and articles that contained meaningful code samples. So for each tip, as much as possible, I'll minimize the yakking and focus on the hacking (there was a time when the term hacker meant something complimentary!)

Tip 1: Establishing the Requirements
Have you ever gone to a Web site (for example, a browser-based e-mail Web page) that allows you to sort on any column, and navigate across pages with either next/previous links or page number links? For occasional use, pages with this general navigation design are fine.

But what happens if you want to go to a particular row/item that begins with the letter R? Odds are, you'll sort the column in descending sequence, and click the previous link (or guess at the page number), until you zero in on the desired item. Sort of reminds me of the contest on the TV game show where the contestant guesses 500, the host says "higher", the contestant guesses 550, the host says "lower," and they keep going back and forth until the contestant guesses the actual price (or the buzzer sounds).

That's the segue into the theme for the sample project in this article: navigation. The famous line about bad design patterns have a certain unflattering "whiff" also applies to Web pages that require extra work by the user to navigate. Sure, an aesthetically-pleasing Web page design will attract users, but a Web page built for navigation will keep them coming back.

In this article, I'll show you how to build a Web page like the ones in Figures 1, 2, and 3. The solution will contain these features:

  • Allow the user to perform lookups into a customer table of half a million rows, based on lookup parameters of name, address, city, state, and zip code. The process must perform partial text searches (e.g. a city search on HAMILTON will result in a hit for 123 HAMILTON BLVD).
  • Display the results in a paging grid and allow the user to set the maximum number of rows (MAXROWS) displayed at one time.
  • Allow the user to navigate through the pages with next/previous/top/bottom links.
  • Allow the user to sort on a column by clicking on the header.
  • Allow the user to perform a quick-jump to a particular set of results, based on the current column sort definition. For example, if the user is viewing customers in city order, starting with the letter A, allow the user to quickly jump to the first city that begins with the letter M. If the number of rows where the city begins with M is less than MAXROWS, the page will display all the M rows and any rows after that, until MAXROWS are displayed
  • The quick-jump will be a pulldown, with an entry for each letter of the alphabet and each single digit.
Figure 4: The development solution for the Web site.
Now let me talk about implementing this. Your first reaction may be that you can find most or all of this functionality in the newest version of ASP.NET or in third-party Web grids. That's partly correct, but you'll need to provide a little elbow grease to address all the requirements. You'll build this using a stored procedure in SQL 2005, the ASP.NET 2.0 GridView, and some C# 2.0 code. Figure 4 shows the entire development solution for the Web site. Here's the plan of attack:

  1. Establish all of the input criteria and variables for the search page, and establish the parameters for the stored procedure.
  2. Build and test the stored procedure, which will utilize the new ranking functions in T-SQL 2005 to correlate with the paging parameters.
  3. Create a data access class in C# to communicate between the Web page and the stored procedure.
  4. Design the Web page and write the C# code in the code-behind to handle all the Web page behavior (formatting the grid results, page navigation, page controls, and variables, etc).
  5. Setting up the basic data access layer to return either a DataSet or a custom collection.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date