Login | Register   
LinkedIn
Google+
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 3

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


advertisement
Tip 4: Handling Optional Lookup Parameters and Page/Rowindex Ranges
The stored procedure contains several parameters for lookup criteria, but you can only use a few at any one time. For instance, an end user may only want to filter on name, or address, or zip, etc. The stored procedure should only query on input criteria that the user specifies. Some developers choose to handle this by constructing Dynamic SQL statements; an alternative is to use the T-SQL COALESCE function, which will check for any NULL input parameters.

Additionally, the stored procedure can implement partial text searches by using the LIKE statement.

WHERE LastName LIKE '%' + COALESCE(@LastName,LastName)+ '%' AND Address LIKE '%' + COALESCE(@Address,Address) + '%' AND ...

Additionally, the stored procedure must filter either on a range of rows (for numeric paging), or based on the current sort column starting with a specific letter or number (for quick navigation to a page that begins with a certain letter). Here the stored procedure becomes a bit more complicated, where in-line CASE statements are necessary in the WHERE clause.

WHERE CASE WHEN @lPaging = 1 AND @SortCol= 'LASTNAME' AND SUBSTRING(LastName,1,1) >= RTRIM(@AlphaChar) THEN 1 WHEN @lPaging = 1 AND @SortCol= 'ADDRESS' AND SUBSTRING(Address,1,1) >= RTRIM(@AlphaChar) THEN 1 WHEN @lPaging = 0 AND RowNum BETWEEN ( CASE @StartRowIndex WHEN -1 THEN (RecCount ) - @MaxRows ELSE @StartRowIndex END ) AND (CASE @StartRowIndex WHEN -1 THEN ( RecCount ) - @MaxRows ELSE @StartRowIndex END ) + @maxRows THEN 1

Tip 5: Tying the Query Together
Tip 3 covered the function to generate a ranking number, and Tip 4 covered two different T-SQL WHERE clauses—one as part of the first query, and the second that further filtered the original result set based either on a row index range or based on the single alphanumeric letter. The concept of further filtering the first result set is the segue to the next tip.

Prior to SQL 2005, developers could write subsequent SQL statements against intermediate results using derived tables, table variables, temporary tables, etc. SQL 2005 introduces Common Table Expressions (CTEs), which are essentially dynamic views with visibility for one subsequent statement.

Developers can create a CTE using a simple WITH statement. The following example creates a CTE called CustListTemp:



WITH CustListTemp AS (SELECT CustomerID, LastName, FirstName, Address, City, State, Zip, ROW_NUMBER() OVER (ORDER BY….. )

A developer can then write code to query against the CTE. The CTE will remain visible only for the next SQL statement. Note that the query against the CTE actually performs a subquery to determine the COUNT of rows—the WHERE clause later in the query conditionally examines the COUNT from the CTE if the user wants to view the last MAXROW number of rows.

SELECT TOP (@MaxRows) CustomerID, LastName, FirstName, Address, City, State, Zip, RowNum FROM (SELECT CustListTemp.*, (SELECT COUNT(*) from CustListTemp) AS RecCount FROM CustListTemp) CustList WHERE...

Tip 6: Building the Data Access Layer
With the stored procedure completed, you next want to construct the data access component to work with the stored procedure.

Don't overlook the new language features in SQL Server 2005. You can use them to address many data-handling requirements more easily than before.
The Sept/Oct 2006 Baker's Dozen article from CoDe Magazine featured a base data access class that utilized .NET generics to populate a typed DataSet (or a standard DataSet) directly, without using either a TableAdapter or a DataSet Merge function. A developer can pass an instance of a typed DataSet, along with a list of SQL parameters and the name of a stored procedure. The base data access class will automatically fill the typed DataSet. (The download project for this article contains this method.)

Listing 2 contains a DAL (daCustomer) that inherits from the base DAL. The method GetCustomers does the following:

  1. Receives the parameters listed in Table 2.
  2. Creates a list of SQL parameters.
  3. For each of the lookup criteria parameters, checks the length and passes a NULL value if the string is empty.
  4. Creates an instance of a typed DataSet (the example uses a simple typed DataSet).
  5. Calls the base method ReadIntoTypedDs.

List<SqlParameter> oSQLParms = new List<SqlParameter>(); // set any parameters to NULL, if they are blank oSQLParms.Add(new SqlParameter("@LastName", LastName.Length > 0 ? LastName : null)); dsCustomer odsCustomer = new dsCustomer(); this.ReadIntoTypedDs(odsCustomer, "[dbo].[LookupEmployees]", oSQLParms); return odsCustomer;

Tip 7: Building the Basic ASP.NET 2.0 Web Page
Listing 3 contains the complete code-behind source code (Default.aspx.cs) for the Web page. The page contains a reference to the data access class from Tip 6, so that the developer can call the DAL. Here are the highlights of the code:

  • The drop-down list (cboAlphaIndex) for the single alphanumeric "quick-nav" is populated from an array of alphanumeric values (see Tip 8 for details).
  • The Click event of the Retrieve button (btnRetrieve) calls the page method GetData, which in turn calls the GetCustomers method in the DAL.
  • Any time the page calls GetData, the page also calls a method named SetInfo (covered in Tip 10). The method displays the number of rows in the result set, and also refreshes the session variables CurrentFirstRow and CurrentLastRow.
  • The four navigation command buttons (btnFirst, btnPrev, btnNext, btnLast) call four navigation methods, covered in detail in Tip 8.
  • The GridView (grdResults) contains an event (grdResults_Sorting) that fires every time the user clicks a column header. Tip 9 covers this in detail.
  • The GridView also contains an event (grdResults_SelectedIndexChanged) that fires when the user selects a row in the GridView. Tip 11 covers how to handle this event, and how to determine the value of the row by using the defined value for the GridView's DataKeyNames property (set to CustomerID).


Comment and Contribute

 

 

 

 

 


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

 

 

Sitemap