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 : Page 2

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

Tip 2: Defining Session Variables and Stored Procedure Parameters
Table 1 and Table 2 list the session variables and stored procedure parameters for the search page, respectively.

Table 1: Session variables for the page.
Variable Description
CurrentFirstRow The first row number of the paged result set
CurrentLastRow The last row number of the paged result set
SortCol The current sort column
StartRowIndex When paging occurs, the first row to retrieve

Table 2: Stored procedure parameters.
Variable Description
FirstName First Name text search lookup
LastName Last Name text search lookup
Address Address search lookup
City City text search lookup
State State text search lookup
Zip Zip code text search lookup
StartRowIndex The first row to retrieve
MaxRows The maximum number of rows to retrieve
AlphaChar The alpha character to navigate to
SortCol The column to sort on

Because the result set page will only display a certain number of rows, the logic must store the current startrow index and endrow index, to handle next and previous navigation. Here's a simple scenario: Suppose the user initially selects a city that produces a result set of 15 rows, and the search page displays only four rows at a time.

   2  ARTHUR
   3  BARTON
   4  BOUTON
   6  DOBSON
   12 TILLY
   13 WILCOX
   15 ZEUSS 
On the first run, the stored procedure parameters for StartRowIndex and AlphaChar will be zero and blank, so the stored procedure will return the first four rows. The code-behind for the page will set the values for CurrentFirstRow and CurrentLastRow to 1 and 4, respectively.

.NET generics can help developers build some terrific factory classes. Anonymous methods allow developers to write custom filter logic. Together or apart, they rule!
If the user wants to navigate to the next page, the code-behind logic will set the stored procedure StartRowIndex parameter to 5 (CurrentLastRow + 1), and then call the stored procedure again. The stored proc will then return DEIDRICK through JERICHO, and the Web page code-behind will set the CurrentFirstRow and CurrentLastRow to 5 and 8. Then if the user wants to go back one page (previous), the code-behind logic will set the StartRow index to the value of the session variable CurrentFirstRow minus the value of the session variable MaxRows.

As for the top and bottom functions, the code-behind will set the StartRowIndex to either zero, or -1 (the stored procedure will handle a -1).

Finally, if the user wants to quickly navigate to a specific letter (for example, the letter R), the code-behind will set the stored procedure parameter AlphaChar to A, the stored procedure will return RIDDLEY through WILCOX, and the code-behind will set the session variables CurrentFirstRow and CurrentLastRow to 10 and 13.

A few more notes on the handling of variables:

  • When users wish to navigate to a particular letter (or number), the StartRowIndex parameter is not used.
  • Conversely, when the user uses the general navigation buttons (Top, Next, Previous, End), the AlphaChar parameter is not used.
Tip 3: The Baker's Dozen Spotlight: New T-SQL 2005 Ranking Functions
Figure 5: Testing the stored procedure.
Listing 1 shows the complete stored procedure for returning the result set (for those who like to jump to the end of a book to see how it turns out). The following code shows a sample SQL file for testing the stored procedure interactively in SQL Management Studio (see Figure 5), using the SQL parameters from Tip 2:

   DECLARE @LastName varchar(50), 
      @FirstName varchar(50), 
      @Address varchar(50), 
      @City varchar(50),  
      @State varchar(2), 
      @Zip  varchar(50), 
      @StartRowIndex int, 
      @MaxRows int,  
      @Alphachar varchar(1) ,
      @SortCol varchar(20)
   SET @STATE  = 'NY'
   SET @MaxRows = 100
   SET @startRowIndex = 0
   SET @SortCol  = 'ADDRESS'
   SET @AlphaChar  = ''
   EXEC [dbo].[LookupEmployees]   @LastName, @FirstName , @Address ,
      @city, @state, @zip, @startRowIndex , @MaxRows ,  @alphachar,
The next three tips discussed in this article will break down the stored procedure.

The data example back in Tip 2 contained a sequential row number for each record that matches the lookup criteria (name, address, etc.) The stored procedure will assign this row number based on the criteria, using the new ROW_NUMBER function in T-SQL 2005. ROW_NUMBER allows developers to assign a sequential ranking number, and order the ranking in the same way a developer would code an ORDER BY statement.

   SELECT CustomerID, LastName, FirstName, Address,  
      City, State, Zip, ROW_NUMBER() OVER (ORDER BY 
   CASE  @SortCol
      WHEN 'LASTNAME' THEN LastName+Firstname
      WHEN 'ADDRESS' THEN Address 
      WHEN 'CITY' THEN City+LastName+Firstname
      WHEN 'STATE' THEN State+LastName+Firstname
      WHEN 'ZIP'  THEN Zip+LastName+Firstname
      ELSE LastName + Firstname 
   AS RowNum
   FROM Customers
The next tip will cover the subsequent filtering of the ranking, to only return the results for a particular page/rowindex range.

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