Displaying listname and sitename when querying data with CQWP

A colleague of mine who works with our MOSS Intranet asked me if I could help him tweak the Content Query Webpart to aggregate all announcements from all sites in our Intranet.

He had already started to tune the content, following Heather Solomons excellent article:
http://www.heathersolomon.com/blog/articles/customitemstyle.aspx and an Microsoft article: http://blogs.msdn.com/ecm/archive/2006/10/25/configuring-and-customizing-the-content-query-web-part.aspx

However, there seemed to be no way of getting the listname and sitename where the item was coming from to the XSL transformation. After some digging deep with the reflector and some testing with the SPSiteDataQuery I found a solution that seems to do the trick.

First (as described in step 3 in the Microsoft article) you need to export the .webpart file from the CQWP-webpart that you have configured.

In the .webpart file there is a property called ViewFieldsOverride. This property overrides the fields that are loaded by the CQWP. This gives us an opportunity to add the Listname when querying the data.

Since we are overriding it, we first need to add the default fields (Title, Created etc) manually. After that, we add the listname and sitename using the <ListProperty Name="Title" />and <ProjectProperty Name="Title" />

<property name="ViewFieldsOverride" type="string"><![CDATA[<FieldRef ID="{fa564e0f-0c70-4ab9-b863-0177e6ddd247}" Nullable="True" Type="Text" /><FieldRef ID="{94f89715-e097-4e8b-ba79-ea02aa8b7adb}" Nullable="True" Type="Lookup" /><FieldRef ID="{1d22ea11-1e32-424e-89ab-9fedbadb6ce1}" Nullable="True" Type="Counter" /><FieldRef ID="{28cf69c5-fa48-462a-b5cd-27b6f9d2bd5f}" Nullable="True" Type="DateTime" /><FieldRef ID="{1df5e554-ec7e-46a6-901d-d85a3881cb18}" Nullable="True" Type="User" /><FieldRef ID="{d31655d1-1d5b-4511-95a1-7a09e9b75bf2}" Nullable="True" Type="User" /><FieldRef ID="{8c06beca-0777-48f7-91c7-6da68bc07b69}" Nullable="True" Type="DateTime" /><FieldRef Name="PublishingRollupImage" Nullable="True" Type="Image" /><FieldRef Name="_Level" Nullable="True" Type="Number" /><FieldRef Name="Comments" Nullable="True" Type="Note" /><ListProperty Name="Title" /><ProjectProperty Name="Title" />]]></property>

(Note: I found the GUID:s of the default fields using the reflector)

After importing the webpart file, the listname and sitename are now available in the xsl-file as ListProperty.Title and ProjectProperty.Title

Insert the following xsl code in ItemStyle.xsl (for more details, see the Heather Solomon or Microsoft guides above):

<xsl:value-of select="@ListProperty.Title" />
<xsl:value-of select="@ProjectProperty.Title" />

There is still one problem I'm trying to solve. The Author is for some reason presented as ID#Name (where ID is a number, specifying the user) when using it in the XSL-file. This can be fixed with the variable below, but I would rather find why the override causes Author to be changed.

<xsl:variable name="Author">
 <xsl:choose>
  <xsl:when test="contains(@Author, '#')">
   <xsl:value-of select="substring-after(@Author, '#')" />
  </xsl:when>
  <xsl:otherwise>
   <xsl:value-of select="@Author" />
  </xsl:otherwise>
 </xsl:choose>
</xsl:variable>

Use it with the following xsl code:
<xsl:value-of select="$Author" />



Update: To enable Audience targeting, we first need to add another field into ViewFieldsOverride. Otherwise, the settings in the webpart won't work:
<FieldRef ID="{61cbb965-1e04-4273-b658-eedaa662f48d}" Nullable="True" Type="TargetTo" />

Find localized name of the Everyone group

Today a colleague asked me if there is any way to find out what the Everyone group is called in a localized version of Windows. In a Swedish Windows, this group is kalled "Alla" and in a German version it's called "Jeder" etc.

There is an easy way to do this with .NET with the (new) .NET 2.0 Security functions.

SecurityIdentifier si = new SecurityIdentifier(WellKnownSidType.WorldSid, null);
NTAccount everyOneGroup = si.Translate(typeof(NTAccount)) as NTAccount;
if (everyOneGroup != null) {
      Console.WriteLine(everyOneGroup.Value); // now contains the localized name
}

Relative URL in Sharepoint is incorrectly resolved

Today I had a problem with the TreeView WebControl. I wanted to set the NavigateUrl to a relative url in Sharepoint in one of my webparts (I allow user configuration of this property so this must be resolved correctly).

When I set the NavigateUrl to "test.aspx" it's resolved to: "_catalogs/masterpage/test.aspx" which probably has something to do with the fact that the webpart manager containing my webparts is declared in the masterpage.

The solution was simple: Use Page.ResolveUrl before setting the url. This correctly resolves the relative url before it's set to the TreeView:

string relativeUrl = "test.aspx";
treeNode.NavigateUrl = Page.ResolveUrl(relativeUrl);

CSS reference map!

There are a lot of css classes defined in Sharepoint and it's not always easy to know what they look like. There is however a great site that lists all sharepoint css classes with examples:

http://www.heathersolomon.com/content/sp07cssreference.htm

Custom TreeView with a Select (onclick) event

One thing that disturbs me a bit is that the TreeView control does not have an event that is triggered when the user clicks an already selected item (in my case, I need to know about this so that I can refresh data. The SelectedNodeChanged is never fired when you click on an already selected item).

So wondered if it is possible to create my custom implementation of the TreeView and it turned out to be quite easy to do after a quick check in the reflector

using System;
using System.Collections.Generic;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace GB.ControlLib { 
public class GTreeView : System.Web.UI.WebControls.TreeView
{
 private static readonly object TreeViewSelected = new object();
 public event EventHandler Select
 {
  add { base.Events.AddHandler(TreeViewSelected, value); }
  remove { base.Events.RemoveHandler(TreeViewSelected, value); }
 }
 protected virtual void OnSelect(EventArgs e)
 {
  EventHandler handler = (EventHandler)base.Events[TreeViewSelected];
  if (handler != null) {
    handler(this, e);
  }
 }
 internal void RaiseOnSelect()
 {
  this.OnSelect(EventArgs.Empty);
 }
 protected override void RaisePostBackEvent(string eventArgument)
 {
  base.RaisePostBackEvent(eventArgument);


  if (eventArgument.Length != 0) {
   char ch = eventArgument[0];
   string val = HttpUtility.HtmlDecode(eventArgument.Substring(1));
   // Pathseparator in eventArgument is backslash, so we must temporary set the separator
   char oldPathSeparator = PathSeparator;
   PathSeparator = '\\';
   TreeNode node = FindNode(val);
   PathSeparator = oldPathSeparator;

   if (ch == 's') {
    if (node != null) {
     if ((node.SelectAction == TreeNodeSelectAction.Select) || (node.SelectAction == TreeNodeSelectAction.SelectExpand)) {
      RaiseOnSelect();
     }
    }
   }
  }
 } 

}

}

RichText control in Sharepoint

Today I needed a RichText editor in a sharepoint webpart, so after some googling and using the reflector I found a class in the Microsoft.SharePoint.WebControls namespace that fits my needs, InputFormTextBox!

The control is not well documented (the same as all other controls in the Microsoft.SharePoint.WebControls namespace) but it's quite easy to use:

InputFormTextBox richTextEditor = new InputFormTextBox();
richTextEditor.RichText = true;
richTextEditor.RichTextMode = SPRichTextMode.FullHtml; // (Can be set as FullHtml, Compatible or HtmlAsXml

The text entered into the control can be get/set in the Text property.

Set the richTextEditor.MultiLine to true if you want to enable multiline editing.

You can control the height and width in the same way as you use the regular TextBox control from the System.Web.UI.WebControls namespace.

References:
http://msdn2.microsoft.com/en-us/library/microsoft.sharepoint.webcontrols.inputformtextbox.aspx
http://msdn2.microsoft.com/en-us/library/microsoft.sharepoint.sprichtextmode.aspx

How to determine the current database in MSSQL

Today I wanted to know what database I currently was connected to in an SQL-script (since I dynamically set the database name when calling SQLCMD) and found out that there is a simple function for this: db_name()

SELECT DB_NAME()

http://technet.microsoft.com/en-us/library/ms189753.aspx