One of the problems users experience using SharePoint WSS 3.0 or even SharePoint Office Server 2007 out of the box is the lack of support to perform filter queries on items in a list.   This article shows you how you can use Visual Studio to create a custom webparts which can perform Filter Queries on SharePoint lists.   This functionality with Webparts is supported in either SharePoint 2007 or WSS 3.0.   The other advantage with writing your own filtered webpart is you have the ability to optimise your query to perform these search queries efficiently and reduce any bottleneck in your SharePoint application.  Today we are going to be using the SharePoint API to perform queries across lists and being able to return this data into a view.

Pre-requisites

Before you start it is important that you have a copy of either Visual Studio 2005 or 2008 with the following extension Visual Studio extensions for SharePoint version 1.2.  Once you have downloaded this and installed this extension you are ready to commence this tutorial and create the following webpart.

Start Developing Custom Webpart with Visual Studio

Now we can start to develop this webpart using Visual Studio, so this is a quick guide to creating a webpart and deploying this onto your web-server.

Step 1: Open Visual Studio

Step 2: Create a new Project -> Visual C# -> SharePoint -> Web Part

Step 3: Name this Web Part whatever you desire

Step 4: Select FULL TRUST (Deploy to GAC) on the dialog box “Select Trust Level”

Step 5: Now open up Webpart1.cs file and inside you should see a section of code resembling the following:

using System;

using System.Runtime.InteropServices;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Serialization;

 

using Microsoft.SharePoint;

using Microsoft.SharePoint.WebControls;

using Microsoft.SharePoint.WebPartPages;

 

namespace TestWebpart

{

[Guid("876ffbbe-6196-47d4-b77a-12b69c07be45")]

public class WebPart1 : System.Web.UI.WebControls.WebParts.WebPart

{

public WebPart1()

{

}

 

protected override void CreateChildControls()

{

base.CreateChildControls();

 

// TODO: add custom rendering code here.

// Label label = new Label();

// label.Text = “Hello World”;

// this.Controls.Add(label);

}

}

}

Step 6: We are going to use the following object inside our code “ListViewByQuery”.  The will provide the output for our query as this is a SharePoint component which will render a list view which will resemble what we see inside SharePoint.

Now add this “ListViewByQuery” into the following section of code:

protected override void CreateChildControls()

{

base.CreateChildControls();

ListViewByQuery view = new ListViewByQuery();

this.Controls.Add(view);

}

Step 7: Now we need to include the query which is what performs the lookup of your SharePoint list to retrieve the relevant data.  The following line of code is required to perform this:

query = new SPQuery(list.DefaultView);

query.ViewFields = “<FieldRef Name=’Created’/><FieldRef Name=’LinkTitleNoMenu’/><FieldRef Name=’FirstName’/><FieldRef Name=’Current_x0020_Company’/><FieldRef Name=’Community_x0020_Group’/><FieldRef Name=’Skill_x0020_1′/><FieldRef Name=’Skill_x0020_2′/><FieldRef Name=’Skill_x0020_3′/>”;

query.Query = “<Where></Where><Orderby><FieldRef Name=’Created’ Ascending=’false’ /></OrderBy>”;

query.RecurrenceOrderBy = true;

The “ViewFields” is used to assign the columns to be displayed inside the list view on your rendered SharePoint webpart.  It is a requirement that you supply some values here otherwise you will find that your webpart will not execute inside your SharePoint site.

A typical value you might provide for your “ViewFields” would be:

Query.ViewFields = “<FieldRefName=’Created’/><FiedRefName=’LinkTitleNoMenu’/>”

This would display a list consisting of two columns which will display a hyperlink title to display the List details and Created will display the date when the list item was created.

Now the Query string provides you with the flexibility to define your own query on what list items you want retrieved.   Collaborative Application Markup Language (CAML) is an XML based query language which is used here to help you build custom SQL like queries to run on SharePoint Lists. There is a utility written which can assist you with writing a query.  I suggest you download the following utility CAML Query Builder for SharePoint 2003 and SharePoint 2007.

Here is a sample CAML query for searching data on a Customer List:

<WHERE>

<CONTAINS>

<FIELDREF NAM=’Title’ /><VALUE TYPE=’TEXT’>John</VALUE>

</CONTAINS>

</WHERE>

Step 8: Putting it all together

Now is the time to finally put this off these components inside the webpart together so it can perform the filtered search correctly.  Below is the required code for you to build your Custom Filtered Webpart to be compiled in Visual Studio:

public class WebPart1 : System.Web.UI.WebControls.WebParts.WebPart

{

public WebPart1()

{

}

protected override void CreateChildControls()

{

base.CreateChildControls();

ListViewByQuery view = new ListViewByQuery();

SPWeb currentweb = SPContext.Current.Web;

SPList list = currentweb.Lists["Customers"];

view.List = list;

SPQuery query = new SPQuery(list.DefaultView);

query.ViewFields = “<FieldRef Name=’Created’/><FieldRef Name=’LinkTitleNoMenu’/>”;

query.Query = “<Where><CONTAINS>

<FIELDREF NAM=’Title’ /><VALUE TYPE=’TEXT’>John</VALUE>

</CONTAINS>

</Where><Orderby><FieldRef Name=’Created’ Ascending=’false’ /></OrderBy>”;

query.RecurrenceOrderBy = true;

view.Query = query;

this.Controls.Add(view);

}

}

So this is a quick reference of how you can build a Filtered Web Part in SharePoint which used a ListViewByQuery component to render the searched contents into a SharePoint list view.   The SPQuery class is used to perform a CAML XML based query to extract the specific List Items from the desired SharePoint List you have selected.

This custom webpart can be included in either Windows SharePoint Services 3.0 (WSS 3.0) or SharePoint Office Server 2007.

 At Click IT Solutions we specialise in developing custom Web Parts for SharePoint.  If you would like to customise your SharePoint portal contact us to speak with a consultant who can provide advice regarding this.  You can contact us at (02) 9029 5194 or alternatively email us at info@clickitsolutions.com.au.