Creating a Personalized SharePoint 2007 Site Directory

Organizations that deploy Microsoft Office SharePoint Server 2007 for collaboration often find that the number of site collections and subsites grow very quickly.  It seems like it doesn’t take long for end users to figure out how flexible and easy SharePoint is for creating simple “applications”.   Even with a good taxonomy and solid governance plan there is little that can be done to hold back the growth of a SharePoint environment.    This growth can make it complex and confusing for users to navigate to their sites.   One option is for the SharePoint administrators to manage a complex navigation hierarchy that supports every site in the farm.   Another option is for the users to use search to locate their sites, however, many users still seem to prefer browsing over search.   One solution I created to help address this is a personalized site directory.   This directory lists out all of the sites under a specific web application that the user has permissions to view.    This display is in alphabetical order based on site titles.  The display also has visual clues to show the relationships between sites and subsites.

To create this site directory I looked at many options.  The first and most obvious one is to use the SharePoint API to enumerate all of the sites in a web application and then look for the sites that the currently logged in user has permissions to view.   This could work, but would be very slow and processor intensive.   The option I elected to go with was using a custom query to the SharePoint search engine and then process the results.   Why search?  Simple, search is security trimmed.   I do not have to perform any additional logic to make sure I only locate sites that the user has permissions to view.  Search is also very fast.

My original version used SharePoint Designer and a DataView web part to submit a query to the SharePoint search web service.  An XSL stylesheet was then applied to the XML returned from the query to render the directory.   The problem with this was that the resulting directory listing was not in alphabetical order and maintaining visual clues about relationships between parent and child sites was difficult and did not always work properly.

I revisited my original idea and came up with a custom SharePoint web part that still uses a search query but then applies much more in depth logic to properly order and display links to the SharePoint sites.    So how did I create the personalized SharePoint site directory?

The first step is creating the proper query to send to the SharePoint search web service.   Below is an example query.   The variable server should contain the domain name of the web application you wish to use as the basis for the directory.  An example would be:  my.domain.com

string query = "<QueryPacket xmlns ="urn:Microsoft.Search.Query" Revision ="1000">";
  query+="<Query><Context><QueryText language ="en-US" type ="MSSQLFT">SELECT Title, url, contentclass";
  query+="FROM SCOPE() where (ContentClass =''sts_web'' or ContentClass =''sts_site'' or ";
  query+="contentClass =''sts_listitem_850'') and site ='2012-04-10 22:11:51'" + server + "'' and isDocument =0</QueryText>";
  query+="</Context><Range><StartAt>1</StartAt><Count>1000</Count></Range></Query></QueryPacket>";

This query uses contentClass to limit the results of the query to just publishing and team sites.   The <Count> parameter limits the maximum number of sites to return to 1000.   This could be adjusted if you have more sites to list, however, I would be very careful trying to display that many sites on a single web page.

Once the query is executed we receive a DataSet that contains a single DataTable with the query results.   As you can see by the query we only receive back title, URL and contentClass.   Before we can display our directory we need to figure out a way to relate each of the rows so we can display the parent / child relationship between sites and subsites.    To build this relationship I start by adding 3 new columns to our query results DataTable:

dt.Columns.Add(new DataColumn("id", typeof(System.Int32)));
dt.Columns.Add(new DataColumn("parentid", typeof(System.Int32)));
dt.Columns.Add(new DataColumn("origURL",typeof(System.String)));

The id column will hold a unique identifier for each record.   The parentid will hold the unique identifier of the parent record and the origURL will be used to hold the original URL to the site.    Once we have added the new columns we need to populate the id column with data.    I also store the original url in the origURL field and then modify the URL field slightly so I can more easily figure out parent / child relationships based on that URL.

for (int i = 0; i < dt.Rows.Count; i++)
{
    dt.Rows[i]["id"] = i + 1;
    dt.Rows[i]["origURL"] = dt.Rows[i]["URL"];
    dt.Rows[i]["URL"] = dt.Rows[i]["URL"].ToString().ToLower().Replace("/sites","");
    dt.Rows[i]["URL"] = dt.Rows[i]["URL"].ToString().ToLower().Replace("http://" + server, "");
}

After populating the id and origURL fields and making a few modifications to the URL field we can begin to update the parentid field.  This is accomplished by looping through all rows in the DataTable and using the select method on the DataTable to locate the parentid for each record.

for (int i = 0; i < dt.Rows.Count; i++)
{
    if (string.IsNullOrEmpty(dt.Rows[i]["url"].ToString()))
        dt.Rows[i]["parentid"] = 0;
    else
    {
        string parentURL = dt.Rows[i]["url"].ToString();
        parentURL = parentURL.Substring(0, parentURL.LastIndexOf("/"));

        DataRow[] rows = dt.Select("url=''" + parentURL + "''");
        if (rows.Length == 1)
            dt.Rows[i]["parentid"] = rows[0]["id"];
        else
        {
            rows = dt.Select("url=''''");
            if (rows.Length == 1)
                dt.Rows[i]["parentid"] = rows[0]["id"];
            else
                dt.Rows[i]["parentid"] = 0;
        }

    }
}

Now that we have a DataTable that contains all of our sites, including parent / child relationships we can create a recursive method for displaying the records in alphabetical order.   The method shown below first creates a DataRow array that contains all of the children for a specific parentID.   LINQ is then used to sort the child records based on Title.   Finally we loop through each of the rows in the sorted array and display the links for the sites.

private void showDirectory(DataTable dt, int parentID, int level)
{
    DataRow[] childs = dt.Select("parentid=" + parentID.ToString());
    level++;

    var sortedRows = from p in childs orderby p["title"] select p;

    foreach (DataRow child in sortedRows)
    {
        Controls.Add(new LiteralControl("&nbsp;".Repeat(level * 3)));
        if (level<3)
            Controls.Add(new LiteralControl("<b><a class=''ms-navheader'' href=''" + child["origurl"].ToString() + "''>" + child["title"].ToString() + "</a></b><br>"));
        else
            Controls.Add(new LiteralControl("<img src=''/_layouts/images/navBullet.gif'' alt='''' border=''0''>&nbsp;<a class=''ms-navitem'' href=''" + child["origurl"].ToString() + "''>" + child["title"].ToString() + "</a><br>"));
        showDirectory(dt, Convert.ToInt32(child["id"]), level);
    }

}

As you looked through the showDirectory method you might have noticed the .Repeat method being called on the string “&nbsp”.   This is an extension method that I wrote to return the original string repeated the number of times specified in the parenthesis.  In this case I am repeating a non-breaking space character so that the text is indented 3 characters for each level of the site hierarchy.  Shown below is the Repeat extension method used.    It could probably be made more efficient by using a StringBuilder object instead of using the += operator.

public static string Repeat(this string instr, int n)
{
    var result = string.Empty;
    for (var i = 0; i < n; i++)
        result += instr;
    return result;
}

Important note: With .NET Framework 3.5 SP1 all requests to a webservice located on the same server as the calling application may with a 401 unauthorized error.   There is a good article on blogs.iis.net that explains the issue and how to resolve.

Download example code here

The example code is intended to show how you could implement a similar directory.  The code may not compile or work as is in your environment.

Leave a Reply