Building a Query Web Service Call Declaratively in a Data Source

Experimenting with ways of retrieving data from the search query web service for rendering in data view web parts.

Couldn't seem to get any way of injecting search text inside a CONTAINS or FREETEXT predicate within the SQL search statement being sent to the query web service. Finally opted for a string construction technique - defining the start and the end of the SQL search statement in parameters in the datasource, and gluing these together around the search text term inside the selectcommand node. Surely their is a cleaner way than this - I must be missing something obvious here?

<DataSources>

  <SharePoint:SoapDataSource runat="server" SelectUrl="http://someserver/sites/DC/_vti_bin/search.asmx" SelectAction="http://microsoft.com/webservices/OfficeServer/QueryService/QueryEx" SelectPort="QueryServiceSoap" SelectServiceName="QueryService" AuthType="Windows" WsdlPath="http://ssc-moss:200/sites/test/_vti_bin/search.asmx?WSDL" XPath="" ID="SoapDataSource2">

  <SelectCommand>

    <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

      <soap:Body>

        <QueryEx xmlns="http://microsoft.com/webservices/OfficeServer/QueryService">

          <queryXml>{queryXmlStart}"{QueryText}*"{queryXmlEnd}< / queryXml>

/QueryEx>

      /soap:Body>

   < /soap:Envelope>

  < /SelectCommand>

  <SelectParameters>

    <WebPartPages:DataFormParameter Name="queryXmlStart" ParameterKey="queryXmlStart" PropertyName="ParameterValues" DefaultValue="&lt;QueryPacket xmlns=&quot;urn:Microsoft.Search.Query&quot;&gt;&lt;Query&gt;&lt;SupportedFormats&gt;&lt;Format&gt;urn:Microsoft.Search.Response.Document:Document&lt;/Format&gt;&lt;/SupportedFormats&gt;  &lt;Context&gt;   &lt;QueryText type=&quot;MSSQLFT&quot; language=&quot;en-us&quot;&gt;select preferredname, firstname, lastname, workemail, workphone, homephone, Title, Path, OfficeNumber, ActiveAsText, jobtitle, pictureurl, description, write, rank, size from scope() where &quot;scope&quot;= 'People' and CONTAINS(DefaultProperties,'"/>

    <WebPartPages:DataFormParameter Name="queryXmlEnd" ParameterKey="queryXmlEnd" PropertyName="ParameterValues" DefaultValue="') and firstname != '' order by preferredname asc&lt;/QueryText&gt;  &lt;/Context&gt;  &lt;Range&gt;   &lt;StartAt&gt;1&lt;/StartAt&gt;   &lt;Count&gt;1000&lt;/Count&gt;   &lt;/Range&gt;  &lt;EnableStemming&gt;true&lt;/EnableStemming&gt;   &lt;TrimDuplicates&gt;true&lt;/TrimDuplicates&gt;   &lt;IgnoreAllNoiseQuery&gt;true&lt;/IgnoreAllNoiseQuery&gt;   &lt;ImplicitAndBehavior&gt;true&lt;/ImplicitAndBehavior&gt;   &lt;IncludeRelevanceResults&gt;true&lt;/IncludeRelevanceResults&gt;   &lt;IncludeSpecialTermResults&gt;true&lt;/IncludeSpecialTermResults&gt;   &lt;IncludeHighConfidenceResults&gt;true&lt;/IncludeHighConfidenceResults&gt; &lt;/Query&gt;&lt;/QueryPacket&gt;"/>

    <WebPartPages:DataFormParameter Name="QueryText" ParameterKey="QueryText" PropertyName="ParameterValues" />

  < /SelectParameters>

  < /SharePoint:SoapDataSource> 

Note that the QueryText parameter is added as a Parameterbinding with location of "QueryString(qt)", meaning that the datasource will use any text in the querystring parameter named "qt" as the search text submitted to the query service.

The drawback of this is that if no default value is defined for the QueryText parameter then this dataview will show an error when no qt querystring value is supplied.

The benefit of this technique, however, is the ability to add quotes and wildcard search characters to the queryXML string in order to achieve the required search SQL

Using jQuery for AJAX User Profile Queries in SharePoint

Continuing on my vein of experimenting with customisation of SharePoint using jQuery, I wanted to see if I could use the AJAX methods in jQuery to call SharePoint web services. And it just so happens a suitable requirement arose - the need to use details from the profile for the current logged-in user of a web part page.

A data view web part was required to display staff located in the same office as the current logged-in user of the web part page. The office name for each staff member was being stored in the user profiles.

Of course this functionality could have been developed in a custom web part, but I am growing to like the rapidity of creating business functionality with data view web parts. Combined with jQuery, they offer a suprising amount of flexibility.

In SharePoint designer I created a data source pointing at the user profiles. Then I created a data form web part using this data source to display a table of information on each user profile. Also included in the web part was a hidden text field that contained the login name of the current user. The purpose of this hidden value was to provide the search text for the AJAX call to the profile web service, so that I could retrieve the details for the current user.

I needed the help of Fiddler and the SharePoint Search Service Tool to help craft the necessary QueryPacket XML. Once I had the working XML, I placed it in a JavaScript file referenced within the data form web part.

The jQuery went something like this:

$(document).ready(function() {

    startAjaxOfficeSearch();

});

function startAjaxOfficeSearch() {

    var userlogin = $("#CurrentUserLogin").text();

    if (userlogin != '') {

      var queryXml = 'urn:Microsoft.Search.Response.Document:Documentselect accountname, preferredname, firstname, lastname, workemail, workphone, Title, Path, pictureurl, description, write, rank, size, OfficeNumber from scope() where "scope"= \'People\' and accountname = \'' + userlogin + '\' order by preferredname asc';

      var queryXmlOptions = '110truetruetruetruetruetruetrue';

      var completeQueryXml = queryXml + queryXmlOptions;

      var regex = new RegExp(String.fromCharCode(60), 'g');

      queryXml = completeQueryXml.replace(/</G, '&lt;').replace(/>/g, '&gt;');

      var soapMessage = String.fromCharCode(60) + '?xml version="1.0" encoding="utf-8"?>' + queryXml + '';

      $.ajax({ type: "POST",

            url: "/_vti_bin/search.asmx",

            contentType: "text/xml",

            dataType: "xml",

            data: soapMessage,

            processData: false,

            beforeSend: function(req) {

              req.setRequestHeader("X-Vermeer-Content-Type", "text/xml; charset=utf-8");

              req.setRequestHeader("soapaction", http://microsoft.com/webservices/OfficeServer/QueryService/QueryEx);

            },

            error: function(XMLHttpRequest, textStatus, errorThrown) { ajaxError(XMLHttpRequest,textStatus, errorThrown); },

            success: function(xml) { ajaxFinish(xml); }

      });

    }

}

function ajaxFinish(xml) {

  $("RelevantResults", xml).each(function() {

    var selectedOfficeValue = $("OFFICENUMBER", this).text();

    //Do other stuff with this office value

  });

}   

function ajaxError(xmlObj,textStatus,errorThrown)   {

    alert("(Ajax error: "+textStatus+")");

    alert(xmlObj.statusText);

}

The login of the current user is contained in the hidden text box with ID of 'CurrentUserLogin', as created by the data form web part. The derived query seeks exact matches for that user login against the accountname property in the user profile records - you may find this needs to query against the preferredname property instead of accountname.

The ajaxfinish method gets all the XML returned from the web service call, and uses jQuery parsing to extract the office name from the returned profile.

Note that the url parameter in the ajax call really needs to be replaced by a variable that adjusts to the actual site collection root, and that the ajaxerror function is not production ready!

I've missed out a lot of detail in this post (for instance, how to create a data source in SharePoint designer that points at the user profile store), but hopefully have given a taster of another use for jQuery, and another way of approaching SharePoint customisations.

One other tip - to find out the property names to use in the query, go to the Shared Services Administration page for your farm, open the search settings page, and then open the Metadata property mappings page. That is where you will discover those strange names (such as OFFICENUMBER)!

SPGridView Requests FileIOPermission When Added to a Web Part

I have been struggling with the SPGridView causing a security exception in a custom web part. Exploring the code with reflector reveals that when the SPGridView loads it tries to register a JavaScript file from the Templates\layouts folder in the 12 hive, and this process involves a call to the MakeBrowserCacheSafeLayoutsUrl method. That method attempts to create a unique file name for the JavaScript file. This requires that the JavaScript file is opened and read, therefore needing FileIOPermissions. If the web part is not explicitly being granted FileIOPermission in CAS, then the rendering fails.

I added the following to the manifest file that configures the CAS settings for my custom web part:

<ipermission class="System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" version="1" read="C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12;$AppDir$" write="$AppDir$" append="$AppDir$" pathdiscovery="$AppDir$">

Must admit to not being happy with hard-coding the path, but it got the web part working!

If you are wondering about the $AppDir$ parameter, I quote "it refers to the initial folder the application points to, and any subfolders". Not sure what these are for SharePoint - I tried using just $AppDir$ for the Read attribute but that did not supply the necessary access in this case (hence the hard-coded path).

Displaying Google Search Results in the MOSS Search Center

I thought of a couple of possibilities to meet this requirement, but the only approach I could get to work satisfactorily in a short space of time was to embed a call to the Google AJAX Search API inside a content editor web part.

To achieve this, first you need a Google Search API Key which you can get from a sign up form on the Google Code site. The process creates a key that will work against a specific server URL; surprisingly, the correct key actually permits the search to work from an intranet site, not just from public-facing sites. The page displaying your key also includes some starter code to show how to use that key - nice work, Google!

I modified this code somewhat to restrict search results to New Zealand sites only by applying the following restriction to each Searcher object:

searcher.setSiteRestriction("co.nz");

Had hoped for a more generic way of restricting the search to a country, but that's all I have found so far.

Then the JavaScript code, including references to the search css file "gsearch.css" and to the API launcher, was pasted into a Content Editor control on the search results page in the search center. One addition was a querystring parsing method in JavaScript to read the search term from the "k" querystring parameter in the URL for the search results page, and to use that text in the SearchControl execute call.

Certainly not as flexible or simple a process as that explained by Arpan Shah in his blog post about rendering Microsoft Live search results in SharePoint, but a good starting point to fulfill the client's requirements.

Creating a new form in a list by copying an existing form (in code)

To copy an existing form in a WSS list, and to add a new content editor web part to that list, use the SPFile class (not the SPForm collection of the list):

//Copy the NewForm.aspx to NewForm_new.aspx
string formPath = "Lists/ClientTest/NewForm.aspx";
string newformPath = formPath.Replace(".aspx", "_new.aspx");
SPFile form = web.GetFile(formPath);
form.CopyTo(newformPath, false);

//Get a reference to the new form, and create a new content editor web part containing onload JavaScript
SPFile copiedForm = web.GetFile(newformPath);
SPLimitedWebPartManager wpManager = copiedForm.GetLimitedWebPartManager(PersonalizationScope.Shared);

//Create the new web part
ContentEditorWebPart wpContent = new ContentEditorWebPart();
XmlDocument xmlContent = new XmlDocument();
XmlElement el = xmlContent.CreateElement("El");
el.InnerText = "";
wpContent.Content = el;
wpManager.AddWebPart(wpContent, "Main", 0);

The JavaScript adds an onload handler to the page, allowing for behaviours to modify the content of the standard list form for example.

Using Parameters in the SelectCommand of a Data View Web Part

The SelectCommand attribute of the SPDataSource element within a SharePoint DataView web part can use parameters supplied to the web part - and these parameters can come from a varity of sources, including the querystring in the page request.

This opens the way to easily control the data displayed in the data view; for example, via an ID supplied in the querystring.

The following code snippet shows some of the DataFormWebPart elements that use a value in the querystring to select a single list item to be displayed (the parameter is named SelectedRisk). Notice how the parameter is referenced in the SelectCommand:

<DataSources>

    <SharePoint:SPDataSource runat="server" SelectCommand="<View><Query><Where><Eq><FieldRef Name="ID"/><Value Type="Counter">{SelectedRisk}</Value></Eq></Where></Query></View>" ...>

        <SelectParameters>

            <WebPartPages:DataFormParameter PropertyName="ParameterValues" ParameterKey="SelectedRisk" DefaultValue="0" Name="SelectedRisk"></WebPartPages:DataFormParameter>

        </SelectParameters>

    </SharePoint:SPDataSource>

</DataSources>

<ParameterBindings>

    <ParameterBinding Name="SelectedRisk" Location="QueryString(ID)" DefaultValue="0"/>

</ParameterBindings>

Connecting to Two Web Parts with a Different Parameter for Each

Had a solution requiring a single drop-down list in a dataview web part to filter the contents of two other data view web parts, each of which required a different field as the input parameter - i.e. the Controls web part was to be filtered by Control Refs and the Gaps web parts was to be filtered by Gap Refs. The two fields (Control Refs and Gap Refs) are multi-select lookup columns in the Risks list.

After much experimentation (and a fair bit of cursing!), I found the answer.

The dropdown dataview uses the following code for each row. The important points to note are the concatenation of the two required field values in the fields variable, and the concatenation of the two consumer GUIDs in the GenFireCOnnection call.

<option>
    <xsl:attribute name="value">
        <xsl:variable name="cursel">dvt_curselkey={<xsl:call-template name="dvt.gencurselkey"><xsl:with-param name="RowPath" select="." /></xsl:call-template>}</xsl:variable>
        <xsl:variable name="fields">@Control_x0020_Refs=<xsl:value-of select="ddwrt:ConnEncode(string(@Control_x0020_Refs))" />#@Gap_x0020_Refs0=<xsl:value-of select="ddwrt:ConnEncode(string(@Gap_x0020_Refs0))" /></xsl:variable>
        <xsl:text>javascript:</xsl:text>
        <xsl:value-of select="ddwrt:GenFireConnection( concat( 'g_1a7694bc_1a99_427e_aece_bcbfdf0ba2b9#g_c79865fa_2833_4543_aefe_0a8f466211aa*', $fields),string($cursel))"></xsl:value-of>
        <xsl:text>;</xsl:text>
    </xsl:attribute>
    <xsl:if test="$dvt_curselkey=@ID">
        <xsl:attribute name="selected">true</xsl:attribute>
    </xsl:if>
    <xsl:value-of select="@Risk_x0020_Ref" /> - <xsl:value-of select="@Title" />
</option>

Data Source Details in SharePoint Designer Fails to Show List Data

I have a list with a calculated value which is based on two other text values. Having entered a few test items in the list, the Data Source Details pane in SharePoint Designer was reporting an error with retrieving data for that list.

Further investigation revealed that one of the paths through the formula for the calculated column was returning a number rather than a string. This incorrect data type was causing the failure in the Data Source Details

Usernames in Data View Web Parts

Data View web parts by default offer the "friendly" name in the UserID parameter - if the logon username is required, find the ParameterBinding element with the name of UserID in the Data view's XSL, and change the Location attribute value from CAMLVariable to ServerVariable(LOGON_USER)

Create a Dynamic Title in a Data View Web Part Without Code

Well, sort of - this is a bit of a cheat, but the effect is satisfying in cases where users require no control over web parts on a page. All that is required is a little extra XSL. The beauty of this approach is that no web part needs to be written in C# or VB.Net, and that any data available in the data view can be used in the title.

First, set the Chrome Type for the web part to None. Then add a new row to the table in the Data View. The contents of the cell in the row will become the title.

The trick is to apply the standard Web Part title bar CSS styles to the new table. The fragment below shows the styles that need to be applied to the row and to the contents of the cell:

<tr valign="top" class="ms-WPHeader">
    <td nowrap=""><h3 class="ms-standardheader ms-WPTitle"><span>Matching Suppliers</span></h3></td>
</tr>

Displaying a Document Icon in a Data View

To display the correct document icon in a dataview, use the following XSLT source:

<a target="_self">
   <xsl:attribute name="href">
      <xsl:value-of select="@FileRef"/>
   </xsl:attribute> 
   <img border="0">
      <xsl:attribute name="src">
         /_layouts/images/<xsl:value-of select="ddwrt:MapToIcon(string(@HTML_x0020_File_x0020_Type),string(@File_x0020_Type))"/>
      </xsl:attribute> 
   </img>
</a>

The MapToIcon method derives the correct icon name from the supplier document type (but note it returns only the icon file name, not the path).

Filtering a List by a Partial Match

On a SharePoint site I have been creating, a requirement is to display a list of Suppliers filtered by the first few letters that have been entered into a search box. One method to achieve this is using a Form Web Part and a Data View Web Part.

Add a Form Web Part to the page - in my case it is called "Search By Supplier". Next, in SharePoint Designer, add a Data View Web Part (called "Suppliers" in this case) that contains the Supplier Name column. A parameter is next added to the Data View Web Part - I have named it SupplierNameStart. This parameter will be used to tie the Web Part connection to a filter.

A new filter is added next to the Data View - the filter criteria is set so that the "Supplier Name field starts with [SupplierNameStart].

The final task is to create the web part connection. The wizard steps are as follows:

1. Create a connection for the form web part. The "Search By Supplier" web part is to Provide Form Values To another part.
2. The web part needs to Connect to Another Web Part on this Page
3. Select the Suppliers web part as the target, and the target action to be Get Parameters From
4. Choose columns - The column in the "Search By Supplier" web part is the text box name (in the HTML source of the Form Web Part). This column should be associated with the input of "SupplierNameStart" to the Supplier Web Part (i.e. the parameter).

And thus the supplier list is filtered by the letters entered into the form (when the go button is clicked)

Error when a Connected Web Part is Deleted

Caused an error on a development site today by deleting two web parts without first deleting the connection between them. This action seems to have left an orphaned connection within the page source, as the default.aspx page then failed to open, instead showing the following error:

"Cannot find the connection provider web part for..."

To remove the error, it was necessary to open the default.aspx in code view in the SharePoint Designer, and to delete the WebPartPages:SPWebPartConnection element completely.