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

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

Beginning with the End in Mind
Let’s get down to business. This article will build a lookup and results/navigation screen (see Figures 1, 2, and 3).

?
Figure 1: First page of results, user can make various selections and page through results
?
Figure 2: Default sort/page on Last Name, jump to letter “K.”
?
Figure 3: Sort/Page on address, then jump to page 5.

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 (DAL) 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.
  • Create an environment to build and test your stored procedures independently of your application.
  • 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, 2, and 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.
?
Figure 4: The development solution for the Web site.

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:

  1. Establish all of the input criteria and variables for the search page, and establish the parameters for the stored procedure.
  2. Build and test the stored procedure, which will utilize the new ranking functions in T-SQL 2005 to correlate with the paging parameters.
  3. Create a data access class in C# to communicate between the Web page and the stored procedure.
  4. Design the Web page and write the C# code in the code-behind to handle all the Web page behavior (formatting the grid results, page navigation, page controls, and variables, etc).
  5. Setting up the basic data access layer to return either a DataSet or a custom collection.

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.

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 oSQLParms = new       List();      // 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).

Tip 8: Handling Quick-lookup Navigation and 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: Handling 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 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 Columns to 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 shown in Figures 1, 2, and 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: Allowing for 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 CheckedListBox Web control.
  • You'll write code to read the CheckedListBox 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 data type, 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 Server 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 shred, 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 4 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.

The code in Listing 4 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    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 2 to include the new parameter.

   // code also adds XMLStatuses as a parameter   // to GetCustomers in Listing 4   oSQLParms.Add(   new SqlParameter("@XMLString", XMLStatuses));

You're almost there! In the last step you'll handle the Web page by populating a CheckedListBox 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 CheckedListBox 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 (see Listing 5) executes a stored procedure and pumps 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 oSQLParms = new List();   oSQLParms.Add(new SqlParameter("@LastName", LastName));   List oCustomers = new List();   // 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 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 ReadIntoCollectionList oCollection,       string cStoredProc, List 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

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?although 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 LocationID, Customer ID, and Amount Due fields, 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 vs. 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 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 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).

You can find the entire source code for this article on my Web site. For additional information, check out my blog.

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!

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

homes in the real estate industry

Exploring the Latest Tech Trends Impacting the Real Estate Industry

The real estate industry is changing thanks to the newest technological advancements. These new developments — from blockchain and AI to virtual reality and 3D printing — are poised to change how we buy and sell homes. Real estate brokers, buyers, sellers, wholesale real estate professionals, fix and flippers, and beyond may

man on floor with data

DevX Quick Guide to Data Ingestion

One of the biggest trends of the 21st century is the massive surge in internet usage. With major innovations such as smart technology, social media, and online shopping sites, the internet has become an essential part of everyday life for a large portion of the population. Due to this internet

payment via phone

7 Ways Technology Has Changed Traditional Payments

In today’s digital world, technology has changed how we make payments. From contactless cards to mobile wallets, it’s now easier to pay for goods and services without carrying cash or using a checkbook. This article will look at seven of the most significant ways technology has transformed traditional payment methods.