Lookup and navigation screens initially seem like no-brainers, when compared to other parts of an application-yet by the time a developer has met all the user requirements and requests, he/she has expended much more time than expected.
This issue of The Baker’s Dozen will build a lookup Web page using ASP.NET 2.0, SQL Server 2005, and C# 2.0. The lookup and results page will contain optional lookup criteria and custom paging of the result set. The solution will utilize new language features in SQL 2005 for providing ranking numbers to correlate with custom paging, and new capabilities in .NET generics to pump the results of a stored procedure into a custom collection. Just like Mr. Mayagi taught Daniel the martial arts by doing exercises, the example in this article will demonstrate some common design patterns, such as factory creation patterns, using .NET generics. The article also subtly presents a general methodology for building database Web pages.
Create an environment to build and test your stored procedures independently of your application.
Beginning with the End in Mind
Let’s get down to business-this article will build a lookup and results/navigation screen (Figures 1-3). I’ll build the end result in thirteen steps, as follows:
- Establishing all the requirements for the lookup screen, and how the software will address them.
- Defining all the session variables and stored procedure parameters.
- Writing the stored procedure code to handle all of the optional lookup parameters.
- Writing the stored procedure code to integrate the ranking with the paging using the new SQL 2005 ranking functions.
- Implementing SQL 2005 Common Table Expressions to tie the query together.
- Building the Data Access Layer in C#.
- Building the basic ASP.NET 2.0 Web page.
- Building the ASP.NET 2.0 code to handle the quick lookup navigation and the paging.
- Building the page to handle sorting on any column.
- Designing the grid result set and results info.
- Setting up the page to set up a column as a link to another page.
- Modifying the application for users to make a variable number of selections against a list.
- Modifying the DAL to pump in a custom object.
As a bonus at the end, I’ll show some code using .NET generics to handle sorting with anonymous methods.
Less Talk, More Code
I have a New Year’s resolution for the Baker’s Dozen. (As I type this, there are still a few days remaining in 2006): short on talk and long on code. When I began the .NET learning curve years ago, I got the most value out of books and articles that contained meaningful code samples. So for each tip, as much as possible, I’ll minimize the yakking and focus on the hacking (there was a time when the term hacker meant something complimentary!)
Tip 1: Establishing the Requirements
Have you ever gone to a Web site (for example, a browser-based e-mail Web page) that allows you to sort on any column, and navigate across pages with either next/previous links or page number links? For occasional use, pages with this general navigation design are fine.
But what happens if you want to go to a particular row/item that begins with the letter R? Odds are, you’ll sort the column in descending sequence, and click the previous link (or guess at the page number), until you zero in on the desired item. Sort of reminds me of the contest on the TV game show where the contestant guesses 500, the host says “higher”, the contestant guesses 550, the host says “lower,” and they keep going back and forth until the contestant guesses the actual price (or the buzzer sounds).
That’s the segue into the theme for the sample project in this article: navigation. The famous line about bad design patterns have a certain unflattering “whiff” also applies to Web pages that require extra work by the user to navigate. Sure, an aesthetically-pleasing Web page design will attract users, but a Web page built for navigation will keep them coming back.
In this article, I’ll show you how to build a Web page like the ones in Figures 1-3. The solution will contain these features:
- Allow the user to perform lookups into a customer table of half a million rows, based on lookup parameters of name, address, city, state, and zip code. The process must perform partial text searches (e.g. a city search on HAMILTON will result in a hit for 123 HAMILTON BLVD).
- Display the results in a paging grid and allow the user to set the maximum number of rows (MAXROWS) displayed at one time.
- Allow the user to navigate through the pages with next/previous/top/bottom links.
- Allow the user to sort on a column by clicking on the header.
- Allow the user to perform a quick-jump to a particular set of results, based on the current column sort definition. For example, if the user is viewing customers in city order, starting with the letter A, allow the user to quickly jump to the first city that begins with the letter M. If the number of rows where the city begins with M is less than MAXROWS, the page will display all the M rows and any rows after that, until MAXROWS are displayed
- The quick-jump will be a pulldown, with an entry for each letter of the alphabet and each single digit.
Now let me talk about implementing this. Your first reaction may be that you can find most or all of this functionality in the newest version of ASP.NET or in third-party Web grids. That’s partly correct, but you’ll need to provide a little elbow grease to address all the requirements. You’ll build this using a stored procedure in SQL 2005, the ASP.NET 2.0 GridView, and some C# 2.0 code. Figure 4 shows the entire development solution for the Web site. Here’s the plan of attack:
Tip 2: Defining All the Session Variables and Stored Procedure Parameters
Table 1 and Table 2 list the session variables and stored procedure parameters for the search page. 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 only displays 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 plus 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 sessionvar CurrentFirstRow minus sessionvar 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 sessionvars CurrentFirstRow and CurrentLastRow to 10 and 13.
A few more notes on the handling of variables:
- When the user wishes to navigate to a particular letter [or number], the StartRowIndex parameter is not used.
- Conversely, when the user uses the general navigations buttons [top, next, previous, end], the AlphaChar parameter is not used.
Tip 3: The Baker’s Dozen Spotlight: Using the New T-SQL 2005 Ranking Functions
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). Listing 2 shows a sample SQL file for testing the stored procedure interactively in SQL Management Studio (Figure 5), using the SQL parameters from Tip 2. The following three tips 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.
Tip 4: Writing the Stored Procedure Code to Handle All of the 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: Implementing SQL 2005 Common Table Expressions to Tie 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 only remain visible 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 in C#
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 3 contains a DAL (daCustomer) that inherits from the base DAL. The method (GetCustomers) does the following:
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 4 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 for 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.
- Anytime 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).
Tip 8: Building the ASP.NET 2.0 Code to Handle the Quick Lookup Navigation and the Paging
The Load event of the page populates the dropdown list of one-character values that the user can select for quick navigation:
string[] alphabet = new string[] { " ", "A", "B",
"C",..., "0", "1", "2", "3"…;
for (int i = 0; i < alphabet.Length; i++)
this.cboAlphaIndex.Items.Add
(alphabet[i].Trim());
The code also has four methods for navigation, corresponding to the Click events for the four navigation buttons. These methods set the StartRowIndex accordingly, based on the navigation direction.
private void NavBegin()
{
// set the startrowindex to zero, and make sure
// we're not specifying a letter
Session["startRowIndex"] = 0;
// set alpha index pulldown back to nothing
this.cboAlphaIndex.SelectedIndex = 0;
this.GetData();
}
private void NavPrevious()
{
// set the startrowindex to the row number for the
// first record in the current page, minus 1, and
// minus maxrows
// so if we're looking at rows 200-249, and we go
// back one page, the new start row index would be
// 200-1-50, or 149....and we'd get back 149-199
Session["startRowIndex"] =
(int)Session["CurrentFirstRow"] -
(int)Session["MaxRows"];
this.cboAlphaIndex.SelectedIndex = 0;
this.GetData();
}
private void NavNext()
{
// startrow index becomes the value of the last
// row [the stored proc does a 'greater than']
Session["startRowIndex"] =
(int)Session["CurrentLastRow"] + 1;
this.cboAlphaIndex.SelectedIndex = 0;
this.GetData();
}
private void NavEnd()
{
// -1 is the 'magic number', it tells the stored
// proc to just grab everything from
// rowcount-maxrows, to rowcount
Session["startRowIndex"] = -1;
this.cboAlphaIndex.SelectedIndex = 0;
this.GetData();
}
Tip 9: Building the Page to Handle Sorting on Any Column
To sort on a column, you can tap into the Sorting event, which exposes the SortExpression for the column heading that the user selected. If you have not explicitly set the SortExpression programmatically, the SortExpression will be the name of the data column.
protected void grdResults_Sorting
(object sender, GridViewSortEventArgs e)
{
Session["SortCol"] =
e.SortExpression.ToString().Trim();
this.lblAlphaNav.Text =
e.SortExpression.ToString().Trim() +
" starting with...";
this.GetData();
}
Tip 10: Displaying the Grid Result Set Information
Once you call GetData to return the result set, you can pass the result set to SetInfo. This method will first strip out the tally row, which represents the total number of rows that met the filter criteria, as follows:
using daCustomer;
private void SetInfo(dsCustomer odsCustomer)
{
DataRow[] aRows = odsCustomer.dtCustomer.Select
("customerid = -1");
int nTotalCount = 0;
if( aRows.Length> 0) {
dsCustomer.dtCustomerRow oRow =
(dsCustomer.dtCustomerRow)aRows[0];
nTotalCount = oRow.RowNum;
oRow.Delete();
odsCustomer.dtCustomer.AcceptChanges();
}
Then SetInfo will determine the actual number of rows to be displayed. In most but not all instances, this will be the same as MaxRows. If the number is at least one, the method will read the RowNum column of the first and last row of the result set, and assign those values to the session variables CurrentFirstRow and CurrentLastRow.
int nResultCount =
odsCustomer.dtCustomer.Rows.Count;
if (nResultCount > 0) {
Session["CurrentFirstRow"] =
odsCustomer.dtCustomer[0].RowNum;
Session["CurrentLastRow"] =
odsCustomer.dtCustomer
[nResultCount - 1].RowNum;
}
this.grdResults.Caption =
"Number of matching records: " +
nTotalCount.ToString().Trim() +
"...click on any column heading to sort";
Tip 11: Setting up the Page to Set up a Column as a Link to Another Page
A common beginner ASP.NET question is how to set up a link for each row in a GridView-additionally, how to add a select icon for each row, like the ones show in Figures 1 through 3.
Just follow three basic steps to implement this. First, you need to identify a key field from the list of columns bound to the GridView. You can do this by setting the DataKeyNames property of the GridView. The key field (or combination of fields) must represent a unique value for each row. The example project uses CustomerID as the unique identifier, so a developer can define the GridView’s unique identifier as follows:
// set the DataKeyNames property
// to uniquely determine a selected row
this.grdResults.DataKeyNames =
new string[] { "CustomerID" };
Second, you can add an icon column by creating a ButtonField object, setting some properties, and adding the object to the GridView.
// Insert a button field to the GridView
// so that the user can select a row by clicking
// on the button icon
ButtonField obt = new ButtonField();
obt.CommandName = "Select";
obt.ButtonType = ButtonType.Image;
obt.ImageUrl = "openfolder.ico";
this.grdResults.Columns.Clear();
this.grdResults.Columns.Add(obt);
Finally, you can tap into the GridView’s SelectedIndexChanged event, which will fire when the user clicks on the icon. In the event, you can cast the value of the SelectedDataKey property to the valid type for the key. In this example, the code grabs the value of the current customer ID and sets up a call to another Web page that includes a query string with the ID.
protected void grdResults_SelectedIndexChanged
(object sender, EventArgs e)
{
// this reads from the DataKeyNames property
int nCustomerID =
(int)this.grdResults.SelectedDataKey.
Values[0];
Response.Redirect
("CustomerPage.aspx?CUSTID=" +
nCustomerID.ToString().Trim());
}
Tip 12: Modifying the Application to Make Variable Selections
Despite your best efforts, users have come back with a request-in addition to filtering on demographic information, they also want to filter on customer accounting statuses (paid to date, overdue 30 days, etc.) from a lookup table. The end user may want to select one status, more than one status, or all of them. The status code exists in the Customer table.
An aesthetically-pleasing Web page design will attract users. A Web page built for navigation will keep them coming back.
This will be a nice little exercise to repeat the process of building a plan of attack. The exercise also demonstrates a reusable technique for handling a variable number of selections.
- You’ll modify the Web page to include a data-bound control capable of processing multiple selections. Though some developers may opt for a sophisticated third-party control, this example will use a simple ASP.NET 2.0 CheckBoxList Web control.
- You’ll write code to read the CheckedBoxList control for selected values into an XML string.
- You’ll modify the data access class to pass this XML string as a parameter to the stored procedure.
- Finally, you’ll modify the stored procedure code to handle the XML parameter.
Since these tasks began with the user interface and ended with the database, you might be tempted to make the changes in that order. However, there’s a subtle level of increased efficiency by starting from the back-end and working forward. Design from the outside-in, and then build from the inside-out.
First add an XML parameter to the stored procedure. Again, the XML string will contain one column for statuses that the user selects. SQL Server 2005 supports a new XML datatype, which makes this process quite easy. The stored procedure will convert the XML string to a table variable, for a subsequent join into the Customer table.
Prior to SQL Sever 2005, developers often converted XML selections to a SQL table by using the sp_xml_preparedocument system stored procedure and the OPENXML function. While still functional, this methodology has two issues. First, it can be memory-intensive. The stored procedure sp_xml_preparedocument returns a memory pointer to a COM XML document object model, so using OPENXML on large XML documents can tax a server. Second, developers cannot use sp_xml_preparedocument inside a T-SQL User-defined function, making it difficult to develop generic, reusable SQL functionality for this task.
Included in all the new XML functionality in SQL Server 2005 is a general capability called XQuery and a specific method called nodes(). These allow developers to strip out, or shread, specific pieces of data from an XML string into a table. This functionality uses fewer resources and you can accomplish the task in one line of code.
Listing 5 contains a T-SQL 2005 table-valued UDF called XMLtoTable, which takes an XML string as a parameter and returns a table variable containing the integer keys that were stored in the XML string in the IDpk column. If you want the name of the selected column in the XML string to be variable, you can modify the UDF to pass the name of the column as a parameter.
In Listing 5, the code uses the nodes() method to query starting from the IDpk node of the XML string and place the result into an alias with a table/(column) format. From there, the SELECT statement uses the value function to read the value of IDpk into an integer result set.
INSERT INTO @tPKList
SELECT Tbl.col.value('.','int') as IntPK
FROM @XMLString.nodes('//IDpk' ) Tbl(col)
You can now incorporate the UDF into the main query against the Customers table:
-- uses the new parameter XMLString
-- that contains the list of statuses
SELECT <column list>
FROM Customers
JOIN [dbo].[XMLtoTable](@XMLString) StatusList
ON StatusList.IntPK = customers.statusfk
Now that you’ve tackled the database end, the remaining pieces are even easier. You can modify the data access class in Listing 3 to include the new parameter.
// code also adds XMLStatuses as a parameter
// to GetCustomers in Listing 5
oSQLParms.Add(
new SqlParameter("@XMLString", XMLStatuses));
You’re almost there! In the last step you’ll handle the Web page by populating a CheckedBoxList control and then reading from it.
The Web page needs code to populate the control. For simple demonstration purposes, the code will populate a DataTable manually-in production, this would pull from a data class that reads the back-end database for customer status codes.
DataTable dtStatus = new DataTable();
dtStatus.Columns.Add("StatusPK", typeof(Int32));
dtStatus.Columns.Add("Descript", typeof(String));
dtStatus.Rows.Add(1, "Up to Date");
dtStatus.Rows.Add(2, "Overdue 30 days");
dtStatus.Rows.Add(3, "Overdue 60 days");
dtStatus.Rows.Add(4, "Overdue 90 days");
dtStatus.Rows.Add(5, "Overdue 120 days");
dtStatus.Rows.Add(6, "Account suspended");
// Set the data binding, and the text/value fields
this.chkStatusList.DataSource = dtStatus;
this.chkStatusList.DataTextField = "descript";
this.chkStatusList.DataValueField = "statuspk";
this.chkStatusList.DataBind();
Finally, you’ll need code to read the CheckedBoxList for items that the user selected and return an XML string. The code below creates a temporary DataSet, reads through the collection of items in the control, and inserts selected items into the temporary DataSet. The code then returns an XML representation of the temporary DataSet containing the selections, using the GetXML method.
private string GetStatuses()
{
DataTable dtSelected = new DataTable();
dtSelected.Columns.Add("IDpk", typeof(Int32));
foreach (ListItem oItem in
this.chkStatusList.Items)
if (oItem.Selected == true)
dtSelected.Rows.Add(
Convert.ToInt32(oItem.Value));
DataSet ds = new DataSet();
ds.Tables.Add(dtSelected);
return ds.GetXml();
}
Tip 13: Modifying the DAL to Pump in a Custom Object
As I mentioned in Tip 6, in the Sept/Oct 2006 issue of CoDe Magazine I presented code in the Baker’s Dozen that utilized .NET generics to populate a Typed DataSet directly from a stored procedure, without using either a TableAdapter or a DataSet Merge function.
Design from the outside-in, and then build from the inside-out.
To give “equal time” to custom collections, I’ve added a base method using .NET generics to my data access layer. The method (ReadIntoCollection, in Listing 6) will execute a stored procedure and pump the results directly into any custom collection. For example, if you want to use a custom collection instead of a typed DataSet for the example in the previous tips:
List<SqlParameter> oSQLParms = new
List<SqlParameter>();
oSQLParms.Add(new SqlParameter("@LastName",
LastName));
List<CustomerClass> oCustomers =
new List<CustomerClass>();
// pass an instance of the list, SP name/parms,
// and a type reference to the class
this.ReadIntoCollection(oCustomers,
"[dbo].[LookupEmployees]", oSQLParms,
typeof(CustomerClass));
So what happens inside this method?
To start, let’s look at the parameters inside the method. While the calling function passes an instance of a list of CustomerClass items to the base method, the base method knows nothing about this specific class. Here is where developers can use .NET generics in the parameter declaration-by specifying List<T> oCollection as the first parameter, you can pass any type of valid List. The second and third parameters (stored procedure name and list of SQL parameters) are the same as in Tip 6. The final parameter is a type reference to the class itself-the CustomerClass. The base method will need this to iterate through the properties of the class when populating the list.
public void ReadIntoCollection<T>
(List<T> oCollection, string cStoredProc,
List<SqlParameter> oParmList,
Type oCollectionType)
Next, the base method opens a connection, defines a command object for a stored procedure, and establishes any SQL parameters-again, very similar to the DataSet method.
SqlConnection oSqlConn = this.GetConnection();
SqlCommand oCmd =
new SqlCommand(cStoredProc, oSqlConn);
oCmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter oParm in oParmList)
oCmd.Parameters.Add(oParm);
But this time, instead of using the Fill method of the .NET DataAdapter, the method opens the connection and executes a DataReader. The goal will be to iterate through the reader and populate the custom list.
oSqlConn.Open();
SqlDataReader oDR = oCmd.ExecuteReader();
The next series of steps go through the reader, create an instance of the class (using the Type parameter), determine all the properties of the class, and read the actual values of those property names from the reader into the class instance to populate the list. Sounds pretty involved! Well, there’s the old line about eating an elephant one bite at a time…
First, the code sets up a loop with the reader object and creates an instance of the class type. Because the parameters utilized .NET generics to define the class parameter, the code can specify an instance of the class with the T placeholder. If you examine this code in the debugger, you’ll see that oItem is a class of type CustomerClass.
while(oDR.Read()) {
T oItem = (T)Activator.CreateInstance
(oCollectionType);
Second, the code has to use a little bit of .NET reflection to discover all of the properties of the class. The code reads all of the properties of oItem into an array of type PropertyInfo, using GetProperties. The code will call the array oCollectionProps.
// get all the properties of the class
PropertyInfo[] oCollectionProps =
((Type) oItem.GetType()).GetProperties();
Now that the code has an array of properties for the class (oCollectionProps), you can iterate through that array, grab the name of the properties, and use the reflection method SetValue to set the value of the specific property in oItem, from the DataReader.
for (int n=0; n<oCollectionProps.Length; n++)
{
string cPropName = CollectionProps[n].Name;
oCollectionProps[n].SetValue
(oItem, oDR[cPropName], null);
}
// Add the item to the collection
oCollection.Add(oItem);
// Now get the next row in the DataReader
If you’re having difficulty following along, think of it this way-imagine the code if you weren’t trying to write anything generic, and then study/compare it to the generic code.
while(oDR.Read()) {
CustomerClass oCustomer = new CustomerClass();
// no need to loop through properties, we
// know what they are
oCustomerClass.FirstName = oDR["FirstName"];
oCustomerClass.LastName = oDR["LastNName"];
}
oCollection.Add(oItem);
}
Bonus: The Baker’s Dozen Potpourri: Sorting Custom Lists with Anonymous Methods
I’m still a DataSet guy though I admit that the capabilities of the .NET generics List class are powerful, especially when combined with the new anonymous methods in C# 2.0. This Baker’s Dozen Potpourri will present some code snippets for sorting and filtering custom list collections.
For example, I have a list of records with Customer ID and Amount Due, and I want to filter on Locations 1 and 2, where the Amount Due is greater than 10000. I also want to sort the results on Amount Due descending, within Location. If I use ADO.NET, I can accomplish this with a DataView, as follows:
DataView dv = new DataView(dt);
dv.RowFilter =
"LocationID in (1,2) AND AmountDue > 10000";
dv.Sort = "LocationID, AmountDue DESC";
In the DataSets versus custom collections debate, proponents of DataSets argue that you’d have to write more complicated code to achieve the same functionality with custom collections. (I certainly made that argument prior to Visual Studio 2005.)
However, Visual Studio 2005 provides two new capabilities that I can combine to produce a good counterpart to the ADO.NET snippet above. First, the new List class contains methods to sort and filter (using the Sort and FindAll methods). I’ll write a custom method for sorting/filtering, and specify the name of the method as a delegate parameter for the Sort/FindAll method.
Second, C# 2.0 allows developers to use anonymous methods to place logic in place of a delegate. Instead of writing a separate custom method, developers can include code inline where the delegate would otherwise appear. I’ll show you some code samples to demonstrate. Instead of a DataSet, I’ll take an example of a custom list called CustomerRec, with properties for LocationID and AmountDue.
The code inserts an anonymous method inside the list’s FindAll method to create a filtered list of customers where LocationID equals 1. Then the code sorts the filtered list on Amount Due descending.
Note that the delegate parameter for the Sort method receives two parameters, one for each object instance as part of a sort comparison. The anonymous method code will execute for each item in the list. For each execution, the code compares the two incoming values and uses the .NET CompareTo method to return the greater of the two values. If the example called for a sort in ascending sequence, the code would compare the first parameter to the second-but since the example calls for a descending sort, the code reverses the use of the parameters.
// anonymous method to filter on Location = 1
List<CustomerRec> oFilteredCustomers =
oCustomerRecs.FindAll(
(delegate(CustomerRec oRec) {
return (oRec.LocationID == 1 );
}));
// anonymous method to sort on amount due DESC
// by reversing the incoming parameters
oFilteredCustomers.Sort(
delegate(CustomerRec oRec1, CustomerRec oRec2)
{ return
oRec2.AmountDue.CompareTo
(oRec1.AmountDue); });
Developers can include more complex in-line code, such as combinations of OR and AND. The next code sample duplicates the logic from the ADO.NET sample that filters the data on either Location 1 or 2, and Amount Due greater than 10000.
// anonymous method to filter on
// either Location 1 or 2, AND amount due GT 10000
List<CustomerRec> oFilteredCustomers =
oCustomerRecs.FindAll(
(delegate(CustomerRec oRec) {
return (
(oRec.LocationID == 1 ||
oRec.LocationID == 2)
&& oRec.AmountDue > 10000);
}));
Finally, the last code sample shows an anonymous method to sort the filtered list on amount descending within location. The delegate receives two parameters for each incoming comparison: if the locations are equal, the code compares the amount due of the second parameter against the first. If the locations are not equal, the code compares the location ID of the first parameter against the second.
// Now sort on amount due DESC, within Location
// To do so, check the two incoming locations 1st
// If they are equal, reverse the order of two
// incoming parameters, and compare the amount due
// [just like above]
// If they AREN'T equal, compare the two locations
oFilteredCustomers.Sort(
delegate(CustomerRec oRec1, CustomerRec oRec2)
{ return
oRec1.LocationID == oRec2.LocationID ?
oRec2.AmountDue.CompareTo
(oRec1.AmountDue):
oRec1.LocationID.CompareTo(
oRec2.LocationID);
});
So in the end, while the developer must write a little more code, it is now possible using the new List class to implement advanced sorting and filtering functions. In addition, the ability to either implement anonymous methods opens the door to write custom filtering beyond ADO.NET syntax (ADO.NET does not support hooks for custom filtering methods).
Closing Thoughts
Have you ever submitted something (an article, a paper, some code, etc.) and thought of some good ideas AFTER the fact? Well, I’m the king of thinking of things afterwards. Fortunately, that’s the type of thing that makes blogs valuable. Check my blog (www.TheBakersDozen.net) for follow-up tips and notes on Baker’s Dozen articles…and maybe a few additional treats!
Listing 1: Complete stored procedure for Employee Lookup and Ranking
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[LookupEmployees]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[LookupEmployees2]
go
CREATE PROCEDURE [dbo].[LookupEmployees2]
@LastName varchar(50)=null, @FirstName varchar(50)=null,
@Address varchar(50)=null, @City varchar(50)=null,
@State varchar(2)=null, @Zip varchar(50)=null,
@StartRowIndex int, @MaxRows int,
@AlphaChar varchar(1)=null, @SortCol varchar(20)=null
AS
BEGIN
SET NOCOUNT ON
DECLARE @lPaging bit
IF @AlphaChar is null
SET @lPaging = 0
else
SET @lPaging = 1
WITH CustListTemp AS
(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
WHERE LastName LIKE '%'+COALESCE(@LastName,LastName)+'%' AND
FirstName LIKE '%'+COALESCE(@FirstName,FirstName)+'%' AND
Address LIKE '%'+COALESCE(@Address,Address)+'%' AND
City LIKE '%'+COALESCE(@City,City)+'%' AND
State LIKE '%'+COALESCE(@State,State)+'%' AND
Zip LIKE '%'+COALESCE(@Zip,Zip)+'%' )
SELECT TOP (@MaxRows) CustomerID, LastName, FirstName, Address,
City, State, Zip, RowNum
FROM
( SELECT CustListTemp.*,
(SELECT COUNT(*) from CustListTemp) AS RecCount
FROM CustListTemp )CustList
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 = 1 AND @SortCol= 'CITY' AND
SUBSTRING(City,1,1) >= RTRIM(@AlphaChar) THEN 1
WHEN @lPaging = 1 AND @SortCol= 'STATE' AND
SUBSTRING(State,1,1) >= RTRIM(@AlphaChar) THEN 1
WHEN @lPaging = 1 AND @SortCol= 'ZIP' AND
SUBSTRING(Zip,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 ELSE 0 END = 1
END
GO
Listing 2: Testing the stored procedure
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
Listing 3: Custom DAL
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace daCustomer
{
public class daCustomer : SimpleDataAccess.SimpleDataAccess
{
public dsCustomer GetCustomers(string FirstName,
string LastName, string Address, string City,
string State, string Zip,
int StartRowIndex, int MaxRows,
string AlphaChar, string SortCol)
{
if (AlphaChar == "")
AlphaChar = null;
List<SqlParameter> oSQLParms = new
List<SqlParameter>();
oSQLParms.Add(new SqlParameter("@LastName",
LastName.Length > 0 ? LastName : null));
oSQLParms.Add(new SqlParameter("@FirstName",
FirstName.Length > 0 ? FirstName : null));
oSQLParms.Add(new SqlParameter("@Address",
Address.Length > 0 ? Address : null));
oSQLParms.Add(new SqlParameter("@City",
City.Length > 0 ? City : null));
oSQLParms.Add(new SqlParameter("@State",
State.Length > 0 ? State : null));
oSQLParms.Add(new SqlParameter("@Zip",
Zip.Length > 0 ? Zip : null));
oSQLParms.Add(new SqlParameter("@startRowIndex",
StartRowIndex));
oSQLParms.Add(new SqlParameter("@MaxRows", MaxRows));
oSQLParms.Add(new SqlParameter("@alphachar",
AlphaChar));
oSQLParms.Add(new SqlParameter("@SortCol",SortCol ));
dsCustomer odsCustomer = new dsCustomer();
this.RetrieveDataIntoTypedDs(odsCustomer,
"[dbo].[LookupEmployees]", oSQLParms);
return odsCustomer;
}
}
}
Listing 4: Code-behind for paging
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using <a href="http://System.Web.UI">System.Web.UI</a>;
using <a href="http://System.Web.UI">System.Web.UI</a>.WebControls;
using <a href="http://System.Web.UI">System.Web.UI</a>.WebControls.WebParts;
using <a href="http://System.Web.UI">System.Web.UI</a>.HtmlControls;
public partial class _Default : <a href="http://System.Web.UI">System.Web.UI</a>.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
Session["CriteriaSet"] = false;
string[] alphabet = new string[] { " ", "A", "B", "C",
"D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P",
"Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "0", "1", "2",
"3", "4", "5", "6", "7", "8", "9" };
for (int i = 0; i < alphabet.Length; i++)
this.cboAlphaIndex.Items.Add(alphabet[i].Trim());
this.InitializeVars();
}
}
protected void btnRetrieve_Click(object sender, EventArgs e)
{
this.GetData();
}
private void SetInfo(daCustomer.dsCustomer odsCustomer)
{
int nResultCount = odsCustomer.dtCustomer.Rows.Count;
if (nResultCount > 0)
{
Session["CurrentFirstRow"] =
odsCustomer.dtCustomer[0].RowNum;
Session["CurrentLastRow"] =
odsCustomer.dtCustomer[nResultCount - 1].RowNum;
}
this.grdResults.Caption = "Number of matching records: "
+ nResultCount.ToString().Trim();
}
private void GetData()
{
string FirstName =
this.txtFirstName.Text.ToString().Trim();
string LastName =
this.txtLastName.Text.ToString().Trim();
string Address = this.txtAddress.Text.ToString().Trim();
string City = this.txtCity.Text.ToString().Trim();
string State = this.txtState.Text.ToString().Trim();
string Zip = this.txtZip.Text.ToString().Trim();
string AlphaChar =
this.cboAlphaIndex.Text.ToString().Trim();
int StartRowIndex =
Convert.ToInt32(Session["StartRowIndex"]);
int MaxRows = Convert.ToInt32(Session["MaxRows"]);
string SortCol = Convert.ToString(Session["SortCol"]);
daCustomer.daCustomer odaCustomer = new
daCustomer.daCustomer();
daCustomer.dsCustomer odsCustomer =
odaCustomer.GetCustomers(FirstName, LastName,
Address, City, State, Zip,
StartRowIndex, MaxRows,
AlphaChar, SortCol);
this.SetInfo(odsCustomer);
this.grdResults.DataSource = odsCustomer;
this.grdResults.DataBind();
}
private void InitializeVars()
{
Session["startRowIndex"] = 0;
Session["AlphaChar"] = null;
Session["CurrentFirstRow"] = 0;
Session["CurrentLastRow"] = 0;
Session["MaxRows"] = 15;
Session["SortCol"] = "LASTNAME";
}
protected void btnFirst_Click(object sender,
ImageClickEventArgs e)
{
this.NavBegin();
this.GetData();
}
protected void btnPrev_Click(object sender,
ImageClickEventArgs e)
{
this.NavPrevious();
this.GetData();
}
protected void btnNext_Click(object sender,
ImageClickEventArgs e)
{
this.NavNext();
this.GetData();
}
protected void btnLast_Click(object sender,
ImageClickEventArgs e)
{
this.NavEnd();
this.GetData();
}
private void NavBegin()
{
// set the startrowindex to zero, and make sure we're
// not specifying a letter
Session["startRowIndex"] = 0;
Session["AlphaChar"] = null;
this.cboAlphaIndex.SelectedIndex = 0;
}
private void NavPrevious()
{
// set the startrowindex to the row number for the first
// record in the current page, minus 1, and minus maxrows
// so if we're looking at rows 200-249, and we go back one
// page, the new start row index would be 200-1-50, or
// 149....and we'd get back 149-199
Session["startRowIndex"] =
(int)Session["CurrentFirstRow"] -
(int)Session["MaxRows"];
Session["AlphaChar"] = null;
this.cboAlphaIndex.SelectedIndex = 0;
}
private void NavNext()
{
// startrow index becomes the value of the last row [the
// stored proc does a 'greater than']
Session["startRowIndex"] =
(int)Session["CurrentLastRow"] + 1;
Session["AlphaChar"] = null;
this.cboAlphaIndex.SelectedIndex = 0;
}
private void NavEnd()
{
// -1 is the 'magic number', it tells the stored proc to
// just grab everything from rowcount-maxrows, to rowcount
Session["startRowIndex"] = -1;
Session["AlphaChar"] = null;
this.cboAlphaIndex.SelectedIndex = 0;
}
protected void cboAlphaIndex_SelectedIndexChanged(
object sender, EventArgs e)
{
this.GetData();
}
protected void grdResults_Sorting(object sender,
GridViewSortEventArgs e)
{
Session["SortCol"] = e.SortExpression.ToString().Trim();
this.GetData();
}
protected void grdResults_SelectedIndexChanged
(object sender, EventArgs e)
{
int nCustomerID =
(int)this.grdResults.SelectedDataKey.Values[0];
Response.Redirect("CustomerPage.aspx?CUSTID=" +
nCustomerID.ToString().Trim());
}
}
Listing 5: A T-SQL 2005 UDF to convert an XML string to a table variable
-- Table-valued UDF to convert an XML string
-- to a table-valued UDF
-- Useful if you have an XML string of user-selections,
-- and want to convert them to a Table variable that
-- you can use in subsequent JOIN statements
-- Uses the new XML data type
CREATE FUNCTION [dbo].[XMLtoTable]
(@XMLString XML )
RETURNS
@tPKList TABLE ( IntPK int ) -- returns table variable
AS
BEGIN
INSERT INTO @tPKList
SELECT Tbl.col.value('.','int') as IntPK
FROM @XMLString.nodes('//IDpk' ) Tbl(col)
-- use nodes() method to shred XML data into relational
-- data. Incoming XML must have a numeric column
-- with the name IDpk
RETURN
END
Listing 6: A generic DAL method to pump a result set into a custom object
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
namespace SimpleDataAccess
{
public class SimpleDataAccess
{
public void RetrieveIntoCollection<T>(
List<T> oCollection , string cStoredProc,
List<SqlParameter> oParmList, Type oCollectionType)
{
SqlConnection oSqlConn = this.GetConnection();
SqlCommand oCmd = new SqlCommand(cStoredProc, oSqlConn);
oCmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter oParm in oParmList)
oCmd.Parameters.Add(oParm);
oSqlConn.Open();
SqlDataReader oDR = oCmd.ExecuteReader();
while(oDR.Read()) {
T oItem =
(T)Activator.CreateInstance(oCollectionType);
// get all the properties of the class
PropertyInfo[] oCollectionProps =
((Type) oItem.GetType()).GetProperties();
for (int n=0; n<oCollectionProps.Length; n++) {
string cPropName = oCollectionProps[n].Name;
oCollectionProps[n].SetValue
(oItem, oDR[cPropName], null);
}
oCollection.Add(oItem);
}
oSqlConn.Close();
}
public SqlConnection GetConnection()
{
SqlConnectionStringBuilder oStringBuilder = new
SqlConnectionStringBuilder();
oStringBuilder.UserID = "sa";
oStringBuilder.Password = "";
oStringBuilder.InitialCatalog = "NewCustomer";
oStringBuilder.DataSource = "KCI890";
return new
SqlConnection(oStringBuilder.ConnectionString);
}
}
}
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 variables.
| 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 |
