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

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.

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

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.

   1  ANDERSON
   2  ARTHUR
   3  BARTON
   4  BOUTON
   5  DEIDRICK
   6  DOBSON
   7  HAMILTON
   8  JERICHO
   9  MONTGOMERY
   10 RIDDLEY
   11 STEVENS
   12 TILLY
   13 WILCOX
   14 WILLIAMS
   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,
      @SortCol  

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

Previous Page

Next Page