N-Layered Web Applications with ASP.NET 3.5 Part 4: Sorting, Paging and Filtering

After you’ve read the previous series, be sure to check out the three earlier parts of the new series as well as they describe the new application’s architecture, and show you how to implement validation.

Quick Links to All Articles

Previous Series
Building Layered Web Applications with Microsoft ASP.NET 2.0 - Part 1
Building Layered Web Applications with Microsoft ASP.NET 2.0 - Part 2
Building Layered Web Applications with Microsoft ASP.NET 2.0 - Part 3
Custom Sorting with N-Layer Design Classes and the GridView

Current Series
N-Layered Web Applications with ASP.NET 3.5 Part 1: General Introduction
N-Layered Web Applications with ASP.NET 3.5 Part 2: Introducing the Validation Framework
N-Layered Web Applications with ASP.NET 3.5 Part 3: Advanced Validation Topics
N-Layered Web Applications with ASP.NET 3.5 Part 4: Sorting, Paging and Filtering 
N-Layered Web Applications with ASP.NET 3.5 Part 5: Dealing with Concurrency
N-Layered Web Applications with ASP.NET 3.5 Part 6: Security

In this installment, I’ll discuss a number of advanced and miscellaneous subjects, including:

1. Sorting – primarily in the Business Layer using LINQ
2. Paging — done at both the database level and at the business entities level using LINQ
3. Filtering — techniques to let your user search (or filter) their results

Sorting

Sorting of data is a very common operation that users perform in web applications. Sorting a list of revenues per month from highest to lowest, or sorted by month, sorting a list of users in a forum by their activity or number of posts they have made, and sorting news articles in a GridView or ListView control by their title or the number of times they have been read are all examples of sorting custom data. Fancy controls like the GridView support sorting out-of-the box, but only with standard data containers like a DataSet. For custom objects, you’ll need to roll your own solution.

My initial article series didn’t take sorting into account. However, it was an often requested feature so back in June 2007 I wrote a short article about sorting in your custom business entities. If you decide to read that article now, be sure to come back again here later, as some important things have changed.

Sorting can be done at a few levels: in the UI, somewhere in your own business entities or at the database level. Sorting in the UI is not covered in this article, but in short it comes down to writing some JavaScript that targets an HTML container that contains your data to be sorted, like a <table> element and then sorts the items in that container (<tr> and <td> elements for example) based on some criteria. You can use the popular jQuery framework to make this super easy, as explained here: Flexible client-side table sorting.

More practical than sorting in the UI is sorting at the database level, or sorting in your custom business entities. Sorting at the database is easy for static sorts; simply add an ORDER BY to your T-SQL code and define the columns you want to sort on, and the direction you want to sort in. For example, this code sorts your addresses based on their city in ascending order and then in descending order on the street:

	SELECT Id, Street, City FROM Address ORDER BY City, Street DESC      

Dynamic sorts — where the user can determine on what column to sort and in what direction — are slightly trickier. One solution for dynamic sorts at the database level is to use dynamic SQL — where a T-SQL statement is built up in your stored procedure as a string which is then executed. For example, the following code allows you to pass in the sortColumn as a parameter to determine the sort:

	CREATE PROCEDURE sprocAddressGetListDynamicSort

  @sortColumns nvarchar(100)

AS
  DECLARE @sql nvarchar(max)
  SET @sql = 'SELECT Id, Street, City FROM Address ORDER BY ' + @sortColumns
  EXEC(@sql)

As shown above, this is pretty easy to implement. But, depending on how you implement it and call this stored procedure, you could leave yourself open for hack attempts with SQL injection. Besides that, it may also perform worse than a normal stored procedure. Typically, I try to stay away from dynamic SQL if I possible (which usually is the case).

A safer approach is using a CASE block in T-SQL that could look like this:

	CREATE PROCEDURE sprocAddressGetListDynamicSort

  @sortColumn nvarchar(100)

AS

  SELECT Id, Street, City FROM Address ORDER BY 
    CASE @sortColumn 
      WHEN 'Street' THEN Street 
      WHEN 'City' THEN City
      ELSE Convert(nvarchar(200), Id) -- Use Id as the default
END

While this code works fine at run-time and is secured against SQL injection attacks, it has a few drawbacks. First, it requires that all columns are converted to the same data type which may cause a performance hit. Secondly, it doesn’t work with dynamic sort directions easily. Finally, it only allows you to sort on a single column at a time.

If you’re using SQL Server 2005 or later and only have a limited number of columns you want to sort on dynamically, you can take advantage of the new Rank function as explained in the article: "Slightly more dynamic ORDER BY in SQL Server 2005".

But, all in all, dynamic ordering in the database is not a walk in the park. So, typically, for huge record sets, I create a number of predefined stored procedures or blocks of code inside a single stored procedure that handle the ordering. Then, based on a CASE statement, I execute the correct block. Not very flexible, but it performs well and is relatively easy to implement.

For smaller results sets (e.g. under hundreds or maybe thousands of records or objects) sorting at the Business Entities level is a very good option. It gives you an enormous amount of flexibility with determining how to sort (you use pure .NET code so anything that flies in .NET can be used) and it’s pretty easy to implement dynamic sorting. You’ll see how to do this later in this article.

The biggest drawback is that it doesn’t work with database paging; where you only get one “page” of data from the database at a time. If you use .NET sorting combined with database paging, you only sort the “current page” which won’t do you any good considering the entire result set that is available. So, typically, when paging is used, you either sort your data in a predefined order, or you need to resort to some of the work arounds I mentioned earlier.

The remainder of this section will focus on sorting data in custom business entities. Be warned though: don’t use this technique on large result sets. Since all data needs to be copied from the database to the .NET world first, you’ll have a lot of database- and network overhead. Only use this technique for relatively small amounts of data.

Introduction to List<T> and Collection<T>

The sorting mechanism explained in my previous article on custom sorting relied on two core concepts. The first one is a class that implements IComparer<T> and which is responsible for sorting a custom entity. The class is implemented as a private, nested class inside the main business entity like ContactPerson or Address. You’ll see an example of this class a little later in this article.
The second key part is the Sort method of the List<T> class where all the collections in the BusinessEntities namespace inherit from. For example, because AddressCollection was implemented as follows in the previous version of my N-Layer design:

	public class AddressCollection : List<Address> { … }

you could simply sort a list of addresses alphabetically on their Street property like this:

	myList.Sort(new AddressComparer("Street"));

While easy to implement, List<T> isn’t really a collection type you should use in the public API of your Business Layer. Shortly after the release of my first series, I used these classes in an application that was examined with FX Cop – Microsoft’s Code Analysis tool. FX Cop listed a number of warnings including this one:

"Do not expose List<T> in object models. Use Collection<T>, ReadOnlyCollection<T> or KeyedCollection<K,V> instead. List<T> is meant to be used from implementation, not in object model API. List<T> is optimized for performance at the cost of long term versioning. For example, if you return List<T> to the client code, you will not ever be able to receive notifications when client code modifies the collection."

Fixing this problem is pretty simple: just inherit Collection<T> instead of List<T> and the error goes away. That's what I did with the collection classes in the new version of the Contact Manager Application. The collections actually inherit from the BusinessCollectionBase<T> intermediate class that in turn inherits from ValidationCollectionBase<T>. ValidationCollectionBase<T> in turn inherits from Collection<T> and puts a type restriction on the generic T type parameter to enforce that the collections can only be created for classes that inherit ValidationBase.

The ValidationCollectionBase<T> class looks like this:

	namespace Spaanjaars.Validation
{
  public abstract class ValidationCollectionBase<T> : 
                 Collection<T> where T : ValidationBase
  {
    …
  }
}

The BusinessCollectionBase (defined in the Spaanjaars.ContactManager.BusinessEntities project) inherits from this ValidationCollectionBase<T> class and looks like this:

	namespace Spaanjaars.ContactManager.BusinessEntities
{
  public abstract class BusinessCollectionBase<T> : 
      ValidationCollectionBase<T> where T : ValidationBase
  {
    …
  }
}

And finally, a concrete implementation like AddressCollection in the business entities project is then defined as follows:

	namespace Spaanjaars.ContactManager.BusinessEntities
{
  public class AddressCollection : BusinessCollectionBase<Address>
  {
    …
  }
}

There is one big drawback in inheriting from Collection<T> rather than List<T>: you’ll miss some important functionality that is available in List<T> but that Collection<T> doesn’t have. Most notably you’ll miss the Sort method on the List<T> class. This means you can no longer sort a list automatically by calling the Sort method. Fortunately, it’s easy to implement a Sort method yourself. Since all collections inherit from the BusinessCollectionBase, it makes sense to implement the Sort method in that class. In order to do this, you need two things: a modified constructor that ensures that the base Collection<T> class is initialized with an internal List<T> for the items and a Sort method that accepts an IComparer<T> to do the actual sorting. I’ll discuss the constructor first.

Earlier I said that the List<T> has a convenient Sort method that allows you to sort objects in a list. Rather than rewriting your own sort mechanism it’s much easier to use the existing Sort method of the collection. The FX Cop warning warned against using List<T> in the public API of your object model, but there is nothing against using List<T> internally for your objects. So, to minimize the code you need to write and to be able to reuse the Sort method, you should construct your Collection<T> instances with an internal List<T> for the items. Still with me? I can imagine that this is a little confusing, so take a look at the constructor in the ValidationCollectionBase class:

	public abstract class ValidationCollectionBase<T> : 
                   Collection<T> where T : ValidationBase
{
  public ValidationCollectionBase() : base(new List<T>()) { }
}

You can see that when a new ValidationCollectionBase (of type T) is constructed, it calls its base constructor (the one from Collection<T>) and passes in a new List<T>. The Collection class then uses this list internally to store instances of T. So, ValidationCollectionBase is a Collection<T> on the outside, but uses a List<T> on the inside for state management.

Now, consider an AddressCollection that looks like this:

	public class AddressCollection : BusinessCollectionBase<Address>
{
}

This is really all the code there’s in that class. When you instantiate a new AddressCollection, the constructor of the BusinessCollectionBase class is called which fires the base class’s constructor (the one defined in ValidationCollectionBase) which in turn call’s the Collection<T> constructor and passes in a new List<Address> that is used to hold the addresses internally.

This list then is used in the second component of sorting: the actual Sort method. This is implemented in the BusinessCollectionBase class so all collections in the BusinessEntities namespace have it available:

	public void Sort(IComparer<T> comparer)
{
  if (comparer == null)
  {
    throw new ArgumentNullException("comparer", "Comparer is null.");
  } 
  List<T> list = this.Items as List<T>;
  if (list == null)
  {
    return;
  }
  list.Sort(comparer);
}

This code does two important things: it first casts the Items collection (exposed as a generic IList<T>) to List<T>. Since the class made sure on instantiation that the Items collection *is* implemented as a  List<T>, it can successfully do so. The second important part is how it calls Sort. It simply calls this method on the List<T> classes and forwards the comparer object that is responsible for sorting.

Sorting with a Class that Inherits from IComparer<T>

In case you haven’t seen the article on sorting and don’t know what the IComparer<T> is, here’s the definition of it for the AddressComparer, inside the AddressManager class:

	private class AddressComparer : IComparer<Address>
{
  private string _sortColumn;
  private bool _reverse;
  public AddressComparer(string sortExpression)
  {
    if (string.IsNullOrEmpty(sortExpression))
    {
      sortExpression = "Id desc";
    }
    _reverse = sortExpression.ToLowerInvariant().EndsWith(" desc");
    if (_reverse)
    {
      _sortColumn = sortExpression.Substring(0, sortExpression.Length - 5);
    }
    else
    {
      _sortColumn = sortExpression;
    }
  }

  public int Compare(Address lhs, Address rhs)
  {
    int retVal = 0;
    switch (_sortColumn.ToLower())
    {
      case "id":
        retVal = lhs.Id.CompareTo(rhs.Id);
        break;
      case "city":
        retVal = lhs.City.CompareTo(rhs.City);
        break;
      // Other properties go here.
      default:
        throw new ArgumentException(string.Format("Unkown SortColumn: {0}", 
                      _sortColumn));
    }

    int _reverseInt = 1;
    if ((_reverse))
    {
      _reverseInt = -1;
    }
    return (retVal * _reverseInt);
  }
}

As explained in the article on sorting, the constructor of the AddressComparer accepts a sort expression that contains the column you want to sort on, and optionally an indication that sorting must be done in descending order. So, if you want to sort on Id, you simply pass “Id”. If you want to sort on the City in descending order you need to pass “City DESC”.

The Compare method then takes two instances of the class you are comparing: Address lhs (left-hand side) and Address rhs (the right-hand side). The switch block then executes a block of code associated with the sort expression defined in the constructor. In this example, each switch block simply delegates the compare action to the CompareTo method of each property. However, it doesn’t have to be like this, and it’s easy to write your own implementation. For example, if you have an OrderLine class that contains an ItemPrice and a Quantity, you could order the OrderLine instances based on their total value like this:

	case "total":
  retVal = (lhs.ItemPrice * lhs.Quantity).CompareTo(
                rhs.ItemPrice * rhs.Quantity);
  break;

The actual sorting is done by the Sort method of List<T>. It knows how to call Compare for each item in the list, determine whether one item is “larger” than the other or not and then updates the internal list to reflect the new sort order.

Putting it all Together: a Simple Unit Test for Sorting

To show you how sorting works and to test out the IComparer<T> behavior, I wrote a couple of unit tests that sort data in a collection. Inside my unit tests project I have a code file called IComparerTests.cs.  Besides the actual unit tests, this file also contains two nested helper classes called OrderLine and OrderLineCollection respectively whose only purpose is to test and demonstrate the sorting mechanism. These classes look like this:

	public class OrderLineCollection : BusinessCollectionBase<OrderLine>
{
}

public class OrderLine : ValidationBase
{
  public double ItemPrice { get; set; }
  public int Quantity { get; set; }

  public class OrderLineComparer : IComparer<OrderLine>
  {
    private string _sortColumn;
    private bool _reverse;

    public OrderLineComparer(string sortExpression)
    {
      if (sortExpression.Length == 0)
      {
        sortExpression = "ItemPrice";
      }
      _reverse = sortExpression.ToLowerInvariant().EndsWith(" desc");
      if (_reverse)
      {
        _sortColumn = sortExpression.Substring(0, 
                       sortExpression.Length - 5);
      }
      else
      {
        _sortColumn = sortExpression;
      }
    }

    public int Compare(OrderLine lhs, OrderLine rhs)
    {
      int retVal = 0;
      switch (_sortColumn.ToLower())
      {
        case "itemprice":
          retVal = lhs.ItemPrice.CompareTo(rhs.ItemPrice);
          break;
        case "quantity":
          retVal = lhs.Quantity.CompareTo(rhs.Quantity);
          break;
        case "total":
          retVal = (lhs.ItemPrice * lhs.Quantity).CompareTo(
                       rhs.ItemPrice * rhs.Quantity);
          break;
        default:
          throw new ArgumentException(string.Format(
                 "Unkown SortColumn: {0}", _sortColumn));
      }

      int _reverseInt = 1;
      if ((_reverse))
      {
        _reverseInt = -1;
      }
      return (retVal * _reverseInt);
    }
  }
}

All of this code should be pretty familiar by now. The OrderLineCollection simply inherits from BusinessCollectionBase<OrderLine> and adds no behavior of its own. The OrderLine is a very simple class with only two automatically implemented properties for the ItemPrice and the Quantity. Most of code in the OrderLine class deals with the IComparer<T> implementation to order OrderLine items.

Before I show you the unit tests that validate the sorting routines, there’s a small helper method that you need to look at:

	private OrderLineCollection GetOrderLines()
{
  OrderLineCollection myList = new OrderLineCollection();
  myList.Add(new OrderLine() { ItemPrice = 10, Quantity = 4 });
  myList.Add(new OrderLine() { ItemPrice = 2, Quantity = 10 });
  myList.Add(new OrderLine() { ItemPrice = 6, Quantity = 5 });
  return myList;
}

This method is used in all the unit tests to get a small collection of OrderLine items prefilled with data.

Writing unit tests that order the collection and assert a correct order is now very straight forward. For example, to see if you can successfully sort on the Quantity property, you could write the following test:

	[TestMethod]
public void OrderOnQuantityTest()
{
  OrderLineCollection myList = GetOrderLines();
  // standard order is 4, 10, 5. Let's assert that first
  Assert.AreEqual<int>(4, myList[0].Quantity);
  Assert.AreEqual<int>(10, myList[1].Quantity);
  Assert.AreEqual<int>(5, myList[2].Quantity);
  
  // Now sort on Quantity and check if the collection is sorted correctly.
  myList.Sort(new OrderLine.OrderLineComparer("Quantity"));
  Assert.AreEqual<int>(4, myList[0].Quantity);
  Assert.AreEqual<int>(5, myList[1].Quantity);
  Assert.AreEqual<int>(10, myList[2].Quantity);

  // Now sort on Quantity in reverse and check if 
  // the collection is sorted correctly.
  myList.Sort(new OrderLine.OrderLineComparer("Quantity desc"));
  Assert.AreEqual<int>(10, myList[0].Quantity);
  Assert.AreEqual<int>(5, myList[1].Quantity);
  Assert.AreEqual<int>(4, myList[2].Quantity);
}

This code first gets a small collection with OrderLine items. To assure that the values that are setup in the Helper method haven’t been changed (by another developer for example), the code first uses three Assert statements to see if the items still have the expected values. That is, item[0] has a Quantity of 4, Item[1] has a Quantity of 10 and so on.

The next line of code calls Sort and passes in a new OrderLineComparer that is set up to sort on the Quantity property. The three Assert statements that follow check whether the list has been sorted correctly. The code then calls Sort again, but this time it passes “Quantity desc” to sort the list in reversed order. Finally, it uses three more Assert statements to check whether the items are in correct (descending) order again.

When all tests run successfully (my sample project contains more tests) you can safely assume that the new Sort method on the ValdiationCollection base class can successfully sort collections containing your custom business entities.

To make it easier to see what’s going on during sorting, I wrote unit tests against a simpler, temporary class (the OrderLine). However, that doesn’t mean I haven’t implemented these sorting principles in the Contact Manager Application. In the next section I’ll give you an overview of how and where sorting is implemented in the sample application that comes with this article.

Sorting in the Contact Manager Application.

Since you’ve already seen most of the concepts that deal with sorting, I won’t go into the details again in this section. Instead, I’ll simply lists the characteristics of the sorting implementation.

1. Sorting is always done at the code level; not at the database level. This is fine if you’re dealing with tens of hundreds of records at the same time, which is likely to be the case in this application. If you’re working with large data sets, you’ll need to implement paging (discussed later) at the database level and consequently you need to change the code so it supports database sorting instead.
    
2. The BusinessEntities namespace defines a BusinessCollectionBase class where all other collections (ContactPersonCollection, AddressCollection and so on) inherit from. The BusinessCollectionBase class itself inherits from ValidationCollectionBase in the Spaanjaars.Validation namespace to get standard behavior for validating collections.
    
3. Each *Manager class (like AddressManager) defines a nested private class that inherits from IComparer<T> to determine the actual sorting behavior for a business entity. In an earlier example I showed you this class was nested in a BusinessBase like OrderLine, but in the Contact Manager Application I determined to implement this in the Manager classes. This way, you could implement the actual sort behavior differently based on a specific manager class. If you don’t like this behavior, you could simply move the Comparer class to the business entities themselves.
    
4. To make use of the Sort method in the BusinessEntities namespace, I created an overload of GetList that accepts the sortExpression. The original GetList method calls this new overload and passes string.Empty to indicate sorting is not needed. For example, the ContactPersonManager class defines the following two overloads of GetList:

   			[DataObjectMethod(DataObjectMethodType.Select, true)] 
   public static ContactPersonCollection GetList() 
   { 
     return GetList(string.Empty); 
   } 
   
   [DataObjectMethod(DataObjectMethodType.Select, false)]
   public static ContactPersonCollection GetList(string sortExpression)
   {
     ContactPersonCollection myCollection = ContactPersonDB.GetList();
     if (!string.IsNullOrEmpty(sortExpression))
     {
       myCollection.Sort(new ContactPersonComparer(sortExpression));
     }
     return myCollection;
   }
   

   If there is a sortExpression the code initializes a new ContactPersonComparer and then calls Sort on the collection to handle the sort routine before it returns the list to the calling code.

5. To make use of the Sort method in the Presentation Layer to sort contact persons, I made the following changes to the Default.aspx page:
   1. I enabled sorting on the GridView by setting AllowSorting to True:

      					<asp:GridView ID="gvContactPersons" runat="server" … 
                   AllowSorting="True">

   2. I set a SortExpression on each of the relevant grid columns like this:
      

      					<asp:BoundField DataField="FullName" SortExpression="FullName" />

   3. I set a sortParameterName property on the ObjectDataSource control:
      

      					<asp:ObjectDataSource ID="odsContactPersons" 
                     SortParameterName="sortExpression" />
      

By setting the sortParameterName, ASP.NET knows to what parameter of GetList to pass the sortExpression that it retrieves from the GridView.

That’s all you need to do to enable sorting in the Presentation Layer. The ASP.NET run-time and its smart controls like the GridView and the ObjectDataSource controls handle the rest for you. When you now click a column header in the GridView, the associated sort expression is passed to the ObjectDataSource. This control then forwards the expression to the GetList method which returns the collection in the requested sort order.

Note that I only implemented sorting for the ContactPersons GridView although it’s easy to change the other controls yourself as well if you want them to be sortable as well.

Besides sorting, another important topic that deals with data is paging, which is discussed next.

Paging

Consider an on-line book store like Amazon featuring hundreds of thousands or even millions of products. Imagine you’re searching the book catalog for all books that contain the word “Visual” because you’re looking for books on Visual Studio. The last time I checked this resulted in 347,466 products. Clearly, you can’t put all of these products on a single result page. It would take ages for your browser to download and parse the HTML for that page. On the server side, it’s not feasible to select and process that many records as well. So, clearly something needs to be done and paging is the answer. With paging you chop large results into manageable chunks, called pages, so you can retrieve and present them page by page. In the Amazon.com example, you get ten books on a page, and a couple of navigation controls to browse through the list of pages in the result set.

Paging is a great way to optimize the performance of an application and to improve the usability of a web page that contains a lot of data. Just with as sorting, paging can be done at a few different levels which I’ll discuss next.

Client Side Paging

Depending on the amount of data and the browsers you’re targeting, client side paging may work very well. Although there are a few different techniques to do client side paging, they basically all come down to this:

1. Select all available data from a data source at the server using server side code.
2. Push all data to the client; as XML, HTML or some other format like plain text, Json or whatever works for you.
3. Use client side code (xslt, JavaScript) to select a subset of the data and present it as a page.
4. Use client side code to react to navigation commands (next page, previous page and so on) and then filter the correct data from the data source and present it as a page.

I won’t go any deeper into client side paging but if you’re interested, check out the following two articles. The first one deals with the older ASP.NET DataGrid, but the same behavior can be written for the newer GridView control or even the ASP.NET 3.5 ListView. The second article deals with jQuery, a great client-side script library that makes writing this kind of behavior much easier.

• Client-side Paging for DataGrids
• Client Side Table Sorting, Paging and Filtering with jQuery

Business Layer Paging

With Business Layer Paging I am referring to a way to modify classes and methods in the Bll namespace to support paging. The idea is simple: you get the full list from the data source, and then you use LINQ (or your own .NET code) to filter the requested records. In order to support this, you typically create overloaded versions of methods like GetList() and give them two extra parameters: the page size and the start index of the first record of the requested page. For example, the signature of a new GetList method in the ContactManager class could look like this:

	public static ContactPersonCollection GetList(string sortExpression, 
                      int startRowIndex, int maximumRows)

Once you create an overload like this, it’s a good idea to redirect the other overloads to the most extensive one, passing default values for parameters they don’t support. For the ContactPersonManager class, this means you could end up with four overloads: one that supports no additional features at all, one that supports paging, one that supports sorting and one that supports both. These four methods could look like this:

	[DataObjectMethod(DataObjectMethodType.Select, true)]
public static ContactPersonCollection GetList()
{
  return GetList(string.Empty, -1, -1);
}


[DataObjectMethod(DataObjectMethodType.Select, false)]
public static ContactPersonCollection GetList(string sortExpression)
{
  return GetList(sortExpression, -1, -1);
}


[DataObjectMethod(DataObjectMethodType.Select, false)]
public static ContactPersonCollection GetList(
                int startRowIndex, int maximumRows)
{
  return GetList(string.Empty, startRowIndex, maximumRows);
}


[DataObjectMethod(DataObjectMethodType.Select, false)]
public static ContactPersonCollection GetList(string sortExpression, 
                  int startRowIndex, int maximumRows)
{
  ContactPersonCollection myCollection = ContactPersonDB.GetList();
  if (!string.IsNullOrEmpty(sortExpression))
  {
    myCollection.Sort(new ContactPersonComparer(sortExpression));
  }
  if (startRowIndex >= 0 && maximumRows > 0)
  {
    return new ContactPersonCollection(
    myCollection.Skip(startRowIndex).Take(maximumRows).ToList());
  }
  return myCollection;
}

The first three overloads simply delegate responsibility by forwarding calls to the final GetList method and supplying default values for the sortExpression and the paging parameters.

The final method then gets a list of contact people from the database, optionally applies a sort as explained earlier in this article and then (optionally, when startRowIndex is zero or larger) uses a LINQ query to grab a “page” of records. The Skip method simply skips the number of requested items in the sequence, while Take then gets the specified number of items. So when you specify 0 for startRowIndex and 10 for maximumRows, you’ll get the first 10 contacts. Specify 20 and 20 respectively, and you’ll get items 21 to 40.

From an API point of view, this is pretty much all you need. You can now call any of the overloads, pass any parameters you see fit and the GetList method is able to give you a sorted and paged list of results based on the criteria you passed in. Sweet! If, however, you want to use these methods in your ASPX pages, you’ll need to do a bit more work. For instance, if you want to use this method in an ObjectDataSource together with a data bound control like a GridView, you’ll need to supply an additional method: one that is able to return the total number of records in the entire result set, before filtering. This is necessary because controls like the GridView need to be able to determine the page count so they can display the correct number of pages and adjust the navigation controls. Since you only retrieve one page of data at a time, the GridView by default thinks you’re only dealing with a single page and doesn’t display other paging controls. To make this work, you’ll need to create a method that counts and returns the total number of items in the database as an integer. Additionally, you’ll need to configure the ObjectDataSource in the ASPX page so it knows where to find this new method. I’ll discuss the counting method first, followed by a brief discussion of the changes in the ASPX pages.

The method that counts the records has a pretty simple signature: all it needs to do is return an int so the following signature would do:

	public int SelectCountForGetList()
{

}

From here, you have a few different options to get the total number of records. One very lazy (and not so efficient) way is to simply call GetList once more and return its Count property, like this:

	public int SelectCountForGetList()
{
  return GetList(-1, -1).Count;
}

Because GetList is called again (which in returns retrieves all the records from the database), this method has a lot of overhead. None of the records retrieved from the database are actually used, so this method consumes more CPU cycles and memory than necessary. A cleaner way to approach this is to create an additional OUTPUT parameter on the GetList stored procedure that returns the count. You can then call this procedure and retrieve only the number of records, not the actual data. The stored procedure sprocContactPersonSelectList  in the Contact Manager Application looks as follows:

	CREATE PROCEDURE sprocContactPersonSelectList

  @recordCount int = NULL OUTPUT

AS

  IF (@recordCount IS NOT NULL)
  BEGIN
    SET @recordCount = (SELECT COUNT(*) FROM ContactPerson)
    RETURN
  END
  
  SELECT 
    Id,
    FirstName,
    MiddleName,
    LastName,
    DateOfBirth,
    ContactPersonType,
    ConcurrencyId
  FROM 
    ContactPerson

To avoid the need to change existing code, this stored procedure now has a nullable (and thus optional) @recordCount parameter. If this parameter has an initial value (anything other than NULL), the procedure counts and returns the total number of records in the output parameter and skips getting the actual records. If the parameter is null (e.g. in normal cases where you’re interested in the data), the COUNT part is skipped and the query returns the actual records with a SELECT statement.

Calling this procedure is easy: just set up an InputOutput parameter in the corresponding SelectCountForGetList method in the Data Access Layer, pass a value of 0 and then when the call has completed, convert the value of the output parameter to an int and return it:

	public static int SelectCountForGetList()
{
  using (SqlConnection myConnection = new SqlConnection(
                       AppConfiguration.ConnectionString))
  {
    using (SqlCommand myCommand = new SqlCommand(
                      "sprocContactPersonSelectList", myConnection))
    {
      myCommand.CommandType = CommandType.StoredProcedure;
      DbParameter idParam = myCommand.CreateParameter();
      idParam.DbType = DbType.Int32;
      idParam.Direction = ParameterDirection.InputOutput;
      idParam.ParameterName = "@recordCount";
      idParam.Value = 0;
      myCommand.Parameters.Add(idParam);
      
      myConnection.Open();
      myCommand.ExecuteNonQuery();
      myConnection.Close();
      return (int)myCommand.Parameters["@recordCount"].Value;
    }
  }
}

While paging like this is very easy to implement (all you need is a record count procedure and a bit of LINQ), this method has one drawback: it pages at the Business Layer. This means that, at a high cost, it needs to retrieve and process all records from the database, and then only use some of them. Once you start working with tables containing thousands or more records, or when you are accessing a remote SQL Server over a slow network connection (over a VPN for example), you’ll notice this model starts to break. In these cases, you’ll need to use database paging.

Database Paging

While slightly more complicated to implement, database paging is the preferred way to page your data. Since the database only needs to return the records for the requested page, you can save yourself from transferring a lot of unneeded records from the database to the Data Access Layer in your code. This results in improved performance and less network and processing overhead.

In versions of SQL Server before SQL Server 2005, paging required a lot of code inside stored procedures. In short, this is the process you needed to follow:

1. Define a stored procedure with startIndex and pageSize parameters.
2. Define a temp table with an IDENTITY column.
3. Perform your SELECT statement into this temp table, optionally limiting records with the WHERE clause.
4. SELECT from your temp table all records between startIndex and startIndex + pageSize.

While not very difficult to write, this solution required a lot of manually written code. Additionally, every time you needed to add or remove a column from the SELECT statement, you had to alter the temp table and the final SELECT statement as well.

SQL Server 2005 and up make this a lot easier with the new ROW_NUMBER() function. This function returns the sequential number of a row in an ordered result set. You can then use the result of this function in a WHERE clause to limit the number of rows returned. The following T-SQL snippet shows how this works for contact people:

	SELECT 
  Id, 
  Email
FROM 
  (SELECT ROW_NUMBER() OVER (ORDER BY Id) AS Row, 
  Id, Email FROM EmailAddress) AS EmailRows
WHERE 
  Row BETWEEN 1 AND 10

ROW_NUMBER adds an additional column (aliased as Row) to the result set. This Row column is then used to grab only those records with a row number between 1 and 10 – the first “page” of records with a page size of 10. Fixed values for the start index and page size are not very useful of course, so in order to use this in your applications you need to make this more dynamic. The following stored procedure gets the e-mail addresses for a contact person as a paged list:

	CREATE PROCEDURE sprocEmailAddressSelectList
 
  @contactPersonId int,
  @startRowIndex int,
  @pageSize int

AS
  
  SELECT 
    Id,
    Email,
    EmailType,
    ContactPersonId,
    ConcurrencyId
  FROM 
    (SELECT ROW_NUMBER() OVER (ORDER BY Id) AS Row, 
    Id,
    Email,
    EmailType,
    ContactPersonId,
    ConcurrencyId
    FROM EmailAddress) AS EmailRows
  WHERE 
    (Row between (@startRowIndex) AND @startRowIndex + @pageSize - 1)
    AND ContactPersonId = @contactPersonId

Notice how you can add your own stuff to the WHERE clause. In this case, I am getting one page of records for a specific contact person at a time. Both the start index (one based, so you need to pass the number 1 to indicate the first record) and the page size are controlled through parameters, giving you a great deal of flexibility.

If you still want to be able to get the full list of records, you could modify the WHERE clause to return all records when either @startRowIndex or @pageSize is -1, like this:

	WHERE 
  ((Row between (@startRowIndex) AND @startRowIndex + @pageSize - 1) 
        OR @startRowIndex = -1 OR @pageSize = -1)
  AND ContactPersonId = @contactPersonId

Notice that the WHERE clause uses -1 here as the "magic number" to determine that all records should be returned. This value is passed from the Business Layer into the Data Access Layer and then finally passed to this stored procedure. When @startRowIndex or @pageSize are -1, the entire result set is returned. If they are greater than -1, only those records with a matching index are returned.

With these “magic numbers”, you can now create two overloads of GetList in the database: one that accepts only the contactPersonId, and one that accepts paging parameters:

	public static EmailAddressCollection GetList(int contactPersonId)
{
  return GetList(contactPersonId, -1, -1);
}


public static EmailAddressCollection GetList(int contactPersonId, 
                 int startRowIndex, int maximumRows)
{
  // implementation here
}

(Note: I am not showing the implementation of GetList here, as it’s identical to the GetList of the ContactPerson class you saw earlier. If you want to see the full code, check out the file EmailAddressDB.cs in the Data Access Layer project.)

Just as the previous example that showed you how to page over contact people, this solution now works fine from an API point of view. That is, you can simply call GetList in code, pass a contactPersonId, a start index and a page size and you’ll get your records. In order for it to work in ASPX pages using an ObjectDataSource control, you need another SelectCountForGetList method that returns the actual number of records. To see the implementation in the sample application, look at SelectCountForGetList in the EmailAddressManager and EmailAddressManagerDB classes.

Database Paging and Sorting Combined (or the Lack Thereof)

Given the lack of dynamic sorting capabilities in SQL Server, combining custom paging with database sorting is pretty difficult. Most solutions come down to using dynamic T-SQL code inside a stored procedure that builds up the full statement based on incoming parameters. For a detailed explanation of this solution, check out: Sorting Custom Paged Results.

Paging in the Contact Manager Application

I have implemented paging in the Contact Manager Application for the ContactPerson and the EmailAddress class only. However, it’s easy to apply the same principles to the remaining classes as well.

To see how I implemented paging, you’re advised to check out the following classes, methods and code files. Note that I haven’t provided a full implementation of the concepts in all classes to keep things simple to read and understand. Instead, you’ll find Business Layer paging in the ContactPerson class and database paging in the EmailAddress class. Once again, it should be easy to apply the same principles to the other classes to give them paging behavior as well.

ContactPerson Class (using paging in the Business Layer)

	<asp:ObjectDataSource ID="odsContactPersons" runat="server" 
    DataObjectTypeName="Spaanjaars.ContactManager.BusinessEntities.ContactPerson"
    DeleteMethod="Delete" InsertMethod="Save" SelectMethod="GetList"
    TypeName="Spaanjaars.ContactManager.Bll.ContactPersonManager"
    UpdateMethod="Save" SortParameterName="sortExpression" 
	EnablePaging="True" SelectCountMethod="SelectCountForGetList">
</asp:ObjectDataSource>

1. The SelectCountForGetList method in the ContactPersonManager class in the Bll namespace.
2. The SelectCountForGetList method in the ContactPersonManagerDB class in the Dal namespace.
3. The stored procedure sprocContactPersonSelectList that is able to return a row count.
4. Default.aspx where I modified the ObjectDataSource to support paging:

EmailAddress Class (using paging at the database level)

	<asp:ObjectDataSource ID="odsEmailAddresses" runat="server"
      DeleteMethod="Delete"
      DataObjectTypeName="Spaanjaars.ContactManager.BusinessEntities.EmailAddress"
      InsertMethod="Save" SelectMethod="GetList" UpdateMethod="Save"    
      TypeName="Spaanjaars.ContactManager.Bll.EmailAddressManager"
      EnablePaging="true" SelectCountMethod="SelectCountForGetList">
</asp:ObjectDataSource>

1. The SelectCountForGetList method in the EmailAddress class in the Bll namespace.
2. The SelectCountForGetList method in the EmailAddressDB class in the Dal namespace.
3. The stored procedure sprocEmailAddressSelectList that is able to return a row count and only returns the requested page of email address records.
4. Default.aspx where I modified the ObjectDataSource for the e-mail addresses to support paging:

The final important topic that deals with data is filtering. With filtering you can locate and retrieve specific records based on custom search criteria. Filtering is the topic of the next (and last) section of this article.

Custom Filtering using a Criteria Object

Another common operation on data in a database is filtering. With filtering, you limit the result set to only those records a user is interested in. Typically, you do this with a WHERE clause in a T-SQL statement. For example, the stored procedure sprocEmailAddressGetList filters the list of available addresses to only those belonging to the requested contact person. However, in most real-world applications you need a lot more. You probably want to give your users the ability to search for records that match specific criteria. As an example, take a look at the following screen shot:

This screen is taken from the page SearchContactPeople.aspx that you’ll find in the download that comes with this article. That page allows you to search for contact people based on some criteria, like first name, last name and type.

Figure 25 – Searching for Contact People Using Custom Filtering

You simply enter your search criteria in the top bar (all of them are optional) and then press the Search button to get records that match your criteria. The cool thing about this filtering mechanism is that it is very easy to implement. In the Contact Manager Application I implemented filtering using the following steps:

1. Create a ContactPersonCriteria class that holds the criteria that a user can search for.
2. Create overloads of GetList in the Bll and Dal namespaces that accept this criteria instance and forward its values to the database.
3. Write a stored procedure that returns the records that match the criteria being searched for.
4. Write code against an ObjectDataSource to create and fill a ContactPersonCriteria object and pass it to the GetList method.

I’ll discuss each of these steps in detail in the following sections.

1. The Criteria Class

You'll find the criteria class in the SearchCriteria folder of the Business Entities project. Criteria classes are typically extremely simple and mainly serve as a small container for data. The criteria class for contact people looks like this:

	public class ContactPersonCriteria
{
  public string FirstName { get; set; }
  public string LastName { get; set; }
  public PersonType PersonType { get; set; }
}

In this example, the class has three properties that allow you to search for contact people based on their associated properties. For example, you can enter (part of) a first name, and then the system returns all contact people with a matching first name. It’s easy to extend this class so you can search for other criteria as well. For example, you could search for contacts that have no addresses associated with them.

The ContactPersonCriteria is used as a parameter of a GetList overload in the Bll namespace, discussed next.

2. Overloads of GetList in the Bll and Dal namespaces

The GetList method in the Business Logic Layer simply accepts and forwards the criteria object to the Data Access Layer:

	public static ContactPersonCollection GetList(
           ContactPersonCriteria contactPersonCriteria)
{
  return ContactPersonDB.GetList(contactPersonCriteria);
}

It is the method in the DAL that’s worth looking at in detail:

	public static ContactPersonCollection GetList(
           ContactPersonCriteria contactPersonCriteria)
{
  ContactPersonCollection tempList = new ContactPersonCollection();
  using (SqlConnection myConnection = new SqlConnection(
                AppConfiguration.ConnectionString))
  {
    using (SqlCommand myCommand = 
                  new SqlCommand("sprocContactPersonSearchList",myConnection))
    {
      myCommand.CommandType = CommandType.StoredProcedure;

      if (!string.IsNullOrEmpty(contactPersonCriteria.FirstName))
      {
        myCommand.Parameters.AddWithValue("@firstName", 
        contactPersonCriteria.FirstName);
      }
      if (!string.IsNullOrEmpty(contactPersonCriteria.LastName))
      {
        myCommand.Parameters.AddWithValue("@lastName", 
        contactPersonCriteria.LastName);
      }
      if (contactPersonCriteria.PersonType != PersonType.None)
      {
        myCommand.Parameters.AddWithValue("@personType", 
        contactPersonCriteria.PersonType);
      }

      myConnection.Open();
      using (SqlDataReader myReader = myCommand.ExecuteReader())
      {
        if (myReader.HasRows)
        {
          tempList = new ContactPersonCollection();
          while (myReader.Read())
          {
            tempList.Add(FillDataRecord(myReader));
          }
        }
        myReader.Close();
      }
    }
  }
  return tempList;
}

The only thing different in this method compared to the other GetList method is the conditional setup of the stored procedure parameters. Only when the criteria property contains a non-null / empty / none value is the parameter added. Otherwise, it will default to null. This null part is important as it’s used again in the stored procedure that queries for the request contact people. This procedure is discussed next.

3. The Stored Procedure to Search for Contact People

Now that the DAL code is passing the (filled in) criteria to the database, the next step is to write a procedure that is able to filter the requested records. Here’s how sprocContactPersonSearchList looks:

	CREATE PROCEDURE sprocContactPersonSearchList

  @firstName nvarchar(50) = null,
  @lastName nvarchar(50) = null,
  @personType int = null

AS

  SELECT 
    Id,
    FirstName,
    MiddleName,
    LastName,
    DateOfBirth,
    ContactPersonType
  FROM 
    ContactPerson
  WHERE
    (FirstName LIKE '%' + @firstName + '%' OR @firstName IS NULL)
    AND (LastName LIKE '%' + @lastName + '%' OR @lastName IS NULL)
    AND (ContactPersonType = @personType OR @personType IS NULL)

Most of this is pretty straight forward. The procedure has a few parameters and a SELECT statement that retrieves a number of columns from the ContactPerson table (if you’re looking in the code that ships with this article, you’ll see I also select a ConcurrencyId; this is explained in the next part of this article series).

What is special about this procedure is the way the WHERE clause works. Take a look at one of the clauses:

	(FirstName LIKE '%' + @firstName + '%' OR @firstName IS NULL)

The first part probably looks familiar: it simply filters the list of contact people with a first name that matches (for a part) with the @firstName parameter. So, if you pass “mar” in the @firstName parameter, you’ll find Imar, Marianne, Mark and so on.
The second part is special as it compares the actual parameter value against null. This is useful when the code in Data Access Layer passes no value. For example, when you only pass part of the last name, the two other parameters will be null. By comparing the parameter value with null, you can effectively ignore that WHERE clause part altogether. This way, you have a flexible solution that allows you to pass in none, one or more arguments to the procedure. When you pass none, you get all the contact people. Otherwise, you only get those matching the criteria you specified.

The final step is writing code that ensures that the values you enter in the Web form are passed in the Business Layer. This is done in the Selecting event of the ObjectDataSource control in the code behind of the SearchContactPeople.aspx page. I’ll show you how this works in the next section.

4. Creating an Instance of the Criteria Class in the ObjectDataSource

When the GridView in SearchContactPeople.aspx needs its data, it communicates with the ObjectDataSource which in turns calls the method that is configured in its SelectMethod property. However, right before it executes that method, it fires its Selecting event that allows you to alter the data passed into the Select method. This is a perfect location to construct a new ContactPersonCriteria, fill it with the data from the Web form and then pass the object into the GetList method. The code in the Code Behind of the search page looks like this:

	protected void ObjectDataSource1_Selecting(object sender, 
            ObjectDataSourceSelectingEventArgs e)
{
  ContactPersonCriteria myContactPersonCriteria = new ContactPersonCriteria();
  myContactPersonCriteria.FirstName = txtFirstName.Text;
  myContactPersonCriteria.LastName = txtLastName.Text;
  if (!string.IsNullOrEmpty(lstType.SelectedValue))
  {
    myContactPersonCriteria.PersonType = 
        (PersonType)Enum.Parse(typeof(PersonType), lstType.SelectedValue);
  }
  e.InputParameters["contactPersonCriteria"] = myContactPersonCriteria;
}

This code constructs a brand new ContactPersonCriteria instance. It then fills its public properties with those coming from the controls in the Web forms. Since the DropDownList for the type contains strings, you need to use Enum.Parse to parse out a valid PersonType value.

Finally, the ContactPersonCriteria object is assigned to the InputParameters collection of the e argument so it’s sent to GetList in the ContactPersonManager class.

If you run this code as is, it will crash, as the ObjectDataSource isn’t aware you want to use an overload that accepts a ContactPersonCriteria as a parameter. Therefore, you need to add a Parameter to the control’s SelectParameters collection as follows:

	<asp:ObjectDataSource ID="ObjectDataSource1" SelectMethod="GetList" …
        TypeName="Spaanjaars.ContactManager.Bll.ContactPersonManager" 
        OnSelecting="ObjectDataSource1_Selecting">
  <SelectParameters>
    <asp:Parameter Name="contactPersonCriteria" Type="Object" />
  </SelectParameters>
</asp:ObjectDataSource>

Now the ObjectDataSource understands what to do with the ContactPersonCriteria instance that you assign in Code Behind. It sends it to the GetList method in the Bll namespace which in turn forwards it to the DAL. Inside the DAL, the criteria object is used to fill in parameters which are passed to the stored procedure which finally retrieves the requested records. Since the <asp:Parameter /> control doesn’t understand your custom ContactPersonCriteria class, you need to define the type of the parameter as Object. Then in the code behind, you can create and assign a new ContactPersonCriteria to this parameter which is then forwarded to the GetList method.

Filtering in the Contact Manager Application

To keep things simple and easy to find and understand, I haven’t implemented filtering in the main Default.aspx page. Instead, I created a separate page called SearchContactPeople.aspx that allows you to find contact people based on the criteria I have shown you in this article. However, it's easy to take these concepts and add them to the Default.aspx page so your users can search for contact people that match their search criteria.

Summary

This article covered a lot of ground on paging, sorting, and filtering; three important concepts that help you to create flexible and well-performing applications. Paging is an absolute necessity when dealing with large data sets. It’s simply not feasible to present your users with pages of hundreds or thousands of records at a time. Paging can be implemented at various levels, of which the business layer and the database layer are probably the best locations. Paging at the business level is very easy to implement, but you’ll typically see that database paging performs a lot better.

Just like paging, sorting can be done at various levels as well. Again, the business and database layer are the two most obvious candidates. Sorting at the database level can be tricky, or dangerous, so if you’re working with small data sets, you’re probably better off to do your sorting at the Business Layer level.

This article closed with a discussion on filtering by showing you how to create a custom Criteria class that can be used to search for data with specific characteristics. In the sample application that comes with these articles, I only applied filtering to the ContactPerson class (in the SearchContactPeople.aspx page) but you can put filtering in your own classes as well.

So far it’s likely that you are the only one using the Contact Manager Application (or whatever application you’re building on top of the design principles from this article). This means you probably haven’t spent a whole lot of time dealing with multi-user issues like concurrency and security.

In the next part, I’ll show you how to deal with concurrency. Part 6, the final part in this new series on N-Layer design deals with different ways to secure your application.

Downloads

• Full source for this application in C#
• Full source for this application in VB.NET