Code Snippet: List<T>.Sort()

Sorting a generic list of objects using a delegate

List<Filter> filters = new List<Filter>();

filters.Add(new Filter("Z", "Zebra", (int)FilterCategoryEnum.RaamsProdOption));
filters.Add(new Filter("M", "Mars", (int)FilterCategoryEnum.RaamsProdOption));
filters.Add(new Filter("B", "Bellow", (int)FilterCategoryEnum.RaamsProdOption));
filters.Add(new Filter("K", "Karate", (int)FilterCategoryEnum.RaamsProdOption));
filters.Add(new Filter("A", "Arawak", (int)FilterCategoryEnum.RaamsProdOption));

filters.Sort(delegate(Filter a, Filter b)
             { return Comparer<string>.Default.Compare(a.FilterValue, b.FilterValue); });

Code Snippet: List<T>.FindAll()

Find all products in the specified category

private List<product> FindProductssByCategory(List<product> products, ProductCategoryEnum category)
{
   return products.FindAll(delegate(Product p) { return p.CategoryId == (int)category; } );
}

Code Snippet: List<T>.Exist()

Find the given product in the list of saved product

List<Product> savedProducts = Repository.GetSavedProducts();
Product item = (Product)dataItem.DataItem;
bool found = savedProducts.Exists(delegate(Product p) { return a.ProductName == item.ProductName; });

Custom paging with the GridView and SQL Server 2005: Part I

Introduction

The GridView  is new in ASP.Net 2.0 and provides you with an easy way of displaying grids of data from various data sources. If you have lots of data to display it is very easy to turn on paging in the gridview to provide users with more manageable chunks of data and use screen real estate better. However, the default method of paging with the GridView can come with performance drawbacks when you have hundreds of records to deal with. This is because all the data is brought back to the client application and processed to provide paged data to your control. This article will show you how to offload paging to SQL Server to gain the performance benefits of it's optimized query engine. This will cause less data to be transferred over the network, less processing on the client and provide more responsive behaviour to users of your application.

What this post will accomplish

This post is made up of three parts: 

• Implementing paging using a stored procedure created for SQL Server 2005/2008
• Setting up the middle layers of your application to support paging
• Implementing paging with the GridView using the ObjectDataSource
• In this post we will implement and test a stored procedure that provides paging. I will be using the AdventureWorks database for this post which you can find here

Designing The Stored Procedure

I will be using the AdventureWorks database to create the stored procedure. For our example we want to display employees in a specific department. Instead of using temporary tables or table variables to build our result set - as is common in paging routines - we will be using a Common Table Expression (CTE) one of the new features of SQL Server 2005. CTE's provide additional performance gains over temp tables and table variables and also provides for cleaner looking code as oposed to using derived tables. We will also use the ROw_NUMBER() function, another new feature of SQL Server 2005 to generate row numbers that are used as references to remember our position in the dataset when we return to the server for the next or previous page of data. Here is the basic idea:

• Callers of your stored procedrre will specify a department number, a page number and the number of records they want returned which is generally referred to as the page size.
• In order to provide the correct resulset to your callers you will need to have your data queried in a specific order and indexe each row uniquely so that subsequent calls to your stored procedure can use the page number to correctly return the next or previous set of data
• Limit the number of records return to the caller by using the requested page size

Let's take a look at that plumbing involved with implementing our idea.

Listing 1. 

CREATE PROCEDURE [dbo].[HR_GetByDepartmentID]

      @DepartmentID int,

      @PageNum int,

      @PageSize int,

      @TotalRowsNum int output

AS

    -- SET NOCOUNT ON added to prevent extra round trip to the server

    -- to retrieve row count

    SET NOCOUNT ON;

    -- Use ROW_NUMBER function

    WITH Employee_cte As (

       SELECT

           'RowNumber' = ROW_NUMBER() OVER(ORDER BY hre.EmployeeID DESC)

           ,hre.EmployeeID

           ,pc.ContactID

           ,hrd.DepartmentID

           ,pc.FirstName

           ,pc.LastName

           ,pc.EmailAddress

           ,hrd.Name As DepartmentName

       FROM HumanResources.Employee AS hre

       INNER JOIN Person.Contact As pc

          ON hre.ContactID = pc.ContactID

       INNER JOIN HumanResources.EmployeeDepartmentHistory As hredh

          ON hre.EmployeeID = hredh.EmployeeID

       INNER JOIN HumanResources.Department As hrd

          ON hredh.DepartmentID = hrd.DepartmentID

       WHERE hredh.EndDate IS NULL AND hrd.DepartmentID = COALESCE(@DepartmentID hrd.DepartmentID)

     )

    -- Query result

    SELECT *

    FROM Employee_cte

    WHERE RowNumber BETWEEN ((@PageNum - 1) * @PageSize + 1) AND (@PageNum * @PageSize)

    ORDER BY DepartmentName, LastName ASC

    -- Returns total records count

    SELECT

       @TotalRowsNum = COUNT(*)

    FROM HumanResources.Employee AS hre

    INNER JOIN Person.Contact As pc

        ON hre.ContactID = pc.ContactID

    INNER JOIN HumanResources.EmployeeDepartmentHistory As hredh

        ON hre.EmployeeID = hredh.EmployeeID

    INNER JOIN HumanResources.Department As hrd

        ON hredh.DepartmentID = hrd.DepartmentID

    WHERE hredh.EndDate IS NULL AND hrd.DepartmentID = @DepartmentID

Stay tuned for Part II of this article where I will talk about setting up the middle layers to support paging.

Thanks,

Denzil Brown