Happy Birthday to Me

Today is my birthday.

I was surprised when I brought up Google this morning and the "Google Doodle" showed a cupcake, a present, and ribbon. I thought, "What a coincidence. I wonder which famous person also has my birthday?" After all, Google is known for celebrating birthdays of celebrities.

When I hovered over the doodle, I was taken aback! It said, "Happy Birthday Steve!"

Google knew that it was my birthday and changed my Google homepage in celebration of me!

I have no clue what you other less-important people are seeing on your Google homepages, but for me, I'll take my cupcake and present! :-)

[By the way, if I click on the doodle I'm taken to my Google profile page.]

The Right Way to Version Your Assemblies

No, I'm not talking about whether you have Major, Minor, Patch, and Build numbers.  I'm not talking about when you increment which number.  My suggestion is to do whatever works for you in those cases, though I reserve the right to blog about that in the future.
What I am talking about is how you can structure your solution and project files in Visual Studio to make updating your assemblies' version number much easier.
This blog post makes some assumptions:

• You are using version numbers in your assemblies
• You are using a recent version of Visual Studio (I know these steps work with both Visual Studio 2008 and 2010)
• You have multiple assemblies in one Visual Studio solution
• You want to update all of your assemblies' version numbers at the same time and to the same version number

Those assumptions cover a vast majority of the projects I've worked on, but I'll admit that there may be projects out there that might not fit into the mold I've set forth.  I'm OK with that.  This blog post isn't for you if your mold is different.
So let's get started.
I created a solution file which contains several projects.  There's an MVC2 web application project, its associated test project, a business objects project, and a data access layer project.  These are all empty projects for this example—only the structure of the solution and projects is important for my purposes.  Solution explorer for this project is shown here.
The first step is to separate out the version information (and any other information that is common from assembly to assembly if you wish, like company name, product name, and so on) into its own file.  Open up one of the AssemblyInfo.cs files to see what I'm talking about.

Notice that the settings are assembly attributes.  Some people don't realize that you don't have to put all the assembly attributes in one file.  I'm going to take advantage of that fact to accomplish what I set out to do: change the version in one place and have all projects updated at once.  The other key is using a solution-level file and linking to is from each project.
Visual Studio supports files at the solution level as well as at the project level.  You can put anything you want at the solution level and it will be carried along with the solution, though it won't get included in any output unless you write some custom build rules to grab the file and do something with it.  On some projects I've included a Word document about the solution and its projects as a solution level file.
For this example, we're going to create a new C# source file and include it at the solution level.  I'm going to call this new file, "VersionInfo.cs".
Step 1: Create a new file and call it "VersionInfo.cs".  Do this by choosing "File | New | File…" or press Ctrl-N in Visual Studio.  Choose "Visual C# Class" as the file type.
Step 2: Erase everything in this file.  Save it as VersionInfo.cs in the same folder as the solution file.
Step 3: Highlight the version information lines from the AssemblyInfo.cs file from earlier, cut it from that file (Ctrl-X) and paste it in VersionInfo.cs (Ctrl-V).  Add "using System.Reflection;" to the top of VersionInfo.cs to avoid compilation errors.
Step 4: Add the VersionInfo.cs file to the solution by right-clicking on the solution file, choosing "Add | Existing Item…", then selecting "VersionInfo.cs" (find it in the solution's folder where you just saved it), and then click "Add".  You should see a new "Solution Items" folder under your solution name with the VersionInfo.cs file in it (see screenshot).
Step 5: Add the VersionInfo.cs file to each project as a linked file. See below for instructions on how to do this.
Step 6: Remove the version information from the other projects' AssemblyInfo.cs files.
At this point you should be able to build your solution successfully, and each assembly should have the same version information—the information found in VersionInfo.cs.
So what is a "Linked File" and how do I create one?  Read on.
Right-click on the project and choose "Add | Existing Item…", navigate up one folder level, and click once (don't double-click!) on the VersionInfo.cs file.  [This assumes that your solution folder is one level up from your project folder!]  Now instead of clicking "Add", click the little down-arrow next to the "Add" button.  Choose "Add As Link".  You should now see VersionInfo.cs in your project, but with a "shortcut" icon overlaid on top of the file's icon.
Once you have completed this step for each project, you can build your solution without errors.
Now to test it out.  Build the solution and look in the bin folder (or "folders" if you haven't added the other projects as references in the web project) for the DLLs.  Look at the version numbers.  They should all read "1.0.0.0".
Now change the version number in the VersionInfo.cs file information.  Rebuild the solution then check the version numbers.  All of the assemblies should now have the new version number.
Notice, however, that it is the "File Version" that you see when hovering over the DLL in Windows Explorer, so if you change only the "Assembly Version" the you'll have to go to the DLL's properties to see your change.
[I must admit, I don't recall which version number is used when putting DLLs into the Global Assembly Cache.  Please leave a comment if you have that information handy!]
So that is the procedure.  If you have an automated build system then you can make use of the fact that the version number is in its own file and update it automatically.  Then each build will produce DLLs with distinct version numbers.  That is a great way to identify the build that a DLL came from if you happen to get a report from the field that there is an error in a specific DLL.
But I'm sure that's never happened to you…right?
[Update: adding to CodeProject]

Completely Empty the SharePoint Recycle Bin

If you have LOTS of stuff in your SharePoint Recycle Bin and want to empty it all, here’s a quick tip:

1. Navigate to the recycle bin page (e.g. http://localhost/_layouts/recyclebin.aspx)

2. In the address bar, type “javascript:emptyItems()” (minus the quotes)

3. Press enter. You will be asked if you really want to empty the recycle bin.

4. Click OK to empty it; click Cancel to leave it alone.

It’s much faster to empty it this way if you have multiple pages of “stuff” in your recycle bin.

ReaderWriterLock FTW!

Recently I was encountering an issue on a SharePoint site where some site properties were getting cached in the HttpContext.Current.Cache.  The logic went roughly like this:

1. Read values from a SharePoint list
2. Put the values into the cache

I'm oversimplifying, of course, but those are the important steps as they relate to this blog post.
I had implemented proper locking, or so I thought, by using the C# lock statement.  The pattern I learned long ago when dealing with creating a shared cache that multiple threads can access is to do something like this:

1. Try accessing the object.
2. If you get it, great! Go forth and use it.
3. If you don't get it, try obtaining a lock (i.e. you're about to enter a critical section).
4. Once you obtain the lock, try accessing the object again, just in case another thread beat you to it.
5. If you get it, great! Release the lock, go forth and use it.
6. If you still don't get it, create it, then put it in the cache.
7. Exit the critical section.

This pattern prevents unnecessary locking if the object already exists in the cache.  It also prevents the object from being created a second time.
As it turns out, the HttpContext.Current.Cache object isn't entirely thread-safe.  Even though I was locking and such, I was unable to retrieve the site properties I was creating.  To the second thread, it appeared that the properties were not in the cache, and the logic would get invoked to create it again.
That's when I did some research and discovered the System.Threading.ReaderWriterLock class.
This gem allows multiple threads to access an object, but only allows one thread at a time to write to that object.  The object has both a reader lock and a writer lock.  Many reader locks are granted as long as there are no writer locks granted (or pending).  If there is a writer lock that is being requested, it blocks until all the reader locks are released.
Another cool feature is that, once you have a reader lock, you can call "UpgradeToWriterLock" if you discover you need to write to the object.  Once you're done, you then "DowngradeFromWriterLock" and then "ReleaseReaderLock".  Or you can simply call "ReleaseLock", I believe, to release all locks obtained.
Best practice for using this object is to wrap it in a try…finally block of code. In the "try" part, acquire the lock and then do your stuff. In the "finally" part, release the lock.  In this manner you ensure that you always release the locks you acquire, and the system never gets "out of balance".
Here's how I used it to solve my problem:

1. Get a reader lock (will block only if another thread is writing)
2. Try to get the properties
3. If obtained, exit and use the properties (the finally kicks in to release the lock)
4. If not obtained, upgrade to a writer lock (will block if any other reader or writer locks are being held).
5. Try to get the properties again (nested try block)
6. If obtained, exit and use the properties (the finally kicks in to downgrade the lock, and then the outer finally kicks in to release the reader lock)
7. If not obtained, create it and return it (the finally blocks kick in as described in step 6)

It's more complex than using a simple lock statement, but I found that it was necessary in this case.
One final implementation note: the ReaderWriterLock object was stored in a "private static readonly" member variable so that it was accessible from any method that needed it, and so that all threads were accessing the same object.

[Update: adding to CodeProject]

Error While Refreshing External Data Source in Excel

I have an Excel 2007 workbook that pulls its data from an Access 2007 database to give users some feedback via charts and graphs.  [For this discussion it doesn't matter that Access actually gets the data via links to lists on a SharePoint site—the error I encountered deals with the connection between Excel and Access.]

I added an "Update" button that the user presses to ensure that all of the data is freshly pulled from Access.  Initially I just called

ThisWorkbook.RefreshAll

However, I received an error at some point that I couldn't pinpoint.  I have about 150 different queries that pull data from Access, and trying to find the one query that was failing was like finding the proverbial needle in a haystack.

So I changed the code to loop though all of the named ranges in the workbook.  I had given all of the data tables names when developing the spreadsheet, and so the new code looks like:

for each name in ThisWorkbook.Names
   name.RefersToRange.ListObject.QueryTable.Refresh
next

[Yes, I check for whether the name object actually refers to a range, and if so, whether that range has a List Object, and if so, whether that List Object has a Query Table to refresh!]

This way I could tell which range was being refreshed and could inform the developer (me) of which range was causing the problem. I could also calculate a percent complete value to display on the status bar, since I knew how many names there were and which name I was currently working on (via a "currentName" variable).

This looked promising, but I again got an error.  Looking at the named range that the code was refreshing when the error occurred, I didn't see anything wrong with it.  I could manually refresh it with no problem.

So I started looking at the properties of the QueryTable object to see if there were any clues in there.  That's when I stumbled upon the "MaintainConnection" property.

I changed the code to this, to see if it made any difference:

for each n in ThisWorkbook.Names
n.RefersToRange.ListObject.QueryTable.MaintainConnection = False
n.RefersToRange.ListObject.QueryTable.Refresh
next

This time when I ran it, it ran to completion without any errors.

So it seems that if you try to maintain your connection to the database, you'll eventually run out of available connections.  What's strange is that the searches I did came up with information about Excel handling up to 255 connections, and I seemed to bump into this problem at about connection number 60 or so.  I'm not sure of the exact count—it could have been more or less than 60, but it was pretty close, I believe.

So if you're attempting this on your own, take it from me: set the MaintainConnection property to False!

Don't Forget to "DoEvents"

I'm writing a reporting package which uses Microsoft Access and Microsoft Excel to gather and chart data collected from a set of custom SharePoint lists.  I have some VBA code which does some calculations on all of the list items before the reports are generated to ensure that the data is the most up-to-date that it can be.

The VBA code lives in Access.  It basically does a lookup on each row in one of my lists and sets values in another list.  The business rules for this update had enough conditions on it that I couldn't implement it in one complex Update statement, thus the looping.

I also discovered that you can very easily add a progress bar to the Access status bar by calling "SysCmd acSysCmdInitMeter, 'message', maximum value" to initialize it, and "SysCmd acSysCmdUpdateMeter, currentValue" to increment the counter.  (To reset the status bar when I'm done, I call "SysCmd acSysCmdRemoveMeter".)  So I added this bit of functionality to the VBA code as a bonus for my users to give them some feedback.  After all, some of the lists I'm working with have a few thousand items in them (don't get me started about best practices stating that 2000 items is the workable maximum—I've already fought that battle and lost!) and it can take some time for the VBA code to complete.

Well wouldn't you know it, but the update code continually ran, and the thread blocked on the SQL command, so the progress bar just sat there, no progressing.  Furthermore, the whole UI appeared to be locked up while the update was running.  I couldn't even pause execution of the VBA code because the IDE wouldn't recognize my mouse clicks on the pause button.

And then I remembered something from my old VB6 days: the DoEvents method.

The DoEvents method relinquishes control of the processor by the VBA engine to allow other processes to do things. Or more accurately in my case, other threads. It was more important to call it when a background thread was doing heavy processing and you wanted to allow the UI thread a chance to handle the mouse clicks, keyboard presses, and other events which had queued up since the last time it got a time slice from the operating system.

I remembered one main warning about using it, though:

• You don't want to call it too often, since an expensive context switch most likely would take place

So I decided to see if it worked in VBA by adding this block of code:

If currentRow Mod 10 = 0 Then
    DoEvents
End If

In this block of code, currentRow is an integer which gets incremented each time through the loop. It is the variable that I pass to the method which updates the progress bar. You can see that I don't call DoEvents each time through the loop—I want the main processing code to actually finish in a reasonable amount of time, after all!

The IDE didn't complain about the method call, so it at least recognized it as valid VBA code.  So far, so good. But will it accomplish what I want it to?

The short answer: YES!

So now, in the latest version of the code, the DoEvents method is called every 10 rows.  This allows the UI to get refreshed, so I can now pause execution and scroll through the source code (albeit in bursts).

But more importantly, the progress bar now shows progress as the code runs!

DoEvents FTW!

SharePoint Exception Occurred 0x80020009 (DISP_E_EXCEPTION) – UPDATE

A colleague pointed me to the following article: Description of the Windows SharePoint Services 3.0 Cumulative Update Server Hotfix Package (Sts.msp): June 30, 2009.  In it, it makes reference to the DISP_E_EXCEPTION error.  Specifically, it states:

SPList.GetItems(SPQuery) fails when the item count reaches the threshold value 2000, and you will receive the following error message:

Exception occurred. (Exception from HRESULT: 0x80020009 (DISP_E_EXCEPTION))

Presumably, if you have a custom query on a list, and that query returns more than 2000 items (which is the case on the pages where I'm encountering this error), you'll get this exception.  The fix for this, supposedly, is in the June 30, 2009 hotfix package.

I'm in the process of requesting that this hotfix be applied to our SharePoint server.  I'll post again once that happens and let you all know whether it fixes this problem as advertised.

While searching for possible causes for this exception, the blog posts I found mentioned improperly-configured load balancers and other maladies, but not this 2000-item limitation nor the hotfix.  Hopefully this post will help someone else out there who is having a similar problem on their system.  Please comment if that person is you!