Connect Gridview to CustomBusinessObject with Sorting, Paging and Filter Functionality

• Download source - 346.36 KB

Introduction

If you have tried to work with a Gridview and control the paging and sorting with different codebehind events, you know it’s not easy. In this article I show how to connect the Gridview to a MsSql server and make the most out of the built in functionality of the Gridview with paging and sorting.

The heart of the article is the ObjectDataSource and the CustomBusinessObject. We look at how different setting in the ObjectDataSource will call the same SelectMethod function but with different parameters. This makes it possible to create sql commands with sorting, paging and other functionality, like filter or search. In the project files I have five .aspx that calls the same CustomBusinessObject. They all call a function named getList() and the Gridview will pass different parameters based on the settings in the ObjectDataSource.

Background

If you never worked with ObjectDataSource and a CustomBusinessObject I recommend you read some startup articles.

ObjectDataSource Web Server Control Overview
http://msdn.microsoft.com/en-us/library/9a4kyhcx.aspx

Walkthrough: Data Binding to a Custom Business Object
http://msdn.microsoft.com/en-us/library/1se6685s.aspx

I highly recommend this book that get me started on this article.
ASP.NET 2.0 Website Programming: Problem - Design – Solution
http://www.amazon.com/ASP-NET-2-0-Website-Programming-Programmer/dp/0764584642/ref=sr_1_3?ie=UTF8&s=books&qid=1250623892&sr=1-3

I also found the following article in the same area on codeproject.
http://www.codeproject.com/KB/webforms/CustomBusinessObjects.aspx

Using the Code

The project is based on a SqlErrorLog that I use in my projects and the CustomBusinessObject (pxLogMessage) is built to work with this specific sqltable.

There is five .aspx files that is numbered from one to five. For every number I added some functionality. They all have a Gridview and one ObjectDataSource. The ObjectDataSource is connected to a CustomBusinessObject that is calling the SQL Server and returning a list of objects. have striped the code as much as possible to demonstrate the concept. I removed the code for Data application layer, data caching, exception handling and input validation.

VS2008 and SQL2005 is used.

Settings in the ObjectDataScorce

Here is a short description of the different settings that is important to know for this article.

1. SelectMetod.

The name of the function in CustomBusinessObject that return data to the Gridview. This is the main function for this article. We override this function with different parameters based on our settings in ObjectDataSource.

2. SelectParameters.

If you want to add some extra parameters that will be sent to the CustomBusinessObject, you add them here. This is used in last .aspx file for filter functionality.

3. SortParameterName.

Name of the sort parameter in the SelectMetod. You must also enable AllowSorting in the Gridview to make this work.

4. EnablePaging.

When EnablePaging is active the ObjectDataSource will always look for a SelectMetod with the maximumRowsParameterName and startRowIndexParameterName. In the CustomBusinessObject I use the default names maximumRows and startRowIndex.

You must also enable Gridviews AllowPaging to make this work.

Paging also needs one extra function from the CustomBusinessObject. The SelectCountMethod.

5. SelectCountMethod.

To make Paging work, the Gridview needs to know the total number of objects to show. When Paging is enabled the CustomBusinessObject must have a SelectCountMethod that returns the total affected row number.

If you only use sorting and paging the SelectCountMethod always returns the same number. If you add extra SelectParameters, you need a SelectCountMethod that handles the extra parameters. That’s because the extra parameters can change the returning row number from SQL.

The effect of settings in the ObjectDataSource.

The first .aspx file only has the SelectMethod active. (No sorting or paging.) The ObjectDataSource calls getList() to return a complete list of all rows in the SQL table.

public static List<pxLogMessage><pxLogMessage> getList() {...}

The second .aspx file has the SelectMethod and SortParameterName.. The ObjectDataSource calls this function that return a complete list of all rows in the SQL table with a sorting string.

public static List<pxLogMessage> getList(string sortType) {...}

The Third .aspx has SelectMethod and Paging. The ObjectDataSource now calls two functions.

public static List<pxLogMessage>xLogMessage> getList(int startRowIndex,
   int maximumRows)   {...}

public static int getCount() {...}

The Forth .aspx has SelectMethod, Paging and SortParameterName. The ObjectDataSource now calls two functions. (Note the extra parameter for sorting.)

public static ListxLogMessage> getList(string sortType, int startRowIndex,
    int maximumRows) {...}

public static int getCount() {...}

The Fifth .aspx has SelectMethod, Paging, SortParameterName and two new SelectParameters. The ObjectDataSource calls two functions.  (Note the extra parameter from SelectParameters in getCount() )

public static <pxLogMessage>List<pxLogMessage> getList(string filterColumn,
    string filterStr, string sortType, int startRowIndex, int maximumRows) {...}  
          

public static int getCount(string filterColumn, string filterStr) {...}

Points of Interest

• I like this way of using the Gridview and calling the SQL server. The .aspx codebehind is clean from Gridview events and it’s easy to reuse the CustomBusinessObject with different Gridview functionality. You can also add cashing and error checking in the CustomBusinessObject.
• If you only map a Sql table to a CustomBusinessObject, the code is pretty much the same. The CRUD functions can be automatically created with a code generator. I have a Codesmith template I run on all my tables to generate the same code as in this article.
• The negative side is the amount of code you need to write to cover all the different settings in the ObjectDataSource. You easily end up with ten different getList() methods and several getCount() methods.

History

• 2009-08-18 Publish article.

This is my first article. I hope you like it.