Tuesday, October 28, 2008

Create SharePoint BDC connector for AdventureWorks database with F# Script

I have been exploring how to create Business Data Catalog in the SharePoint environment. It seems the best way to create BDC application definition file is via a tool called BDC Meta Man, which provides a graphical environment to create the definition file. For my own understanding, I tried to script the creation of the BDC application definition file. In the F# script below, I created the BDC to the HR data in the sample AdventureWorks SQL Server database. This script loosely follows Chapter 4 of Patrick Tisseghem's book Inside Microsoft Office SharePoint Server 2007.

#I @"C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\ISAPI"
#r @"Microsoft.SharePoint.dll"
#r @"Microsoft.SharePoint.Portal.dll"
#r @"Microsoft.Office.Server.dll"

open Microsoft.Office.Server.ApplicationRegistry.Administration
open Microsoft.Office.Server.ApplicationRegistry.Infrastructure
open Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db
open List
open System.Data


// Create LOB System
let lobsystem =
("Adventure Works Human Resources Data",
|> ApplicationRegistry.Instance.LobSystems.Create

// Add property defining the character % will be replacing the * wildcar entered by users
("WildcardCharacter","%") |> lobsystem.Properties.Add

// Create LOB System instance
let sysInstance = lobsystem.LobSystemInstances.Create("HR data",true)

("AuthenticationMode",DbAuthenticationMode.RevertToSelf) |> sysInstance.Properties.Add
("DatabaseAccessProvider", DbAccessProvider.SqlServer) |> sysInstance.Properties.Add

[("RdbConnection Data Source", "MOSS");
("RdbConnection Initial Catalog","AdventureWorks");
("RdbConnection Integrated Security", "SSPI")]
|> iter (fun (k,v) -> sysInstance.Properties.Add (k,v))


// Create Employee Entity
let entity = lobsystem.Entities.Create("Employee",true)
let identifier = entity.Identifiers.Create("[EmployeeID]",true,"System.Int32")
let mthd = entity.Methods.Create("GetEmployees",true,true)

@"Select [EmployeeID],[Title],[FirstName],[MiddleName],[LastName],[Suffix],[JobTitle],[Phone],[EmailAddress],[EmailPromotion],[AddressLine1],[AddressLine2],[City],[StateProvinceName],[PostalCode],[CountryRegionName],[AdditionalContactInfo] From HumanResources.[vEmployee]"
) |> mthd.Properties.Add

("RdbCommandType",CommandType.Text) |> mthd.Properties.Add

let createParameter (thismethod:Method) name direction =
|> thismethod.Parameters.Create

let parEmployees = createParameter mthd "Employees" Microsoft.Office.Server.ApplicationRegistry.MetadataModel.DirectionType.Return

let tdEmployees =
let root =
"System.Data.IDataReader, System.Data, Version=2.0.3600.0,Culture=neutral, PublicKeyToken=b77a5c561934e089",
|> parEmployees.CreateRootTypeDescriptor
let node =
"System.Data.IDataRecord, System.Data, Version=2.0.3600.0,Culture=neutral, PublicKeyToken=b77a5c561934e089",
|> root.ChildTypeDescriptors.Create

(("EmployeeID", true, "System.Int32", identifier, null, false)
|> node.ChildTypeDescriptors.Create).LocalizedDisplayName <- "Employee ID"

let firstname =
("FirstName", true, "System.String", null, null, false)
|> node.ChildTypeDescriptors.Create
firstname.LocalizedDisplayName <- "First Name"

let lastname =
("LastName", true, "System.String", null, null, false)
|> node.ChildTypeDescriptors.Create
lastname.LocalizedDisplayName <- "Last Name"

[("JobTitle","System.String","Job Title");
] |> iter (fun (label,typename,display) ->
((label, true, typename, null, null, false)
|> node.ChildTypeDescriptors.Create).LocalizedDisplayName <- display)

// Create method instances
let getEmployees =
|> mthd.MethodInstances.Create

Monday, October 13, 2008

Add Web Part to SharePoint with F# Script

In building deployment scripts for SharePoint, there are times when I want to automate the deployment of webparts by building a script to automatically add web parts to specific web pages. I found a book that talked about writing these types of scripts in Mark Gerow's SharePoint 2007 Development Recipes, which had many other useful examples of scripting SharePoint 2007. He's already written a C# version of this script that I've decided to implement in F# for contrast.

Here's the F# version of the code:

#I @"C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\ISAPI"
#r @"Microsoft.SharePoint.dll"

open Microsoft.SharePoint
open Microsoft.SharePoint.WebPartPages
open System.Web.UI.WebControls.WebParts
open System.Xml

let addWebPart page wp zone order pscope (web:SPWeb) =
let webparts = (page, pscope) |> web.GetLimitedWebPartManager
(wp,zone,order) |> webparts.AddWebPart
wp |> webparts.SaveChanges

let disableCheckout (splist:SPList) spOperation =
let oldvalue = splist.ForceCheckout
splist.ForceCheckout <- false


splist.ForceCheckout <- oldvalue

let spUnsafeUpdate spOperation (siteurl:string) (webname:string) doclib =
let site = new SPSite(siteurl)
let web = site.OpenWeb(webname)
web.AllowUnsafeUpdates <- true

// Disable versioning when updating Document Library
match doclib with
| "" ->
spOperation web
| _ ->
<| web.Lists.Item(doclib)
<| spOperation web


let siteurl = "http://localhost/"
let webname = "recipes"
let doclibName=""

// Add page viewer web part
let viewer = new PageViewerWebPart(SourceType=PathPattern.URL,

// Add webpart using unsafe updates...
// Experimenting with ranges of expression with F#'s pipeline operators.
// Traditional format for the following code would be
// spUnsafeUpdate siteurl webname doclibName (addWebPart "Default.aspx" editor "Right" 0 Personalization.Shared)
<| "Default.aspx"
<| viewer
<| "Left"
<| 0
<| PersonalizationScope.Shared
|> spUnsafeUpdate
<| siteurl
<| webname
<| doclibName

// Add content editor web part
let xmlDoc = new XmlDocument()
let xmlElem = xmlDoc.CreateElement("xmlElem")
xmlElem.InnerText <- "Test content editor web part"
let editor = new ContentEditorWebPart(Content=xmlElem)

<| "Default.aspx"
<| editor
<| "Right"
<| 0
<| PersonalizationScope.Shared
|> spUnsafeUpdate
<| siteurl
<| webname
<| doclibName

Wednesday, October 01, 2008

More SharePoint scripting with F#

F# Interactive mode in Visual Studio is a great way to interactively script SharePoint objects. I'd almost compare this to using sql query editor against a database. I can write the SharePoint code in Visual Studio and have all the code completion and type checking features. I can select the portion of the source code and execute it by pressing Alt+Enter. It's pretty sweet. Combine this with queries built with CAML statements, I can basically do ad hoc queries against SharePoint objects. I don't know any other tool in the SharePoint tool suite that can do this. I can easily imagine a SharePoint management studio tool built with customized Visual Studio plugins that provide a way to explore the SharePoint objects graphically similar to what SQL Server Management Studio does for SQL Server database and uses F# to manipulate SharePoint similar to how SQL manipulates the database.

Here are some example scripts adapted from the book Inside Microsoft Windows SharePoint Services 3.0 by Ted Pattison and Daniel Larson

#I @"C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\ISAPI"
#r @"Microsoft.SharePoint.dll"

open System
open Microsoft.SharePoint

// Utility functions to convert SPxCollection to seq<SPx>
let SPListToSeq (splist:SPListCollection) =
seq { for i in 0 .. (splist.Count-1) -> splist.get_Item(i)}

let SPFieldToSeq (splist:SPFieldCollection) =
seq { for i in 0 .. (splist.Count-1) -> splist.get_Item(i)}

let SPListItemToSeq (splist:SPListItemCollection) =
seq { for i in 0 .. (splist.Count-1) -> splist.get_Item(i)}

let SPContentTypeToSeq (splist:SPContentTypeCollection) =
seq { for i in 0 .. (splist.Count-1) -> splist.get_Item(i)}

let site = new SPSite("http://localhost/")
let web = site.OpenWeb()

// Add new SharePoint List called "F# SharePoint News
let id = ("F# SharePoint News",
"List for news on F# and SharePoint items.",
|> web.Lists.Add
let list = web.Lists.[id]
list.OnQuickLaunch <- true

// Add news item to the newly created SharePoint List
let newItem = list.Items.Add()
newItem.["Title"] <-"Check for expired items today!"
newItem.["Body"] <- "We're are expiring this today and see if our query works!"
newItem.["Expires"] <- DateTime.Now

// Checking all items in "F# SharePoint News" list that expires today
let queryClause =
<FieldRef Name='Expires' />
<Value Type='DateTime'><Today /></Value>

let query = new SPQuery(ViewFields = @"<FieldRef Name='Title'/><FieldRef Name='Expires'/>",

let mylist = web.Lists.["F# Sharepoint News"]
SPListItemToSeq (mylist.GetItems(query))
|> Seq.iter (fun x -> printf "%s\n" x.Title)

// Checking available content types
SPContentTypeToSeq web.AvailableContentTypes
|> Seq.iter (fun x -> printf "%s\n\tDescription = %s\n\tID=%s\n" x.Name x.Description (x.Id.ToString()))