Show large set of data with sorting and filtering in a Sharepoint web part

I'd like to create a Sharepoint web part to show a large set of data from an ODBC source (in first instance a SQL Server 2008 database) with the ability to sort and filter data, possibly with progressive fetching.

What is your advice on technology to be used?

• Report Viewer Web Part: I think it's kinda cool but will not enable progressive fetching of data
• Custom Web part with a custom GridView control embedded
• Custom Web part with a vendor grid开发者_开发问答 control embedded (Telerik, DevExpress, etc)
• Vendor Sharepoint grid control (exists?)

Any other option available?

Thanks in advance for your thoughts

Mauro

Take SharePoint out of the equation. If this was a regular ASP.NET application, how would you do it? From what you have described, your custom web part is just a custom ASP.NET custom control that can be placed on the page at run time. It sounds like SharePoint is just a wrapper for your web part with no interaction necessary with SharePoint data or the API.

If in an ASP.NET application you would use SSRS for this, then use the Report Viewer Web Part. Otherwise you can use a GridView, Repeater, ListView, third party control, etc for display with Entity Framework, ADO.NET, LINQ to SQL, etc for data access. There are pros and cons with each and there is no one size fits all answer. Use whatever you are most comfortable with that makes the most sense for exactly what you are trying to do.

Does the standard list view not do what you require? It sorts and filters, and has progressive fetching. I would try to get users to use that interface before duplicating it.

I have done a custom SPGridView. It behaves sort of like the standard list views. But it is a lot of effort to get it working correctly, especially with sorting and paging. The paging sort of ended up like this. SPListItemCollectionPosition is the key. In the end though, when it got upgraded to sp2010, i made the stakeholders change their requirements, so we could use the standard list views.

public class SPPagedGridView : SPGridView {

    protected override void CreateChildControls() {
        base.CreateChildControls();     
        AllowPaging = true;
        PageSize = PAGE_SIZE;
        PageIndexChanging += new GridViewPageEventHandler(oGrid_PageIndexChanging);
    }

    protected override void InitializePager(GridViewRow row, int columnSpan, PagedDataSource pagedDataSource) {
        pagedDataSource.AllowCustomPaging = true;
        base.InitializePager(row, columnSpan, pagedDataSource);
    }

    void oGrid_PageIndexChanging(object sender, GridViewPageEventArgs e) {
        PageIndex = e.NewPageIndex;
        DataBind();
    }

    public override DataBind() {
        SPQuery q = new SPQuery();
        q.RowLimit = PAGE_SIZE;
        SPListItemCollectionPosition pos = new SPListItemCollectionPosition(value);
        q.ListItemCollectionPosition = pos;
        //SPList list = something;
        //list.GetListItems()
    }
}

I recently used the Telerik SPRadGrid web part (for SP 2010) which enables completely declarative binding to SP2010 SQL backend. You may give it a spin on their demo site here: http://sharepoint.telerik.com/aspnet-ajax/Pages/default.aspx. It also supports SP lists binding from design-time and I heard that they have Excel binding in the works and it will be released with their next major installment.