Once in awhile I run across a need to create a delimited list from a set of rows in a SQL table.  The old-school method is to use a cursor to iterate over these rows and build a dynamic string.  As we all know, cursors are expensive and should be avoided whenever possible.  I recently found a better way to accomplish this and wanted to share it in case you’ve run into this before.

For this example, I’m going to be working with a simple Cities table that contains a City field and a State field.  A simple SELECT statement yields the following result:

select * from Cities where State = 'New York'

The first step is to transform this result into a chunk of XML, and for that the FOR XML statement is perfect.  Consider the following SQL:

declare @xml varchar(1000)
set @xml = (select City from Cities where State = 'New York' for xml path(''))

This returns the following (I trimmed it down a little to fit on the page, but it does return all rows):

<City>New York City</City><City>Buffalo</City><City>Rochester</City>

Now that we have an XML string, we can just use the built-in REPLACE functions to remove the XML nodes:

set @xml = replace(@xml, '<City>', ',')
set @xml = replace(@xml, '</City>','')

This returns the following:

,New York City,Buffalo,Rochester,Yonkers,Syracuse

Now, the only thing left is to remove the beginning space and comma from this for which the new SQL 2008 STUFF function is perfect:

select stuff(@xml,1,1,'')

This returns the following, which is exactly what we need:

New York City,Buffalo,Rochester,Yonkers,Syracuse

Now, chances are you don’t want to have to declare variables and do all this in multiple operations.  This can all be wrapped into a single select statement, which makes it easy to implement:

select stuff((replace(replace(
       (select City from Cities where State = 'New York' for xml path('')), '<City>', ','
       ), '</City>','')),1,1,'')

That’s it!  Pretty slick, huh?

At the end of my last post in the Integrating a Custom Application Into SharePoint series, I had completely forgotten that I promised to describe how to package everything up into features and solution packages, and how to deploy that to SharePoint in a simple and streamlined fashion.  I received a couple comments asking me to make good on my promise, so here it is! Ask and ye shall receive

In my last three posts here, here, and here, I began describing how to integrate a custom ASP.NET application into SharePoint.  The first post focused on the essentials, and detailed how to get your application into the SharePoint LAYOUTS folder structure, specifically where to place your files and how to inherit SharePoint’s look and feel by using its master page.  The second post focused on configuring permissions for your application and also demonstrated a few handy built-in controls that you can leverage to give your application that true SharePoint-like integrated look and feel.  The third post focused on navigation and how to integrate that with the out-of-the-box navigation that is provided with SharePoint.

The concept of SharePoint features and solutions packages is not a secret, and there is no shortage of articles, blogs, and how-to’s that document them.  Instead of reinventing the wheel and writing something up that describes what each of these are, here are a few excerpts I stole…err…borrowed from other sources:

Features

From MSDN:

“Features allow you to test, deploy, and activate custom functionality inside Office SharePoint Server 2007 and provide a way to make functionality available across your server farm. This functionality can be custom workflows, content types, modifications to the user interface, or new templates for lists and document libraries.”

From the SharePoint Developer Blog:

“A SharePoint feature is a module of functionality that can be enabled at specific scopes within a SharePoint farm, namely the farm level, web application level, site collection level, and site level.  SharePoint itself uses features for nearly everything it provides out of the box – the standard list definitions, the built-in site columns & content types, built-in web parts, and they are even used to define what is displayed on the Site Settings page and the Site Actions menu.  Each of the features, both built-in and custom ones, live within the “12” hive at 12\TEMPLATE\FEATURES, and each is contained within its own unique folder, whose name reflects the feature’s purpose.  Within this folder there is one and only one required file, whose name must be feature.xml.  This file defines the basic characteristics of the feature including its ID, name, description, activation scope, and visibility.  Additionally this XML file can define additional information that is specific to the feature itself, such as a receiving assembly, and potentially one or more element manifests.  Each element manifest that is defined is represented by an additional XML file that is also contained within the feature’s folder, and within these files is where the uniqueness of the feature comes out, as they can be used to define the set of files that the feature is going to inject into a given site, or the custom actions the feature will add to the Site Actions menu, or the site columns and/or content types that the feature will add to its targeted site, just to name a few.  Finally, in addition to the element manifest XML files, the feature’s folder can contain any number of other files and folders that are needed for the feature itself, based upon its intended purposes.”

Solution Packages

From MSDN:

“Microsoft Windows SharePoint Services 3.0 introduces a deployment mechanism named "solution packages." A solution package is a CAB file with a .wsp file-name extension that contains all the files that must be deployed on the front-end Web server and a set of XML-based installation instructions. Windows SharePoint Services provides a rich infrastructure that simplifies deployment of solution packages in a Web farm environment.”

From Bill Baer:

“Solution packages are designed to provide the ability to develop and deploy reusable  site and feature definitions, web part files, templates, assemblies, and code access security policies across one or more server farms.  A solution package is a cabinet file that can contain, site and feature definitions, web part files, templates, assemblies, and code access security policies.  A solution package contains a web manifest that that defines the list of features, site definitions, resource files, Web Part files, and assemblies to process when the solution is deployed.  The directory structure within the cabinet file dictates the resulting structure on the web front-end computer when the solution is deployed.”

 
In the simplest terms, a feature is used to deploy something to SharePoint.  One or more features can then be packaged up into a solution package, and that solution package can then be deployed to SharePoint.  Got it?

So, what can you use a SharePoint features to deploy?  Pretty much any custom development artifact.  Here are just a few of the items that come to mind:

• Web Parts
• Event Handlers
• User Controls
• Visual Studio-authored Workflows
• Site Columns
• Content Types
• Site Definitions
• List Templates
• CSS style sheets
• JavaScript files
• LAYOUTS application pages
• THEMES (I describe how here)
• Custom Actions
• Delegate Controls
• Master Pages/Page Layouts/Files/Documents/List Items
• Site Definitions
• Web Services
• WCF Services
• HTTP Modules
• HTTP Handlers
• Executing custom code when the feature is activated/deactivated/installed/uninstalled
• Staple other features onto existing site definitions

If you’ve developed any of these items, then you should know that many of these artifacts are deployed to somewhere in SharePoint’s “12 Hive” folder structure.  Certain types of files belong in certain places in the 12 Hive.  For example, images belong in the ~12\TEMPLATE\IMAGES folder, application pages belong in teh ~12\TEMPLATE\LAYOUTS folder, etc.  If you’re building a custom web part, you will be placing your web part assembly either in the GAC or in SharePoint’s bin directory.  If you’re deploying style sheets, then you need to place that in a different location.  What am I getting at here?  9 times out of 10 when we’re deploying custom development artifacts to SharePoint, we will be placing many files in multiple locations.  This is very troublesome and error-prone, because 1.) it’s very easy to miss one, or put a file in the wrong directory, 2.) it’s a tedious manual process, and 3.) it’s not the right way to do it.

To illustrate what I’m talking about, let’s examine the Visual Studio project I put together for this blog series.  If you take a look at the following screenshot of how I have my solution set up, the important thing to notice is my folder structure, which mimics that of the 12 Hive.  Inside these standard SharePoint folders, I place custom folders specific to the project I’m working on.  There’s a very good reason for this.  Let’s say you’re deploying a custom image to the IMAGES directory.  There are over 2,000 images in that folder, and adding yours into that folder makes it hard to find and is much less maintainable.  A better practice is to add your own sub-folder, then add your images to that which will isolate your files that belong to their respective solution.

As you can see, I’m deploying a lot of different files. I have some images, a script file, a style sheet, etc.  I also have a feature that will be used to provision Custom Actions, as I describe here.  Remember, a feature is just an XML file that describes what the feature does and what should be included with it.  My feature for the above solution is pretty simple, and only includes the CustomActions.xml manifest file reference:

<?xml version="1.0" encoding="utf-8"?>
<Feature
  Id="5856617E-BED2-4705-B030-735F7483225E"
  Title="DevExpertise Layouts Application"
  Description="Contains the necessary components for the DevExpertise custom LAYOUTS application."
  Version="1.0.0.0"
  Scope="Web"
  Hidden="false"
  ImageUrl="DevExpertise\devexpertiseLogo.png"
  ReceiverAssembly="DevExpertise.LayoutsApp, Version=1.0.0.0, culture=neutral, PublicKeyToken=d39eedb6cff9b1c8"
  ReceiverClass="DevExpertise.LayoutsApp.FeatureReceiver"
  xmlns="http://schemas.microsoft.com/sharepoint/">
  <ElementManifests>
    <ElementManifest Location="CustomActions.xml" />
  </ElementManifests>
</Feature>

Now it’s time to deploy everything.  While I could just copy all of these files to the file system manually, then install the Custom Actions feature on the SharePoint site, I instead bundle everything up in a single deployable solution package. Enter WSPBuilder.

Without getting into the nasty details of actually how to create a solution package from scratch, I will say that it’s a nightmare.  A solution package is a .WSP file, which is really a .CAB file.  To create .CAB files, you use MakeCab.exe, which involves creating your own .DDF file and XML manifest.  It’s ugly, trust me.  WSPBuilder eliminates the need to manually build these files, and offers a simple command-line interface to build the package, which traverses a 12 Hive folder structure and creates the solution automatically.

Most anything I do frequently, I have a script for.  Creating solution packages is no exception.  First, in my VS solution folder on the file system, I created a Solution folder that my script will generate the package in.  In addition, I included a 12 folder and a GAC folder.  The 12 folder will obviously contain the folder structure for the 12 Hive, and the GAC folder will contain all assemblies that will need to be deployed to the GAC.  WSPBuilder automatically builds this into the solution package for us.  To manage my solution creation and deployment, I use 2 scripts: wsp.bat to build it, and install.bat to deploy it.

My wsp.bat is as follows (NOTE, the last command that builds the package should all be on one line.  I had to break it up to fit into this post):

@SET WSPPBUILDER="C:\Tools\WspBuilder\WspBuilder.exe"
@SET SOLUTIONNAME=DevExpertise.LayoutsApp.wsp
@SET URL=http://server
@SET BUILD=Debug

ECHO Copying Files to Temporary Solution Directory
  xcopy bin\DevExpertise.LayoutsApp.dll Solution\GAC\ /y /r

ECHO Building Solution Package
  %WSPPBUILDER% -CreateWSPFileList wspfiles.txt -outputpath solution
    -12path Solution\12 -gacpath Solution\GAC -Excludepaths bin
    -createfolder true -wspname %SOLUTIONNAME%

This script only generates the .WSP file; I still need a script to install it.  My install.bat file is as follows:

@SET STSADM="C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN\STSADM"
@SET SOLUTIONNAME=DevExpertise.LayoutsApp.wsp
@SET URL=http://server/

ECHO Removing Existing Solution
  %STSADM% -o retractsolution -name %SOLUTIONNAME% -url %URL% -immediate
  %STSADM% -o execadmsvcjobs
  %STSADM% -o deletesolution -name %SOLUTIONNAME%

ECHO Installing Solution
  %STSADM% -o addsolution -filename Solution\%SOLUTIONNAME%
  %STSADM% -o deploysolution -name %SOLUTIONNAME% -url %URL%  -immediate -allowGacDeployment -force
  %STSADM% -o execadmsvcjobs 

ECHO Recycling App Pool
  iisapp /a "Sharepoint - 80" /r

 
Now, you could just as easily fold both of these scripts into 1, but I like to be able to generate the solution package without actually deploying it sometimes.  Whatever floats your boat I suppose.

Once your feature is installed, you will see it appear in Central Administration > Operations > Solution Management:

Remember, this particular solution package is essentially responsible for 3 things: deploying files to the file system, adding an assembly to the GAC, and creating a web-scoped feature.  Once the solution is deployed, these will automatically be done for you:

Some of the files that were deployed to the LAYOUTS folder:

The assembly installed in the GAC:

And finally the web-scoped feature:

Good stuff, huh?  Hopefully you can see how easy it is to create features and solution packages, and understand why it is Microsoft’s recommended best practice for deploying custom SharePoint artifacts.  I know it seems like a lot of work, but once you do this once or twice and realize the benefits of what features and solution packages provide, I guarantee you’ll never look back.

Here are a few tools that may help:

• WSP Builder (a MUST have)
• STSDEV
• VSeWSS 1.2 (for Visual Studio 2008)
• SPDeploy
• SharePoint Solution Installer