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
This is my first article. I hope you like it.
Henrik is the CEO and developer on a small company,called Pixcode, in Sweden.
He started to work with 3D graphics in Maya and moved to programming in Vb6, Director Lingo, Flash, C++, C# forms and last Asp.Net. It's long days in front of the computer and his wife is not always to happy about it.
He has recently developed a driving simulator at Saab automobile and is now focusing on the webb.