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.

---

#light
#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

SqlSessionProvider.Instance().SetSharedResourceProviderToUse("SharedServices1")

// Create LOB System
let lobsystem = 
   ("Adventure Works Human Resources Data",
    false,
    "Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbSystemUtility",
    "Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbConnectionManager",
    "Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbEntityInstance") 
   |> ApplicationRegistry.Instance.LobSystems.Create


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


// 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)) 

sysInstance.Update()

// 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)

("RdbCommandText",
 @"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 =
     (name,true,direction,
      "Microsoft.Office.Server.ApplicationRegistry.Infrastructure.DotNetTypeReflector")
      |> thismethod.Parameters.Create
      

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

let tdEmployees =
   let root = 
       ("EmployeeDataReader",true,
        "System.Data.IDataReader, System.Data, Version=2.0.3600.0,Culture=neutral, PublicKeyToken=b77a5c561934e089",
        null,null,true)
       |> parEmployees.CreateRootTypeDescriptor
   let node = 
       ("EmployeeDataRecord",true,
        "System.Data.IDataRecord, System.Data, Version=2.0.3600.0,Culture=neutral, PublicKeyToken=b77a5c561934e089",
        null,null,false)
       |> 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"
   firstname.Update()
    
   let lastname = 
      ("LastName", true, "System.String", null, null, false)
      |> node.ChildTypeDescriptors.Create
   lastname.LocalizedDisplayName <- "Last Name"
   lastname.Update()
   
   [("JobTitle","System.String","Job Title");
    ("Phone","System.String","Phone");
    ("EmailAddress","System.String","Email");   
    ("AddressLine1","System.String","Address");   
    ("City","System.String","City");       
    ("StateProvinceName","System.String","State");       
    ("PostalCode","System.String","Zip");       
    ] |> iter (fun (label,typename,display) ->
       ((label, true, typename, null, null, false)
       |> node.ChildTypeDescriptors.Create).LocalizedDisplayName <- display)
   root

   
// Create method instances
let getEmployees = 
   ("EmployeeFinderInstance",true,tdEmployees,Microsoft.Office.Server.ApplicationRegistry.MetadataModel.MethodInstanceType.Finder)
   |> mthd.MethodInstances.Create

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:

#light
#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.Update()
  
  spOperation 

  splist.ForceCheckout <- oldvalue
  splist.Update()

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
   | _ ->
       disableCheckout
       <| web.Lists.Item(doclib)
       <| spOperation web 

   web.Update()
   web.Dispose()
   site.Dispose()
  

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

// Add page viewer web part
let viewer = new PageViewerWebPart(SourceType=PathPattern.URL,
                                   ContentLink="http://msdn.microsoft.com/en-us/fsharp/default.aspx")

// 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)
//
addWebPart
  <| "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)

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

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

#light
#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.",
          SPListTemplateType.Announcements)
         |> web.Lists.Add
let list = web.Lists.[id]
list.OnQuickLaunch <- true
list.Update()

// 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 
newItem.Update()


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

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

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()))

web.Close()
site.Close()