Fabian Williams SharePoint Blog

Solving problems with SharePoint day and night

Why it pays to have Smart Friends to Encourage you

Why write this blog post?

Sure its late and YES I could be rotting my brain away on the Idiot Box, but what started off as a Taunt by Ram turned out to consume my life for 2 days straight but with useful results, challenges that were overcome, and a better appreciation for not just Cloud, but the integration of Cloud and On-Prem using Windows Azure.  Let me tell you what I did, and how easy it was… in the end, plus the real implications of it.

First a little history – Pre Taunt

So, as I prepare for my sessions at #SPLive in Orlando in 3 weeks, I wanted to knock out a few Demos and make sure that I could showcase just about anything anyone asked of me based on the Session Abstract/Topic. In furtherance of that, I decided to do the following, build out solutions employing

SharePoint Designer 2013

  • External Content Type using a Native SQL Data Connection
  • External Content Type using an On-Prem (i.e. Hosted on one of my Dev Rig VMs) Windows Communications Foundation [WCF] Data Connection – JSON enabled
  • External Content Type using a .NET Assembly (built on my Visual Studio 2012, Deployed as a Farm Solution to my On-Prem SP2013) Data Connection
  • Workflow using the same On-Prem WCF above in my On-Prem Farm

Visual Studio 2012

  • A Locally IIS hosted WCF Service Library that exposes Northwind data from a Local SQL Server 2012 Box
  • A Locally IIS hosted OData Service Endpoint that exposes Northwind data from a Local SQL Server 2012 Box
  • External Content Type built as a .NET Assembly to be deployed to my ON-Prem SharePoint 2013 Farm
  • External Content Type AND External List SharePoint 2013 App built as an OData Sourced Application deployed to my ON-Prem SharePoint 2013 Farm

For every Instance of the WCF and the App Model External Content Type, I also targeted my Office 365 Tennant to surface the Data as well. I was pretty satisfied with myself until my good friend sent this tweet, and I know it was in jest…


but… I took it as a challenge and, I rarely back down from a challenge 🙂 so, since I have a MSDN subscription, there was “really” nothing holding me back besides my inexperience creating Azure Web Roles, which is what you need to do in order to have the requisite Data Access Points (URI) to create External Content Types and potentially Workflows. 

What Next you say…

So, with a little research on Google scratch that, I mean Bing, I found a few MSDN, and TechNet blogs on how to create Web Roles, although not many of them “accurately” showed you how to use Visual Studio to DEPLOY your solution back to Azure. Anyway, it was surprisingly easy, although initially it was somewhat intimidating and i did get snagged on a GOTCHA which i will blog about later when doing a ASP.NET Web Role where it requires you to lower your version of the System.Data.Client assembly from 5.0 to 4.something in order for it work in Azure, i say that because it worked perfectly when I debugged it locally on my Visual Studio IIS. I also had to create an Azure SQL Instance and used a script I downloaded from CodePlex to restore a Northwind Database to my Azure SQL Instance and then I set up firewall rules to allow me to access it over the internet.

But with quick turn around and because i could just Refactor my original On-Prem code logic, I was able to create the following

Visual Studio 2012

  • Azure ASP.NET Web Role that published as a Cloud Service which used OData via ADO.NET Entity Framework to get a Cloud based URI that i can do CRUD operations on
  • Azure WCF Web Role that published as a Cloud Service which used WCF via ADO.NET Entity Framework to get a Cloud based URI that i can do CRUD operations on as well


Now I can really Mix and Match, I can

  1. Use Azure (Cloud) Hosted Data via an ON-Prem WCF or OData End Point and surface that information both on my ON-Prem SharePoint or my Office 365 Instance
  2. Use Azure (Cloud) Hosted Data via an Azure Cloud Service WCF or OData End Point and surface that information both on my ON-Prem SharePoint or my Office 365 Instance
  3. Plus everything I had when I started off

The takeaway here is that with very little investments in time and effort I extended my solution to a “MODERN” approach. I had Cloud in the sentence 🙂


Other Positive Implications

So, what made this so easy for me was I signed up for TFS Online here http://tfs.visualstudio.com/en-us/tfs-welcome.aspx and EVEN WITHOUT a MSDN Subscription you can sign up for FREE and get 5 accounts in one instance.


What I think is GOLD is…

  • You have a place to store all your CODE/ Work and before you ask, it supports different formats (see image below)
  • Your Code/Work is accessible from Anywhere you have an Internet Connection now
  • You can reliably share your Code/Work with anyone now, rather than Zipping it up and Email it
  • Need Help with some of you work… Invite people as smart or smarter than you to review our code, many hands make light load 🙂
  • Most beneficial of all is that you can configure Continual Integration. I know my good friend Jeremy Thake always talk about Application Lifecycle Management (ALM) and you can employ some of those techniques from Azure through TFS Online and your Visual Studio with multiple Team Member… and guess what… FOR FREE

So that’s all I have to say, I guess we really Paid It Forward RAM, you encouraged me to do this, I in turn, documented my efforts and hope that someone else can take it from here.


Cheers all, have a great night. Oh yeah, VS 2013 is out now. GO get it.

October 17, 2013 Posted by | Azure, JSON, OData, REST, SharePoint 2013, SharePoint 2013 Workflows, SharePoint Designer 2013, SharePoint Development, SQL Server, Visual Studio 2012, WCF | , , | Leave a comment

Part 2 of 3-Blog Update on SharePoint BCS with full CRUD

Consistently two of my most heavily viewed/visited post based on WordPress Metrics is the one highlighted below, see the image for the top 5 all

  1. How To: Create, Configure, Consume SharePoint 2010 Secure Store in Business Connectivity Services
  2. Creating a SharePoint 2010 External Content Type with CRUD Methods using LINQ and a SQL LOB System


Since I have already done this in SharePoint 2010 using Secure Store and a SQL Server Native Line of Business (LOB) System, as well as a Visual Studio 2010 .Net Assembly, I figured I can offer value by updating this post to include the following Scenarios

There are tons of examples (the #1 viewed blog above uses that as an example) on how to use SharePoint Designer to do a SQL Native Connection, so i wont bother beating that horse to death, and I would like to also point out to this excellent post by my good friend Chris Givens on how he extended the third bullet point example above to include Notification and Subscriptions Alert, so i will not be doing that either.


In this Part 2 of 3 Update I am targeting a second post cited above [Creating a SharePoint 2010 External Content Type with CRUD Methods using LINQ and a SQL LOB System]; again, I am specifically leaving out the precursor activities that are needed before you begin to create the External Content Type (ECT), I will just pick up at the point of Creating the ECT and as for cases where I introduce new methodologies, I will ofcourse showcase those aspects.

In this blog, specifically I will be showing the following:

  1. Point you to an old post by Scott Guthrie “The Gu” post on using Linq to SQL, I am going to skip that part in this post but demonstrate in my code how I use my DataContext that is created by the Linq to SQL addition to my Project Solution in Visual Studio 2012
  2. I will certainly show you how to Create your BDC Entity and the full CRUD StereoTyped Methods necessary to carry out those functions
  3. I will illustrate the chronological steps you NEED to take, otherwise you will spend Days Troubleshooting why your crap doesn’t work 🙂 and all the Gotchas that are hidden as well
  4. Deploy the Solution to the Farm & Create an External List from It

Lets Begin – Create a New Empty SharePoint 2013 Visual Studio Project

This is going to be a Farm based solution that will connect using “Linq to SQL” to our SQL Sever Database and create a DataContext class that we will use to communicate from our BCS entity to the underlying Database. 


this will be a Farm Solution


Once your project comes to life, you will need to establish a connection to your SQL Server or whatever Database you are using.  You do this by going to “Tools > Connect to Database > Fill out the Add Connection Fields similar to what i have below


and you should end up with a ORD Designer and in your “Server Explorer” you should expand that Database Connection that is now there and drag your Table/View etc to the Object Relational Designer (ORD). Now in my case I am using SQL Auth so I will get a warning, Im ok with that, this is Demoware


When I click YES, i get my Northwind Employee Entity in the context of the DataContext Class and my Project now looks like this below


Next we begin our work on the BCS piece. This does it for Data Access.

Create the BCS Data Model Entity

So, now you will need to add a new item to your project. you will add a BDCM as seen below Business Data Connectivity Model. Now, if you have read my part 1 you will know that I vehemently hate when Visual Studio gives me helper/sample implementations, well, this happens here after you name and add your item to your project.


as you can see below, you get a Entity1.cs and an Entity1Servcie.cs which ties to the GUI entity you see in the designer. We will get rid of these as we did previously in out WCF part 1 blog post and create our own entity based on the NorthWindEntity we got with the Linq To SQL DataContext we created earlier. so for now just select the two files and delete them, alternatively, you can delete the item out of the designer and “I think” it deletes the file along with it, perhaps it leaves the service file, but long and short, kill em all.


What you will after you delete those is, from the toolbar, drag a new entity onto the design surface like so and rename the entity in the Properties Window to something like “Employee” you will also notice that it creates a Serivce class for you as well, this one is called [EntityName]Service.cs


Next you will add an Identifier, by right clicking on the New Employee Identity, click Add, then click Identifier. When the Identifier appears, in the Properties Window again, change the name to EmployeeID and set the Type Name  to System.Int32


The next piece is Arguably the place MOST people will Run-A-Muck because they will either FORGET to change the TypeName of the Specific Finder to reflect the Employee Class created by the Data Context and leave it as the ‘Generic” that it is originally set to, or they will fail to set the Identifier property in this method. The reason folks mess this up is because THIS IS THE ONLY METHOD THAT REQUIRES THIS, every method thereafter inherits from this. The next things folks mess up on is the Update Method but I will go into that in detail in a few.


Add a Specific Finder Method (Read Item)

  1. Click on the Employee entity in the BDC Designer
  2. You should see in the pane at the bottom of the Visual Studio IDE a window called “BDC Method Details”, inside there click
  3. Add a Method from the dropdown list and select “Create a Specific Finder Method”
  4. A few things will be added for you by default but what I want you to concern yourself with is under “Type Descriptor” heading in the same window you will see “Employee”, click on that then click “Edit”

This should open up the BDC Explorer and a Hierarchical View should appear of the Model. The properties window should also be open as well,

  1. Locate TypeName in the Properties Window, click on it, then click “CURRENT PROJECT” tab, then select Employee which should be under the DataContext Class (it should only be 1 in there anyway)
  2. Inside the BDC Explorer, right click on Employee, and click “Add Type Descriptor” – Later on you will repeat this for every Field in the Entity(Data Store) that you want to surface paying attention to the data type of the field
  3. When the new TypeDescriptor is created, in the Properties window change the name to EmployeeID and set the TypeName to Int32, also
  4. Click the dropdown list next to Identifier and select EmployeeID

You will then repeat Step 2 for all the Fields in your Entity, refer back to my Image above that has the Employee Entity, remember to set the TypeName to the correct Field Type (Int32, String, DateTime, etc). Once that is complete, you can either (1) Double Click or (2) Right Click and select ‘View Code’’ on the ReadItem Method that is in the Design Area. This will take you inside EmployeeService.cs or whatever name gave your entity appended with Service.cs. It will be subbed out but I want you to replace what is there with

  1. public static Employee ReadItem(int employeeID)
  2.         {
  3.             NorthWindDataContext dataContext = new NorthWindDataContext
  4.             ("Data Source=Farm1Server1ADSQL;Initial Catalog=Northwind;uid=BCSUser1;pwd=P@ssword1");
  6.             Employee Employee =
  7.             (from employees in dataContext.Employees.AsEnumerable().Take(20)
  8.              where employees.EmployeeID == employeeID
  9.              select employees).Single();
  10.             return Employee;
  11.         }


Add a Finder Method (Read List)

  1. Click on the Employee entity in the BDC Designer
  2. You should see in the pane at the bottom of the Visual Studio IDE a window called “BDC Method Details”, inside there click
  3. Add a Method from the dropdown list and select “Create Finder Method”

Once that is complete, you can either (1) Double Click or (2) Right Click and select ‘View Code’’ on the ReadList Method that is in the Design Area. This will take you inside EmployeeService.cs or whatever name gave your entity appended with Service.cs. It will be subbed out but I want you to replace what is there with

  1. public static IEnumerable<Employee> ReadList()
  2.       {
  3.           NorthWindDataContext dataContext = new NorthWindDataContext
  4.           ("Data Source=Farm1Server1ADSQL;Initial Catalog=Northwind;uid=BCSUser1;pwd=P@ssword1");
  7.           IEnumerable<Employee> Employees =
  8.               from employees in dataContext.Employees
  9.               select employees;
  10.           return Employees;
  11.       }


Add a Creator Method

  1. Click on the Employee entity in the BDC Designer
  2. You should see in the pane at the bottom of the Visual Studio IDE a window called “BDC Method Details”, inside there click
  3. Add a Method from the dropdown list and select “Create Creator Method”

Once that is complete, you can either (1) Double Click or (2) Right Click and select ‘View Code’’ on the Create Method that is in the Design Area. This will take you inside EmployeeService.cs or whatever name gave your entity appended with Service.cs. It will be subbed out but I want you to replace what is there with

  1. public static Employee Create(Employee newEmployee)
  2.   {
  3.       NorthWindDataContext dataContext = new NorthWindDataContext
  4.       ("Data Source=Farm1Server1ADSQL;Initial Catalog=Northwind;uid=BCSUser1;pwd=P@ssword1");
  7.       Employee emp = new Employee();
  9.       emp.FirstName = newEmployee.FirstName;
  10.       emp.LastName = newEmployee.LastName;
  11.       emp.Title = newEmployee.Title;
  12.       emp.TitleOfCourtesy = newEmployee.TitleOfCourtesy;
  13.       emp.BirthDate = newEmployee.BirthDate;
  14.       emp.HireDate = newEmployee.HireDate;
  15.       emp.Address = newEmployee.Address;
  16.       emp.City = newEmployee.City;
  17.       emp.Region = newEmployee.Region;
  18.       emp.PostalCode = newEmployee.PostalCode;
  19.       emp.Country = newEmployee.Country;
  20.       emp.HomePhone = newEmployee.HomePhone;
  21.       emp.Extension = newEmployee.Extension;
  22.       emp.Notes = newEmployee.Notes;
  24.       dataContext.Employees.InsertOnSubmit(emp);
  25.       dataContext.SubmitChanges();
  26.       return emp;
  27.   }


Add a Updater Method

  1. Click on the Employee entity in the BDC Designer
  2. You should see in the pane at the bottom of the Visual Studio IDE a window called “BDC Method Details”, inside there click
  3. Add a Method from the dropdown list and select “Create Updater Method”


Now based on YOUR particular Data Source/Store you may have a Primary Key that either (1) AutoUpdates or (2) Doesnt – If it DOES NOT Auto Update you MUST click on the Employee Type Descriptor in the BDC Method Details Window and in the Properties Window you MUST set he “Pre-Updater Field” to True


If YOUR Primary Key actually DOES Auto Increment, then what “I” have found to work for me is to Add another Type Descriptor to the Updater Method (see Image below), name it appropriately and SET its Pre-Updater property to TRUE


This means that the Updater Method will take in two Parameters now, not one as most MSDN and TechNet articles will say, but I am only using my EmployeeID Input Parameter to locate the Specific Item needing to update and the employee parameter to set the Fields of what will be committed back to the Data Source. All attempts to do it otherwise GAVE AN ERROR about the EmployeeID being a Read Only Field and needed the PreUpdate Field set to True when using an External List to Update an Item.


Once that is complete, you can either (1) Double Click or (2) Right Click and select ‘View Code’’ on the Update Method that is in the Design Area. This will take you inside EmployeeService.cs or whatever name gave your entity appended with Service.cs. It will be subbed out but I want you to replace what is there with

  1. public static void Update(Employee employee, int parameter)
  2.     {
  3.         NorthWindDataContext dataContext = new NorthWindDataContext
  4.         ("Data Source=Farm1Server1ADSQL;Initial Catalog=Northwind;uid=BCSUser1;pwd=P@ssword1");
  6.         var employeeToUpdate = (from employees in dataContext.Employees
  7.                                where employees.EmployeeID == parameter
  8.                                select employees).Single();
  10.         employeeToUpdate.FirstName = employee.FirstName;
  11.         employeeToUpdate.LastName = employee.LastName;
  12.         employeeToUpdate.Title = employee.Title;
  13.         employeeToUpdate.TitleOfCourtesy = employee.TitleOfCourtesy;
  14.         employeeToUpdate.BirthDate = employee.BirthDate;
  15.         employeeToUpdate.HireDate = employee.HireDate;
  16.         employeeToUpdate.Address = employee.Address;
  17.         employeeToUpdate.City = employee.City;
  18.         employeeToUpdate.Region = employee.Region;
  19.         employeeToUpdate.PostalCode = employee.PostalCode;
  20.         employeeToUpdate.Country = employee.Country;
  21.         employeeToUpdate.HomePhone = employee.HomePhone;
  22.         employeeToUpdate.Extension = employee.Extension;
  23.         employeeToUpdate.Notes = employee.Notes;
  24.         dataContext.SubmitChanges();
  25.     }


Add a Deleter Method

  1. Click on the Employee entity in the BDC Designer
  2. You should see in the pane at the bottom of the Visual Studio IDE a window called “BDC Method Details”, inside there click
  3. Add a Method from the dropdown list and select “Create a Deleter Method”

Once that is complete, you can either (1) Double Click or (2) Right Click and select ‘View Code’’ on the Delete Method that is in the Design Area. This will take you inside EmployeeService.cs or whatever name gave your entity appended with Service.cs. It will be subbed out but I want you to replace what is there with


  2. public static void Delete(int employeeID)
  3. {
  4.     NorthWindDataContext dataContext = new NorthWindDataContext
  5.     ("Data Source=Farm1Server1ADSQL;Initial Catalog=Northwind;uid=BCSUser1;pwd=P@ssword1");
  7.     Employee Employee =
  8.     (from employees in dataContext.Employees.AsEnumerable().Take(20)
  9.      where employees.EmployeeID == employeeID
  10.      select employees).Single();
  13.     dataContext.Employees.DeleteOnSubmit(Employee);
  14.     dataContext.SubmitChanges();
  16. }


All in all the full code should look like below when you put it all together.


  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using NorthWindEmployees;
  7. namespace NorthWindEmployees.NWindFarmECTEmployees
  8. {
  9.     public partial class EmployeeService
  10.     {
  11.         public static Employee ReadItem(int employeeID)
  12.         {
  13.             NorthWindDataContext dataContext = new NorthWindDataContext
  14.             ("Data Source=Farm1Server1ADSQL;Initial Catalog=Northwind;uid=BCSUser1;pwd=P@ssword1");
  16.             Employee Employee =
  17.             (from employees in dataContext.Employees.AsEnumerable().Take(20)
  18.              where employees.EmployeeID == employeeID
  19.              select employees).Single();
  20.             return Employee;
  21.         }
  23.         public static IEnumerable<Employee> ReadList()
  24.         {
  25.             NorthWindDataContext dataContext = new NorthWindDataContext
  26.             ("Data Source=Farm1Server1ADSQL;Initial Catalog=Northwind;uid=BCSUser1;pwd=P@ssword1");
  29.             IEnumerable<Employee> Employees =
  30.                 from employees in dataContext.Employees
  31.                 select employees;
  32.             return Employees;
  33.         }
  35.         public static Employee Create(Employee newEmployee)
  36.         {
  37.             NorthWindDataContext dataContext = new NorthWindDataContext
  38.             ("Data Source=Farm1Server1ADSQL;Initial Catalog=Northwind;uid=BCSUser1;pwd=P@ssword1");
  41.             Employee emp = new Employee();
  43.             emp.FirstName = newEmployee.FirstName;
  44.             emp.LastName = newEmployee.LastName;
  45.             emp.Title = newEmployee.Title;
  46.             emp.TitleOfCourtesy = newEmployee.TitleOfCourtesy;
  47.             emp.BirthDate = newEmployee.BirthDate;
  48.             emp.HireDate = newEmployee.HireDate;
  49.             emp.Address = newEmployee.Address;
  50.             emp.City = newEmployee.City;
  51.             emp.Region = newEmployee.Region;
  52.             emp.PostalCode = newEmployee.PostalCode;
  53.             emp.Country = newEmployee.Country;
  54.             emp.HomePhone = newEmployee.HomePhone;
  55.             emp.Extension = newEmployee.Extension;
  56.             emp.Notes = newEmployee.Notes;
  58.             dataContext.Employees.InsertOnSubmit(emp);
  59.             dataContext.SubmitChanges();
  60.             return emp;
  61.         }
  63.         public static void Update(Employee employee, int parameter)
  64.         {
  65.             NorthWindDataContext dataContext = new NorthWindDataContext
  66.             ("Data Source=Farm1Server1ADSQL;Initial Catalog=Northwind;uid=BCSUser1;pwd=P@ssword1");
  68.             var employeeToUpdate = (from employees in dataContext.Employees
  69.                                    where employees.EmployeeID == parameter
  70.                                    select employees).Single();
  72.             employeeToUpdate.FirstName = employee.FirstName;
  73.             employeeToUpdate.LastName = employee.LastName;
  74.             employeeToUpdate.Title = employee.Title;
  75.             employeeToUpdate.TitleOfCourtesy = employee.TitleOfCourtesy;
  76.             employeeToUpdate.BirthDate = employee.BirthDate;
  77.             employeeToUpdate.HireDate = employee.HireDate;
  78.             employeeToUpdate.Address = employee.Address;
  79.             employeeToUpdate.City = employee.City;
  80.             employeeToUpdate.Region = employee.Region;
  81.             employeeToUpdate.PostalCode = employee.PostalCode;
  82.             employeeToUpdate.Country = employee.Country;
  83.             employeeToUpdate.HomePhone = employee.HomePhone;
  84.             employeeToUpdate.Extension = employee.Extension;
  85.             employeeToUpdate.Notes = employee.Notes;
  86.             dataContext.SubmitChanges();
  87.         }
  89.         public static void Delete(int employeeID)
  90.         {
  91.             NorthWindDataContext dataContext = new NorthWindDataContext
  92.             ("Data Source=Farm1Server1ADSQL;Initial Catalog=Northwind;uid=BCSUser1;pwd=P@ssword1");
  94.             Employee Employee =
  95.             (from employees in dataContext.Employees.AsEnumerable().Take(20)
  96.              where employees.EmployeeID == employeeID
  97.              select employees).Single();
  100.             dataContext.Employees.DeleteOnSubmit(Employee);
  101.             dataContext.SubmitChanges();
  103.         }
  104.     }
  105. }


In a nutshell you are done, and you can just “F5” to debug and test it, or just “Deploy” from here, BUT!!! you can also copy that BDCM file out. Its what was previously known as your Application Definition File or now your Model File and you can Import that INTO

  • SharePoint Designer or
  • Central Administration

Why would you do that? Maybe your SDLC calls for separation, and your coders need to and off here. This is just an XML file, i have a snippet below

  1. <?xml version="1.0" encoding="utf-8"?>
  2. <Model xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog" Name="NWindFarmECTEmployees">
  3.   <LobSystems>
  4.     <LobSystem Name="NWindFarmECTEmployees" Type="DotNetAssembly">
  5.       <LobSystemInstances>
  6.         <LobSystemInstance Name="NWindFarmECTEmployees" />
  7.       </LobSystemInstances>
  8.       <Entities>
  9.         <Entity Name="Employee" Namespace="NorthWindEmployees.NWindFarmECTEmployees" Version="">
  10.           <Properties>
  11.             <Property Name="Class" Type="System.String">NorthWindEmployees.NWindFarmECTEmployees.EmployeeService, NWindFarmECTEmployees</Property>
  12.           </Properties>
  13.           <Identifiers>
  14.             <Identifier Name="EmployeeID" TypeName="System.Int32" />
  15.           </Identifiers>
  16.           <Methods>
  17.             <Method Name="ReadItem">
  18.               <Parameters>
  19.                 <Parameter Name="employee" Direction="Return">
  20.                   <TypeDescriptor Name="Employee" TypeName="NorthWindEmployees.Employee, NWindFarmECTEmployees" IsCollection="false" PreUpdaterField="false">
  21.                     <TypeDescriptors>
  22.                       <TypeDescriptor Name="EmployeeID" TypeName="System.Int32" IsCollection="false" IdentifierName="EmployeeID" />
  23.                       <TypeDescriptor Name="LastName" TypeName="System.String" />
  24.                       <TypeDescriptor Name="FirstName" TypeName="System.String" />
  25.                       <TypeDescriptor Name="Title" TypeName="System.String" />
  26.                       <TypeDescriptor Name="TitleOfCourtesy" TypeName="System.String" />
  27.                       <TypeDescriptor Name="BirthDate" TypeName="System.DateTime" IsCollection="false" />
  28.                       <TypeDescriptor Name="HireDate" TypeName="System.DateTime" IsCollection="false" />
  29.                       <TypeDescriptor Name="Address" TypeName="System.String" />
  30.                       <TypeDescriptor Name="City" TypeName="System.String" />
  31.                       <TypeDescriptor Name="Region" TypeName="System.String" />
  32.                       <TypeDescriptor Name="PostalCode" TypeName="System.String" />
  33.                       <TypeDescriptor Name="Country" TypeName="System.String" />
  34.                       <TypeDescriptor Name="HomePhone" TypeName="System.String" />
  35.                       <TypeDescriptor Name="Extension" TypeName="System.String" />
  36.                       <TypeDescriptor Name="Notes" TypeName="System.String" /></TypeDescriptors></TypeDescriptor></Parameter>
  37.                 <Parameter Name="employeeID" Direction="In">
  38.                   <TypeDescriptor Name="EmployeeID" TypeName="System.Int32" IdentifierEntityName="Employee" IdentifierEntityNamespace="NorthWindEmployees.NWindFarmECTEmployees" IdentifierName="EmployeeID" PreUpdaterField="false" /></Parameter>
  39.               </Parameters>
  40.               <MethodInstances>
  41.                 <MethodInstance Name="ReadItem" Type="SpecificFinder" ReturnParameterName="employee" ReturnTypeDescriptorPath="Employee" />
  42.               </MethodInstances></Method>
  43.             <Method Name="ReadList">
  44.               <Parameters>
  45.                 <Parameter Name="employeeList" Direction="Return">
  46.                   <TypeDescriptor Name="EmployeeList" TypeName="System.Collections.Generic.IEnumerable`1[[NorthWindEmployees.Employee, NWindFarmECTEmployees]]" IsCollection="true">
  47.                     <TypeDescriptors>
  48.                       <TypeDescriptor Name="Employee" IsCollection="false" TypeName="NorthWindEmployees.Employee, NWindFarmECTEmployees">
  49.                         <TypeDescriptors>
  50.                           <TypeDescriptor Name="EmployeeID" IdentifierName="EmployeeID" IsCollection="false" TypeName="System.Int32" />
  51.                           <TypeDescriptor Name="LastName" TypeName="System.String" />
  52.                           <TypeDescriptor Name="FirstName" TypeName="System.String" />
  53.                           <TypeDescriptor Name="Title" TypeName="System.String" />
  54.                           <TypeDescriptor Name="TitleOfCourtesy" TypeName="System.String" />
  55.                           <TypeDescriptor Name="BirthDate" IsCollection="false" TypeName="System.DateTime" />
  56.                           <TypeDescriptor Name="HireDate" IsCollection="false" TypeName="System.DateTime" />
  57.                           <TypeDescriptor Name="Address" TypeName="System.String" />
  58.                           <TypeDescriptor Name="City" TypeName="System.String" />
  59.                           <TypeDescriptor Name="Region" TypeName="System.String" />
  60.                           <TypeDescriptor Name="PostalCode" TypeName="System.String" />
  61.                           <TypeDescriptor Name="Country" TypeName="System.String" />
  62.                           <TypeDescriptor Name="HomePhone" TypeName="System.String" />
  63.                           <TypeDescriptor Name="Extension" TypeName="System.String" />
  64.                           <TypeDescriptor Name="Notes" TypeName="System.String" /></TypeDescriptors></TypeDescriptor></TypeDescriptors></TypeDescriptor></Parameter>
  65.               </Parameters>
  66.               <MethodInstances>
  67.                 <MethodInstance Name="ReadList" Type="Finder" ReturnParameterName="employeeList" ReturnTypeDescriptorPath="EmployeeList" />
  68.               </MethodInstances></Method>
  69.             <Method Name="Create">
  70.               <Parameters>
  71.                 <Parameter Name="returnEmployee" Direction="Return">
  72.                   <TypeDescriptor Name="ReturnEmployee" IsCollection="false" TypeName="NorthWindEmployees.Employee, NWindFarmECTEmployees">
  73.                     <TypeDescriptors>
  74.                       <TypeDescriptor Name="EmployeeID" IdentifierName="EmployeeID" IsCollection="false" TypeName="System.Int32" />
  75.                       <TypeDescriptor Name="LastName" TypeName="System.String" />
  76.                       <TypeDescriptor Name="FirstName" TypeName="System.String" />
  77.                       <TypeDescriptor Name="Title" TypeName="System.String" />
  78.                       <TypeDescriptor Name="TitleOfCourtesy" TypeName="System.String" />
  79.                       <TypeDescriptor Name="BirthDate" IsCollection="false" TypeName="System.DateTime" />
  80.                       <TypeDescriptor Name="HireDate" IsCollection="false" TypeName="System.DateTime" />
  81.                       <TypeDescriptor Name="Address" TypeName="System.String" />
  82.                       <TypeDescriptor Name="City" TypeName="System.String" />
  83.                       <TypeDescriptor Name="Region" TypeName="System.String" />
  84.                       <TypeDescriptor Name="PostalCode" TypeName="System.String" />
  85.                       <TypeDescriptor Name="Country" TypeName="System.String" />
  86.                       <TypeDescriptor Name="HomePhone" TypeName="System.String" />
  87.                       <TypeDescriptor Name="Extension" TypeName="System.String" />
  88.                       <TypeDescriptor Name="Notes" TypeName="System.String" /></TypeDescriptors></TypeDescriptor></Parameter>
  89.                 <Parameter Name="newEmployee" Direction="In">
  90.                   <TypeDescriptor Name="NewEmployee" IsCollection="false" TypeName="NorthWindEmployees.Employee, NWindFarmECTEmployees">
  91.                     <TypeDescriptors>
  92.                       <TypeDescriptor Name="EmployeeID" IdentifierName="EmployeeID" IsCollection="false" TypeName="System.Int32" CreatorField="true" />
  93.                       <TypeDescriptor Name="LastName" TypeName="System.String" CreatorField="true" />
  94.                       <TypeDescriptor Name="FirstName" TypeName="System.String" CreatorField="true" />
  95.                       <TypeDescriptor Name="Title" TypeName="System.String" CreatorField="true" />
  96.                       <TypeDescriptor Name="TitleOfCourtesy" TypeName="System.String" CreatorField="true" />
  97.                       <TypeDescriptor Name="BirthDate" IsCollection="false" TypeName="System.DateTime" CreatorField="true" />
  98.                       <TypeDescriptor Name="HireDate" IsCollection="false" TypeName="System.DateTime" CreatorField="true" />
  99.                       <TypeDescriptor Name="Address" TypeName="System.String" CreatorField="true" />
  100.                       <TypeDescriptor Name="City" TypeName="System.String" CreatorField="true" />
  101.                       <TypeDescriptor Name="Region" TypeName="System.String" CreatorField="true" />
  102.                       <TypeDescriptor Name="PostalCode" TypeName="System.String" CreatorField="true" />
  103.                       <TypeDescriptor Name="Country" TypeName="System.String" CreatorField="true" />
  104.                       <TypeDescriptor Name="HomePhone" TypeName="System.String" CreatorField="true" />
  105.                       <TypeDescriptor Name="Extension" TypeName="System.String" CreatorField="true" />
  106.                       <TypeDescriptor Name="Notes" TypeName="System.String" CreatorField="true" /></TypeDescriptors></TypeDescriptor></Parameter>
  107.               </Parameters>
  108.               <MethodInstances>
  109.                 <MethodInstance Name="Create" Type="Creator" ReturnParameterName="returnEmployee" ReturnTypeDescriptorPath="ReturnEmployee" />
  110.               </MethodInstances></Method>
  111.             <Method Name="Update">
  112.               <Parameters>
  113.                 <Parameter Name="employee" Direction="In">
  114.                   <TypeDescriptor Name="Employee" IsCollection="false" TypeName="NorthWindEmployees.Employee, NWindFarmECTEmployees">
  115.                     <TypeDescriptors>
  116.                       <TypeDescriptor Name="EmployeeID" IdentifierName="EmployeeID" IsCollection="false" TypeName="System.Int32" UpdaterField="true" />
  117.                       <TypeDescriptor Name="LastName" TypeName="System.String" UpdaterField="true" />
  118.                       <TypeDescriptor Name="FirstName" TypeName="System.String" UpdaterField="true" />
  119.                       <TypeDescriptor Name="Title" TypeName="System.String" UpdaterField="true" />
  120.                       <TypeDescriptor Name="TitleOfCourtesy" TypeName="System.String" UpdaterField="true" />
  121.                       <TypeDescriptor Name="BirthDate" IsCollection="false" TypeName="System.DateTime" UpdaterField="true" />
  122.                       <TypeDescriptor Name="HireDate" IsCollection="false" TypeName="System.DateTime" UpdaterField="true" />
  123.                       <TypeDescriptor Name="Address" TypeName="System.String" UpdaterField="true" />
  124.                       <TypeDescriptor Name="City" TypeName="System.String" UpdaterField="true" />
  125.                       <TypeDescriptor Name="Region" TypeName="System.String" UpdaterField="true" />
  126.                       <TypeDescriptor Name="PostalCode" TypeName="System.String" UpdaterField="true" />
  127.                       <TypeDescriptor Name="Country" TypeName="System.String" UpdaterField="true" />
  128.                       <TypeDescriptor Name="HomePhone" TypeName="System.String" UpdaterField="true" />
  129.                       <TypeDescriptor Name="Extension" TypeName="System.String" UpdaterField="true" />
  130.                       <TypeDescriptor Name="Notes" TypeName="System.String" UpdaterField="true" /></TypeDescriptors></TypeDescriptor></Parameter>
  131.                 <Parameter Name="parameter" Direction="In">
  132.                   <TypeDescriptor Name="EmployeeID" TypeName="System.Int32" IsCollection="false" IdentifierName="EmployeeID" PreUpdaterField="true" /></Parameter>
  133.               </Parameters>
  134.               <MethodInstances>
  135.                 <MethodInstance Name="Update" Type="Updater" />
  136.               </MethodInstances></Method>
  137.             <Method Name="Delete">
  138.               <Parameters>
  139.                 <Parameter Name="employeeID" Direction="In">
  140.                   <TypeDescriptor Name="EmployeeID" TypeName="System.Int32" IdentifierEntityName="Employee" IdentifierEntityNamespace="NorthWindEmployees.NWindFarmECTEmployees" IdentifierName="EmployeeID" /></Parameter>
  141.               </Parameters>
  142.               <MethodInstances>
  143.                 <MethodInstance Name="Delete" Type="Deleter" />
  144.               </MethodInstances></Method>
  145.           </Methods></Entity>
  146.       </Entities>
  147.     </LobSystem>
  148.   </LobSystems>
  149. </Model>


For US howevever we will just Deploy it. What we expect to see then in Central Admin under the BDC Service Application is the following

  • A brand new External Content Type
  • A new Model and
  • A new External System


As in my original post, make sure you also do the following as it relates to the ECT

  1. Set the Metadata Store Permissions
  2. Set at a Minimum the “Execute” Permissions on the ECT so that people can use it. You may consider giving the Search Account permissions if you intend to use this ECT as a Content Source in Search

The Finale

Now we create our External List and we should have FULL CRUD Capabilities. Here you can see the Methods exposed though their actions. To see and learn more come see my VS Live 360 Session that i spoke about on my blog here.


October 14, 2013 Posted by | Business Connectivity Services, Secure Store, SharePoint 2013, SharePoint Development, SharePoint How-To, Visual Studio 2012 | , , , , | 2 Comments

Come see my three session at SPLive360 in Orlando

What am I speaking on

I am honored to be accepted to speak at this auspicious event led by co-Chairs Andrew Connell and Dan Holme this November 18th through 22nd, at the Royal Pacific Resort at Universal in Orlando, Florida. I will be delivering three sessions

  1. SPH14 Case Study: When Should I Use SharePoint 2013 Business Connectivity Services (BCS) and When Should I Use SharePoint 2013 Workflows to Interact with External
  2. SPW11 No-Code CRUD Business Connectivity Services (BCS) Solutions Using SharePoint Designer 2013
  3. SPW02 What’s New with SharePoint Business Connectivity Services (BCS) and OData Services

SAVE MORE when your REGISTER by using PromoCode: SPLSP21


What to expect?

if you have ever been to any of my sessions before, you will know it is highly interactive, and we remain in dialog for the entire time. My demos will incorporate your ideas and challenges, therefore we all walk away winners!

Indeed, there is a good mix for just about everybody over these three sessions; Im actually getting back to my BCS roots after spending the last few months, maybe a year now i think focusing in large part on Workflows and External Data. Infact, the Case Study session is all about how to make the decision of using a Workflow v/s using BCS.

Emphasis on External Data

All these sessions have a common theme…External Data… and in that vein, we will begin the sessions by discussing various types of External Data, their entry point to SharePoint and also how to manipulate them in the browser and Fiddler. I feel it is important for us to get/set our expectations of what our desired results should be before we get too far into the technical weeds of how SharePoint can expose/surface this data.

At this very moment of blog authorship [October, 10, 2013 1721 hrs] Im actually building out my Data Services, different flavors (Native SQL, oData, WCF) so we can have a few interaction points and see full CRUD-Q capacities between SharePoint Designer and Visual Studio.


As we work in a world consumed by data, we are often challenged to make sense out of it, i.e. get INFORMATION from DATA, and the speed to which you can accomplish that usually will determine your success over another. My job in these sessions is to demonstrate various techniques to that end (the HOW), and also in the case of the Case Study Session, engage you in conversation as to the WHY.


October 10, 2013 Posted by | Business Connectivity Services, Public Speaking, REST, SharePoint 2013, SharePoint 2013 Workflows, SharePoint Designer 2013, SharePoint Development, SharePoint How-To, Visual Studio 2012, Where is Fabian, Workflows | , , , , , , | 1 Comment

Solving Recursive Loops in SharePoint Designer using SandBoxed Solutions

This is a blog post that I looked forward to writing; this post deals with a problem that has plagued SharePoint Workflows when limited to using SharePoint Designer, and a solution is here now in SharePoint 2010 using Sandboxed Solutions. What problem is this you may ask, well it is the means by which you implement a loop structure in your workflow, you may notice that here is no such activity as an available option in SharePoint Designer 2007 or 2010, hmm I wonder will it be in the next version? J Inquiring minds want to know.

Problem Stated

You have two SharePoint Lists, data elements in List A are related to data elements in List B. You want to make a change to a singular item in List A and for every related element in List B, you want to effectuate a change as well.

In practical terms lets say that you have a pool of candidates that you plan to hire, those candidates are in List A, they will go through a list of interviews with individuals and teams. You want a workflow such that if (1) During the process, the candidate drops out of consideration [Cancelled], is put on a wait list [Deffered], or “knows someone” so they don’t need all those formalaties[Approved].. I know im stretching on the last one. But basically you want it, for as many people involved in that process [List B], for it to update the status of that candidate to those (for now) Deffered and Cancelled status.

How would you do it if you were limited to creating the workflow in SharePoint Designer 2010?

Above: List A – The Candidates Table (Before Shot)

Above: List B- The Interview Board Tasks (Before Shot)

Solution Design

Now, there are several post and guidance’s out there in the Blog-osphere that speak to solutions that employ using Multiple List and Libraries to act as counters with delay activities to ensure that the timing is maintained between iterating through the items in LIST B when the helper table managing the counter is in a work state; now I have my opinions on those solutions, but notwithstanding that, I fully understand that those solutions in the past were necessary in order to get the job done. Enter today with SharePoint 2010 and Sandboxed Solutions; this affords us the ability to write targeted solutions that are scoped at the Site Collections level and accessible through SharePoint Designer tooling, here is an excerpt of what a Sandboxed Solution is taken from Microsoft’s site.

A sandbox is a restricted execution environment that enables programs to access only certain resources, and that keeps problems that occur in the sandbox from affecting the rest of the server environment. Solutions that you deploy into a sandbox, which are known as sandboxed solutions, cannot use certain computer and network resources, and cannot access content outside the site collection they are deployed in. For more information about solutions, see Solutions Overview(http://go.microsoft.com/fwlink/p/?LinkID=156638).


Using Custom Workflow Activities, create a loop structure to look for changes in List A (on changed event fires) and then traverse List B, looking for the qualifying criteria that will count as a match in List A, then update that item. Loop through the entire list until all every list item has been evaluated. This logic will be encompassed in a Method of a Class you will create, the class must be scoped “public” and the method will take as a parameter “SPUserCodeWorkflowContext” object and it will return a type of Hashtable, also remember that your feature needs to be Scoped at “Site”.

  • Create a Visual Studio Empty SharePoint Project that is Scoped to the Sandbox
  • Create Logic to use the SharePoint API to traverse items in List B based on changes in List A
  • Deploy the Solution, open up SharePoint Designer for the Site Collection it is Activated on
  • Use the Custom Activity in SharePoint Designer as you would for any other OOB Activity


  1. Create your SharePoint Project

Begin by launching Visual Studio and create an Empty Project, scoped at Sandboxed Solution in the Project Properties or as you are setting up your Project for the first time. Next add a Class to create the method.

You are not over quite yet, now you need to make this available to SharePoint Designer, we do that by adding an Empty Module to our project. In my example below I am calling it CASDefinition and it includes and Elements.xml file that I will modify as such…

You will notice that the structure of the file lends itself to how it will be viewed and used in SharePoint Designer; also there is a reference to the class file function names, class names[lines 4 through 8], what category (placement)[my self serving line 10] it will appear in SharePoint Designer. Next you have parameters, now certainly here I could have made this configurable by taking the List name as an input Parameter or maybe a specific parameter that I wanted to update rather than all, but this is a demo, you get the idea, for now I have an output parameter.

By now, your project should look like this below.

A few housekeeping notes here, I did mention it above but just to re-inforce it, please change the Feature File to “Site” Scope so it is available at the Site Collection level, and you can see in the project properties that the “Sandboxed Solutions” property is set to true. One more thing, you will see that there is another project in there, “CASTester”, that is just a console app I use for testing ANY solution logic, it is not necessary for this to work.

Assemble your SharePoint Designer Workflow

Now that you have deployed your package, you should see it as an available option in SharePoint Designer 2010 under Workflow Actions.

Notice the Fabian Custom Actions section and the Name of the Custom Workflow Activity therein. Now at this point you can set this workflow to run on a Changed Event in List A, or set it for Manual, either way, when it runs, it will subsequently change all items related in List B accordingly.



Above: List A – The Candidates Table (After Shot)


Above: List B- The Interview Board Tasks (After Shot)



So now you have a tried and tested way of doing Loops in SharePoint Designer 2010 albeit through a Custom Workflow Activity within the boundaries of a Sandboxed Solution in SharePoint 2010.


Cheers, happy SharePoint-ing



April 2, 2012 Posted by | SharePoint Development | 2 Comments

Error/Resolution: Could not load type ‘System.Data.Services.Providers.IDataServiceUpdateProvider’ from…


Cryptic message huh, but basically i got a tweet from @dfollette regarding using the Client Site Object Model (CSOM) in SharePoint to gain access to List Data and other LOB System and he also had a reference in a Web Cast to do the same thing using the REST API.  Even as tired as i was at 1:16 am in the morning it would just eat at me in bed, so i decided to review it before I tuck in.  However, when I tried to review the REST API by using a SharePoint Web Service I got an error…

The tweet was:


The Error was:




So after a little research I found this blog entry “REST and SharePoint 2010 Quick Start Guide: Table of Contents” on Scott Currier Blog; and it indicated that you need to install install the ADO.NET Data Services Update for .NET 3.5 SP1 Depending on what flavor OS you are running you may need one over the other, the one i need was here because I am running WIN 2 K8 R2 http://www.microsoft.com/downloads/details.aspx?familyid=79d7f6f8-d6e9-4b8c-8640-17f89452148e&displaylang=en

Once you install it you will be prompted to restart.



Trust but Verify

So after my reboot, let us now go to my SharePoint Site Collection and query the REST API again.

What i wanted to test was a little nugget I learned from the web cast…


You know come to think of it, I should have done a snapshot before running that update, but oh well, with finger crossed…


and it worked, lets drill down now


Looking at the XML you see the actual data..


July 6, 2010 Posted by | SharePoint 2010, SharePoint 2010 RTM, SharePoint Bug, SharePoint Development, SharePoint Error/Resolution, Strange Stuff | Leave a comment

How To: Create Content Types with Site Columns in Code Visual Studio 2010 for SharePoint 2010

Synopsis: So why would you Ever Ever need to feel the pain of creating Content Types from scratch and associate Site Columns also in code with them? Well, the one glaring reason is that if you create site columns and content types out of Code, you loose the control of assigning the GUID which is in effect the id designator of the object.  If you cannot control that, then as you move through a proper SDLC with a DTP environment, you in essence loose control over the same ID’s [objects] being the same across all the environment.  The second reason is that you have a clean and automated way to deploy your solution to any environment, inherently you could package and sell this solution if you wanted to.

So how do you do it?

Sahil Malk (@sahilmalik)has a great book in Building Solution in SharePoint 2010 and he has a great chapter on this topic. I used that as my premise for creating a full fledge Content Type with Site Columns to boot. Doing this in Visual Studio 2010 on SharePoint 2010 and the entire process to deploy is so easy now, even a caveman can do it.

Like Bud Light — Here we go!

First we need to start a new SharePoint Empty Project and start to build your solution out.


Next, and very appropriately, we are doing this as a Sandbox Solution because EVERYTHING we are doing for this effort will be in the Database, nothing will be on the File System. This is where you as a developer can do all the damage you want without worrying about the Farm Admin calling you saying “…hey dude, you brought down the server..” will never happen, sure you can bring down the Site Collection but hey, thats on you buddy..


Once you have your Sandbox Solution set, lets had some “SharePoint Items” first will be a Content Type


As you see below, I am calling mine AdotobClient. The idea here is that for my company Adotob, LLC, I am creating a Content Type that inherits from the Item Content Type which will hold Client specific data. The important thing here is that as a Content Type, this can  be used all across the Enterprise in any Web by anyone.  Build it once, use it many.


Once named, now your project will look like this.


As I mentioned before and as you will see below, everything in SharePoint inherits from a base, in this example I am inheriting from the Item Content Type, the wizard asks me to choose and i selected my choice



Now we will do some clean up. When you add the Content Type, you also get an elements.xml file created for the definition of the Content Type, now realistically if this content type is to be of any use, it will need Site Columns, but for now, we will just rename the elements.xml to something more descriptive.



So, this Content Type will have a few site columns to express what it is about the client we want to capture, it will be of many data types, text, date/time, etc. lets go and add an Empty Element which will be the Site Columns and populate the Elements.xml with the fieldtypes.


Before we do lets spot check what our Project looks like now.


As for clean up, lets rename the Site Columns elements.xml to a more representative name…


Lets us now flesh out the Site Columns, as you see below i am capturing all the necessary information one would capture about a client. Name, address, contact info, social content, etc. Below I use the Make Guid tool in Visual Studio to create those Field ID’s, and i with all my cut and paste, i screwed up the last entry, that type was to be a Text but i left it as date time.. oops.


Once we do that, we now associate those site columns via the GUID [Field ID == Field Ref] in what was the elements.xml of the Content Type


Once you are done, go ahead and build and deploy your solution. Oh make sure that you have Sandbox Solution Service turned on in the Central Administration before you do that, it is not on by default. Below if you inspected your Site Settings at the top level you will find the new Content Type

Trust but Verify



Click inside the Content Type, and you will find the site columns we defined.


Once we have done that lets spin up a new Custom List. We will include this content type in there and create an entry


To do that we need to manage content types…


We select the one we created and move it over


I also disabled the Item default content type what was there before so the only one that is an option is the one we created.. see below


Now we will make an entry for a new client



And our result is..


Voilla! you have a Content Type with Site Columns that you can manage NOT ONLY across the enterprise, but between Development, Test and Production Environment.

As usual, your comments, critique and questions are welcomed.

July 3, 2010 Posted by | Content Types, SharePoint 2010, SharePoint 2010 RTM, SharePoint Development, Site Columns, Visual Studio 2010 | 8 Comments

How To: So you have a GUID in your External List huh, Yes you can Update the List… if you set it up right

Synopsis:  I fielded a question on MSDN again and it peaked my interest because it had to do with of course BCS. The stated question was an issue when trying to update an External List when a GUID was present.  So I setup a new table, created a… well you will see it below.



Part 1: Set the Environment and Duplicate the Issue


Above: So I created the Table above and as you can see I used a GUID with (newid()) property set


Above: Added two people to the list and as you can see the GUID is present.

Part 2: Test it out


Above: Created an External List from the ECT, External List is called “GUID in People List” and what I did was also a “Create All Operation” however you will notice that you DO NOT SEE that the GUID Field is not present in the List View


Above:  I created a New Item and populated it with values. Notice here again that the GUID is not present and my Required or “NOT NULL” values from SQL is also denoted


Above: The New User is persisted


Above:  The External Content Type with CRUD capabilities

Part 3: Explaining why my example presumably worked “once” as  yours probably did too and not afterwards


Above: As you would expect the IDGUID field as a primary key / Identity field is protected.  So, it is set to Read Only in the Return Parameter Configuration Wizard.


Above: Just to show you, the second field i am putting in the Picker is NOT read only but it is a Required Field in the LOB

Part 4:  Why Subsequent try’s FAIL and how to make it work… yes Visual Studio



Above:  So this is the entry that is made from the last example. BUT because the GUID is set to all Zeros as the question indicated in the Forum and it HAS TO BE a primary key, any subsequent addition in SharePoint will fail because of the duplicate entry.


Above:  Here is our Test Case, let us add a new user called “User 95”


Above:  And of course we error out because of the Primary Key Constraint in the LOB system


Above:  So just to take this all the way to the end, lets do something that we SHOULD NOT DO. We will remove the Read-Only property of the Primary Key in SharePoint; in doing so you will find out exactly WHY it fails and how to fix it in the end. Notice it says that it needs the “PreUpdaterField” this field is in Visual Studio NOT SharePoint Designer.  So this is yet another answer that I give to people that constantly ask me “Hey Fabian, why do i need to use Visual Studio”


Above: We try to put another record there..


Above: Yes Same Error because the LOB system WILL NOT permit it to do so.

Part 5: How to Make it Work

See my BLOG on how to do CRUD ECT and External Lists using Visual Studio. See https://fabiangwilliams.wordpress.com/2009/12/03/creating-a-sharepoint-2010-external-content-type-with-crud-methods-using-linq-and-a-sql-lob-system/

I will try to do one specific for this example over the weekend time permitting.

April 17, 2010 Posted by | Business Connectivity Services, SharePoint 2010, SharePoint Designer 2010, SharePoint Development, SharePoint Error/Resolution, SharePoint How-To, SQL Server, Visual Studio 2010 | 3 Comments

How To: Create, Configure, Consume SharePoint 2010 Secure Store in Business Connectivity Services

Synopsis: I have seen quite a bit of confusion out there regarding how to use Secure Store Service for SharePoint 2010.  While MSDN does have interesting articles, there has been no Alpha to Omega process that shows the relationship to the LOB System, Security Groups representive of the BCS Consumers, BCS Access Account representive of the Credential Owner [Impersonated User], and how to wire it up in SharePoint Designer 2010. This blog hopefully will dispel all fears about Secure Store and answer a MSDN Forum question while at it.

UPDATE: – On 10/14/2012 I have added another blog post Series that will extend this post for SharePoint 2013 employing WCF, .Net Assembly and OData with SharePoint Apps see it HERE


The Blog is broken up into sections

  • Prep Work
    • Active Directory Users in Play
      • The Service Account I am selecting as the Impersonated User (Credential Owner)
      • The Security Group where all the people that will consume BCS Data will reside
    • SQL Server Security
      • Who has Access to What
  • Setup
    • Creating & Configuring the Secure Store Object
    • Creating & Configuring the External Content Type in SharePoint Designer 2010
      • Creating External Connection with Secure Store
      • Creating the External Content Type
    • Reviewing the External Content Type (ECT)
    • Reviewing the Security on the ECT
  • Test & Validation
    • Creating an External List derived from the ECT
    • Logging on as a User from the Security Group AND Secured in the permission setting of the ECT
    • Logging on as a User from the Security Group NOT Secured in the permission setting of the ECT

Part 1: Setup


Above:  This represents the AD Account [appBCSUser] which I will use as the Impersonated User i.e. the Broker if you will that will connect to the LOB system on behalf of the Group of people who should have access to the data but DOES NOT have access to the database. This is something your DBA will love because he doesn’t have a flurry of people having accounts on his/her DB.


Above: This represents the AD Security Group [SecureStoreBCSUsers] that have access or should have access to LOB Systems. You can of-course have multiple of these for any number of LOB Systems. Note here that Fabian and Hardeep are in this list, we will be the test users later on.


Above:  Lets look into CA now and set up our environment


Above:  Click Applications Management then Manage Service Applications


Above: We are interested in the Secure Store Service so we click it


Above:  We already have some there from previous Labs, but we will create a new one… click New


Above:  We create a Target Application ID [note this cant be changed once committed], Display Name which can be the Same App ID, and so on.


Above:  I populate the fields and choose “Group” as my Target Application Type. MSDN has a good explanation as to why you want to do that over other options. the Long and Short is that it allows me in this example to tie an AD Group FabianLab\SecureStoreBCSUsers to a single set of credentials i.e. the FabianLab\appBCSUser account. Ill show a few other options below


Above:  By default it wants to know how you will collect the credential of the Impersonated User in my case it is a Windows Account so this works.


Above: I change it around a bit for kicks by adding the word Testing infront of the default text


Above:  Here are a few other options that you can use. SSS is a Claims Aware SSO solution and can take in just about any Authentication Mechanism


Above:  So here because I only log on to CA with the Farm Admin Account, I set that as the target App Admin, however here is where we start to make the App Work for our design. In Members, you can see that i have my AD Group Account earlier. This means that I dont have to meddle with the SSS App anymore, just add and subtract from the AD Security Group.


Above:  It processes once i click OK


Above: Now i have a NEW SSS App, but wait you may ask… what about the Impersonated User.. we are coming to that…


Above:  We click on the custom actions available and select SET CREDENTIALS to set the Mapping for the Impersonated Users to the Group that we will Manage of “Allowed Users”…


Above:  Our trusty Silverlight App shows the progress of us opening a Dialog Pane


Above: The default look of the Credential Mapping


Above: I populated the values with my User Account previously mentioned in the AD Step

Part 2: Validation and Testing



Above:  So in SQL Sever you can clearly see that the only account that has Access to the Database “FabianPlayPen” is the AD User mentioned above right…


Above:  We create a new External Content Type by defining the name and Selecting External system to define our Connectivity


Above:  We choose SQL from the list of choices


Above: We define our SSO connection. One note here though in full disclosure, I had tried a few times to make this work and did a typo, so I re-did my SSS App and called it FabianLABSSSMSDNForumQ from what i had it last but the steps are the same.


Above:  Here you may or may not get challenged for credentials when you click OK. The credentials you put here are or should be your own; assuming that you are in that Security Group that will be mapped to the Impersonated User. If not, then you need an account in that Security Group List.


Above:  Once completed you will be able to connect to your LOB System, expand it and perform any operation allowable to you


Above: In our instance lets just create a FULL CRUD operation


Above: Validation that it is complete


Above: Click the “Save” button to push the ECT up to the BDC Metadata Store.


Above:  Now we can check a place where alot of Gotchas happen. Now one may assume that because they have access to the LOB system via the impersonated user and Group Mapping you are done… You’d be wrong, now you NEED to have permission to use the ECT and I already have mine set up by default under “Set Store Permission” to add myself, the search account, and my service account by default. You may need to put your security group here to make it seamless, but because i am doing demos and want it to break depending on my use case, i leave it fluid.


Above:  to do that, click the custom actions and select “Set Permissions”


Above: Do your business here by adding the users you want to have access. Here note that Hardeep doesnt have access while he IS a member of the Security Group.


Above:  Once done, now we can create our External List by choosing our ETC recently created.


Above:  Commit to the System and cross your fingers…. Voilla!


Part 3: UAT


Above: Logged on as Me…


Above: Logged on as Hardeep



Hopefully this helps you understand the mechanism of SSS, alot more can be done in Code using Visual Studio, have full all. Your comments and reposts are welcomed.

April 16, 2010 Posted by | Business Connectivity Services, Secure Store, SharePoint 2010, SharePoint Administration, SharePoint Designer 2010, SharePoint Development, SharePoint How-To, SQL Server | | 68 Comments

No Code SharePoint BCS solutions with Workspace 2010 & Outlook 2010 & SPD 2010


I have not done a blog in a while on SharePoint Business Connectivity Services (BCS) and as I prepare for my next SharePoint Saturday (Arabia), which will go into BCS from a SPD 2010 perspective I wanted to add some additional tid-bits on SharePoint workspace for people unfamiliar with it or its predecessor Microsoft Groove.


Below you will see screen shots for how to set up SharePoint Workspace 2010 (formerly Groove), and the integration to SharePoint Server 2010; further, I will demonstrate how you offline List/Libraries with both Workspace and Outlook to a SQL Server LOB system.


Above:  To create a new SharePoint Workspace link, you click “New”


Above:  There are a few options available to you here, in our example we will be doing a copy of SharePoint sites (offlining) to our local environment so we choose SharePoint Workspace


Above:  Enter the site that you are interested in Off-lining to Workspace


Above:  If you click “Configure” button you get the screen above which allows you to see exactly what is coming down to your Workspace… I haven’t played with modifying the default settings


Above:  Once you click “Ok”, you will have various site list and libraries that will sync up to your SharePoint Workspace; notice that the Site Pages are not supported.


Above:  Just as in Microsoft Outlook, as you connect to BCS External Content Types, you have to install a VSTO file on your local client (laptop/desktop) and this now will use BCSSync.exe to direct sync between your Rich Client and the LOB System.


Above:  this is the installation of the VSTO file


Above:  The VSTO file is successfully installed.


Above:  Same for additional list and libraries


Above:  ditto


Above:  ditto


Above:  When competed you see a check box to denote success and any warnings and errors.


Above:  Notice in the External List called “Store Sales” you have both a data view and individual view for the list. You are able to add, edit, and delete items in SharePoint workspace that will sync with SharePoint and the LOB system


Above:  As an example let us look at Elin Woods profile here, but we have renamed it Tiger for now…


Above:  We note that in the SharePoint browser UX we see the same thing as in Worspace


Above:  We make the edit to change the first name back to Elin…


Above:  We commit the change to the Workspace


Above:  We verify that the change is made as seen above….


Above:  Next we will connect Outlook to the same External List. To to this we will click on the “Connect to Outlook” icon in the SharePoint browser UX


Above:  When you do that, you will see the VSTO fill being installed on your local client


Above:  Once complete you now have access to the List in MS Outlook.


Above:  Verification phase… we are in SQL Management Studio looking at the LOB System


Above:  Next we will make a change to Fabian Williams Name


Above:  Save our Changes


Above:  Verify it in the Browser UX


Above:  Verify it in SharePoint Workspace


Above:  Verify it in the LOB System

March 20, 2010 Posted by | Office 2010, SharePoint 2010, SharePoint Designer 2010, SharePoint Development, SharePoint Workspace 2010 | , , , | Leave a comment

My SharePoint Deck for my BCS Sessions now on SlideShare.net

February 28, 2010 Posted by | SharePoint 2010, SharePoint Designer 2010, SharePoint Development, SharePoint General, SharePoint How-To, SharePoint Saturdays, Visual Studio 2010 | , , , | Leave a comment