Wesley Bakker

Certifications

In one way or another I keep stumbling on threads / discussion on certifications. Whether or not certifications are useful, or whether a certification means you’re skilled. Here’s my ten cents on the subject.

Discussion

Here are some of the arguments against certification:

• I’m experienced, why bother with a certification!?
• Everybody can pass an exam by using cheat sheets which are available on the internet!
• If you are certified it doesn’t mean you are skilled! You should get experience!

And most of the time – and probably by no coincidence – it is the people which are not certified that use these arguments. Now I will try to counter those arguments based on my experience with developers.

Reinventing the wheel

“I’m experienced, why bother with a certification!?”
Answer: It keeps you from reinventing the wheel!

Have you noticed it’s always the experienced developers who keep reinventing the wheel? And it is pretty obvious why they reinvent the wheel. They are experienced and know how something can be solved. They don’t know however that that same solution is already in the framework. A framework is like a toolbox and without reading books you will only scrape the surface of that toolbox. One reason for reading books… preparing for an exam.

Real life example:

One day a developer had to show me something awesome he developed. He developed an extension method to transform a SPListItemCollection into a data table. He is an experienced developer so the code looked lovely. Nicely refactored, no duplication etc. etc. But his jaw hit the ground the moment I showed him the “GetDataTable()” method. He wasted a whole day of work(if not more) to reinvent the wheel.

Do you know:

• Mail definitions?
• TraceListeners / Switches / TraceWriters?
• HashSet?
• <system.net><mailSettings>?
• Health Monitor / WebEvents?
• Membership, Authentication and Profile providers?
• DbProviderFactories?

And that’s just to name a few of the things I see reinvented continuously.

And lets be honest. If you are that experienced and if you are so good at what you are doing, how hard can it be to pass an exam? Put your money where your mouth is!

Make yourself useful

“Everybody can pass an exam by using cheat sheets which are available on the internet!”
Reply: You are not everybody!

If you plan to pass an exam by cheating, indeed, don’t bother with it. It’s just that you can not use that as an excuse for yourself. You are responsible for the way you pass your exam, let the others be responsible for the way they pass their exam.

Getting experienced

“If you are certified it doesn’t mean you are skilled! You should get experience!”
Reply: True!

But being certified is a great basis for becoming skilled! You’ve learned what tools there are, and now you can get skilled in using those tools. If you don’t know what tools there are, it will take a lot longer to get skilled and you’ll probably get yourself skilled on how to get a screw into the wall with a hammer.

Conclusion

To me there is NO EXCUSE for not being certified. If you are that good and experienced… prove it. If you can’t prove it, you are probably not as good as you say you are.

Cheers,

Wes

Windows 7 RTM available on Technet.

How to make a Linq to Sql query twice as fast…

An important action with every LinqToSql query is to transform your lambda’s into T-SQL. In simple queries this can take half of the time. By compiling the query you can cache the result of this transformation which saves a lot of processing time on each succeeding query.

There’s a nice write-up over here on how to compile your queries at :http://aspguy.wordpress.com/2008/08/15/speed-up-linq-to-sql-with-compiled-linq-queries/

There’s one tip though I like to mention. If you use simple filter criteria you do not need to create a special SearchCriteria class as mentioned in the post.

var countByCityName = CompiledQuery.Compile(

                        (AdventureWorksDataContext dc, string cityName) =>

                            (from CustomerAddress a in dc.CustomerAddresses

                             where a.Address.City == cityName

                             select a).Count()

                      );

Deadly Poison of Lazy Loading and not following Design Guidelines

Sometimes I perform a code review and at first glance everything looks fine, but - while I’m digging my way in - I slowly start to encounter some ‘hidden features’. This post is all about the dangers of lazy loading and not following some of the design guidelines.

The case

In this specific case I have been asked to review a library because it’s not performing very well. They have this data intensive application library and some single requests can take up to 22 seconds to return a result. So I started to look at some of those requests and at first all looks pretty well. It’s just that some actions take a long time while they don’t seem to perform any real work at all.

For example:

public Tree<Product> LoadProductsTree(CustomerId customerId){

    Tree<Product> produktTree = new Tree<Product>();

    foreach(Product product in GetProducts(customerId)){

       TreeNode productNode = CreateTreeNode(product);

       productTree.Add(productNode);

    }

    return productTree;

}

public static TreeNode CreateTreeNode(Product item) {

    TreeNode node = new TreeNode();

    node.Title = item.Title;

    node.Description = item.Description;

    return node;

}

Lazy loading

The underlying problem turns out to be a combination of two very common design mistakes. The first one is lazy loading of properties which are almost always needed when someone uses the object. The second one is using properties instead of methods.

In the above code, both Title and Description properties are language specific and require a join in the underlying database between two tables. The designer of the code thought it might be expensive to retrieve this data and so decided to use lazy loading for these properties. But he forgot one very important rule to know about lazy loading: Lazy loading of properties, is only of use if – most of the time – you don’t use these properties. Since Title is such a prominent part of a product, it simply does not apply for lazy loading.

Design Guidelines conventions

The reason it took me some time to find out what the problem was with this library, is due to the fact there is no way for me to know that accessing the Title property results in a database call. The biggest problem is, that most of their developers don’t know either which properties in this library result in database calls. That’s all because they’ve used properties where they should have used methods.
A method indicates work to be done, and a property should not perform a lot more work than retrieving a field value. If they wanted the Title and Description values to be lazy loaded, these ‘properties’ should have been methods named GetTitle() and GetDescription().

Deadly Poison

If they followed the design guidelines on properties and methods, they would have known what the problem is with this library. They probable would have noticed all the Get methods being called a lot. If they would have loaded these properties eagerly they wouldn’t have this problem either. It’s just a combination of the two that was poison to the performance. While profiling I discovered that one simple WCF request could result in no less than 23.000 connections and queries to their database and knowing that, it’s no wonder that this does take some time…

Conclusion

1. Do get as much of the data you need, but no more, in as few as possible requests to the database.
2. As soon as you have to retrieve a value from the database it's not a property no more.
3. Be aware that LinqToSql also uses a lazy loading pattern for joins by default and there's also no hint at all that we are going back to the database with those lazy properties either!
4. Entity Framework 4.0 allows for lazy loading but it's not the default.

SharePoint 2007 fully certified

Finally...

I was certified in a lot of things but SharePoint. Today however I've finished my last SharePoint exam with succes. I've passed all WSS en Moss exams.

Up to the next ones!(Maybe the next round of MCM?)

Cheers,

Wes

What makes a good manager?

Who’s doing what?

In my life time I’ve only met three good managers and they share some common characteristics which made them so good at what they are doing. In this post I’ll try to determine what it is that makes them that good while writing.

Directions

The first thing to get people to run in the right direction is to give them bearings. Which direction do we want to be running? A manager should be responsible for that and on the steering wheel at all times. If we leave it up to the young and eager developers we’ll all be running really hard, but probably in opposite directions. The first thing a manager needs to do is direct people in the right way. This cannot be forced however!

Leadership

If somebody is being force fed he’ll throw up eventually, leaving a big mess behind. The thing with good managers is that the people usually come to them to ask them for advise on how and what to do. It is a combination of both knowledge and a natural born leadership capacity that makes this work. You can not rest assured however that this will continue to work without effort.

Patience

People will only keep continue to ask their manager on what and how to do things as long as the manager is approachable and patience. A good manager cannot be out of office most of the day and should take time to really send somebody in the right direction. That takes skills.

Teaching

Teaching is one of those skills. A good manager will not give you the answer to your questions. It will ask you questions that will get you to your answer. A good manager is like an oracle. Teaching is not about using a red pen and pointing out what somebody is not good at. It is the opposite way around.

Appointments

Another thing that makes a manager a good manager is that he or she can delegate responsibilities without losing track. They’ll have to have authority to make people work for them and they should not be afraid to point out to someone that he or she is lacking behind. Again, this cannot be forced, it’s got to be something that comes naturally.

Bonding

The last thing I would like to mention is bonding. A good manager is the glue of a group of workers. All workers need to trust the manager to cover their backs if they do what they were told to do. A good manager can scent frustrations within a group, and knows when to act.

Conclusion

Of course this post isn’t complete at all. The three good managers I met are far more complicated and interesting than I can describe in one blog post. Please add to my list using the comments if you like.

Cheers,

Wes

P.S. I would really like to thank Karin van Klink, Fred van Luttikhuizen and Beat Nideröst.

DownloadAsZip SharePoint Feature RTW

I have to admit. I am late! Much to late! I promised you all a DownloadAsZip feature for SharePoint in a few days and it took me more than a month. But finally it’s here. First have a look at what it’s actually all about.

A picture says so much more

Hover the images for explanation…

How did we get to this result?

I’ve explained how to create the multi select button and how to create the download handler in two previous posts. So now I’m going to focus on the last part. The ‘DownloadAsZip’ buttons.

Easy peasy

Let’s start with the easiest part. With the handler in place we can add custom actions to list items simply by adding some CustomAction elements to our Elements manifest file. Here’s what the elements.xml looks like:

<?xml version="1.0" encoding="utf-8" ?>

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

  ...

  <CustomAction

    Id="{18A0608F-7917-4fa3-8164-18E81B55A551}"

    ImageUrl="/_layouts/images/ICZIP.GIF"

    Title="Download as Zip"

    Description="Download all files in this folder and view as one zip"

    RegistrationType="ContentType"

    RegistrationId="0x0120"

    Location="EditControlBlock">

    <UrlAction Url="{SiteUrl}/_layouts/DownloadAsZip.ashx?List={ListId}&amp;Item={ItemId}" />

  </CustomAction>

  <CustomAction

    Id="{175D475D-C962-4965-9C9B-7CAFBB36A669}"

    ImageUrl="/_layouts/images/ICZIP.GIF"

    Title="Download as Zip"

    Description="Download this file as zip"

    RegistrationType="ContentType"

    RegistrationId="0x0101"

    Location="EditControlBlock">

    <UrlAction Url="{SiteUrl}/_layouts/DownloadAsZip.ashx?List={ListId}&amp;Item={ItemId}" />

  </CustomAction>

</Elements>

What this fragment does is that it adds two buttons to the edit control block. One for files (RegistrationId=”0x0101”) and one for folders (RegistrationId=”0x0120”). These custom actions simply redirect the client to the previously created DownloadAsZip.ashx with the correct query string values.

So this adds single item DownloadAsZip functionality to our SharePoint site.

Little bit more difficult

The code for the DownloadAsZip button on the ListView level is somewhat more difficult but still not to complicated. The code looks like this:

//-----------------------------------------------------------------------

// <copyright file="DownloadAsZipAction.cs" company="motion10">

//     Copyright (c) motion10. All rights reserved.

// </copyright>

//-----------------------------------------------------------------------

using System.Globalization;

using System.Security.Permissions;

using System.Web;

using System.Web.UI;

using Microsoft.SharePoint;

using Microsoft.SharePoint.Security;

using Microsoft.SharePoint.WebControls;

using Microsoft.SharePoint.WebPartPages;

namespace Motion10.SharePoint2007 {

    public class DownloadAsZipAction : MenuItemTemplate {

        /// <summary>

        /// Initializes a new instance of the <see cref="DownloadViewAsZipAction"/> class.

        /// </summary>

        public DownloadAsZipAction()

            : base("Download as Zip", "/_layouts/images/ICZIP.GIF") {

            base.Description = "Download selected files or complete view if no items selected.";

        }

        /// <summary>

        /// Sends the content of the control to the specified <see cref="T:System.Web.UI.HtmlTextWriter"></see> object, which writes the content that is rendered on the client.

        /// </summary>

        /// <param name="output">The HtmlTextWriter object that receives the server control content.</param>

        [AspNetHostingPermission(SecurityAction.LinkDemand, Level=AspNetHostingPermissionLevel.Minimal)]

        [SharePointPermission(SecurityAction.LinkDemand, ObjectModel=true)]

        protected override void Render(System.Web.UI.HtmlTextWriter output) {

            ListViewWebPart listViewWebPart = FindListView(this.Parent);

            if (listViewWebPart == null) {

                this.Visible = false;

            }

            if (this.Visible) {

                string navigateUrl = string.Format(CultureInfo.InvariantCulture,

                                                   "{0}/_layouts/DownloadAsZip.ashx?List={1}&View={2}&Item=",

                                                   SPContext.Current.Web.Url,

                                                   listViewWebPart.ListName,

                                                   listViewWebPart.ViewGuid);

                string clientClick = string.Format(CultureInfo.InvariantCulture,

                                                   "window.location = '{0}' + GetSelectedItemsString('WebPart{1}')",

                                                   navigateUrl,

                                                   listViewWebPart.Qualifier);

                this.ClientOnClickScript = clientClick;

            }

            base.Render(output);

        }

        protected ListViewWebPart FindListView(Control parent) {

            ListViewWebPart retVal = parent as ListViewWebPart;

            if (retVal != null) {

                return retVal;

            }

            if (parent.Parent == null) return null;

            return FindListView(parent.Parent);

        }

    }

}

The code is pretty straightforward. We create a class that inherits the MenuItemTemplate class and we change the ClientOnClickScript property in the render method. What the result of this is, is that client will be redirected to the DownloadAsZip.ashx with the selected items in the querystring. The javascript is explained in one of the previous posts.

To add this button to the ListView we need to adjust our elements.xml a little. We actually add two more CustomAction elements in there.

<CustomAction

  Id="{DE394AD0-0A8E-4e5c-B246-A498BA2A7FB2}"

  Title="Download as Zip"

  RegistrationType="List"

  RegistrationId="101"

  Location="Microsoft.SharePoint.StandardMenu"

  GroupId="ActionsMenu"

  ControlAssembly="SharePointSolutionPack, Version=1.0.0.0, Culture=neutral, PublicKeyToken=4a7cd02bdf107f7a"

  ControlClass="Motion10.SharePoint2007.DownloadAsZipAction">

</CustomAction>

<CustomAction

  Id="{CB4BE13C-C095-4a02-B875-787325045759}"

  Title="Enable item selection"

  RegistrationType="List"

  RegistrationId="101"

  Location="Microsoft.SharePoint.StandardMenu"

  GroupId="ActionsMenu"

  ControlAssembly="SharePointSolutionPack, Version=1.0.0.0, Culture=neutral, PublicKeyToken=4a7cd02bdf107f7a"

  ControlClass="Motion10.SharePoint2007.SelectItemsAction">

</CustomAction>

The second one is to add the ‘Enable item selection’ button and the first one to add the ‘Download as Zip’ button. Pay attention to the RegistrationId again. We add this button only to lists that inherit from the Document Library.

Wrapping it up

Well if you read all of the posts you’ve noticed that this is a pretty complex feature. We have a dependency on the jQuery feature. A lot of JavaScript. A custom HttpHandler etc. etc.

If you don’t know how to create solution packages yourself. Or if you don’t have the time to read all posts and create your own packages you can download the motion10 SharePoint Solution Pack.  I’ve updated the Share Point Solution Pack to contain all these and and some more features.

!Please be smart and test the solution package in a test environment!

Cheers and have fun,

Wes

BizTalk + SharePoint: 1+1=3

You probably wondered why my blog has been quiet the last two weeks. Well, today my colleague Gijs in ‘t Veld and I published our white paper on BizTalk Server and SharePoint integration, describing best practices for providing a “face” to BizTalk Server. It describes three examples: Exception Handling, Manager Approval and BizTalk Dashboard.

We cover a lot of integration features of SharePoint so even if you are not interested in BizTalk at all, you might want to have a look at this whitepaper. It contains complete walkthroughs, tips and explanations,  on:

• How to configure Excel Services to run in delegation mode
• How to create and use Data Connection Libraries in SharePoint
• How to integrate SharePoint, Excel and Analysis Services 2008
• How to create and publish InfoPath forms using InfoPath Forms Services
• How to integrate SharePoint task lists with Microsoft Outlook
• How to create a workflow with SharePoint Designer
• How to design large SharePoint lists
• How to design for large SharePoint sites
• How to design SharePoint authorization
• And more!

You can download it here: http://www.motion10.com/best-practices/view.asp.

Cheers,

Wesley

DownloadAsZip SharePoint Feature Part II

Sometimes I get a very simple question which results in a not so simple solution. And this was one of those.

“How can I download multiple files and or folder from a SharePoint list at once?”

My first response was to use ‘explorer view’ but it lacks a lot of functionality. How about filters or views applied? As soon as you select the explorer view, all filters are dropped. How about cross browser support? Explorer view is available on IE only. So explorer view is more an ‘all or nothing IE only’ solution.

The idea for another SharePoint customization was born….

Two issues

Actually we’re dealing with two issues here. The first one is that we do not have a way to select multiple items from a SharePoint list view. The second one is to actually download those selected files.

I decided to really cut them loose. Because the feature of selecting multiple items could come in handy for other features than downloading as well. I can think of mailing them as attachment, deleting multiple items at once, send them to the repository etc. etc. etc.

First things first, so I solved the selection of multiple items in one of my previous posts. Today we’re going to create the code for our http handler.

Considerations

Just to be short. We do NOT want to create a file on the server and then send it to the client and we definitely do NOT want to write the zip file to a memory stream before sending it to the client either for multiple reasons.

1. Where to leave the temporary files and when to cleanup?
2. What will a client request for 3Gb. of data will do with my memory?
3. What about timeouts if the creation of a file takes 3 minutes?

We have only one option left and that’s to create the zip stream on the fly and hook it to our response stream. We don’t have any timeout issues because the client will start to receive bytes right away. We don’t have any issues with file storage and cleanup and if we use a buffer of only 4k for our stream we don’t have any memory considerations left.

! Do keep in mind though that compacting a file does cost a lot of processing power. Unfortunately that’s not something we can solve. !

Http Handler

I can’t stress this out enough: Don’t use a Page to stream data to your client. Just don’t. It is an http handler indeed but it will introduce a lot of overhead for nothing. You should create your own class that implements the IHttpHandler interface. So that’s what we’ll do.

We need some query string variable in there for us to know which files to add to the zip. So let’s outline them:

• List
  - required id of the list
• View
  - optional id of the view. We’ll use the default view if no view is given.
• Item
  - optional comma separated list of item ids. If no items are selected the complete view will be added to the zip.

So the start of the ProcessRequest method in our custom handler looks like this:

/// <summary>

/// Enables processing of HTTP Web requests by a custom HttpHandler that implements the <see cref="T:System.Web.IHttpHandler"/> interface.

/// </summary>

/// <param name="context">An <see cref="T:System.Web.HttpContext"/> object that provides references to the intrinsic server objects (for example, Request, Response, Session, and Server) used to service HTTP requests.</param>

public void ProcessRequest(HttpContext context) {

    HttpResponse response = context.Response;

    string fileName = string.Concat(DateTime.Now.ToFileTimeUtc(), ".zip");

    string contentDisposition = string.Concat("attachment; filename=\"", fileName, "\"");

    response.Clear();

    response.ContentType = "application/zip";

    response.AddHeader("Content-Disposition", contentDisposition);

    response.Buffer = false;

    NameValueCollection queryString = context.Request.QueryString;

    string listId = queryString["List"];

    if (string.IsNullOrEmpty(listId)) {

        throw new WebException("Required query string parameter 'List' is not defined.");

    }

    string viewId = queryString["View"];

    string itemsString = queryString["Item"];

We clear the response stream and add the necessary headers before we send any content to the response stream. I decided to use DateTime.Now.ToFileTimeUtc() as the proposed filename to the client. We simply extract the query string parameters and check if the required List parameter is indeed there. If not we throw a WebException which SharePoint will graciously handle for us.

! What’s very important to notice here is that we set response.Buffer to false. In this way, everything we write to the response stream will be sent to the client right away. If we don’t set response.Buffer to false… we’ll have memory issues in no time. !

Zip Library

Although the .NET framework has a DeflateStream class to compress streams it still lacks the creation of zip files. In order to create a zip file you need to create file headers and stuff so I decided to use another library for it. First I went for the SharpZipLib but it has a GPL license which is not always permissible in a company so I looked around a little and there’s a very good alternative in the form of DotNetZip. Have a look at the conversation I had with one of the guys over here on how to solve some issues with it. They are really helpful! I didn’t have the time to rewrite my initial version of DownloadAsZip but I probably will do so in the future.

For now the code is based on the SharpZipLib and the rest of the ProcessRequest method looks like this:

SPWeb currentWeb = SPContext.Current.Web;

SPList selectedList = currentWeb.Lists[new Guid(listId)];

using (ZipOutputStream zipOutputStream = new ZipOutputStream(context.Response.OutputStream)) {

    zipOutputStream.SetLevel(9);

    if (!string.IsNullOrEmpty(itemsString)) {

        string[] items = itemsString.Split(',');

        foreach (string item in items) {

            int itemId;

            if (int.TryParse(item, out itemId)) {

                SPListItem listItem = selectedList.GetItemById(itemId);

                switch (listItem.FileSystemObjectType) {

                    case SPFileSystemObjectType.File:

                        zipOutputStream.AddSPFile(listItem.File);

                        break;

                    case SPFileSystemObjectType.Folder:

                        zipOutputStream.AddSPFolder(listItem.Folder);

                        break;

                    default:

                        throw new FileNotFoundException("No such file or folder.");

                }

            }

        }

    }

    else {

        SPView selectedView = selectedList.DefaultView;

        if (!string.IsNullOrEmpty(viewId)) {

            selectedView = selectedList.GetView(new Guid(viewId));

        }

        SPListItemCollection selectedItems = selectedList.GetItems(selectedView);

        zipOutputStream.AddSPListItemCollection(selectedItems);

    }

}

context.Response.End();

}

A few important things to notice.

In line 4 we create a ZipOutputStream and tie it to the OutputStream and on line five we set the compression level to the highest possible. Unfortunately there’s no documentation on what this does to performance and what’s the benefits in terms of compression ratio. It’s something we’ll still have to figure out.

And secondly we write files and folders to our (now zipped) response stream with the methods:

• AddSPFile(SPFile)
• AddSPFolder(SPFolder)
• AddSPListItemCollection(SPListItemCollection)

Extension Methods

The guys of the SharpZipLib did not implement the methods to add SharePoint files to a ZipOutputStream. That’s something we have to do ourselves. I started with a few static methods to add items and I found myself passing the ZipOutputStream object continuously from one method to the other. With extension methods this is something we can refactor to a very nice and clean solution with virtualy no extra effort. So here’s the code for the accompanying ZipUtility class:

//-----------------------------------------------------------------------

// <copyright file="ZipUtility.cs" company="motion10">

//     Copyright (c) motion10. All rights reserved.

// </copyright>

//-----------------------------------------------------------------------

using System.IO;

using ICSharpCode.SharpZipLib.Zip;

using Microsoft.SharePoint;

namespace Motion10.SharePoint2007.Zip {

    public static class ZipUtility {

        /// <summary>

        /// Adds the SPListItemCollection to the zip output stream.

        /// </summary>

        /// <param name="zipOutputStream">The zip output stream.</param>

        /// <param name="listItemCollection">The list item collection.</param>

        public static void AddSPListItemCollection(this ZipOutputStream zipOutputStream, SPListItemCollection listItemCollection) {

            foreach (SPListItem listItem in listItemCollection) {

                switch (listItem.FileSystemObjectType) {

                    case SPFileSystemObjectType.File:

                        zipOutputStream.AddSPFile(listItem.File);

                        break;

                    case SPFileSystemObjectType.Folder:

                        zipOutputStream.AddSPFolder(listItem.Folder);

                        break;

                }

            }

        }

        /// <summary>

        /// Adds the SPFolder to the zip output stream.

        /// </summary>

        /// <param name="zipOutputStream">The zip output stream.</param>

        /// <param name="folder">The folder.</param>

        public static void AddSPFolder(this ZipOutputStream zipOutputStream, SPFolder folder) {

            zipOutputStream.AddSPFolder(folder, string.Empty);

        }

        /// <summary>

        /// Adds the SPFolder to the zip output stream.

        /// </summary>

        /// <param name="zipOutputStream">The zip output stream.</param>

        /// <param name="folder">The folder.</param>

        /// <param name="path">The path.</param>

        public static void AddSPFolder(this ZipOutputStream zipOutputStream, SPFolder folder, string path) {

            path = Path.Combine(path, folder.Name);

            ZipEntry entry = new ZipEntry(path + "/");

            zipOutputStream.PutNextEntry(entry);

            foreach (SPFile file in folder.Files) {

                zipOutputStream.AddSPFile(file, path);

            }

            foreach (SPFolder subFolder in folder.SubFolders) {

                AddSPFolder(zipOutputStream, subFolder, path);

            }

        }

        /// <summary>

        /// Adds the SPFile to the zip output stream.

        /// </summary>

        /// <param name="zipOutputStream">The zip output stream.</param>

        /// <param name="file">The file.</param>

        public static void AddSPFile(this ZipOutputStream zipOutputStream, SPFile file) {

            zipOutputStream.AddSPFile(file, string.Empty);

        }

        /// <summary>

        /// Adds the SPFile to the zip output stream.

        /// </summary>

        /// <param name="zipOutputStream">The zip output stream.</param>

        /// <param name="file">The file.</param>

        /// <param name="path">The path.</param>

        public static void AddSPFile(this ZipOutputStream zipOutputStream, SPFile file, string path) {

            string filePath = Path.Combine(path, file.Name);

            ZipEntry entry = new ZipEntry(filePath);

            entry.DateTime = file.TimeCreated;

            zipOutputStream.PutNextEntry(entry);

            using (Stream fileStream = file.OpenBinaryStream()) {

                byte[] buffer = new byte[4096];

                int sourceBytes;

                do {

                    sourceBytes = fileStream.Read(buffer, 0, buffer.Length);

                    zipOutputStream.Write(buffer, 0, sourceBytes);

                } while (sourceBytes > 0);

            }

        }

    }

}

Please take a look at the code carefully. What’s really important to notice here is that we always add one file at a time and while doing so we always have just one file stream opened inside a using statement(see AddSPFile method). Another important thing to notice is that we use a small buffer of only 4k. This is to make sure we don’t create a huge memory monster or choke SharePoint with many open file streams.

Implementing the handler

We have the code for the handler now but we have not yet registered this handler with IIS. There are two way to do so.

1. Register the handler in the Web.config
   (we’ve edited the web.config before)
2. Create an ashx file and add it to the _layouts directory

This time I decided to go for the last option. Because it has some minor advantages. The content of the DownloadAsZip.ashx file looks like this:

<%@ Assembly Name="SharePointSolutionPack, Version=1.0.0.0, Culture=neutral, PublicKeyToken=4a7cd02bdf107f7a"%>

<%@ WebHandler Language="C#" Class="Motion10.SharePoint2007.DownloadAsZipHandler" %>

This of course doesn’t contain any real code as that’s all in our DownloadAsZipHandler class.

Conclusion

I know I promised a complete solution in my previous post but you guys and girls have to wait a few more days for the button. This post will be way to long if I’ll go through the code for the DownloadAsZip custom action as well. Just bare with me for a few more days.

And please DO leave comments if you like or dislike anything you read on this blog. I’m always in for improvements.

Cheers and have fun,

Wes

Solving the “Data Refresh Failed” error message.

I’m pretty convenient with SharePoint and MOSS but sometimes get bitten in the rear if I try to do something on autopilot. While working on a presentation about the combination of SharePoint and BizTalk I wanted to insert an Excel sheet in SharePoint to show some BAM data. This is simply a cube on analysis services. After doing all the stuff I normally do I expected all to work but BOOM!:

Oh no! Let’s go through the steps again.

The standard steps to perform

1. Use STSadm to configure the use of delegation
   
   
2. Create a Data Connection Library and Report Library
    
   
   
3. Configure the SharedServiceProvider to trust my Data Connection Library
   
   
4. Configure the SharedServiceProvider to trust my Report Library
   
   
5. Create a Data Connection with Excel
   
   
6. Export the Data Connection
   
   
7. Save the Data Connection File to our trusted library and approve it
   
   
8. Create an Excel sheet using the connection file from the library
   
   
9. Design and publish the sheet to our trusted file location
    

But still it doesn’t work. I can use the connection from the library in Excel. I can view the sheet, but I can’t update the connection!

How come it doesn’t work!

Viewed the SharePoint logs. Nothing in there. Viewed the event viewer. Nothing in there.

Hmmm… lets use SQL server profiler to see if we actually get to login to the database. Strange, I don’t see a trace for Audit login when I try to connect. Ok this tells us that it’s a SharePoint issue at first.

Ok, maybe it’s permissions. Allowed all authenticated users “Full control” to both the data connection library and the excel sheet but this is not the issue. Didn’t think it would be cause View rights are enough but ok. What else?

I forgot something!

Going through a walkthrough can sometimes lead you to a forgotten step. If you decide to do a walkthrough please don’t be ignorant and perform and check each and every step and don’t skip one because you think you’ve done it correctly. The walkthrough I used is the Plan external data connections for Excel Services its a great resource which explains a lot of the details of Excel Services. I almost decided to skip the first step but fortunately I didn’t.

The first step tells you to go to the ‘Trusted data providers’ section and add your provider. I never had to do that before because this library contains most of the standard data providers already.

This time however I was developing all this on Windows Server 2008 with SQL Server 2008 and as you can see in one of the images above(step 6 and 8) we use the MSOLAP.4 provider and that’s not in there!!!

After searching for all this time it was just a matter of adding the data provider name to the trusted data provider library and it al worked like a charm!

Conclusion

I do hope this story / walkthrough is of any help to y’all. I took me quite some time to figure out. Next time I’ll definitely take all steps in a walkthrough!

Cheers,

Wes