Hi, I need to comeback to blogging anyhow, since it was too long break. Hence yesterday i decided to start blogging again, and thought of this topic.
When Microsoft shipped GridView control with Asp.net, it came with lot of new functionality, but it also lacking of existing functionality like 'VirtualCount' property which was available with DataGrid but not with GridView. Since because of this it is very much difficult to implement Custom Paging with Grid View.
Now what is custom paging and default paging? To answer this question i will take an example. I need to display records on a page in tabular format, with paging and sorting. Well if you go with default paging and default sorting your development work is easy. But with it there is a drawback. - It is grid view control which decides what records to display and sorted by which column. So for that it need complete set of all records. Suppose if you want to display only 25 records, and from database you have 100 records, you need to bring all 100 records and give it to grid view. Out of these 100 records grid view decides which 25 records should be displayed. So it picks only those 25 and discards the other 75. so all efforts to bring those 75 records are wasted. Imagine if you have 1000K records and need to display only 20 at a time. So in this scenario it is better to implement Custom paging, which is not that easy. In custom paging you only bring those 25 records which needs to be displayed.
I have used AdventureWorks database for sample database.
I have used AdventureWorks database for sample database.
Well you can not use just GridView alone to implement Custom Paging. You need GridView, ObjectDatasource and Stored Procedure.
Grid View- To Display, Navigate through Paging links, and perform Sorting
ObjectDatasource - To Display Pager control for Grid View, passing page index and sortfield values to stored procedure
Stored Procedure - To select only those records needs to display.
Step 1: Create a Stored Procedure with Paging and Sorting Support:
To implement paging at database level we need to pass four parameters:
@startRowIndex int, @MaxRows int, @SortField int, @RowCount int OUTPUT as shown in the below:
@StartRowIndex - as Name suggests it specifies which rowIndex you need to start. Remember it is Zero Based index.
@MaxRows - Number of Rows you need to return (which grid view will display.) Basically it will be PageSize for Grid View.
@SortField - Fields to sort (I will show later how it is being used.)
@Rowcount -Total Number of Rows. which will help object datasource to decide pages for Grid View.
As you can see it is a two step process. 1. Perform Sorting across all data. 2. Then Perform Paging. I have used Row_Number() function to perform sorting. There is one catch here - If you want to use case statement, be careful. Group same data type columns for order by. Like i did.
Step 2: Set up Grid and Object Data Source to handle Sorting and Paging:
For Paging we need to set these properties for grid:
AllowSorting = "True", AllowPaging="True"
Need to handle two events:
OnPageIndexChanging and OnSorting.
For Data Source View we need to set it up as below:
Please take a closely look at the Ods. We need to set up TypeName, SelectMethod, SelectCountMethod, MaximumRowsParameterName, StartRowIndexParameterName and SelectParameters.
Step 3: Code behind for this page:
Step 2: Set up Grid and Object Data Source to handle Sorting and Paging:
AllowSorting = "True", AllowPaging="True"
Need to handle two events:
OnPageIndexChanging and OnSorting.
For Data Source View we need to set it up as below:
1. We need four Properties: ProductCount, SortField, StartRowIndex, PageSize.
2. Bind Grid On Page Load. - When user comes to first time on the page, bind the grid with default parameters i.e - StartRowIndex = 0, Sortfield = 1.
3. Set up Select Method and Select Count Method:
GetProductDetails: will call to Data Access Layer method to pass parameters and execute the stored procedure. It will also bring back the TotalNoOfRecords and store it to ProductCount property.
GetProductCount: This will just returns value of ProductCount property. Now Object Datasource requires that signature of SelectMethod and SelectCountMethod must be same. So even though we are not using any parameter of GetProductCount function, we can not ignore them in signature.
4. Now Handle two Events for Grid View:
5. Set up your DAL Class and Method.
You can choose different approach for DAL, but keep in mind, we need to return result and TotalCount in the same method.
Now Put all these things to gather and you will have Custom Paging and Sorting. I hope you will successfully implement the Custom Paging and Sorting. If not, let me I am happy to help you out.
Keep Coding....
Comments
<asp:ObjectDataSource ID="odsIncidents" runat="server" TypeName="HSDCriticalIncident.Classes.HSDDataAccessLayer"
SelectMethod="GetIncidents" SelectCountMethod="GetIncidentCount" EnableViewState="true"
MaximumRowsParameterName="maxRows" StartRowIndexParameterName="startRowIndex"
EnablePaging="true">
<SelectParameters>
<asp:Parameter Name="startRowIndex" Direction="Input" Type="Int32" />
<asp:Parameter Name="MaxRows" Direction="Input" DefaultValue="25" Type="Int32" />
<asp:Parameter Name="sortField" Direction="Input" Type="Int32" />
<asp:Parameter Name="rowCount" Direction="Output" Type="Int32" />
<asp:Parameter Name="spName" Direction="Input" Type="String" />
</SelectParameters>
</asp:ObjectDataSource>
Here are my "GetIncidents" and "GetIncidentCount" methods (declared in the code-behind for the .aspx page):
public DataTable GetIncidents(int startRowIndex, int maxRows, int sortField, ref int rowCount, string spName)
{
HSDDataAccessLayer dal = new HSDDataAccessLayer();
DataTable dt = dal.GetIncidents(startRowIndex, maxRows, sortField, ref rowCount, spName);
IncidentCount = rowCount;
return dt;
} //end method GetIncidents
public int GetIncidentCount(int startRowIndex, int maxRows, int sortField, ref int rowCount, string spName)
{
return IncidentCount;
} //end method GetIncidentCount
My DataAccessLayer class is almost identical to yours, except I'm passing it the name of the stored procedure so that I can use the class on multiple pages.
The error I'm getting is "ObjectDataSource 'odsIncidents' could not find a non-generic method 'GetIncidents' that has parameters: startRowIndex, maxRows, sortField, rowCount, spName", the error is occuring when I attempt to DataBind the GridView on Page_Load.
If you need to see more of my code in order to diagnose the problem, or if you can spot the problem right away please email me at timbck2 @ gmail.com. Thanks so much!
Now I have another question -- is it possible to pass an optional parameter to a stored procedure using this ObjectDataSource method?