HttpCode

Continuing on my theme to bring more JQuery plugins into SharePoint, I present a sample application that makes use of the search features of MOSS and some custom web parts that provide Ajax tag suggestion and a tag cloud web part that works over all SharePoint document libraries with a little configuration of the search engine.

Here is a screen cast that demonstrates the concept:

SharePoint Tagging

Alternatively you can download a higher quality video here (7mb).

I will blog more about how I built this application in the coming days and I'll also release the source code to it along with a more detailed description.

If I can convey any message about this application it would be that the search experience is an integral part of the overall application and it can really add some value. For example in the tagging demo I've created a metadata property mapping called Tags, this property is used to crawl the Tag column of the document library. The search cloud web part renders a link to the search centre with the selected tag embedded in the search query, so for no code I've managed to display all the documents that are tagged with the selected tag.

The Ajax tag suggestion web part is based on the great Tag Suggestion plugin, it uses a IHttpHandler to iterate over all of the lists to create a collection of matching tags which are returned as JSON, now this approach won't scale, but we can work on that in the future, hopefully we can also leverage the search engine for that as well.

For now, hopefully this demo will give you some ideas to employ the search engine inside your MOSS development.

One of my favourite features of SQL Server 2008 is the Merge or 'upsert' Statement. Lets say we have an ETL process that prepares a data stage table, without the merge statement you might first delete all the records from the staging table and then insert them all again. If you have large data sets this can become quite time consuming and well frankly it doesn't seem very elegant. Another alternative is to use a cursor to iterate over each record and look for changes, you might also have data that has a timestamp which might help you write a set based operation, both of these approaches are less than ideal.

Well as of SQL 2008 you can use the Merge Statement:

Lets say you have a simple table like:

CREATE TABLE People
(
  ID       INT PRIMARY KEY,
  Name     Varchar(50) NOT NULL,
  Position Varchar(50) NOT NULL,
  Age      INT NOT NULL
);

Assuming our StagedPeople table is the same structure the merge statement would look like:

MERGE INTO People
USING StagedPeople
 ON People.ID = StagedPeople.ID
WHEN MATCHED THEN
-- if we find a match, then perform an update  
UPDATE SET
  Age = StagedPeople.Age,
  Name = StagedPeople.Name,
  Position = StagedPeople.Position
WHEN NOT MATCHED THEN
 -- if we don't find a match, then insert a new record
 INSERT (ID, Name, Position, Age)
 VALUES (StagedPeople.keycol, StagedPeople.Name, StagedPeople.Position, StagedPeople.Age)
WHEN SOURCE NOT MATCHED THEN
 -- we could delete the record, since it's not found in the Staged table
DELETE;
OUTPUT $Action

 

The $action variable returns a varchar(20) of the operation that has taken place,  i.e. 'INSERT', 'UPDATE', 'DELETE'

The operations and full reference can be found here.

It seems like a pretty fundamental operation, glad it only took till 2008 for us to have it :)

This week I picked up this simple tip for spatial enabled applications. Just say you have an existing database that has the latitude and longitude stored as separate columns such as a Latitude and Longitude as you would like to make these columns available so that spatial operations can be performed on them.

Create a view on the table with the Lat, Long as parameters to the Geography::Point function which creates a spatial point.

Your table might look like:

The following SQL will do the trick to convert the points to spatial column:

CREATE VIEW SpatialView
AS

-- Create a view over a table without a spatial column
SELECT Name, geography::Point(Latitude, Longitude, 4326) as Location FROM UnSpatialTable

So now you have a spatial column that you can include in your spatial queries such as, finding the distance between points:

DECLARE @location GEOGRAPHY

SELECT @Location = Location from SpatialView where name = 'test'


SELECT @Location.STDistance(Location) as DistanceToOther from SpatialView

Creating a buffer around a point:

SELECT Location.STBuffer(20) as BufferedLocation from SpatialView where Name = 'test'

This approach might be useful if you collecting data on a mobile device running the compact edition of SQL which doesn't have any spatial features. You can collect your data as lat,long values and then do the spatial manipulation via the view. Don't forget to think about spatial indexes!

Recently I posted about using JQuery in SharePoint web parts in this post I wrapped up the JCarousel plugin. Instead of requiring the JCarousel.js file to be included into the master page, I instead made the web part inject the HTML to cause the JavaScript file to be loaded.

The first step was to include the packed version of jquery.jcarousel.pack.js into my project:

Notice the Embedded Resource setting for the build action.

Now if we add the following code to our assembly.cs file:

[assembly: System.Web.UI.WebResource("SPImageCarousel.jquery.jcarousel.pack.js", "text/javascript")]

The above line now makes our embedded resource available for use in our web part which is done by the following code:

protected override void OnInit(EventArgs e)
{
    Page.ClientScript.RegisterClientScriptInclude("JCarousel", Page.ClientScript.GetWebResourceUrl(this.GetType(), "SPImageCarousel.jquery.jcarousel.pack.js"));
    base.OnLoad(e);
}

This code will ensure that the script is only added to the page once since the key "JCarousel" is the first parameter, remember that more than one web part can be added to a page at the same time, so we need to consider this as we develop our web parts. OnInit is the perfect event to call RegisterClientScriptInclude it's nice and early in the page lifecycle.

So hopefully you'll find this a nice and easy way to include your JavaScript resources, it does have the disadvantage of requiring a recompile to include a later version of the JavaScript file, so your mileage may vary if this is a concern.

Better still if your using .NET 3.5 Service Pack 1, you could make use of the CompsiteScript element of the script manager to combine the javascript files so the browser makes fewer calls to the server.

I've mentioned before that I'm partial to JQuery, I consider it to be the Jessica Alba of JavaScript.

With all the work that has been done creating awesome JQuery based UI components, it seems like such a shame that us SharePoint developers aren't making more use of them.

So I present to you my first JQuery based web part, the Image Carousel:

Based on JCarousel, this web part presents the contents of a picture library in a carousel that can be scrolled by the navigation buttons.

The web part simply imports the jcarousel.js file, the web part then injects a JavaScript array of the images that reside in the selected picture library along with some javascript that initialises the carousel. The code to find the SharePoint picture library list is trivial:

SPSite site = SPContext.GetContext(HttpContext.Current).Site;
SPWeb web = site.OpenWeb();

foreach (SPList list in web.Lists)
{
    if (list.BaseTemplate == SPListTemplateType.PictureLibrary)
    {
        //grab the images from this list
        jsArray.Append(ProcessList(list));
    }
}

The web part makes use of custom toolpart for the selection of the picture list:

I think this provides a good template and starting point to start putting more of those JQuery plugins to use inside SharePoint

A small screencast of the control in action can be found here.

The end effect is some exceptional functionality for little development effort, which is really what JQuery is all about.

Some of the things that you need to think about when integrating:

• Make sure that the injected div tag that will be used at the host has a unique id, remember that more than one webpart can be added to a page, if you hard code the html to have specific ID's, you will run into problems.
• Make sure the JavaScript gets injected into the right part of the page, also just like the above point, ensure that each injected bits of code have a unique name, so that multiple webparts on the same page don't cause problems.
• Use the packed version of the component, SharePoint is bloated enough already, use the smallest possible footprint you can.

The source code can be found here.

In my first post dissecting the portal connection code, I showed the following code:

PortalConfig portalConfig = 
(PortalConfig)curSite.WebApplication.Farm.GetObject
(
    new Guid(curSite.WebApplication.Properties[PROP_KEY].ToString())
);

Notice the line: curSite.WebApplication.Properties[PROP_KEY].ToString()

The properties collection is scoped at the web application level. So if someone was to build a tool that called the Clear() method on this collection it would cause issues inside the Portal Connection tool.

So my point is to use the web application properties with other applications in mind, only clear your own state.

This is the follow up article on the SPPortalConnection tool that I recently introduced.

So taking a look at how I've broken the solution up, you'll see that firstly I've got the PortalConnection project which is a class library project, the second project 'SPPortalConnectionSolution' has all the bits to make a Sharepoint solution by running the CreateSolution.cmd batch file.

So starting with the PortalConnection project, the first file ManagePortalConnectionPage.cs is the code behind page for the central admin interface. The most interesting method in this page is the logic to apply the portal connection:

private void ProcessStaple(SPWebApplication webApp)
{
    PortalConfig portalConfig = null;
    Guid stapleGuid = new Guid(SPPortalConnectionFeatureReciever.STAPLE_GUID);

    SPFeature portalConnectionStaple = webApp.Features[stapleGuid];
    try
    {                
        //set property  
        if (webApp.Properties.ContainsKey(SPPortalConnectionFeatureReciever.PROP_KEY))
        {
            portalConfig = (PortalConfig)webApp.Farm.GetObject(
            new Guid(webApp.Properties[SPPortalConnectionFeatureReciever.PROP_KEY].ToString()));
        }
        else
        {
            Guid guid = Guid.NewGuid();
            webApp.Properties.Add(SPPortalConnectionFeatureReciever.PROP_KEY, guid);
            portalConfig = new PortalConfig(webApp.DisplayName, webApp.Farm, guid);
        }

        portalConfig.PortalName = txtPortalName.Text.Trim();
        portalConfig.PortalUrl = txtPortalUrl.Text.Trim();
        
        portalConfig.Update();
        portalConfig.Provision();

        //activate feature
        if (portalConnectionStaple == null)
            webApp.Features.Add(stapleGuid);
        
        webApp.Update();
        BindData(webApp);
    }
    catch (Exception ex)
    {
        throw ex;
        
    }
}

The second file is the SPPortalConnectionFeatureReciever.cs, this class derives from SPFeature and is called each time a new site is created, this is the feature staple.

public override void FeatureActivated(SPFeatureReceiverProperties properties)
{
    using (SPWeb curWeb = (SPWeb)properties.Feature.Parent)
    {
        SPSite curSite = curWeb.Site;                
        if (curSite.WebApplication.Properties.ContainsKey(PROP_KEY))
        {
            ApplyPortalConnectionSettings(curWeb, curSite);
        }
    }
}

The Feature folder SPPortalConnectionStaple contains the feature and manifest definitions.

The Elements.xml file declares the feature staple, notice the use of Global for the TemplateName (not GLOBAL#1):

<Elements xmlns="http://schemas.microsoft.com/sharepoint/">
    <FeatureSiteTemplateAssociation Id="7D174E8A-E68E-4a9b-9462-1A65301F7317" TemplateName="GLOBAL"/>
</Elements>

So feel free to download the source code and have a play.

If your profile properties includes the organisation hierarchy, you may have noticed that when an AD account is deleted or deactivated the manager of that person is sent an email notifying the manager that the site is scheduled for deletion and that they now have permissions to the mysite so they can clean it up. Of course the MySite will only be deleted if the Automatic Site Deletion is enabled. Be aware that if the AD account is deleted and Automatic Site Deletion is enabled, the manager won't have access because the site will be deleted.

Lets say you work in an organisation that wants to suppress this email (I know it sounds silly), how would you go about that? The first thing to understand is the process that caused that email to be sent in the first place.

The first step is the profile import will detect if the AD account has been deleted or disabled and will change the bDeleted flag to 1 in the UserProfile_Full table of the SSP database.

The next step of the process is handled by the My Site Cleanup Job:

If you disable this job then you will prevent the notification email being sent. However you will still need to have a process in place to clean out the mysites and the profiles. The crawler will still find the profiles and index them (so your people searches will include outdated people). The MySites will still be around taking up disk space.

One potential solution is to call the profile stored procedure manually:

DECLARE @UserID uniqueidentifier
DECLARE @NTName nvarchar(400)
DECLARE @SID varbinary(512)

DECLARE userCursor CURSOR FOR
SELECT Userid, NTName, SID from userprofile _full where bdeleted = 1

OPEN userCursor

FETCH NEXT FROM userCursor INTO @UserID, @NTName, @SID

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC profile_RemoveUser @UserID, @NTName, @SID

FETCH NEXT FROM userCursor INTO @UserID, @NTName, @SID

END

CLOSE userCursor
DEALLOCATE userCursor

I've had the chance to see two different approaches to implementing MOSS search, the first is the standard of the box experience and the second is by making use of all the features that MOSS offers. Once you understand what MOSS search can do, there is no reason why you shouldn't be making full use of the product.

I found this fantastic article called Plan the end-user search experience, which outlines in great detail all the options the search products provide.

I thought that I would list a few small things that give big bang for the effort that is needed to get them running.

1. Best Bets

Number 4 on the above image is the best bet. A best bet is predefined link that is shown for certain search terms. It doesn't have to be a link to your MOSS site, it can point to external resources.

2. Synonyms

Synonyms should be configured with words that are commonly used in your organisation, in the example above 'oof' is used for 'out of office', so both search items 'oof' and 'out of office' would show the same best bet.

3. Search Results Display

The layout options are not mentioned in the referenced article, I do think it is a good idea to build your XSLT to make your results look like Google. So align your results to the left, get rid of the horizontal line and maybe think about getting rid of the file type icon.

If anything I just want to bring the search experience to your attention, it's worth spending some time to become familiar with the various options that exist.

In a previous post I introduced the Automatic Portal Connection tool, which can be used to automatically setup the portal connection on new and existing sites.

I thought I would take a peek at how I've made this happen:

Firstly I'd like to introduce the SPPersistedObject:

The description MSDN uses is:

The Portal Connection tool has a class called PortalConfig which derives from SPPersistedObject this class holds the configuration information such as the portal name and portal URL:

/// <summary>
/// Simple persisted object that stores the portal connection name and the portal connection url
/// </summary>
public class PortalConfig : SPPersistedObject
{
    [Persisted]
    private string portalName = string.Empty;

    public string PortalName
    {
        get { return portalName; }
        set { portalName = value; }
    }

    [Persisted]
    private string portalUrl = string.Empty;

    public string PortalUrl
    {
        get { return portalUrl; }
        set { portalUrl = value; }
    }

    public PortalConfig()
    {

    }
    public PortalConfig(string name, SPPersistedObject parent, Guid guid)
        : base(name, parent, guid)
    {

    }
}

SPPersistedObject is a very handy class, especially for configuration type scenario that is used in this solution, a method that makes use of this object is:

/// <summary>
        /// Simple method to persist our settings to the site
        /// </summary>
        /// <param name="curWeb"></param>
        /// <param name="curSite"></param>
        public static void ApplyPortalConnectionSettings(SPWeb curWeb, SPSite curSite)
        {
            PortalConfig portalConfig = (PortalConfig)curSite.WebApplication.Farm.GetObject(
                                        new Guid(curSite.WebApplication.Properties[PROP_KEY].ToString())
                                        );

            if (curWeb.IsRootWeb)
            {
                curSite.PortalName = portalConfig.PortalName;
                curSite.PortalUrl = portalConfig.PortalUrl;
            }
            curWeb.Update();
        }

This object is filled out and persisted by the central admin page, then a feature staple gets this information when the site is created and applies it, but I'll expand on this in future posts.