HttpCode

I've just rolled off a project that made use of the Business Data Catalog (BDC), I thought I'd share my experiences.

Firstly I'll give a quick heads up on the BDC, you've probably seen a diagram similar to the following one, basically it shows all the features in MOSS that hang off the BDC:

 Business Data Web Parts - These are the out of the box list web parts, they provide enough functionality out of the box to at the very least expose the user to the data.

SharePoint Lists - A user can create a column of type 'Business Data' this is very handy for end users, for example they may want to create an Asset register for locations, the locations that are relevant to them maybe exposed via the BDC, this would then save the user the task of maintaining the location list.

 Search - Provide a consistent search experience across all the enterprise data.

 User Profile Importer - Use business data to enhance the profile information you have about your user base, from the example above, you could import the base location into your profile property, then web parts could be built to personalise the information that is displayed to you.

 Custom Solutions - Use the BDC as a data access layer.

So anyway the application that I did some work on fits into the custom solution box mostly, but with a little bit of thought you can generally make it cross over a number of areas (search, profile import etc) and by doing so create a greater experience for your end users.

For example, our BDC profile import process imported a whole range of properties, one in particular was a business unit code. So by creating a custom metadata property, the people search was able to become a business unit people searcher. We then build some custom web parts that visualised the data on some maps and in some other ways that were relevant to the business, we constructed links that pointed to the people search which made use of the location code, much in the same way that I created tag links in the search demo.

We added the BDC data to the search centre but changed the result links to point to pages with our custom web parts, so now the search centre feeds into the custom visualisations, so no matter where the user comes from, either search or navigation they are going to get sucked into our experience.

So my tips are simple:

• Plan your navigation experience - does search play a part in your experience?, its such a powerful concept to ignore, more people especially the younger generation are very search centric.
• The BDC is best for read only type data - do you need to push back information? might be other easier ways to go about it.
• Try to use all the options like web parts, search and profile information to be part of your overall custom solution.
• Can users use your data in lists? Would it make the end users job easier?

I've posted a couple of articles on JQuery and it's use in SharePoint and also provided some sample web parts. Recently I was putting together these web parts into a single project and I ran into a problem. Each web part was adding the JQuery script resources to the page as if it was the only control on the page, for example the ImageCarousel and the Tag Suggestion web parts both had code that needed to be run on the JQuery load event, this problem would also crop up when multiple instances of the same web part were added to the page. What was outputted to the page was something like:

$(document).ready(function(){ 
//function call for webpart1
}
$(document).ready(function(){ 
//function call for webpart2
}
$(document).ready(function(){ 
//function call for webpart2 instance 2
}

So I started developing a Script Manager control that I could add to the page that would output the JQuery load event just once, but with all the calls for the page inside this event. As with anything programming related these days, I found what I was looking for at http://codeplex.com/JQueryScriptManager/ I've changed a bit of the implementation, but the basic intent has remained the same. Hopefully I'll be able to get these changes updated to codeplex.

Looking at the revised code for the ImageCarousel web part:

jQueryManager jqueryManager = null;

protected override void OnInit(EventArgs e)
{
    
    jqueryManager = jQueryManager.GetCurrent(Page);
    if (jqueryManager == null)
    {
        jqueryManager = new jQueryManager();
        Page.Controls.Add(jqueryManager);
    }
    Page.ClientScript.RegisterClientScriptInclude("JCarousel", Page.ClientScript.GetWebResourceUrl(this.GetType(), 
        "JQueryWebParts.js.jquery.jcarousel.pack.js"));
    base.OnLoad(e);
}

Now inside the PageInit we check for the presence of a JQueryManager script control, if we don't find one, we add it to the controls collection. Just like the ASP.NET Ajax ScriptManager control, there can only be one per page.

Now to add code that is globally scoped which also resides in the same script block where our OnLoad event lives:

jqueryManager.RegisterScript("-- javascript code without <script> blocks");
//example:
jqueryManager.RegisterScript("var someItems = [5,4,3]");
            

To get javascript to run when the page is loaded (this will bundle calls into a single load event):

jqueryManager.ReadyFunctions.Add(new RegisterStartFunction("jQuery('#carousel').jcarousel();"));

Use the ReadyFunctions collection which takes a RegisterStartFunction object as a parameter.

Internally the JQuery script manager control is performing the following:

StringBuilder Start = new StringBuilder();

if (Scripts != null)
{
    foreach (RegisterScriptBlock r in Scripts)
        Start.Append(r.ScriptBlock + Environment.NewLine);
}

if (ReadyFunctions != null)
{
    Start.Append("$(document).ready(function(){");

    foreach (RegisterStartFunction r in ReadyFunctions)
        Start.Append(r.FunctionName + Environment.NewLine);

    Start.Append("});\n\n");
}

Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "JQuery", Start.ToString(), true);

The source code can be downloaded here. This includes the updated JQuery Script Manager and the ImageCarousel web part that I've previously posted about.

I think this approach will provide a nice platform to continue building web parts based on JQuery Plugins, have fun.

I've blogged previously about the SharePoint solution that I created that modifies the web.config file of a web application to enable the ASP.NET Ajax toolkit.

While I was at Tech-Ed I saw a presentation where the presenter needed to add this exact functionality to his web.config file, so he preceded to create a blank ASP.NET Ajax web project just so he could copy the relevant lines from the web.config that was created.

So that has prompted me to package up my solution and put it up on codeplex.

You can find the solution and source code at: http://www.codeplex.com/SPAjaxEnabler/

I've put together a screen cast of how to use the tool here as well.

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.