How to Search custom columns for a content type and display in SPGridView

Question

I have a custom task content type based on the task content type which has a custom priority column, custom assigned to column etc. There are many instances of the task list in multiple site collections.

My requirement is to present a view (custom web part) to a logged in user of all the tasks which are assigned to them. This view needs to look like the actual task list view but filtered for that user.

I have been advised to use search to solve the problem, but my search knowledge is limited. Currently, I have been looking at the FullTextSQLQuery but I'm unsure on how to get all the columns in the content type. I want to get the results as a datatable with all the columns from the task list and bind them to a SPGridView.

I want to say for example: query = "SELECT Priority, taskUrl, Status, workflowUrl, ItemId, ListUrl from Scopes() WHERE scope = 'My Custom Tasks Scope' and assignedUser = " + SPContext.Current.User.LoginName;

Any ideas on how I do this?

Answer 1

This is a pretty big question so I'll do my best to outline the steps you'll need to take in order to get this done.

First, you'll want to make sure all of your columns are crawled. If you're using out of the box Tasks lists then some of them may already be in there. If not, it will take some monkeying around in Central Admin (full disclosure: link to my blog). I'd recommend setting up a small test console app to query the index so you can see which results come back.. it is worth the effort because you'll need it later.

Second, setup a very basic SPGridView with some dummy data. Erik Burger has an excellent series about building an SPGridView. I'm not kidding about the dummy data either. SPGridView's are a tough beast if you haven't messed with them before.. plus you'll have some time while full crawls are running in your DEV environment.

Third and lastly, combine the various queries and FullTextSQLQuery code you've written with your basic SPGridView. Hopefully by now, you have some code that returns a DataTable which will be easy to hook up to the ObjectDataSource in your SPGridView.