Connect Text Filter to List View with Wildcard Support

In a recent project, we were trying to add search/filter funcationality to a page to support search/filter a list view (XsltListViewWebPart). This is different from the SharePoint "Search", which will search either the entire site or a list and return the results in a search result page.

What we need is a search or filter that will search certain text in a list and make the list to show only items that match. After the search, user should be able to select the items in the list and trigger some actions on them.

The easiest way to achieve this is to use SharePoint Text Filter web part and connect it to a column in the XsltListViewWebPart. However this filter can only do exact match which make it not very useful as a search tool.

To have a Text Filter that can do wild card match or partial string match manually through SharePoint Designer, you can reference this link

http://coolsharepoint.blogspot.com/2012/03/sharepoint-2010-configure-textfilter-to.html#!/2012/03/sharepoint-2010-configure-textfilter-to.html

Another limit of Text Filter is that it can only connect to one column in the list, to do a search on multiple columns you can reference this article

http://gustavogarciadotnet.blogspot.com/2011/01/sharepoint-list-filter-wildcard-search.html

What I am going to show here is how to do all these programmatically. I didn't find anyone else have done this before. I did all these by reverse engineering what the SharePoint Designer does.

public static void SetTextFilterAndXsltViewWebPartConnection(SPWeb web, string pageUrl, string filterWebPartName, string gridWebPartName)
{
    SPFile file = web.GetFile(pageUrl);
    if (file == null && !file.Exists)
        return;
 
    if (file.CheckOutType != SPFile.SPCheckOutType.None)
        file.CheckIn("");
 
    file.CheckOut();
 
    try
    {
        SPLimitedWebPartManager webPartManager = file.GetLimitedWebPartManager(PersonalizationScope.Shared);
        if (webPartManager == null)
            return;
 
 
        System.Web.UI.WebControls.WebParts.WebPart filterwp = null;
        XsltListViewWebPart xsltwp = null;
 
        foreach (System.Web.UI.WebControls.WebParts.WebPart wp in webPartManager.WebParts)
        {
            if (!string.IsNullOrEmpty(wp.Title) && wp.Title.ToLower() == filterWebPartName.ToLower())
            {
                filterwp = wp;
                continue;
            }
 
            if (!string.IsNullOrEmpty(wp.Title) && wp.Title.ToLower() == gridWebPartName.ToLower())
            {
                xsltwp = wp as XsltListViewWebPart;
                continue;
            }
        }
 
        SPWebPartConnection connection = new SPWebPartConnection();
        connection.ID = "custodianFilterConnection";
 
        connection.ConsumerConnectionPointID = "DFWP Parameter Consumer ID";
        connection.ConsumerID = xsltwp.ID;
 
        connection.ProviderConnectionPointID = "ITransformableFilterValues";
        connection.ProviderID = filterwp.ID;
 
        TransformableFilterValuesToParametersTransformer searchTransformer = new TransformableFilterValuesToParametersTransformer();
        searchTransformer.ConsumerFieldNames = new string[] { "SearchQuery" };
        searchTransformer.ProviderFieldNames = new string[] { "Search" };
 
        connection.SPTransformers.Add(searchTransformer);
 
        webPartManager.SPWebPartConnections.Add(connection);
 
        webPartManager.SaveChanges(xsltwp);
        webPartManager.SaveChanges(filterwp);
 
    }
    catch (Exception expt)
    {
        Console.WriteLine(expt.Message + expt.StackTrace);
    }
}

To have this solution work, there is another hurdle to overcome. You need to create the a view in the XsltListViewWebPart as described in my previous bloghttp://ethandeng.blogspot.com/2012/12/define-list-view-for.html
 

public static void SetXsltListViewCustomterView(SPWeb web, SPView view)
{
    //Create view
    //http://sarangasl.blogspot.com/2009/12/create-sharepoint-list-view.html
    string viewQuery = "<OrderBy><FieldRef Name=\"fullname\" /></OrderBy><Where><Contains><FieldRef Name=\"SearchableText\"/><Value Type=\"Text\">{SearchQuery}</Value></Contains></And></Where>";
            
    view.Query = viewQuery;
    view.ViewFields.DeleteAll();
    view.ViewFields.Add("fullname");
    view.ViewFields.Add("company");
    view.ViewFields.Add("status");
    view.ViewFields.Add("noticeTitle");
 
    view.Paged = true;
    view.RowLimit = 3;
    view.DefaultView = true;
 
    view.Update();
}

Reference:
http://stackoverflow.com/questions/1048736/web-part-connections-in-site-definitions

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

Define the List View for XsltListViewWebPart

When you add XsltListViewWebPart to your page, you will have needs to define the proper list view for the XsltListViewWebPart.

There are two ways you can define the list view for a XsltListViewWebPart programmatically.

First, you can reference to an existing list view of the list from the XsltListViewWebPart. Second, you can use a reflection to change the SPView object currently associated with the XsltListViewWebPart. This is more or less a hack since SharePoint doesn't expose the list view object as public property.

The first step is to get the XsltListViewWebPart from the page using SPLimitedWebPartManager

SPLimitedWebPartManager webPartManager = file.GetLimitedWebPartManager(PersonalizationScope.Shared);
if (webPartManager == null)
  return;
 
foreach (System.Web.UI.WebControls.WebParts.WebPart wp in webPartManager.WebParts)
{
    // Only handle xslt web part
    XsltListViewWebPart xsltwp = wp as XsltListViewWebPart;
    if (xsltwp == null)
        continue;

The second step is to get the list and list view of the list.

 // Get list by listUrl
 // catch invalid list url exception
 try
 {
   list = web.GetList(webServerRelativeUrl + "/" + listUrl);
   view = list.Views[viewName];
 }
 catch (Exception expt)
 {
   EventLogger.LogError("SetXsltWebPartView:" + expt.Message + expt.StackTrace);
 }

The third step is to set and save the list view of the XsltListViewWebPart

// Must set ViewId and ViewGuid at the same time
xsltwp.ViewId = Int32.Parse(view.BaseViewID);
xsltwp.ViewGuid = view.ID.ToString("B");
 
webPartManager.SaveChanges(xsltwp);

This approach may not work for some XsltListViewWebPart, for example I found if the list is External List, this won't work. Another approach is to use reflection to get the "ContextView" property as SPView object and change the SPView object.

PropertyInfo pi = xsltwp.GetType().GetProperty("ContextView", BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance);
SPView view = (SPView)(pi.GetValue(xsltwp, null));
SetXsltListViewCustodianView(web, view);        
webPartManager.SaveChanges(xsltwp);

public static void SetXsltListViewCustomerView(SPWeb web, SPView view)
{
    //Create view
    //http://sarangasl.blogspot.com/2009/12/create-sharepoint-list-view.html
    string viewQuery = "<OrderBy><FieldRef Name=\"FirstName\" /></OrderBy>";
            
    view.Query = viewQuery;
    view.ViewFields.DeleteAll();
    view.ViewFields.Add("FirstName");
    view.ViewFields.Add("LastName");
    view.ViewFields.Add("JobTitle");
    view.ViewFields.Add("Company");
 
    view.Paged = true;
    view.RowLimit = 3;
    view.DefaultView = true;
 
    view.Update();
}

Happy SharePointing!

Program SharePoint Mulitple Item Selection in List View

Reference:
http://tomaszrabinski.pl/wordpress/2012/02/25/get-selected-list-items-sharepoint-2010/
http://programmers.stackexchange.com/questions/122357/how-should-data-be-passed-between-client-side-javascript-and-c-code-behind-an-a

This solution involes using SharePoint Client Object Model and jQuery ...

I recently worked on a project that required triggering server side code based on what user selects in a list view. A typical senario is a user select multiple customers from a contact list and send a emails to them with one click.

Just as OOTB of SharePoint, you can select more than one items in a list and click the "Delete Item" button in the ribbon to delete them all.

I need to implement this in a web part instead of add custom action to the ribbon since the portal site we developed won't show ribbon.

To implement this, I have both the XsltListViewWebPart and the custom "Actions" web part provisioned in a web part page.

The custom "Actions" web part is implemented as SharePoint visual web part and in the ascx file, it has

<script type="text/javascript">
 
     function CustomActionOnSelectedItem() {
        // get current client context
        var context = SP.ClientContext.get_current();
        var selectedItems = SP.ListOperation.Selection.getSelectedItems(context);
        var selectedList = SP.ListOperation.Selection.getSelectedList(context);
 
        var itemIds = "";
        for (i in selectedItems) {
            itemIds += selectedItems[i].id + ";";
        }
 
        var hiddenFieldId = '<%= HiddenField1.ClientID %>';
        $('#' + hiddenFieldId).val(selectedList + ":" + itemIds);
    }
 
 
 
</script>
 
<asp:HiddenField ID="HiddenField1" runat="server"/>
<asp:Button ID="btOK" runat="server" Text="OK" OnClientClick="CustomActionOnSelectedItem()" onclick="btOK_Click" Width="133px" />

The JavaScript use sSharePoint Client Object Model to get the list ID and the selected item IDs and pass the information to server side through ASP.NET HiddenField control.

One trick to notice is to get the ASP.NET control client side  ID by using inline .NET code

var hiddenFieldId = '<%= HiddenField1.ClientID %>';

If you are not going to have the JavaScript directly in the ascx file and loading it from a seperate js file, then this trick will not work.

If this is the case, you can do the jQuery selection based on class name as

$('.MyHiddenField').val(itemIds);

However if you use this technique, you cannot use the ASP.NET HiddenField control since the it doesn't support "CssClass" attribute.

You can use the HTML input control instead but add runat="Server" to make sure the server side code can get its value.

<input ID="HiddenField1"  type="hidden" runat="server" class="MyHiddenField"/>

The server side will be able to get the item ID as next

HashSet<Guid> itemGuidList = Utility.GetItemGuidList(HiddenField1.Value);

The "GetItemGuidList" is a custom utility method that will parse the item IDs from the string.

        public static HashSet<Guid> GetItemGuidList(string value)
        {
            HashSet<Guid> guidList = new HashSet<Guid>();
 
            if (string.IsNullOrEmpty(value))
                return guidList;
 
            string[] guids = value.Split(';');
            foreach (string idstr in guids)
            {
                try
                {
                    if (idstr == null || idstr.Trim().Length == 0)
                        continue;
                     
                    Guid guid = new Guid(idstr);
                    guidList.Add(guid);
                }
                catch (Exception expt)
                {
                    EventLogger.LogError("Invalid guid : " + idstr + " " + expt.Message);
                }
            }
 
            return guidList;
        }



Happy SharePointing!

Install SharePoint 2010 on Windows 7 - The good old way

It is time to rebuild my dev machine again after our network moved to a new domain. What's a pain? I have to refresh my memory about all the work-arounds I had done to install SharePoint 2010 on Windows 7.

What makes it even worse for now is that SharePoint 2013 is out and all the good old links about SharePoint 2010 had disappeared from Google search results.

When searching topic of "Install SharePoint 2010 on Windows 7" on Google, the top results are about SharePoint 2013 and a few "smart" automated script that automate the installation such as "Office Script to Install SharePoint 2010 on Windows 7". After giving it a try, I found it automates the installation of SharePoint 2010 as Standalone configuration. I don't like it!

I prefer to use SQL Server 2008 R2 and install SharePoint 2010 as "complete" "Server Farm" solution using service account just as installing it in production environment.

Here is the good old instructions from Microsoft about how to do it manually and reliably.

Setting Up the Development Environment for SharePoint 2010 on Windows Vista, Windows 7, and Windows Server 2008

http://msdn.microsoft.com/en-us/library/ee554869(office.14).aspx