Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

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


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

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.



Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap