Sunday, December 28, 2008

Re-Provisioning Data in a SharePoint List

Synopsis: This post addresses a technique I recently found to provision data in a SharePoint list instance where a feature may be activated and deactivated.

The documentation here tells you how you can use the ListInstance element to provision an instance of a list and you can provision that list instance with rows of data.  Here's a simplified version of ListInstance XML I've used in a file:

<ListInstance
    Description="Contains all work locations."
    Id="Location"
    OnQuickLaunch="FALSE"
    TemplateType="10012"
    Title="Location"
    Url="Lists/Location">
    <Data>
        <Rows>
            <Row>
                <Field Name="Title">Jacksonville</Field>
            </Row>
            <Row>
                <Field Name="Title">Louisville</Field>
            </Row>
            <Row>
                <Field Name="Title">Marion</Field>
            </Row>
        </Rows>
    </Data>
</ListInstance>

What the documentation lacks is any explanation of what you can put in the Field attributes.  It's pretty intuitive that the Name attribute corresponds to the name of a field. It would have been nice if the documentation explained that this was the internal name, but that's ok.  Now we know, right?  Anyway, you can just specify any field available in your list structure.  Now, if we provision our feature the data will be provisioned to the designated list.  In the example above, I had created my own list (#10012), but this could be any out-of-the-box list or document library.

That's all good, but what happens if someone deactivates and re-activates the feature.  A very likely scenario.  In that case, data is duplicated each time.  The first time the feature is activated on an empty list the follow are created:

  • Jacksonville
  • Louisville
  • Marion

When the feature is deactivated and re-activated, the list instance provisioning process occurs again and the data is duplicated.  You end up with the following:

  • Jacksonville
  • Louisville
  • Marion
  • Jacksonville
  • Louisville
  • Marion

This was certainly not desirable behavior and was confusing to me why SharePoint would do this by default.  So my options had been to programmatically delete the list or empty the list during deactivation.  Not pretty in my opinion, but it got the job done.

I lived with this "problem" for many months until I got fed up and went on a hunt for the solution.  I spent time looking for a flag somewhere that would change this default behavior.  I looked for the longest time for a way to turn this off.  What I finally figured out is that by simply specifying the ID, I can control this.  The ID field is usually automatically generated for each item in the list.  Here we're able to set the value.  All I needed to do was to specify a unique value for the ID field for each row.  That's it!  So my resulting XML looked like this:

<ListInstance
    Description="Contains all work locations."
    Id="Location"
    OnQuickLaunch="FALSE"
    TemplateType="10012"
    Title="Location"
    Url="Lists/Location">
    <Data>
        <Rows>
            <Row>
                <Field Name="ID">1</Field>
                <Field Name="Title">Jacksonville</Field>
            </Row>
            <Row>
                <Field Name="ID">2</Field>
                <Field Name="Title">Louisville</Field>
            </Row>
            <Row>
                <Field Name="ID">3</Field>
                <Field Name="Title">Marion</Field>
            </Row>
        </Rows>
    </Data>
</ListInstance>

Now, when re-provisioning the list instance data, the three items I originally provisioned will never be duplicated like before.  I can deactivate/activate the feature to my heart's content.  This is helpful for simple lookup lists like states/provinces.

Tuesday, June 24, 2008

Fun with SharePoint Designer: Press Releases

Synopsis: This is one in a series of blogs on how I used SharePoint Designer in real-world solutions.  This covers creating a list and a set of pages in a WSS site that render press releases online.

I recently helped a client build a web site that required a press release page. On the main page, the anonymous users were supposed to see a list of short titles in the corner of the page.  When they click on that short title, they should be directed to a page that displays a full title and the full article.  Additionally, when users navigate to the tab on the top toolbar, they should see a listing of all the active press releases.  All this is to be styled using custom HTML defined in the master page.

From the administration perspective, we want the admin (not me as the programmer) to go into an admin area to edit these press releases.  They need to be able to create new ones, approve them and set begin/end dates on them.

A SharePoint list is a great solution to this.  The first big win is that we don't have to build much of the administration functionality at all.  With any SharePoint list, of course, you get all the input/listing and edit forms.  So the admin need only know how a list works.  Also, we want to be able to support things like versioning, check-in/check-out and lightweight approvals -- again, all available in a simple SharePoint list and all available in WSS as well as MOSS.  So we'll start in a simple blank site and build a custom list that meets our needs and call it "Press Releases."  From here, we'll add some columns to end up with these:

  • Title (already on the list, required)
  • ShortTitle (single line of text, required)
  • Body (multi-line text that supports rich text, required)
  • StartDate (date and time, required)
  • EndDate (date and time, required)

Your columns should look like this now:

image_2_01409BA9.png (559×219)

Then, while in List Settings, go to Versioning and enable that.  Also enable item approval, and check-in/check-out.  Approval is really all we need here, but the other is an added bonus for the administrator.  Add a couple of press releases to the list to have something like the following:

image_4_01409BA9.png (640×419)

The next thing we need to do is turn our attention to SharePoint Designer, as this will be the tool we'll use to render our data in various pages.  There are two pages we're concerned about: PressRelease.aspx, and ReleaseList.aspx.  The PressRelease.aspx page will show a single press release.  ReleaseList.aspx is going to show us multiple press release titles in a full-view page.  The default page could display a short list of press releases so users can see them right up front on our page. 

ReleaseList.aspx

Let's start with our most simple page, the ReleaseList page.  Using SharePoint Designer, create the ReleaseList.aspx page in the root of the site and, if you want, attach the master page so you have the look and feel you want to achieve.  In this exercise, we'll simply use a blank page so we don't make things too complicated.  You'll see something like this:

image_6_01409BA9.png (640×330)

Next we need to add some data from our list.  Go to the Task Panes menu and make sure the Data Source Library tab is selected.  If selected, you can see it appear to the right as an additional tab which is peer to the toolbox.  This task pane shows us all the data sources available in the environment.  When SharePoint Designer opens up, it communicates to the server to see what's available.  Notice you can have lists, document libraries, XML files, and external data.  Also, if you're using MOSS Enterprise, you'll see BDC data sources show up here.

Next, we need to select our data source (Press Releases list) and right-click to see a drop-down.  We'll choose Show Data, which will take us another task pane which shows us all the columns available in the list we've chosen.  Select the Press Releases list from the available lists and then choose to Show Data from the drop-down that appears, just like this:

image_10_01409BA9.png (622×480)

Once you do this, all the available columns show up in the display tab called Data Source Details.  From here, click the Title field and drag it to the design surface below the title.  This will render all the items in the list in design-time, which is pretty cool.  The resulting screen looks like this:

image_14_01409BA9.png (640×355)

Next we need to filter out those item which aren't approved and don't fall into the desired publishing timeframe.  Note, we'll use the Common Data View Tasks to do this.  Click on the filter link and then set the criteria as follows:

Approval Status = Approved

Start Date <= today

End Date >= today

The filter should look like:

image_18_01409BA9.png (438×258)

Next, you can adjust the layout by selecting the Data View Properties.

Now that we have a press release listing page, what happens when I click on a title?  Now, we need to link the title to the PressRelease page, which will display a single press release.  Select the Title and change it's type to Hyperlink as below.

image_12_01409BA9.png (640×355)

Continue through the warning about the trusted environment.  Change the following:

Address: PressRelease.aspx?id={@ID}

Text to Display: {@Title}

Your screen should look like this:

image_16_2F2DEE61.png (640×320)

PressRelease.aspx

Now, let's create PressRelease.aspx.  This page is going to receive a querystring parameter.  Let's start with another blank aspx page and choose the data library to find our Press Releases list first.  Then select "show data" to view the available columns.  Next, choose the Title and Body columns an choose to insert a single item view:

image_22_2F2DEE61.png (370×450)

Now, we need to filter the item that displays with the querystring parameter passed in.  Choose "Parameters" from the Common Data View Tasks pane that appears.  Then add a new parameter that looks at ID as a source parameter in the querystring.

image_24_2F2DEE61.png (502×333)

Next choose Filter from the Common Data View Tasks.  We need to filter the press releases to find the one from our newly created parameter.  The filter should look like this:

image_26_2F2DEE61.png (438×258)

Now you have a press release system.  Administrators can get to administrative pages to add/update press releases and end users can only see those press releases which are active and approved.  Through the use of a SharePoint list, we also have versioning, which is difficult to achieve in a typical database.

Monday, June 23, 2008

How to Provision a Link in Site Settings

Have you ever wondered how you could add your own links to the Site Settings area within SharePoint?  If you want to add a link that shows up in that location try this out.  The following is CAML provisioning code that is initiated by a feature.  This assumes you know what SharePoint Features are.

<Elements xmlns="http://schemas.microsoft.com/sharepoint/">

    <CustomAction
       Id="MyCustomLink"
       GroupId="SiteCollectionAdmin"
       Location="Microsoft.SharePoint.SiteSettings"
       RequireSiteAdministrator="true"
       Sequence="300"
       Title="Synchronize Root Names">
        <UrlAction
           Url="_layouts/CustomApplicationPage.aspx" />
    </CustomAction>

</Elements>

This will give you a link as shown below:


Location tells SharePoint to target the site settings page.

GroupId tells SharePoint to put the link in the site collection administration section.

Sequence is the position in the list.  If you're not happy with it's position, just change the number.  Lower numbers appear higher in the list.

RequireSiteAdministrator is pretty self-explanitory.  If you're not a site administrator, you won't see the link.

Friday, May 30, 2008

Getting Date Information From Photos in SharePoint

Synopsis: SharePoint doesn't automatically extract the date the photo was taken when loaded into a SharePoint picture library, although this information is already embedded in JPEG images.  This article walks through the process of tapping into the photo image data when uploaded in SharePoint using an item event receiver.

SharePoint is a great tool for many things.  I find many applications for it in both the business world and the personal world.  I'm usually using MOSS (Microsoft Office SharePoint Server 2007) in most business scenarios and I use WSS (Windows SharePoint Services 3.0) for a lot of personal uses.  In either product, I find uploading photos a useful thing to do.  However, why should I have to go back and manually put in the date the photo was taken when my JPEG photos already contain this information?

The date the photo was taken as well as a lot of other information is embedded in certain image types, JPEG and TIFF being just two of those formats.  The standard for storing data in images is known as Exif.  Exif is an abbreviation for Exchangeable Image File Format.  I've written about how to use .NET to tap into this information here.

Now that we know how to get our date from our image, let's turn our attention to SharePoint.  In SharePoint, you have the ability to respond to events by way of "receivers."  We can respond to several different event receivers, here are a few:

  • SPItemEventReceiver - for responding to list item events, such as when we insert or update list item data
  • SPListEventReceiver - for responding to list structure changes, i.e. when new columns are created/modified
  • SPFeatureReceiver - for responding to feature activation and installation events

Since we want to intercept pictures as their loaded into a picture library, we're going to be interested in the SPItemEventReceiver.  We'll also need to use the SPFeatureReceiver to "wire everything up."  First, let's look at item events.

When items are inserted or updated in a list or document library, we can respond to those events.  Looking at possible overrides available on the SPItemEventReceiver class shows us some of the possible events.  We're interested in the ItemAdded event.

Before going too much further, lets talk about our Visual Studio project.  I'm using Visual Studio 2005 and STSDEV, which is a tool released by Ted Pattison on Codeplex.  This tool simplifies the creation of software that will be deployed in SharePoint.  There's a lot of good training on the Codeplex site so I won't go into the details.  In short, this tool simply creates my Visual Studio project from which I'll build my feature for looking at images.  Here's a snapshot of my Visual Studio solution:

Most of the files in the solution are created for me automatically.  I only need to modify the solution config XML file.  Again, there's training on the STSDEV site, so I won't go into that.  However, notice that there are two code files: FeatureReceiver.cs and PictureItemEventReceiver.cs.  FeatureReceiver is used to "wire up" the event to all picture libraries.  The PictureItemEventReceiver file is used to do the work of getting the image date and setting that in the item's metadata.

Let's look at the PictureItemEventReceiver file first.

    public class PictureItemEventReceiver : SPItemEventReceiver
    {
        public override void ItemAdded(SPItemEventProperties properties)
        {
            try
            {
                SPListItem item = properties.ListItem;

                if (item.File.Name.ToLower().EndsWith("jpg") || item.File.Name.ToLower().EndsWith("jpeg")) )
                {
                    Stream stream = properties.ListItem.File.OpenBinaryStream();

                    Image image = Image.FromStream(stream);

                    Nullable<DateTime> imageDate = GetImageDate(image);

                    if (imageDate != null)
                    {
                        properties.ListItem["Date Picture Taken"] =

                               ((DateTime)imageDate).ToString("MM/dd/yyyy hh:mm tt");

                        item.Update();
                    }
                }
            }
            catch (Exception ex)
            {
                Trace.WriteLine(

                   string.Concat("Error obtaining image datestamp: ", ex.ToString()));
            }
        }

First we need to inherit from SPItemEventReceiver.  Then we override the ItemAdded event, which occurs after the image is added. 

    public class PictureItemEventReceiver : SPItemEventReceiver
    {
        public override void ItemAdded(SPItemEventProperties properties)

At this point, we get a reference of the item itself using the properties parameter. 

SPListItem item = properties.ListItem;

If the file is a JPG type, then we go about opening a binary stream from the File property of the item.  Next, we go about getting a System.Drawing.Image from the binary stream.  At this point, we are able to call another block of code (shown in a previous blog post here) that will get the date stamp out of the image.

        Stream stream = properties.ListItem.File.OpenBinaryStream();

        Image image = Image.FromStream(stream);

        Nullable<DateTime> imageDate = GetImageDate(image);

Once we have that date, then we can update the SharePoint list item metadata.

        properties.ListItem["Date Picture Taken"] =

              ((DateTime)imageDate).ToString("MM/dd/yyyy hh:mm tt");

        item.Update();

Note that we must update the date with the correct date format for SharePoint.  The call to GetImageDate retrieves the date from the Exif information embedded in the image.

Next, we turn our attention to the FeatureReceiver.  We need to tell SharePoint that we want to watch for ItemAdded events for all Picture Libraries.  The FeatureReceiver.cs file contains that code:

public class FeatureReceiver : SPFeatureReceiver
{
    private string assembly = "MetaPic, Version=1.0.0.0, Culture=neutral, PublicKeyToken=ce54f2f07031df02";
    private string receiver = "MetaPic.PictureItemEventReceiver";

    public override void FeatureActivated(SPFeatureReceiverProperties properties)
    {
        try
        {
            SPWeb site = (SPWeb)properties.Feature.Parent;

            // look for any picture library in the site
            for (int i = 0; i < site.Lists.Count; i++)
            {
                if (site.Lists[i].BaseTemplate == SPListTemplateType.PictureLibrary)
                {
                    // add the event receiver to wath for new images
                    site.Lists[i].EventReceivers.Add(
                        SPEventReceiverType.ItemAdded,
                        assembly,
                        receiver);
                }
            }
        }
        catch (Exception ex)
        {
            Trace.WriteLine(ex.ToString());
            throw;
        }
    }

In the FeatureReceiver, we inherit from SPFeatureReceiver.  This lets us respond to Activation/Deactivation events.  Upon activation of this feature, we enumerate through all lists that are of type SPListTemplateType.PictureLibrary.  For each one of these lists, we add an event receiver.

                    site.Lists[i].EventReceivers.Add(
                        SPEventReceiverType.ItemAdded,
                        assembly,
                        receiver);

Note that we must add the event type by specifying the proper enumeration SPEventReceiverType.ItemAdded.  Then we must pass in the fully-qualified name of the signed assembly and the actual receiver class.  This requires that you at least sign and build the project to get the public key token using Reflector or sn.exe.

Finally we clean up when the feature is deactivated using this chunk of code:

public override void FeatureDeactivating(SPFeatureReceiverProperties properties)
{
    try
    {
        SPWeb site = (SPWeb)properties.Feature.Parent;

        // look for any picture library in the site
        for (int i = 0; i < site.Lists.Count; i++)
        {
            if (site.Lists[i].BaseTemplate == SPListTemplateType.PictureLibrary)
            {
                // find and remove the event handler
                foreach (SPEventReceiverDefinition receiver in site.Lists[i].EventReceivers)
                {
                    if (receiver.Type == SPEventReceiverType.ItemAdded)
                    {
                        receiver.Delete();
                        break;
                    }
                }
            }
        }
    }
    catch (Exception ex)
    {
        Trace.WriteLine(ex.ToString());
        throw;
    }
}

This code runs when the feature is deactivated and removes the event receivers previously added.  You'll want to activate this feature after you create one or more picture libraries.

Room for Opportunity

While the solution here is pretty cool, there are some things I'd like to address someday.  Here are some of them:

1. Add to the event receiver to a content type instead of each picture library so you don't have to re-activate the feature each time a new picture library is created.
2. Fix the user interface when a single image is loaded.  When a single item is uploaded you're sent to the edit page, but that page shows the date info as blank when the date is really there.  It really needs to be displayed as a read-only field.  Here's what you see:

And if you simply navigate to the image without updating, you see that it is there.

Multiple file upload, which is most useful, works great (of course I changed my view to show the date info):


3. Obtain image data upon adding the image to the document library instead of after the image has already been added.  Right now, we're going through an additional step to re-open the image.  It would be better to intercept the upload stream.

I hope you enjoy this.  I know I'll get a lot more use out of the picture library now that I can get date information out of my images.  Hopefully, you can use the above code to look into other event receiver concepts.

Tuesday, May 27, 2008

Kentucky Area Code Camp 2008

I'm co-leading the KY Day of .NET code camp for this Summer.  If you're interested in speaking or volunteering, please check out the details here.

Thursday, May 22, 2008

Creating Connection Strings in 30 Seconds with UDL Files

I did this once in a training session and the class was amazed.  For years, I've been whipping out connection strings using a little-known technique involving UDL (Universal Data Link) files.  Here's the trick:

1. Create a new text file on your desktop and rename it to "Test.UDL" instead of the default  ".TXT" extension.  You'll need to show file extensions for this.

2. Next, double-click on this file to open up a connection string editor called "Data Link Properties."

3. Change to the Provider tab and choose the provider (OLE DB Provider for SQL Server in this example).

image_8_6A764D87.png (377×471)

4. Change to the Connection tab and set your basic connection settings.  You can also test your connection at this point if you want.

image_10_6A764D87.png (377×471)

5. Set any additional settings in the Advanced and/or All tabs, such as Connection Timeout.  Then click OK to save the file.

image_12_6A764D87.png (377×471)

6. Open the .UDL file with notepad and there you go...

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestDB;Data Source=localhost

You can remove the property called "Persist Security Info" because it's only used by the tool itself.  Enjoy.

Monday, February 4, 2008

SharePoint in the Kitchen

Synopsis: This is about how I tried to use SharePoint in the home for simple calendaring -- in our kitchen.

Ok.  I'm a geek.  My kids will certainly come to appreciate that some day.  I love gadgets and such.  I guess that comes from the influence of Star Wars and the like.  What was so cool about those movies was that technology was so integrated into everyone's life.  The movies made the technology appear dependable, omnipresent and just down right cool.

My Grand Plan

So when it comes to my own life, I try to make technology just blend into the background and be helpful to our lives.  I had this old Sony VAIO laptop from 1998 that I was just itching to use somewhere in the house, because it was still cool to look at -- it was one of the super slim designs which is less than an inch think when folded up.  Unfortunately, it's dog slow these days, but ok for some simple browsing.  I'm running Windows 2000 on it.  I can't believe I used it as a dev machine (Classic ASP and VB). 

Anyway, I came up with the great idea of displaying the family calendar so my wife always knows what's going on with family events.  So I cleaned up the kitchen counter (just enough to place the laptop there) and plugged in an external wireless adapter and pointed it to a SharePoint site, which is running on a machine in the basement... I mean, my server room.  I used SharePoint Designer to create a custom page which showed just the family calendar and I dropped some JavaScript that refreshes the page.  Then I point my browser to it in full-screen mode and it looks something like this:

image

To update this calendar, I can use the SharePoint interface or I can use Outlook 2007.  From within SharePoint (WSS or MOSS), you can add two-way synchronization to an event calendar very easily (only with Outlook 2007, however).  Just navigate to the calendar within SharePoint and choose "Add to Outlook" from the Action menu of my SharePoint site.

image

After that, you can just go into SharePoint and edit the calendar data that is stored in SharePoint.  Very slick.  There are other client integration points with Office 2007, but I will have to talk about that in another post.

Ok.  My wife just loves this... NOT.  I thought about taping the laptop to the fridge to increase visibility, but that was quickly shot down for some reason.  For now she lets me keep it on the counter.  However, she doesn't use it.  Why not?  Doesn't this provide all the information one might need at a glance?  It's it cool. 

What's Really Cool?

Someone to use and truly love your software day in and day out -- that's cool (at least if you're a developer-type like me).  I've developed enough software to realize that just because you have something that looks cool and seems to address the needs of the user (and does exactly what they asked for), you're not always guaranteed a win.  If what you build doesn't get used, you're just wasting time (which means money -- mine or somebody else's).

"So what can I do to make this better?," I asked myself.  Well, when looking at the calendar, I don't always need to see the past history.  I really just need to see today and then a few days ahead.  The font is really too small.  I also think it would be helpful to see a short list of tasks.  And I'm not really saving the screen here.  I'm wearing it out since the page is always the same and the screen saver is turned off.

When I looked at the platform I was running on, I thought the use of a screen saver might be good here.  A screen saver would be pretty clean from a usability perspective.  And there's a sample project in Visual Studio just for that.  I know that I probably could have done something with dynamic HTML or Silverlight, but I was really curious to see how to make a screen saver for some reason.

You can actually do this with minimal work since the sample screen saver application allows you to point to an RSS feed.  Since any SharePoint list or document library can expose itself as an RSS feed, you're set.  Well that is until you decide to want to display things like tasks and calendar items on the same screen with nice fade-in and fade-out functionality.  Below is the starter project you can use.

image

So it might be nice to use this project to display SharePoint data.  Maybe I could tap into the SharePoint web services interfaces.  But for now I have a simple calendar view that displays full-screen in a browser.  It's OK, but I really think a screen saver would be more interesting.  That's for a future post.