Migrating documents to SharePoint the easy way

OK, easy is relative, but it if you don't mind making a couple XML configuration files, it's as easy as easy gets with Folders2SP PowerShell script.

Folders2SP Codeplex Project

Before writing the script I evaluated a number of products, but I found non offered the flexibility I wanted and at ~$2500 I reasoned I could do better; hence Folders2SP. Sure it doesn't have a UI and it doesn't support Permission migration... but it's free, 42kb, doesn't soil your production servers, no custom web services (for item level permissions) and you can extend it to your hearts content... so you can add a permissions method.

Description

---

Folder2SP.ps1 is a PowerShell script which helps you migrate documents from SharePoint and folder structures to SPS2010 while preserving versions, metadata and converting folder names to managed metadata terms.
Use case: You have documents in a MOSS library and in a network share folder structure which need to be migrated to a SPS 2010 library. You want to use the words in the folders as taxonomy terms in your SPS2010 library and perform regular expressions on the file name/path.

Case setup

---

In this example we're going to get some documents from MOSS document library "Documents", and along with the sample documents included in this package upload them to a "Codeplex" document library in a SPS2010 site.

1. Create a working directory either on the SPS2010 server or a network share Eg: C:\Folders2SP.
2. Extract the contents of this project to your working directory. You should have Folders2SP.ps1, run.ps1, codeplex.xml and a "files" directory with some more directories and text files.
3. Create a destination document library in you SPS2010 site eg: Codeplex.
4. Add the required fields to the document library eg: Year (choice with 2008-2011), Enterprise Metadata field (site settings > Enterprise Metadata and Keywords Settings), LibMeta1 (Managed Metadata single), LibMeta2(Metadata multi), LibMeta3(Metadata multi) and connect them to an appropriate termset or term.
5. Modify the example codeplex.xml. Use your own MOSS library (works with a SPS2010 document library too).
   • Lookup site: the site you want to get the documents from. The script uses web services. To interrogate the source site I recommend you try my FireWS script , cos it rocks.
     library: the library to get the files from.
     view: if you want specific files, enter the view Guid here.
     rootfolder: is the folder to look in... default is the name of the library.
   • Target site: is the only attribute you need to change for this demo.
   • The xml file is commented so you know the options.

Process overview

---

1. Run PowerShell as Administrator (make sure your execution policy is set to remote) from your SPS2010 server, run.ps1 includes Folder2SP.ps1 and calls the function Folder2SP passing Codeplex.xml as the configuration file.
2. Folder2SP loads the config file, assigns a bunch of variables and creates a log file.
3. Goes to http://spsdev/documents and starts traversing the library... if you get access denied, it's your fault, try running the scripts from C:\ or use a local MOSS library.
4. Downloads all files it finds to the "Folders" path
5. Loops through all the files in Folders path and (if replicatefolders=true) recreates the folder hierarchy on the SPS document library.
   • Connects the Lookup site
   • Gets Metadata
   • Downloads versions to the working directory
   • Uploads the file to the Target document library
   • Loops through each field and check if it's configured in the xml config file. This is an inefficient way of doing it, but the script started out as just a way to copy all field data from the Lookup to the Target.
   • If it's a taxonomy field, it looks up the value, if it can't find the value it creates it. There are a couple options here, so read the comments in the example xml config file.
   • It then loops through the "Field" "Value"s and performs logic associated with Value type. I've only created 3 Value types "FileDate" uses properties from the file, "RegEx" takes an expression and matches on the full file name, and "Lookup" uses a value from the Web Service call.
   • If there's a user field it will 1st attempt to find the user, if it can't find them it will attempt to add them, if that fails it uses the account who's running the script.
   • Sets the new item value.
6. At the end you should have a migration report, and all the files from the Lookup site and your Folder path in the SPS2010 library with the metadata.

To do

---

1. View Fields ($wsFields) for the web service call should be moved to the XML config file.
2. Permissions could be also migrated if a item level permissions web service were available on the Lookup server.
3. Improve error handling, and more consistent variable naming.
4. Improve xml config file
5. Add more field type handlers

Getting BeforeProperties and AfterProperties in Event Receivers

You're here because you're trying to find away of comparing values of a record before or after saving it in an event receiver, and BeforeProperties and AfterProperties are giving you null, right? Well chances are you searched and found posts about saving the values to a cache, and a lot of other rubbish... I know I did.

Anyway the proper way of doing things is real simple, and I stumbled upon it using Powershell to check my event receivers.

SPEventReceiverDefinition has a Synchronization property.

An Asynchronous event passes the objects to another thread and you lose the BeforeProperties and AfterProperties.
A Synchronous event keeps the thread and you can access the BeforeProperties and AfterProperties.

In my case after specifying the event receiver as Synchronous I was able to compare existing values with values in properties.AfterProperties on an Receiver Type = ItemUpdating.

So to set a Receiver as Synchronous, just add Synchronous to your definition (elements.xml) or in code set the Synchronization property.

Eg:

<?xml version="1.0" encoding="utf-8"?> <Elements xmlns="http://schemas.microsoft.com/sharepoint/"> <Receivers> <Receiver> ... ... <Synchronization>Synchronous</Synchronization> </Receiver> </Receivers> </Elements>

Bug in SPWeb CreateDefaultAssociatedGroups

In an environment I manage I recently encountered an MS bug in some site generation code.

Case: Sharepoint list form with site metadata. When the record gets approved a site is automatically generated, with unique permissions based on data from the list.

Bug: The site is created but the Visitor group property isn't saved. By default CreateDefaultAssociatedGroups will not create the Visitor group, but instead associate the Site Collection Visitor group with the new site's AssociatedVisitorGroup property. To get around this you need to set this property to null before you call CreateDefaultAssociatedGroups eg:

oNewSite.AssociatedVisitorGroup = null;

oNewSite.BreakRoleInheritance(false);

oNewSite.Update();

oNewSite.CreateDefaultAssociatedGroups(oOwner.LoginName, oOwner.LoginName, sNewSite);

oNewSite.Update();

But, and here's the bug; while this does create the groups and the proper association, the site property vti_associatevisitorgroup is not updated. What happens now is that even if you update the Site Groups through the UI it still won't update this property and there will never be a Visitor group association.

Fix: Fortunately the fix is easy; just update the property with the created visitor group id.

oNewSite.CreateDefaultAssociatedGroups(oOwner.LoginName, oOwner.LoginName, sNewSite);

oNewSite.Properties["vti_associatevisitorgroup"] = oNewSite.AssociatedVisitorGroup.ID.ToString();

oNewSite.Properties.Update();

oNewSite.Update();

SharePoint List in SQL

In was recently tasked with Exporting a SharePoint list to Excel for users logging in using Forms Authentication.

I scratched my head for a minute and I came up with creating a Reporting Services report that queries the SP DB directly.

Issues: I wanted the query to be dynamic and use the exact same SP columns. So if a user adds a field or changes a column name I don't need to amend the SSRS report.

Solution: I created a Stored Proc, that takes 1 parameter (ListID). The proc gets the fields for the list then builds a query and executes it. The Stored Proc needs to reside on the same Content DB.

Code:

CREATE procedure [dbo].[usp_PrintList] (@ListId uniqueidentifier)
AS
BEGIN
  DECLARE @XMLFields TABLE (Row INT IDENTITY, Field XML);
  DECLARE @xFields XML; 
  SELECT @xFields = (SELECT cast(
      replace(cast(tp_Fields as varchar(max)),'<FieldRef','<Field') 
      as XML) as Fields
      FROM Lists 
      WHERE (tp_ID = @ListId))
 
  INSERT INTO  @XMLFields
    SELECT Tbl.xFlds.query('.') from @xFields.nodes('/Field') as Tbl(xFlds)
 
  DECLARE @sql VARCHAR(8000), @field XML, 
    @colname VARCHAR(30), @Type VARCHAR(30), @dispname VARCHAR(255);
  
  SET @sql = 'SELECT '
 
  DECLARE tmpCursor CURSOR FOR
    SELECT Field, 't1.'+ Field.value('(/Field/@ColName)[1]', 'varchar(max)') , 
      Field.value('(/Field/@Type)[1]', 'varchar(max)'), 
      ' as ['+ isnull(Field.value('(/Field/@DisplayName)[1]', 'varchar(max)'), 
      Field.value('(/Field/@Name)[1]', 'varchar(max)')) +'], '
    FROM @XMLFields
 
  OPEN tmpCursor
  FETCH NEXT FROM tmpCursor INTO @field, @colname, @Type, @dispname
    WHILE @@FETCH_STATUS = 0
    BEGIN
      IF(@colname IS NOT NULL)
      BEGIN  
 
        SET @sql = (CASE @Type
          WHEN 'Lookup' THEN 
            @sql + '(SELECT nvarchar1 FROM UserData WHERE tp_ListId = '''+ 
            (@field.value('(/Field/@List)[1]', 'varchar(max)')) +
            ''' AND tp_ID = '+ @colname +')' + @dispname    
          
          WHEN 'User' THEN 
            @sql + '(SELECT tp_Title FROM 
            UserInfo WHERE tp_ID = '+ @colname +')' + @dispname            
          
          ELSE @sql + @colname + @dispname
          END)
  
      END
      FETCH NEXT FROM tmpCursor INTO @field, @colname, @Type, @dispname
    END
  CLOSE tmpCursor
  DEALLOCATE tmpCursor  
   
  --strip off last comma
  SET @sql = SUBSTRING( RTRIM(@sql), 1, LEN(@sql) - 1 )
  SET @sql = @sql + ' FROM UserData t1 WHERE t1.tp_ListId = '''+ 
      CAST(@ListId AS VARCHAR(50)) +''' AND t1.tp_RowOrdinal = 0'
 
  PRINT @sql
  EXEC(@sql)
END

Limitations:

1. It doesn't get the Display Name for field references (ie not customized field)
2. It only looks at the Title of the lookup list/user, and then only on custom look up.

Example:

MS SQL Management Studio View

SharePoint View

Keywords: Export to spreadsheet, SQL field view, AllUserData

File link

Redirect groups to a specific site or url

I created 2 web parts for aiding the redirection of users to specific area I would like to share with y'all.

1. Group Page Redirector; this has a group property, a URL and redirect once property. If you belong to the selected group it will redirect you to the specified URL. This can be handy if you want to show specific content to a group; like an announcement or terms and conditions. Just add more web parts for other groups.
2. SubSite Redirector; this doesn't any properties... it just checks which Sub Sites you have access to, and if you only have access to one it will redirect you to it.

They're both simple Web Parts with the source/project code and wsp. Also you can set them to Minimized and no Chrome, so that they're not visible.
Keywords: Sharepoint Redirect Redirector Web Part once Usage policy ToolPart[]

Item level permission checking web part

So you want to know what a specific user can and can't see in your SharePoint site?... well for that you'll need to perform an eye examination.

OK really you want to know what items/list/sites for which a specific user has access?
Well believe it or not you can't with the standard SharePoint tools, but it's a fairly simple exercise to create a web part that does, and to save time here's one I made before.

It's fairly simple... get the SPUser > Check what groups he/she belongs to > loop through all the lists > items then the sub sites...
I show an icon for SPBasePermissions instead of the usual Full Control, Contribute, ect. The tree goes down to items, but won't fetch them until you click on the list expand icon to aid performance.
Since I'm using SPUser it also works for forms authentication.
If you don't have permission to check permissions on an object you can set a web part property to show this or not.

In any event the whole project source is included, so feel free to play and recommend changes.

Keywords: Check User Permission Inherited Unique Access RenderPreText RenderPostText TreeNode PeopleEditor SPUser TreeView SPGroup HasUniqueRoleAssignments TreeNodePopulate

Programmatically creating a Rich Text Field in a SharePoint List

Sounds easy enough, but it wasn't immediately obvious.

The below creates a list, adds a Rich Text field with full HTML and adds it to the default view.

27   SPWeb oWeb = null;

28   oWeb = oSite.RootWeb;

29   Guid gList;

30   SPList oList = null;

31   gList = oWeb.Lists.Add("My List", "Some description", SPListTemplateType.GenericList);

32   oWeb.Update();

33   oList = oWeb.Lists[gList];

34   SPFieldMultiLineText oFldBody = (SPFieldMultiLineText)oList.Fields[oList.Fields.Add("Body", SPFieldType.Note, true)];

35   oFldBody.Description = "Replaceable Parameters: {password}, {userid}, {usergroups}, {usersite}, {siteadmin}";

36   oFldBody.RichText = true;

37   oFldBody.RichTextMode = SPRichTextMode.FullHtml;

38   oFldBody.Update();

39   oList.Update();

40 

41   SPView oView = oList.DefaultView;

42   SPViewFieldCollection oViewFields = oView.ViewFields;

43   oViewFields.Add(oFldBody);

44   oView.Update();

SharePoint Copy list item to multiple sites - Feature

There doesn't seem to be any OOTB solution to copy list items to > 1 other site... so I wrote a quick aspx page and added it to a feature/solution which I would like to share with you.

The APX page just loops through the Web collection and checks for the list and if it's available enables a checkbox so you can copy the item.
The feature adds a context menu to a list item with a link to the page.
There's nothing compiled so it's easy to edit (just open the solution file).

Feature link: Download
To Do: Optimize sorting DataView/DataTable, add Doc Library support, add a FeatureActivated/Deactivated to add ASPX page to sitemap, add option to create list if it doesn't exist and change to a dll/code behind.
Keywords: SPGridView, SPList, SPListItem, Copy, Add, Deploy, Feature, context menu.
Notes: I haven't tested this in a production environment
Screenshots:

Filtering SharePoint List Libraries with Web Part Filters

Sometimes you need to filter data (ListViews/DataViews/Other web parts) based on some criteria be it the persons name, their email, a specific date range or from a parameter.
Microsoft provide a number of these Web Parts called filters. The problem is they only provide these in the Enterpri$e ver$ion.

So I made my own and I'm sharing them with you....

ray1.net Web Part Filters feature is comprised of 6 web parts.

1. ray1.net Content Connector. This is a simple Literal Control that renders the value of a connection, or the text you specify. You can use it with, say the UserFilter and render some text that says "Welcome to my site UserFilter value!"
2. ray1.net Filter Web Part: Current Page. This web part provides a filter connection that send information about the current page. The page needs to be in a list. You can use this to filter content based on who created the page or the file size or the modified date.
3. ray1.net Filter Web Part: Date. This web part provides a filter connection that sends information about a selected date or date range. Say you had a DataView web part that sent a parameter to an SQL stored procedure.
4. ray1.net Filter Web Part: QueryString. This filter accepts a QueryString parameter and connects to another web part. Pretty simple really, name the filter the same as the querystring parameter and your done.
5. ray1.net Filter Web Part: User Name. This web part provides a User Name connection filter. This web part makes this feature incompatible with WSS v3, as I use the "Microsoft.Office.Server.UserProfiles.UserProfileManager" class.
6. ray1.net Filter Web Part: Web ID. This web part provides a filter connection that sends the Web ID. Another simple one, but useful... Say you have a bunch of sites using the same template and you wanted to filter an SQL query.

All the filters accept multiple connections, and this is what they look like in edit mode.


This is what they look like normally


Download link: ray1.net Web Part Filters
To do: I could add a Text Filter and maybe change the Content Connector to accept n number of connections.

Keywords: Filter, web part, listview, SPSlicerBaseWebPart, ToolPart, IWebPartField, GetConnectionInterface, Connection Consumer Provider

Customizing the sharepoint search control

There are a couple ways of doing this.

1. Modify ..\12\TEMPLATE\FEATURES\ContentLightup\Controls\searchArea.xml
2. Modify ..\12\TEMPLATE\CONTROLTEMPLATES\searcharea.ascx
3. Create a new feature and control
4. Create a new feature that changes parameters for "SmallSearchInputBox"

I believe option 4 to be the best as it uses Microsoft.SharePoint.PortalWebControls.SearchBoxEx class and you don't need to create a new control.
There are a large number of properties you can use to customize the control and you should find the one you're looking for microsoft.sharepoint.portal.webcontrols.searchboxex_properties.
So how do we customize it? Create a new feature.

1. Create a new directory anywhere eg: C:\RPSearchFeature
2. Create a new manifest.xml in C:\RPSearchFeature with the following:

   <?xml version="1.0" encoding="utf-8"?>
   <Solution
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
    SolutionId="GENERATE_A_GUID" 
    ResetWebServer="True" 
    xmlns="http://schemas.microsoft.com/sharepoint/">
    <FeatureManifests>
     <FeatureManifest Location="PortalSearch\feature.xml" />
    </FeatureManifests>
   </Solution>
   

3. Create "C:\RPSearchFeature\PortalSearch\feature.xml" with:

   <?xml version="1.0" encoding="utf-8"?>
   <Feature  Id="7095f774-1efa-4879-b074-ff211f5559c7"
             Title="Small Portal Search"
             Description="Smaller search bar without scopes"
             Version="12.0.0.0"
             Hidden="FALSE"
             Scope="Web"
             DefaultResourceFile="core"
             xmlns="http://schemas.microsoft.com/sharepoint/">
     <ElementManifests>
       <ElementManifest Location="elements.xml"/>    
     </ElementManifests>
   </Feature>
   

4. Create "C:\RPSearchFeature\PortalSearch\elements.xml" with:

   <?xml version="1.0" encoding="utf-8" ?>
   <Elements xmlns="http://schemas.microsoft.com/sharepoint/">
    <Control
     Id="SmallSearchInputBox"
     Sequence="25"
     ControlClass="Microsoft.SharePoint.Portal.WebControls.SearchBoxEx" 
     ControlAssembly="Microsoft.SharePoint.Portal, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c">
     <Property Name="SearchResultPageURL">/search/results.aspx</Property>
     <Property Name="FrameType">None</Property>
     <Property Name="DropDownMode">HideDD_NoScope</Property>
     <Property Name="TextBoxWidth">140</Property>
     <Property Name="ShowAdvancedSearch">false</Property>
    </Control>
   </Elements>

5. Wrap the feature up in to a solution "SearchFeature.wsp" with cabpack
6. Deploy the solution.
7. Enable the feature (Scope is web, but you can change it to site if that suits you).

The instruction that will enable these parameters over the default is the sequence number. The default sequence is 99, so anything below 99 will load first. The parameters I'm using set the "SearchResultPageURL", hide the scope drop down and remove the advance search link.

AJAX, Web Services, WSDL and SharePoint

You may at some stage have to work for a client who's very particular about security, what web parts can be used and even whether sharepoint designer can be used.
Recently I had the opportunity to work for one such client who wanted considerable customization, but without the use of SPD, creating a custom web part was out of the question, the sharepoint servers were on another continent, and I was given contribute permissions one a subsite. Like I've said many times before I hate telling somebody something can't be done.
The customizations involved cross site permissions checking, aggregating, site meta data displaying and performing actions on new/edit forms for particular content types. The only option I could think of was talking to sharepoint's web services with IE's XMLHttpRequest object.
Some people think that AJAX is some fancy new'ish library that enables you to do clientside post backs; well it is and it isn't... it's been around since the turn of the millennium and most of the AJAX tool kits/libraries have a lot fluff for browser compatibility, and if your not working in an environment where you need to support multiple browsers I wouldn't bother with any pre-packaged library/toolkit.
All you need is to instantiate an HttpRequest object, IE7 and FF have it built in so you can call methods without creating the ActiveXObject.

So how is it done?
1.- Assign a variable to the Request object
2.- Open a connection to the web service
3.- Post some data to the service
4.- Capture the reply
5.- Do some formatting


That simple, but there's more to it...
The web service is going to expect the post in some form, so most have a WSDL (Web Service Definition Language) you can query to build your post. SharePoints web services lay it all out for you to just cut & paste pretty much.
The web service is also going to send back the data in some form, usually XML. You can use javascript to interpret the XML string, but all browsers come with an XML parser. So you load the XML from the web service in to an XML DOM object, and now you can loop through, and extract the data. Look here for available methods: http://msdn2.microsoft.com/en-us/library/ms757828(VS.85).aspx
You're probably here because you want code. Here's fireWS.zip a web page you can run on your local machine that will query the WSDL, or you can put the source in a content query web part and run it from any page in SharePoint. Looking at its source should give you a good idea of how to query the WSDL, loop through nodes and construct the XML post.
Below the break down of the page and bear in mind that this is a demo and the code can be refactored... Originally it was a Javascript class, so all you would do was call new fireWS(constuc webservice), but I've split it out to individual functions and used global variables instead of properties.


Start off by creating a couple of global variables to share among the functions:
var sXmlDomVer='MSXML2.DOMDocument.3.0',oWsdlXml, oSoapXml, oSoapBody, oSoapOp;
The below instantiates a request object, and takes the web service url and the xml string to post. If you want to support other browsers add some checks here.

function fireWS(sWSUrl, sXml){
 var oXmlHttp=false;
 if(!oXmlHttp && typeof XMLHttpRequest!='undefined') {
  try {oXmlHttp = new XMLHttpRequest();}
  catch(e){oXmlHttp=false;}
 }
 if(!oXmlHttp && window.createRequest) {
  try {oXmlHttp = window.createRequest();} 
  catch(e){oXmlHttp=false;}
 }
 if(!oXmlHttp){
  try{oXmlHttp = window.createRequest();} 
  catch(e){oXmlHttp = new ActiveXObject('Microsoft.XMLHTTP');}
 }
 if(!oXmlHttp){alert('Sorry, you\'re browser doesn\'t support this control')}
 if(!sXml){method='GET'}
 else{method='POST'}
 oXmlHttp.open(method, sWSUrl, false); 
 oXmlHttp.setRequestHeader('Content-Type','text/xml; charset=utf-8');
 oXmlHttp.send(sXml);
 document.getElementById('headers').innerText = oXmlHttp.statusText +' '+ oXmlHttp.getAllResponseHeaders();
 if (oXmlHttp.readyState==4){
  return oXmlHttp.responseText;
 }
}

This function parses an XML string and returns an XMLDOM object. If you want to support other browsers add some checks here.

function xmlParse(str){
 oXmlDom = new ActiveXObject(sXmlDomVer)
 oXmlDom.async = false;
 oXmlDom.loadXML(str); 
 if (oXmlDom.parseError.errorCode != 0){
    var myErr = oXmlDom.parseError;
    if(document.getElementById('debug').checked){alert('Error parsing xml ' + myErr.reason +'\n\n'+ str)}
 }
 return oXmlDom;
}

This function is used to search for a paraticular node.

function getMyNode(obj, nodeName, attribute, attributeVal){
 try{objXml = obj.getElementsByTagName(nodeName);}
 catch(e){}
 if(objXml.length == 0){objXml = obj.getElementsByTagName(nodeName.substring(nodeName.indexOf(':')+1,nodeName.length));}
 if(objXml.length == 0){return false}
 if(attribute != null && objXml){
  for(var i=0; i<objXml.length; i++){if(objXml[i].getAttribute(attribute) == attributeVal){return objXml[i];}}
  return false;
 }
 return objXml;
}

This function is used to add a node to another node. Looks like I wanted to be able to define the position to add it in, then I changed my mind.

function addMyNode(oSoap, type, nodeName, parentNode, v, ns){
 var newNode = oSoap.createNode(type, nodeName, ns);
 try{
  switch(type){
   case 1:
    if(v){newNode.text = v};
    return parentNode.appendChild(newNode);
   case 2:    
    if(v){newNode.text = v};
    return parentNode.attributes.setNamedItem(newNode);
   default:
    return parentNode.appendChild(newNode);
  }
 }
 catch(e){
  if(document.getElementById('debug').checked){alert('Error creating node "'+ nodeName +'"\ntype '+ type +'\nv '+ v +'\n\n'+ e.description)}
 }
}

This removes childnodes so you can run a new query.

function removeChildNodes(oXml){
 for(var i=0;i<oXml.childNodes.length;i++){
  oXml.removeChild(oXml.childNodes.item(i));
 }
}

This is for adding table rows to the SOAP properties table.

function renderPropsTbl(sProp,oProp){
 if(document.getElementById('tblConnProps')!=null){
  var oTbl = document.getElementById('tblConnProps');  
 }
 else{
  var oTbl = document.createElement('table'); 
  oTbl.id = 'tblConnProps';
  document.getElementById('divConnProps').appendChild(oTbl);
 }
 var newRow = oTbl.insertRow(oTbl.rows.length);
        newRow.id = newRow.uniqueID;     
 var newCell;
        newCell = newRow.insertCell(0);
        newCell.id = newCell.uniqueID;
        newCell.innerText = sProp;
        newCell = newRow.insertCell(1);
        newCell.id = newCell.uniqueID;
  newCell.appendChild(oProp);
}

This is for removing table rows from the SOAP properties table.

function removePropsTbl(iTR){
 var tblConnProps = document.getElementById('tblConnProps');
 for(var i=tblConnProps.rows.length-1;i>=iTR; i--){tblConnProps.deleteRow(i);}
}

This for finding the bindings in the WSDL, I should really figure out an intelligent way of finding out the namspac, but instead I just use wsdl.

function getWdslBinding(url){
 var oProps = document.getElementById('divConnProps');
  oProps.innerHTML = ''; 
 oWsdlXml = xmlParse(fireWS(url +'?WSDL'));  
 var oWsdlBindings = getMyNode(oWsdlXml, 'wsdl:binding');
 if(oWsdlBindings){
  var oBindings = document.createElement('select');
   oBindings.id = 'selBinding';
   oBindings.attachEvent('onchange',getWdslOperations);   
  for(var i=0; i<oWsdlBindings.length; i++){    
   if(oWsdlBindings[i].attributes.length > 0){
    var newopt = document.createElement('option');
     newopt.value = oWsdlBindings[i].getAttribute('name');
     newopt.text = oWsdlBindings[i].getAttribute('name');
     oBindings.options.add(newopt);
   }
  }
  renderPropsTbl('Binding Port:',oBindings);
  getWdslOperations();
 } 
}

This needs a bit of work but it finds the operations for the selected binding, and it sorts the operations for aestetics.

function getWdslOperations(){
 try{removePropsTbl(1)}
 catch(e){}
 var oWsdlOps = getMyNode(oWsdlXml,'wsdl:binding','name',document.getElementById('selBinding').value);
 if(getMyNode(oWsdlOps,'soap12')){sSoapVer = 'soap12';sSoapNS = 'http://www.w3.org/2003/05/soap-envelope';}
 else{sSoapVer = 'soap';sSoapNS = 'http://schemas.xmlsoap.org/soap/envelope/';} 
 oSoapXml = xmlParse('<' + sSoapVer +':Envelope xmlns:'+ sSoapVer +'="'+ sSoapNS +'"/>');
 oSoapBody = addMyNode(oSoapXml, 1, sSoapVer +':Body', oSoapXml.documentElement, '', sSoapNS);
 oWsdlOps = getMyNode(oWsdlOps,'wsdl:operation'); 
 if(oWsdlOps){
  var oOps = document.createElement('select');
   oOps.id = 'selOps';
   oOps.attachEvent('onchange',getWdslOpElements);      
  arrWsdlOps = new Array();
  for(var i=0; i<oWsdlOps.length; i++){
   if(oWsdlOps[i].attributes.length > 0){
    arrWsdlOps.push(oWsdlOps[i].getAttribute('name'));
   }
  }
  arrWsdlOps.sort();
  for(var i=0; i<arrWsdlOps.length; i++){
   var newopt = document.createElement('option');
    newopt.value = arrWsdlOps[i]; newopt.text = arrWsdlOps[i];
    oOps.options.add(newopt);
  }  
  renderPropsTbl('Operation:',oOps);
  getWdslOpElements();
 }
}

This gets the elements for an operations and adds an input fields.

function getWdslOpElements(){
 try{
  removePropsTbl(2);
  document.getElementById('result').innerText = '';  
  oSoapBody.removeChild(oSoapOp);  
 }
 catch(e){}
 var sOp = document.getElementById('selOps').value;
 oWsdlOpElem = getMyNode(oWsdlXml,'s:element','name',sOp);
 oSoapOp = addMyNode(oSoapXml, 1, sOp, oSoapBody, '', oWsdlXml.documentElement.getAttribute('targetNamespace'));
 if(oWsdlOpElem.hasChildNodes()){  
  oWsdlOpParams = getMyNode(oWsdlOpElem,'s:element');  
  for(var i=0; i<oWsdlOpParams.length; i++){
   if(oWsdlOpParams[i].attributes.length > 0){ 
    var sOpParamType = oWsdlOpParams[i].getAttribute('type'), sOpParamName = oWsdlOpParams[i].getAttribute('name'), iInputType;
    if(oWsdlOpParams[i].hasChildNodes()){iInputType = 1;}
    else{
     if(sOpParamType.indexOf('tns:')>-1){
      var oTNSOpParams = getMyNode(oWsdlXml, 's:simpleType', 'name', sOpParamType.replace('tns:',''));
      if(oTNSOpParams){
       oTNSOpParams = getMyNode(oTNSOpParams, 's:enumeration');      
       iInputType = 2;
      }
      else{iInputType = 1;}
     }
     else{iInputType = 0;}
    }    
    switch(iInputType){
     case 1:
      var oOpPN = document.createElement('textarea');
       oOpPN.cols = 60;oOpPN.rows = 3;
       sOpParamType = 'XML Schema';
      break;
     case 2:
      var oOpPN = document.createElement('select');
      for(var iT=0; iT<oTNSOpParams.length; iT++){
       if(oTNSOpParams[iT].attributes.length > 0){
        var newopt = document.createElement('option');
         newopt.value = oTNSOpParams[iT].getAttribute('value');
         newopt.text = oTNSOpParams[iT].getAttribute('value');
         oOpPN.options.add(newopt);
       }
      }
      break;
     default:
      var oOpPN = document.createElement('input');
      oOpPN.size = 80;
    }    
    oOpPN.id = 'soapp'+ sOpParamName;
    renderPropsTbl(sOpParamName +' ('+ sOpParamType +'):',oOpPN);
    var oOpParam = addMyNode(oSoapXml, 1, sOpParamName, oSoapOp, '',oWsdlXml.documentElement.getAttribute('targetNamespace'));
   }
  }
  renderPropsTbl('',document.createElement('<INPUT TYPE="Button" NAME="buildSoap" VALUE="Fire Soap" onclick="buildSoap();" class="btn">'));
 }
}

This loops through the form and builds the SOAP post.

function buildSoap(){
 var soapFlds = document.getElementById('tblConnProps').getElementsByTagName('*');
 for(var i=0;i<soapFlds.length;i++){
  if(soapFlds[i].value != '' && soapFlds[i].id.indexOf('soapp')>-1){
   if(soapFlds[i].type == 'textarea'){
    try{
     var oFldXml = xmlParse(soapFlds[i].value);
      oFldXml.namespaceURI = oOpParam.namespaceURI;
     var oOpParam = oSoapOp.selectSingleNode(soapFlds[i].id.replace('soapp',''));
      removeChildNodes(oOpParam);
      oOpParam.appendChild(oFldXml.documentElement);
    }
    catch(e){
     try{
      var oOpParam = oSoapOp.selectSingleNode(soapFlds[i].id.replace('soapp',''));
      var oXmlDom = new ActiveXObject(sXmlDomVer)
       oXmlDom.async = false;
       oXmlDom.loadXML('<'+ soapFlds[i].id.replace('soapp','') +' xmlns="'+ oOpParam.namespaceURI +'">'+ soapFlds[i].value +'</'+ soapFlds[i].id.replace('soapp','') +'>');
      oSoapOp.replaceChild(oXmlDom.documentElement, oOpParam);      
     }
     catch(e){alert('Error in buildSoap(): '+ e.description);}
    }
   }
   else{
    try{
     var oOpParam = oSoapOp.selectSingleNode(soapFlds[i].id.replace('soapp',''));
     oOpParam.text = soapFlds[i].value;
    }
    catch(e){}
   }
  }
 } 
 fireSoap(oSoapXml.xml,document.getElementById('result'))
}

The rest just renders the output.

function fireSoap(soap, oResDisp){
 var sUrl = document.getElementById('url').value + document.getElementById('asmx').options[document.getElementById('asmx').selectedIndex].text;
 var sRtnXml = fireWS(sUrl, soap);
 document.getElementById('soap').innerText = oSoapXml.xml.replace(/></gi,'>\n<');;
 //document.getElementById('soap').parentElement.style.display = 'block';
 getMethodXml(sUrl);
 if(sRtnXml.indexOf('z:row')>-1){
  var row = xmlParse(sRtnXml).getElementsByTagName('z:row');
  var sAtt = '<table border="1">';
  if(row[0].attributes.length > 0){
   sAtt += '<tr>';
   for(var i=0; i < row[0].attributes.length; i++){sAtt += '<th>'+ row[0].attributes[i].name +'</th>';}
   sAtt += '</tr>';
  }
  for(var i=0; i<row.length; i++){   
   if(row[i].attributes.length > 0){
    sAtt += '<tr>';
    for(var ii=0; ii < row[i].attributes.length; ii++){sAtt += '<td>'+ row[i].attributes[ii].value +'</td>';}
    sAtt += '</tr>';
   }
  }
  sAtt += '</table>'; 
  oResDisp.innerHTML =  sAtt;
 }
 oResDisp.innerText = sRtnXml.replace(/></gi,'>\n<');oResDisp.parentElement.style.display = 'block';
}

Once you start playing around with this you'll soon realize it's power and flexibility, and you could even save yourself the effort of registering more dll's on your server. The holly grail would be to create Http End Points for all your SQL data.
If "Web page security validation" is enable you will not be able to do any add/edit/delete actions, so you're limited to listing. This is also the case with serverside web service calls unless you explicitly "allowunsafeupdates".
This will only work if you download the html file (fireWS.zip) and run it locally with reduced permissions OR reduce your internet zone IE permissions and run it online fireWS.htm.
Usage Example:

1. Enter service URL: http://tb.ohchr.org/
2. Choose web service eg: /_vti_bin/Lists.asmx
3. Operation: GetListCollection
4. Fire Soap
5. You should now see the reply...

Now you have the list GUIDs so you can do some more stuff.
Like:

1. Operation: GetList
2. listName: {17B611E5-4BF5-48C0-8926-E614A8126197}
3. Fire Soap

Cool, we've got all the field names.
Let's try a list query...

1. Operation: GetListItems
2. listName: {17B611E5-4BF5-48C0-8926-E614A8126197}
3. query: <Query><Where><Contains><FieldRef Name="ConventionCode" /><Value Type="Text">C</Value></Contains></Where></Query>
4. viewFields: <ViewFields><FieldRef Name="ID" /><FieldRef Name="Title" /><FieldRef Name="ConventionCode" /><FieldRef Name="ConventionOrder" /><FieldRef Name="Convention" /><FieldRef Name="Author" /></ViewFields>
5. rowLimit: 7
6. Fire Soap

As you can see it's a easy... I haven't tried all the web services as this is just a curiosity for me.

Web services: http://msdn2.microsoft.com/en-us/library/ms445292.aspx

Enabling HTML and/or Images in a SharePoint List using a calculated field

A client asked me to highlight a row based on the value of a status field... If figured easy, just use a dataview wp, but no, they want to be able to maintain it without using SPDesigner. I always like to give clients what they want, and I wasn't about to tell them to start changing the dataview in notepad... The "proper" way to enable HTML would probably be to create a bew custom field type, but since we need a formula this is not a trivial task; there is however a file which controls the way fields are rendered (see previous post).

We need to modify this to stop sharepoint encoding the HTML. This method has zero performance impact and only takes a few minutes.

To do this the first step is to open \Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\XML\FLDTYPES.XML
There are a couple of ways to change FLDTYPES.XML that will stop it encoding HTML; the one I've chosen is to look for a hidden tag in the value returned by a calculated field.
You can change any field type, but I need to change the calculated field type as I need to use conditional formating based on a formula.

1. Locate <FieldName="TypeName">Calculated</Field> in FLDTYPES.XML.
2. Move down to the <RenderPattern Name="DisplayPattern"> node.
3. Find the <Default> node.
4. Add your logic between the <Default> and </Default> nodes.

You can use various functions and properties to enable your logic, please see http://msdn2.microsoft.com/en-us/library/ms439798.aspx for a list. In my case I just needed an If Else as I didn't want to impact on existing calculated fields, so I opted for the <IfSubString> function, as follows:

<IfSubString>
<Expr1><![CDATA[<.RP>]]></Expr1>
<Expr2><Column/></Expr2>
<Then><HTML><Column/></HTML></Then>
<Else><Column HTMLEncode="TRUE" AutoHyperLink="TRUE" AutoNewLine="TRUE"/></Else>
</IfSubString>

The above basically means if the data in the column contains <.RP> then just render the contents. Otherwise do the MOSS default.

You could of course complicate the above and use a switch with <GetFileExtension> like:

<Switch>
<Expr><GetFileExtension><Column/></GetFileExtension></Expr>
<Case Value="RP>">
<HTML><Column/></HTML>
</Case>
<Default>
<Column HTMLEncode="TRUE" AutoHyperLink="TRUE" AutoNewLine="TRUE"/>
</Default>
</Switch>

The above has the benfit of allowing different logic for different scenarios.
The final step is to create a calculated field that returns <.RP>. Any record with this tag will enable the logic, and since <.RP> is an unknown tag browsers won't render it.
Using <GetFileExtension> you need to make sure your <.RP> is at the end.
The calculated field formula will look something like:

=IF([Active],"<.RP><b style='color:0000ff'>","<.RP><b style='color:ff0000'>") & [Title] &" "& [Column1] &" "& [Column2] &"</b>"

Look here for formulas http://msdn2.microsoft.com/en-us/library/bb862071.aspx

Now just create a view with just your calculated field and you have a list highlighting active records (with limited sorting)... but you could always just create a calculated field for each.

OK now you've probably realized your filtering has HTML... the fix for that is to disable filtering if the column has your tag... create a variable (<SetVar Scope="Request">) in your logic and set it, then check if it's been set outside <RenderPattern> set <Field Name="Filterable">FALSE</Field>. I haven't figured out how to render the heading as it displays without using Javascript... I could write this up one days. Notes: CAML is case sensative, and you'll need to restart IIS for any change to FLDTYPES.XML.

Modify FldTypes.xml to add custom field types and/or change display properties

FldTypes.xml

Each front-end Web server in a deployment of Windows SharePoint Services has one FLDTYPES.XML file located in the Local_Drive:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\XML folder that is used during site or list creation to define how field types are rendered in the different modes for viewing list data.

File Format

The following excerpt outlines the format of FLDTYPES.XML:

Xml

<FieldTypes>
  <FieldType>
    <Field Name="TypeName">Counter</Field>
    <Field Name="TypeDisplayName">$Resources:core,fldtype_counter;</Field>
    <Field Name="InternalType">Counter</Field>
    <Field Name="SQLType">int</Field>
    <Field Name="ParentType"></Field>
    <Field Name="UserCreatable">FALSE</Field>
    <Field Name="Sortable">TRUE</Field>
    <Field Name="Filterable">TRUE</Field>
    <RenderPattern Name="HeaderPattern">
      ...
    </RenderPattern>
    <RenderPattern Name="DisplayPattern">
      ...
    </RenderPattern>
    <RenderPattern Name="EditPattern">
      ...
    </RenderPattern>
    <RenderPattern Name="NewPattern" DisplayName="NewPattern">
      ...
    </RenderPattern>
    <RenderPattern Name="PreviewDisplayPattern">
      ...
    </RenderPattern>
    <RenderPattern Name="PreviewEditPattern">
      ...
    </RenderPattern>
    <RenderPattern Name="PreviewNewPattern">
      ...
    </RenderPattern>
  </FieldType>
  <FieldType>
    ...
  </FieldType>
  ...
</FieldTypes>

RenderPattern elements define how an item is displayed in each of the possible modes for viewing list data. These modes include the header patterns used at the top of each list in the toolbar, the modes used in forms for displaying, editing, or creating items, and preview rendering patterns used by a Web-editing application that is compatible with Windows SharePoint Services, such as Microsoft Office SharePoint Designer 2007.

To create a custom field that derives from a base field type, you can add a field definition to the Schema.xml file of a custom list definition Feature. For a programming task that shows how to add a field to a custom list definition, see How to: Create a Custom List Definition. For information about creating a custom field type, see Custom Field Types.

Creating and deploying a sharepoint solution package without command line tools

OK so you're a SharePoint developer, and you don't like using command line tools... some think you can't create a solution file and deploy it without using at least makecab or stsadm, but this isn't the case. What you need to create the solution file (wsp) and deploy it:

1. Cabpack is a GUI cab compression tool using the MS cab engine.
2. Either stsadmWin this adds a UI and sends the commands to stsadm or sharepoint solution intinstaller

Create your dll, create a manifest.xml cab it up using cabpack and follow the prompts with sharepointinstaller.

I came about this while trying to debug a solution deployment... read my previous post.

Cabpack has been very useful for quickly fixing/editing site templates. And has also help reduce site templates size to 1/3 using LZX compression type.

An error occurred while parsing EntityName

An error occurred while parsing EntityName. Line 11, position 65. RSSRipper.wsp: The Solution installation failed. I racked my brain over the above error for a couple of hours (OK maybe 30mins) until I realized the elements.xml file in my solution had an unescaped ampersand.
Fix: Change & for &amp;

Enumerating sharepoint site templates

You can run stsadm -o enumtemplates
If you've added a template to the Sharepoint Portal Server Template Gallery you'll notice that these template are not listed when using stsadm.
Stsadm will not list stp templates or the default templates either so I'll list them here with Title and Name:

Team Site, STS#0
Blank Site, STS#1
Document Workspace, STS#2
Basic Meeting Workspace, MPS#0
Blank Meeting Workspace, MPS#1
Decision Meeting Workspace, MPS#2
Social Meeting Workspace, MPS#3
Multipage Meeting Workspace, MPS#4
Business Activity Services Team Site, BAS#0
SharePoint Portal Server Site, SPS#0
SharePoint Portal Server Personal Space, SPSPERS#0
SharePoint Portal Server My Site, SPSMSITE#0
Contents area Template, SPSTOC#0
Topic area template, SPSTOPIC#0
News area template, SPSNEWS#0
News Home area template, SPSNHOME#0
Site Directory area template, SPSSITES#0
SharePoint Portal Server BucketWeb Template, SPSBWEB#0
Community area template, SPSCOMMU#0

Debugging SharePoint GAC dll's

Debugging sharepoint dll's is pretty straight forward and just like any other dll.
What you need:

1. The program database file (pdb)
2. The source files
3. A compatible debugger (VS 2005)
4. Access to the dll registered directory (global assembly cache/ webapp bin)

For this example we're gona debug a GAC dll, so the only task post dll registration is to copy the pdb to the GAC directory.
Note: Copying the pdb may NOT be required with VS 2008 as it will look in the project debug directory for the pdb.

To copy the pdb, if required, to the gac you have various options some are listed below by my order of preference.

1. Use the subst command to map a drive to the GAC
   • Type: Win + R > cmd > Enter
   • subst X: %windir%\assembly\gac_msil
   You should now have an X: drive pointing to your intermediate language assembly cache. Locate your dll directory and copy the pdb file.
   
2. Use the copy command to copy your pdb file
   • Type: Win + R > cmd > Enter
   • copy <your_pdb_location> c:\windows\assembly\gac_msil\<your_dll>\<version>__<public_key>
   I create a post build event to accomplish this.
   
3. Edit or rename the Desktop.ini file in c:\windows\assembly\
   • Type: Win + R > cmd > Enter
   • attrib c:\windows\assembly\desktop.ini -h -r -s
     rename c:\windows\assembly\desktop.ini desktop.dir
   You should now be able to browse the GAC directory.
   
4. Disable cache viewer in the registry
   • Type: Win + R > regedit > Enter
   • create a new dword key under HKLM\Software\Microsoft\Fusion\ with the nane DisableCacheViewer and set it’s [DWORD] value to 1.
     Or you could also just change HKEY_CLASSES_ROOT\CLSID\{1D2680C9-0E2A-469d-B787-065558BC7D43}\Server\ default to xShfusion.dll or delete the value.
   You should now be able to browse the GAC directory.
   

Debugging

1. Create a break point.
2. Do an IIS reset, cos 90% of the time if you don't you'll crash IIS and VS.
3. Open a SharePoint page you know uses the dll
4. Attach the w3wp.exe process with your application user id to the debugger.
5. Interact with the page until you reach your break point.