How to use the SPGridView filter together with a SPDataSource

Normally you will no problems finding examples on how to use the SPGridView together with an ObjectDataSource and use the filtering mechanism. But what about using the SPDataSource object?

This can also be done fairly simple. You do the basic things as you do using an ObjectDataSource object, however, there are two things that you will have to change.

1. The ObjectDataSource has a FitlerExpression property that set the filter for the data source. That isn’t available on the SPDataSource object. Instead we have the SelectCommand property. The format string for this property is the same as in a standard CAML Query where clause.

2. We will need to set the SPGridView DataSourceID property instead of the more normally used DataSource property.

3. We have to add the SPDataSource control to the Controls colelction of the Web Part.

So let’s look at some code samples.

First, we have our usual suspects, one SPGridView and one SPDataSource:

private SPGridView gridView;
private SPDataSource dataSource;

this.dataSource = new SPDataSource();
this.dataSource.ID = "myDataSource";

In our CreateChildControls() method ( if it’s in a Web Part), we can do much of the work. I have commented the code, so I will not say so much about it more.

this.gridView = new SPGridView();

// Get our currnet Web object
SPWeb web = SPContext.Current.Web;

// And the SPList
SPList taskList = web.Lists["Tasks"];

// Assign the SPlist to our SPDataSource object
dataSource.List = taskList;

// Add our SPDataSource object to the Controls collection so that our SPGridView can find it.
this.Controls.Add(dataSource);

// Disable auto generation of columns
this.gridView.AutoGenerateColumns = false;
this.gridView.ID = "gridView1";

// Add the Title column.
BoundField colTitle = new BoundField()
{
    DataField = "Title",
    HeaderText = "Title",
    SortExpression = "Title"
};
this.gridView.Columns.Add(colTitle);

// Add the Status column
BoundField colProgress = new BoundField()
{
    DataField = "Status",
    HeaderText = "Status",
    SortExpression = "Status"
};
this.gridView.Columns.Add(colProgress);

// Enable filtering
this.gridView.AllowFiltering = true;

// We will not filter on the Title, so start with a comma
this.gridView.FilterDataFields = ",Status";

// Set the propertyname of the data source object
this.gridView.FilteredDataSourcePropertyName = "SelectCommand";

// Set the filter format. {1} will contain the column and {0} will contain the value.
this.gridView.FilteredDataSourcePropertyFormat = "<View><Query><Where><Eq><FieldRef Name='{1}'/><Value Type='Text'>{0}</Value></Eq></Where></Query></View>";

// Set the DataSource ID to our SPDataSource.ID
this.gridView.DataSourceID = "myDataSource";

// Add the GridView to our Controls collection
this.Controls.Add(this.gridView);

// Now data bind.
this.gridView.DataBind();