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
Additionally, the stored procedure can implement partial text searches by using the LIKE
WHERE LastName LIKE
'%' + COALESCE(@LastName,LastName)+ '%' AND
'%' + 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
Tip 5: Tying the Query Together
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
Tip 3 covered the function to generate a ranking number, and Tip 4 covered two different T-SQL WHERE
clausesone 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 rowsthe 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.
Tip 6: Building the Data Access Layer
SELECT TOP (@MaxRows) CustomerID, LastName,
FirstName, Address, City, State, Zip,
(SELECT CustListTemp.*, (SELECT COUNT(*) from
CustListTemp) AS RecCount FROM CustListTemp) CustList WHERE...
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.)
contains a DAL (daCustomer
) that inherits from the base DAL. The method GetCustomers
does the following:
- Receives the parameters listed in Table 2.
- Creates a list of SQL parameters.
- For each of the lookup criteria parameters, checks the length and passes a NULL value if the string is empty.
- Creates an instance of a typed DataSet (the example uses a simple typed DataSet).
- Calls the base method ReadIntoTypedDs.
Tip 7: Building the Basic ASP.NET 2.0 Web Page
List<SqlParameter> oSQLParms = new
// set any parameters to NULL, if they are blank
LastName.Length > 0 ? LastName : null));
dsCustomer odsCustomer = new dsCustomer();
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).