Build Custom SharePoint Grid View Web Part with Sorting, Filtering and Pagination

 
There are many situations in SharePoint development that demand a custom grid view instead of using the XsltListViewWebPart and do transform on it. These situation includes

1. External data source. The application needs to present data from external data source such as database.

2. Aggregated view of data. The data that need to be presented are not a simple view into one list but an aggregated view of data from more than one SharePoint lists.

3. A complex data presentation. If you need to present data in a complex web part such as a tabbed view of different lists. You cannot reuse XsltListViewWebPart inside your web part (There is no webpart inside webpart. Web Part can only be added to a web part zone on a page), you have to use SPGridView to rebuilt the list view.

After Googling for a few hours, I found I can achieve my goal without writing a single line of C# code. SharePoint had built all the parts for you but it is just a hidden treasure of SharePoint that Microsoft doesn't care to let you know.

The final built POC web part looks like this

As highlighted, the web part supports Sorting, Pagination and Filtering.

Here is the HTML from the ascx file of the SharePoint visual web part. Notice that there are no code behind in this solution.

<asp:SqlDataSource
    ID="SqlDataSource1"
    runat="server"
    SelectCommand="select FirstName, LastName,he.JobTitle, he.HireDate from Person.Person pp join HumanResources.Employee he on pp.BusinessEntityID = he.BusinessEntityID" 
    ConnectionString="Data Source=.;Initial Catalog=AdventureWorks2008R2;Persist Security Info=True;User ID=sql_Service;Password=***********" 
    ProviderName="System.Data.SqlClient">
</asp:SqlDataSource>
 
<SharePoint:SPGridView
    ID="GridView1"
    runat="server"
    DataSourceID="SqlDataSource1"
    AutoGenerateColumns="False"
    EnableModelValidation="True"
 
    AllowPaging="True"
    PageSize="5"
    
    AllowSorting="True"
    
    AllowFiltering="true"
    FilterDataFields="FirstName,LastName,JobTitle,HireDate"
    FilteredDataSourcePropertyName="FilterExpression"
    FilteredDataSourcePropertyFormat="{1} like '{0}'"    
    >
 
    <Columns>
        <asp:BoundField DataField="FirstName" HeaderText="FirstName"
            SortExpression="FirstName" />
        <asp:BoundField DataField="LastName" HeaderText="LastName" 
            SortExpression="LastName" />
        <asp:BoundField DataField="JobTitle" HeaderText="JobTitle" 
            SortExpression="JobTitle" />
        <asp:BoundField DataField="HireDate" HeaderText="HireDate" 
            SortExpression="HireDate" />
    </Columns>
 
</SharePoint:SPGridView>
 
<SharePoint:SPGridViewPager runat="server" GridViewId="GridView1" />

To have this solution works, there are a few things you need to pay attention to.

First, the AutoGenerateColumns must be set to "False" and you must use BoundField to explicitly declare the columns. Otherwise you will see this error in log file

System.InvalidOperationException: This derived class does not allow AutoGenerateColumns to be true. You must specify each of the columns using BoundField, TemplateField, or other compatible class.

Second, by default the pagination of the SPGridView is hidden. This is done on purpose since SharePoint uses SPGridViewPager control to show the pages. If you have next code, the SPGridView pagination will show but you don't want to do this. SPGridViewPager is a better choice to give you the OOTB user experience.

protected void Page_Load(object sender, EventArgs e)
{
    GridView1.PagerTemplate = null;
}

Third, you must turn on the filtering as shown in the code and there are not much documenation elsewhere to show you how to do it.

Update:

If you are using "DataKeyNames" in the SPGridView, make sure the field is a text field. You will have error if the field is not a text field such as  guid value.

DataKeyNames="customerGuid"

Unexpected System.InvalidCastException: Unable to cast object of type 'System.Guid' to type 'System.String'.    at Microsoft.SharePoint.WebControls.SPGridView.set_PageIndex(Int32 value)     at Microsoft.SharePoint.WebControls.SPGridViewPager.OnClickNext(EventArgs args)     at Microsoft.SharePoint.WebControls.SPGridViewPager.RaisePostBackEvent(String eventArgument)     at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)     at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) 2c6d51c4-34bf-47b9-8687-ddaf4266ef24
see: http://stackoverflow.com/questions/3386450/paging-error-using-spgridview

References:

http://www.reversealchemy.net/2009/05/24/building-a-spgridview-control-part-2-filtering/
http://www.codeproject.com/Articles/35536/Creation-of-a-SPGridview-Webpart-having-Pagination
http://ketulpatel.wordpress.com/2008/06/06/custom-paging-in-spgridview-extending-spgridviewpager/
http://books.google.com/books?id=4lKnn9ZzG1AC&printsec=frontcover#v=onepage&q&f=false
http://books.google.com/books?id=4lKnn9ZzG1AC&pg=PA701&lpg=PA701&dq=SPGridView+AllowFiltering+FilterDataFields+FilteredDataSourcePropertyName&source=bl&ots=LR4uKwlTBq&sig=erwxMAk20C4KZNjCuwIYm1pgIeo&hl=en&sa=X&ei=zc3YUN6OFK3BiwKml4CIBw&ved=0CEAQ6AEwAg#v=onepage&q=SPGridView%20AllowFiltering%20FilterDataFields%20FilteredDataSourcePropertyName&f=false