Fabian Williams SharePoint Blog

Solving problems with SharePoint day and night

SQL Azure to EF5 to OData to BCS EL

Précis

So yesterday, I was talking with Ted Pattison as he did a Critical Path class on the Great SharePoint Adventure (GSA) and a question was asked by one of his students about using Entity Framework to connect to a Oracle Database and then make an External Content Type and External List.  I suggested that you can use various Database Technologies to connect to your backend Data and serve it up. in this case I am connecting to a SQL Azure Data in this case Northwind Customers and I will be using ADO.NET Entity Framework to work with it. I will then use WCF Data Service to render this data into OData format and use that URI as a source for my External Content Type in a Visual Studio SharePoint App.

In all of this I will be targeting a SharePoint Online Instance, but this can be used for On Prem, in fact the way i do my example I would either need to have (1) set up my SPO for Hybrid Inbound or it wouldn’t work because my URI is internally hosted, I could of course (2) do a Azure Cloud Service [as i have done in previous posts] as well.

Grab the Code here: http://code.msdn.microsoft.com/SQL-Azure-to-EF5-to-OData-2b2bd362

How To

For this you will need a Visual Studio Solution with two projects. One will be for the ASP.NET project that will make the connection to the SQL Azure Data and host it, alternatively this could be a Web Role Project targeting a Cloud Service in Azure. The second project will be a SharePoint App Model Project that will create the External Content Type.

Create a ASP.NET Project

 

image

As you can see above we are creating a project of an ASP.NET Empty Web Application.  So, we are also targeting a SQL Azure Database, i want to show that to you, see below

image

We will be targeting a Database called FabianNorthWind

image

Above what you see is us adding a ADO.NET Entity Model to the project to connect to the Azure SQL Instance.

image

Follow the wizard through and you should see what I see here

image

Finally you will have your EF in your project

image

Save your project here and next we will have to add a WCF Data Service, we do this so we can have a URI to connect to that will expose the SQL Azure Data, or Oracle Data etc.

image

Above is our URI endpoint and it is at NWindCustomer.svc

Once you do that you will get a template helper as you see below, some of it is stubbed out for you. You will need to enter the name of your ADO.NET Entity Model as well as configure the access for the Data you want

   1: //------------------------------------------------------------------------------

   2: // <copyright file="WebDataService.svc.cs" company="Microsoft">

   3: //     Copyright (c) Microsoft Corporation.  All rights reserved.

   4: // </copyright>

   5: //------------------------------------------------------------------------------

   6: using System;

   7: using System.Collections.Generic;

   8: using System.Data.Services;

   9: using System.Data.Services.Common;

  10: using System.Linq;

  11: using System.ServiceModel.Web;

  12: using System.Web;

  13:  

  14: namespace CPDemoAzureEFBCSEg

  15: {

  16:     public class NWindCustomers : DataService< /* TODO: put your data source class name here */ >

  17:     {

  18:         // This method is called only once to initialize service-wide policies.

  19:         public static void InitializeService(DataServiceConfiguration config)

  20:         {

  21:             // TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.

  22:             // Examples:

  23:             // config.SetEntitySetAccessRule("MyEntityset", EntitySetRights.AllRead);

  24:             // config.SetServiceOperationAccessRule("MyServiceOperation", ServiceOperationRights.All);

  25:             config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V3;

  26:         }

  27:     }

  28: }

As this is Demoware, I am going to use an (*) for the configuration and tell it to do “ALL” but you can be more discrete.

   1: namespace CPDemoAzureEFBCSEg

   2: {

   3:     public class NWindCustomers : DataService<FabianNorthwindEntities>

   4:     {

   5:         // This method is called only once to initialize service-wide policies.

   6:         public static void InitializeService(DataServiceConfiguration config)

   7:         {

   8:             // TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.

   9:             // Examples:

  10:             config.SetEntitySetAccessRule("*", EntitySetRights.All);

  11:             config.SetServiceOperationAccessRule("*", ServiceOperationRights.All);

  12:             config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V3;

  13:         }

  14:     }

  15: }

Once you have done that you can preview the .SVC file in a browser and you should see what i have below

image

by typing in Customers in the URI, you will get an OData result set coming back to you. Pay attention to this URI you will need it for your next project.

image

Create a SharePoint App Project for your ECT

Ok, add a new project to your solution as i have below.

image

We are targeting a SPO tenant but this can be anywhere really. This is also a SharePoint Hosted App

image

Follow through on the wizard till your project is added.

image

Its really easy from here on in. You add a Content Type for an External Type to the new project

image

You provide the URI we have from the first project.

image

You select your Table, and leave the “check mark” for creating the List Instance so you will get your External Content Type and External List.

image

One you are done you will have an ECT and an EL as below in your project

image

If you open up your External Content Type you can see what I have below. At this point  you can

  • deploy it via visual studio
  • give it to someone to upload to a farm
  • deploy it manually yourself

here is the ECT below

   1: <?xml version="1.0" encoding="utf-16"?>

   2: <Model xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Name="FabianNorthwindModel" xmlns="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog">

   3:   <LobSystems>

   4:     <LobSystem Name="CustDataFromAzure" Type="OData">

   5:       <Properties>

   6:         <Property Name="ODataServiceMetadataUrl" Type="System.String">http://localhost:50007/NWindCustomers.svc/$metadata</Property>

   7:         <Property Name="ODataServiceMetadataAuthenticationMode" Type="System.String">PassThrough</Property>

   8:         <Property Name="ODataServicesVersion" Type="System.String">2.0</Property>

   9:       </Properties>

  10:       <AccessControlList>

  11:         <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">

  12:           <Right BdcRight="Edit" />

  13:           <Right BdcRight="Execute" />

  14:           <Right BdcRight="SelectableInClients" />

  15:           <Right BdcRight="SetPermissions" />

  16:         </AccessControlEntry>

  17:       </AccessControlList>

  18:       <LobSystemInstances>

  19:         <LobSystemInstance Name="CustDataFromAzure">

  20:           <Properties>

  21:             <Property Name="ODataServiceUrl" Type="System.String">http://localhost:50007/NWindCustomers.svc</Property>

  22:             <Property Name="ODataServiceAuthenticationMode" Type="System.String">PassThrough</Property>

  23:             <Property Name="ODataFormat" Type="System.String">application/atom+xml</Property>

  24:             <Property Name="HttpHeaderSetAcceptLanguage" Type="System.Boolean">true</Property>

  25:           </Properties>

  26:         </LobSystemInstance>

  27:       </LobSystemInstances>

  28:       <Entities>

  29:         <Entity Name="Customers" DefaultDisplayName="Customers" Namespace="FabianNorthwindModel" Version="1.0.0.0" EstimatedInstanceCount="2000">

  30:           <Properties>

  31:             <Property Name="ExcludeFromOfflineClientForList" Type="System.String">False</Property>

  32:           </Properties>

  33:           <AccessControlList>

  34:             <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">

  35:               <Right BdcRight="Edit" />

  36:               <Right BdcRight="Execute" />

  37:               <Right BdcRight="SelectableInClients" />

  38:               <Right BdcRight="SetPermissions" />

  39:             </AccessControlEntry>

  40:           </AccessControlList>

  41:           <Identifiers>

  42:             <Identifier Name="CustomerID" TypeName="System.String" />

  43:           </Identifiers>

  44:           <Methods>

  45:             <Method Name="CreateCustomer" DefaultDisplayName="Create Customer" IsStatic="false">

  46:               <Properties>

  47:                 <Property Name="ODataEntityUrl" Type="System.String">/Customers</Property>

  48:               </Properties>

  49:               <AccessControlList>

  50:                 <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">

  51:                   <Right BdcRight="Edit" />

  52:                   <Right BdcRight="Execute" />

  53:                   <Right BdcRight="SelectableInClients" />

  54:                   <Right BdcRight="SetPermissions" />

  55:                 </AccessControlEntry>

  56:               </AccessControlList>

  57:               <Parameters>

  58:                 <Parameter Name="@CustomerID" Direction="In">

  59:                   <TypeDescriptor Name="CustomerID" DefaultDisplayName="CustomerID" TypeName="System.String" IdentifierName="CustomerID" CreatorField="true" />

  60:                 </Parameter>

  61:                 <Parameter Name="@CompanyName" Direction="In">

  62:                   <TypeDescriptor Name="CompanyName" DefaultDisplayName="CompanyName" TypeName="System.String" CreatorField="true" />

  63:                 </Parameter>

  64:                 <Parameter Name="@ContactName" Direction="In">

  65:                   <TypeDescriptor Name="ContactName" DefaultDisplayName="ContactName" TypeName="System.String" CreatorField="true" />

  66:                 </Parameter>

  67:                 <Parameter Name="@ContactTitle" Direction="In">

  68:                   <TypeDescriptor Name="ContactTitle" DefaultDisplayName="ContactTitle" TypeName="System.String" CreatorField="true" />

  69:                 </Parameter>

  70:                 <Parameter Name="@Address" Direction="In">

  71:                   <TypeDescriptor Name="Address" DefaultDisplayName="Address" TypeName="System.String" CreatorField="true" />

  72:                 </Parameter>

  73:                 <Parameter Name="@City" Direction="In">

  74:                   <TypeDescriptor Name="City" DefaultDisplayName="City" TypeName="System.String" CreatorField="true" />

  75:                 </Parameter>

  76:                 <Parameter Name="@Region" Direction="In">

  77:                   <TypeDescriptor Name="Region" DefaultDisplayName="Region" TypeName="System.String" CreatorField="true" />

  78:                 </Parameter>

  79:                 <Parameter Name="@PostalCode" Direction="In">

  80:                   <TypeDescriptor Name="PostalCode" DefaultDisplayName="PostalCode" TypeName="System.String" CreatorField="true" />

  81:                 </Parameter>

  82:                 <Parameter Name="@Country" Direction="In">

  83:                   <TypeDescriptor Name="Country" DefaultDisplayName="Country" TypeName="System.String" CreatorField="true" />

  84:                 </Parameter>

  85:                 <Parameter Name="@Phone" Direction="In">

  86:                   <TypeDescriptor Name="Phone" DefaultDisplayName="Phone" TypeName="System.String" CreatorField="true" />

  87:                 </Parameter>

  88:                 <Parameter Name="@Fax" Direction="In">

  89:                   <TypeDescriptor Name="Fax" DefaultDisplayName="Fax" TypeName="System.String" CreatorField="true" />

  90:                 </Parameter>

  91:                 <Parameter Name="@CreateCustomer" Direction="Return">

  92:                   <TypeDescriptor Name="CreateCustomer" DefaultDisplayName="CreateCustomer" TypeName="Microsoft.BusinessData.Runtime.DynamicType">

  93:                     <TypeDescriptors>

  94:                       <TypeDescriptor Name="CustomerID" DefaultDisplayName="CustomerID" TypeName="System.String" IdentifierName="CustomerID" ReadOnly="true" />

  95:                       <TypeDescriptor Name="CompanyName" DefaultDisplayName="CompanyName" TypeName="System.String" />

  96:                       <TypeDescriptor Name="ContactName" DefaultDisplayName="ContactName" TypeName="System.String" />

  97:                       <TypeDescriptor Name="ContactTitle" DefaultDisplayName="ContactTitle" TypeName="System.String" />

  98:                       <TypeDescriptor Name="Address" DefaultDisplayName="Address" TypeName="System.String" />

  99:                       <TypeDescriptor Name="City" DefaultDisplayName="City" TypeName="System.String" />

 100:                       <TypeDescriptor Name="Region" DefaultDisplayName="Region" TypeName="System.String" />

 101:                       <TypeDescriptor Name="PostalCode" DefaultDisplayName="PostalCode" TypeName="System.String" />

 102:                       <TypeDescriptor Name="Country" DefaultDisplayName="Country" TypeName="System.String" />

 103:                       <TypeDescriptor Name="Phone" DefaultDisplayName="Phone" TypeName="System.String" />

 104:                       <TypeDescriptor Name="Fax" DefaultDisplayName="Fax" TypeName="System.String" />

 105:                     </TypeDescriptors>

 106:                   </TypeDescriptor>

 107:                 </Parameter>

 108:               </Parameters>

 109:               <MethodInstances>

 110:                 <MethodInstance Name="CreateCustomer" Type="Creator" ReturnParameterName="@CreateCustomer" ReturnTypeDescriptorPath="CreateCustomer">

 111:                   <AccessControlList>

 112:                     <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">

 113:                       <Right BdcRight="Edit" />

 114:                       <Right BdcRight="Execute" />

 115:                       <Right BdcRight="SelectableInClients" />

 116:                       <Right BdcRight="SetPermissions" />

 117:                     </AccessControlEntry>

 118:                   </AccessControlList>

 119:                 </MethodInstance>

 120:               </MethodInstances>

 121:             </Method>

 122:             <Method Name="ReadSpecificCustomer" DefaultDisplayName="Read Specific Customer" IsStatic="false">

 123:               <Properties>

 124:                 <Property Name="ODataEntityUrl" Type="System.String">/Customers(CustomerID='@CustomerID')</Property>

 125:               </Properties>

 126:               <AccessControlList>

 127:                 <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">

 128:                   <Right BdcRight="Edit" />

 129:                   <Right BdcRight="Execute" />

 130:                   <Right BdcRight="SelectableInClients" />

 131:                   <Right BdcRight="SetPermissions" />

 132:                 </AccessControlEntry>

 133:               </AccessControlList>

 134:               <Parameters>

 135:                 <Parameter Name="@CustomerID" Direction="In">

 136:                   <TypeDescriptor Name="CustomerID" DefaultDisplayName="CustomerID" TypeName="System.String" IdentifierName="CustomerID" />

 137:                 </Parameter>

 138:                 <Parameter Name="@Customer" Direction="Return">

 139:                   <TypeDescriptor Name="Customer" DefaultDisplayName="Customer" TypeName="Microsoft.BusinessData.Runtime.DynamicType">

 140:                     <TypeDescriptors>

 141:                       <TypeDescriptor Name="CustomerID" DefaultDisplayName="CustomerID" TypeName="System.String" IdentifierName="CustomerID" ReadOnly="true" />

 142:                       <TypeDescriptor Name="CompanyName" DefaultDisplayName="CompanyName" TypeName="System.String">

 143:                         <Properties>

 144:                           <Property Name="RequiredInForms" Type="System.Boolean">true</Property>

 145:                         </Properties>

 146:                       </TypeDescriptor>

 147:                       <TypeDescriptor Name="ContactName" DefaultDisplayName="ContactName" TypeName="System.String" />

 148:                       <TypeDescriptor Name="ContactTitle" DefaultDisplayName="ContactTitle" TypeName="System.String" />

 149:                       <TypeDescriptor Name="Address" DefaultDisplayName="Address" TypeName="System.String" />

 150:                       <TypeDescriptor Name="City" DefaultDisplayName="City" TypeName="System.String" />

 151:                       <TypeDescriptor Name="Region" DefaultDisplayName="Region" TypeName="System.String" />

 152:                       <TypeDescriptor Name="PostalCode" DefaultDisplayName="PostalCode" TypeName="System.String" />

 153:                       <TypeDescriptor Name="Country" DefaultDisplayName="Country" TypeName="System.String" />

 154:                       <TypeDescriptor Name="Phone" DefaultDisplayName="Phone" TypeName="System.String" />

 155:                       <TypeDescriptor Name="Fax" DefaultDisplayName="Fax" TypeName="System.String" />

 156:                     </TypeDescriptors>

 157:                   </TypeDescriptor>

 158:                 </Parameter>

 159:               </Parameters>

 160:               <MethodInstances>

 161:                 <MethodInstance Name="ReadSpecificCustomer" Type="SpecificFinder" Default="true" ReturnParameterName="@Customer" ReturnTypeDescriptorPath="Customer">

 162:                   <AccessControlList>

 163:                     <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">

 164:                       <Right BdcRight="Edit" />

 165:                       <Right BdcRight="Execute" />

 166:                       <Right BdcRight="SelectableInClients" />

 167:                       <Right BdcRight="SetPermissions" />

 168:                     </AccessControlEntry>

 169:                   </AccessControlList>

 170:                 </MethodInstance>

 171:               </MethodInstances>

 172:             </Method>

 173:             <Method Name="ReadAllCustomer" DefaultDisplayName="Read All Customer" IsStatic="false">

 174:               <Properties>

 175:                 <Property Name="ODataEntityUrl" Type="System.String">/Customers?$top=@LimitCustomerss</Property>

 176:               </Properties>

 177:               <AccessControlList>

 178:                 <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">

 179:                   <Right BdcRight="Edit" />

 180:                   <Right BdcRight="Execute" />

 181:                   <Right BdcRight="SelectableInClients" />

 182:                   <Right BdcRight="SetPermissions" />

 183:                 </AccessControlEntry>

 184:               </AccessControlList>

 185:               <FilterDescriptors>

 186:                 <FilterDescriptor Name="LimitFilter" DefaultDisplayName="LimitFilter" Type="Limit" />

 187:               </FilterDescriptors>

 188:               <Parameters>

 189:                 <Parameter Name="@LimitCustomerss" Direction="In">

 190:                   <TypeDescriptor Name="LimitCustomerss" DefaultDisplayName="LimitCustomerss" TypeName="System.Int32" AssociatedFilter="LimitFilter">

 191:                     <Properties>

 192:                       <Property Name="LogicalOperatorWithPrevious" Type="System.String">None</Property>

 193:                       <Property Name="Order" Type="System.String">0</Property>

 194:                     </Properties>

 195:                     <DefaultValues>

 196:                       <DefaultValue MethodInstanceName="ReadAllCustomer" Type="System.Int32">100</DefaultValue>

 197:                     </DefaultValues>

 198:                   </TypeDescriptor>

 199:                 </Parameter>

 200:                 <Parameter Name="@Customers" Direction="Return">

 201:                   <TypeDescriptor Name="Customers" DefaultDisplayName="Customers" TypeName="Microsoft.BusinessData.Runtime.IDynamicTypeEnumerator" IsCollection="true">

 202:                     <TypeDescriptors>

 203:                       <TypeDescriptor Name="Customer" DefaultDisplayName="Customer" TypeName="Microsoft.BusinessData.Runtime.DynamicType">

 204:                         <TypeDescriptors>

 205:                           <TypeDescriptor Name="CustomerID" DefaultDisplayName="CustomerID" TypeName="System.String" IdentifierName="CustomerID" ReadOnly="true" />

 206:                           <TypeDescriptor Name="CompanyName" DefaultDisplayName="CompanyName" TypeName="System.String">

 207:                             <Properties>

 208:                               <Property Name="RequiredInForms" Type="System.Boolean">true</Property>

 209:                             </Properties>

 210:                           </TypeDescriptor>

 211:                           <TypeDescriptor Name="ContactName" DefaultDisplayName="ContactName" TypeName="System.String" />

 212:                           <TypeDescriptor Name="ContactTitle" DefaultDisplayName="ContactTitle" TypeName="System.String" />

 213:                           <TypeDescriptor Name="Address" DefaultDisplayName="Address" TypeName="System.String" />

 214:                           <TypeDescriptor Name="City" DefaultDisplayName="City" TypeName="System.String" />

 215:                           <TypeDescriptor Name="Region" DefaultDisplayName="Region" TypeName="System.String" />

 216:                           <TypeDescriptor Name="PostalCode" DefaultDisplayName="PostalCode" TypeName="System.String" />

 217:                           <TypeDescriptor Name="Country" DefaultDisplayName="Country" TypeName="System.String" />

 218:                           <TypeDescriptor Name="Phone" DefaultDisplayName="Phone" TypeName="System.String" />

 219:                           <TypeDescriptor Name="Fax" DefaultDisplayName="Fax" TypeName="System.String" />

 220:                         </TypeDescriptors>

 221:                       </TypeDescriptor>

 222:                     </TypeDescriptors>

 223:                   </TypeDescriptor>

 224:                 </Parameter>

 225:               </Parameters>

 226:               <MethodInstances>

 227:                 <MethodInstance Name="ReadAllCustomer" Type="Finder" Default="true" ReturnParameterName="@Customers" ReturnTypeDescriptorPath="Customers">

 228:                   <AccessControlList>

 229:                     <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">

 230:                       <Right BdcRight="Edit" />

 231:                       <Right BdcRight="Execute" />

 232:                       <Right BdcRight="SelectableInClients" />

 233:                       <Right BdcRight="SetPermissions" />

 234:                     </AccessControlEntry>

 235:                   </AccessControlList>

 236:                 </MethodInstance>

 237:               </MethodInstances>

 238:             </Method>

 239:             <Method Name="UpdateCustomer" DefaultDisplayName="Update Customer" IsStatic="false">

 240:               <Properties>

 241:                 <Property Name="ODataEntityUrl" Type="System.String">/Customers(CustomerID='@CustomerID')</Property>

 242:               </Properties>

 243:               <AccessControlList>

 244:                 <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">

 245:                   <Right BdcRight="Edit" />

 246:                   <Right BdcRight="Execute" />

 247:                   <Right BdcRight="SelectableInClients" />

 248:                   <Right BdcRight="SetPermissions" />

 249:                 </AccessControlEntry>

 250:               </AccessControlList>

 251:               <Parameters>

 252:                 <Parameter Name="@CustomerID" Direction="In">

 253:                   <TypeDescriptor Name="CustomerID" DefaultDisplayName="CustomerID" TypeName="System.String" IdentifierName="CustomerID" UpdaterField="true" />

 254:                 </Parameter>

 255:                 <Parameter Name="@CompanyName" Direction="In">

 256:                   <TypeDescriptor Name="CompanyName" DefaultDisplayName="CompanyName" TypeName="System.String" UpdaterField="true" />

 257:                 </Parameter>

 258:                 <Parameter Name="@ContactName" Direction="In">

 259:                   <TypeDescriptor Name="ContactName" DefaultDisplayName="ContactName" TypeName="System.String" UpdaterField="true" />

 260:                 </Parameter>

 261:                 <Parameter Name="@ContactTitle" Direction="In">

 262:                   <TypeDescriptor Name="ContactTitle" DefaultDisplayName="ContactTitle" TypeName="System.String" UpdaterField="true" />

 263:                 </Parameter>

 264:                 <Parameter Name="@Address" Direction="In">

 265:                   <TypeDescriptor Name="Address" DefaultDisplayName="Address" TypeName="System.String" UpdaterField="true" />

 266:                 </Parameter>

 267:                 <Parameter Name="@City" Direction="In">

 268:                   <TypeDescriptor Name="City" DefaultDisplayName="City" TypeName="System.String" UpdaterField="true" />

 269:                 </Parameter>

 270:                 <Parameter Name="@Region" Direction="In">

 271:                   <TypeDescriptor Name="Region" DefaultDisplayName="Region" TypeName="System.String" UpdaterField="true" />

 272:                 </Parameter>

 273:                 <Parameter Name="@PostalCode" Direction="In">

 274:                   <TypeDescriptor Name="PostalCode" DefaultDisplayName="PostalCode" TypeName="System.String" UpdaterField="true" />

 275:                 </Parameter>

 276:                 <Parameter Name="@Country" Direction="In">

 277:                   <TypeDescriptor Name="Country" DefaultDisplayName="Country" TypeName="System.String" UpdaterField="true" />

 278:                 </Parameter>

 279:                 <Parameter Name="@Phone" Direction="In">

 280:                   <TypeDescriptor Name="Phone" DefaultDisplayName="Phone" TypeName="System.String" UpdaterField="true" />

 281:                 </Parameter>

 282:                 <Parameter Name="@Fax" Direction="In">

 283:                   <TypeDescriptor Name="Fax" DefaultDisplayName="Fax" TypeName="System.String" UpdaterField="true" />

 284:                 </Parameter>

 285:               </Parameters>

 286:               <MethodInstances>

 287:                 <MethodInstance Name="UpdateCustomer" Type="Updater">

 288:                   <AccessControlList>

 289:                     <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">

 290:                       <Right BdcRight="Edit" />

 291:                       <Right BdcRight="Execute" />

 292:                       <Right BdcRight="SelectableInClients" />

 293:                       <Right BdcRight="SetPermissions" />

 294:                     </AccessControlEntry>

 295:                   </AccessControlList>

 296:                 </MethodInstance>

 297:               </MethodInstances>

 298:             </Method>

 299:             <Method Name="DeleteCustomer" DefaultDisplayName="Delete Customer" IsStatic="false">

 300:               <Properties>

 301:                 <Property Name="ODataEntityUrl" Type="System.String">/Customers(CustomerID='@CustomerID')</Property>

 302:               </Properties>

 303:               <AccessControlList>

 304:                 <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">

 305:                   <Right BdcRight="Edit" />

 306:                   <Right BdcRight="Execute" />

 307:                   <Right BdcRight="SelectableInClients" />

 308:                   <Right BdcRight="SetPermissions" />

 309:                 </AccessControlEntry>

 310:               </AccessControlList>

 311:               <Parameters>

 312:                 <Parameter Name="@CustomerID" Direction="In">

 313:                   <TypeDescriptor Name="CustomerID" DefaultDisplayName="CustomerID" TypeName="System.String" IdentifierName="CustomerID" />

 314:                 </Parameter>

 315:               </Parameters>

 316:               <MethodInstances>

 317:                 <MethodInstance Name="DeleteCustomer" Type="Deleter">

 318:                   <AccessControlList>

 319:                     <AccessControlEntry Principal="STS|SecurityTokenService|http://sharepoint.microsoft.com/claims/2009/08/isauthenticated|true|http://www.w3.org/2001/XMLSchema#string">

 320:                       <Right BdcRight="Edit" />

 321:                       <Right BdcRight="Execute" />

 322:                       <Right BdcRight="SelectableInClients" />

 323:                       <Right BdcRight="SetPermissions" />

 324:                     </AccessControlEntry>

 325:                   </AccessControlList>

 326:                 </MethodInstance>

 327:               </MethodInstances>

 328:             </Method>

 329:           </Methods>

 330:         </Entity>

 331:       </Entities>

 332:     </LobSystem>

 333:   </LobSystems>

 334: </Model>

Finally your project will look as i have below and you are done

image

Summary

I hope this is helpful, it was rather rushed, but i promised it in a few hours turnaround.

December 13, 2013 Posted by | Azure, Business Connectivity Services, Entity Framework, OData, Office 365, REST, SharePoint 2013, SharePoint How-To, Visual Studio 2013, WCF, Windows Azure | 1 Comment

Wrap-up on my SPSDC Workflow Session

Thanks

Another year and another successful even by the Organizers (Dan, Adam, Rachel) and all the Volunteers at SharePoint Saturday Washington D.C. – Reston.  I was honored to be an accepted speaker and I delivered this session “The Evolution of Workflows in SharePoint 2013 – A Story about External Calls with REST & JSON”.  This was truly a magnificent event with a great turn out, good mix of attendees, and they were very engaged, asked serious and deliberate questions , and didn’t even mind as yet again i go a little bit over in my Live Demos.

Appreciation

Its always great to meet face to face the folks that you interact with daily on social media in our SharePoint community, foster new relationships, maintain old ones, and Share a Pint at the end of a day where we all walk away with a bit more knowledge in this technology we all share. I was very please for all the attendees that made my session ‘standing room only’ and if any of you all have pics, please email them to me, i would really appreciate them for my blog post. 

My Slide deck may be picked up here

V__C0D1

What’s Next

Make sure to attend the inaugural #SharePoint Saturday, the one that started it all SharePoint Saturday Virginia Beach on January 11th 2014 & of course stop by my session (well after you register here) on Optimizing your SharePoint 2013 with BCS an External Data where we will discuss strategies, security and all the various models to interact with your LOB systems.

As well, I will be delivering three (3) Sessions at SharePoint Conference 2014 March 3rd through 6th 2014 (#SPC14 or @SPConf) in Vegas check out this link for my sessions.

I hope to see you there.

See oonuh latah

December 9, 2013 Posted by | JSON, Public Speaking, REST, SharePoint Designer 2013, Where is Fabian, Workflows | Leave a comment

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…

image

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

    image

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.

image

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)
    image
  • 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
    image

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

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

image

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.

Takeaway

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

RESOLVED: Unable to Create List using SharePoint 2013 REST API in SPD2013

Summation

Ill start this post out by saying that this is why i so LOVE the SharePoint Community. It is a true example of "many hands make light work”, first Jim Bob Howard @jbhoward one of my @PlanetTech work colleagues faced a problem, since it was a topic of interest to me, I decided to put some time into it as well. I was able to prove out most of the issues, but there was this last one (see blog post to what this is referring to in a link below) that just didnt make sense.  What do i do when that happens, well I’ve never been ashamed to reach out to my friends in the SharePoint Community so I blogged the issue, showed what we did so far, and without tagging anyone in the tweet we had folks from the UK joining in the conversation, folks from all Time Zones here in the United States chiming in as well. Late Friday night I emailed a copy of the Workflow to my good buddy @givenscj Chris Givens.  I really wasn’t surprised when even on a Saturday, Chris called me up on the phone, and we spent like 2 hours on a Screen Share looking at ULS logs, then he went off on his own, and within an hour, he came back with a solution. 

The original problem was posted on the forums here

My Plea for help on my blog which gives more details than the forum post allows is here. This post is basically closing the loop on the problem

Findings

Chris blogged his findings here, go look at it, but the main point ill just borrow from is blog…

“…The main issue of Fabian’s was that when he made the Web Service call he would pass a pre-created FedAuth token (yeah I told him he would have to figure out how to generate this in the future) which established a context for him.  This context was as his SharePoint admin account, but the actual running account (the "actor") was the Workflow service.  Of course the SP Admin account can do whatever it wants, but the workflow service cannot.  This workflow service account is NOT the account that the actual workflow manager backend window service is running under.”

Screen Shots form what Chris and I worked on

First we wanted to see what was happening ON Prem with this both from a Fiddler standpoint as well as SharePoint Designer 2013, here is what we saw. The offending item was the account it is running under MS.SP.EXT which is the App Principal for the Workflow from Workflow Manager. Further down you will see this inside the Content Database and the permission it is set to run under.

tmwwgjzs

with a little more detail below

e5mofjsq

Below is a peek into the App Principal Table. This table as Chris describes is where the App Principal Accounts live, you can see that i have a few that are Titled as my Workflow Apps and one called just “Workflow” and it is that one if you look under “Name” has MS.SP.EXT 🙂

image

Now, lets go look at the permissions, now mind you, if you look at permission it is set to “5” right now for the ID 1, that was set to “3” before i ran the PowerShell that Chris has on his web site and i will put below for complexness.

 image

Here is the PowerShell below

PS C:\Users\spadmin> $web = Get-SPWeb http://farm1sp13svr1
PS C:\Users\spadmin> $appworkflow = Get-SPAppPrincipal -site http://farm1sp13svr1 -nameidentifier 31ed5e6e-979b-redacted
PS C:\Users\spadmin> Set-SPAppPrincipalPermission -AppPrincipal $appworkflow -site $web -right fullcontrol -scope site
PS C:\Users\spadmin>

Once I did that, all we did next was re-Run the workflow, NO other changes, here was our result

image

and in All Site Contents

image

 

 

Finally

So, all is well in the world of SharePoint again. I hope Chris goes and puts his solution as the answer to the question in the @Office365 Forums so we can have a record out there as well. I hope this helps someone else that falls prey to this, I agree with Chris, this is the first time I have ever seen anyone blog or talk about this in a public setting.

Cheers

Fabian Williams with contributions from Chris Givens

UPDATE SECTION

So, because i wanted this to work in Office 365 as well, we needed to find out if these PowerShell options are available there too. For completeness I loaded up the ISE and I have here all the options that you can do with the App Model for GETS and SETS, both for On Prem and OnLine, you can see that the Online version is very limited

ON PREM

clip_image001clip_image002

 

and sad to say I could get EVERYTHING from ONLINE in one screenshot. 😦 sad panda

image

If you want to know more about Powershell for SharePoint Online regarding how to set it up in your environment, see Patrick @pcfromdc Currans blog post here.

 

END UPDATE SECTION

 

September 7, 2013 Posted by | Azure, JSON, Office 365, REST, SharePoint 2013, SharePoint 2013 Workflows, SharePoint Designer 2013, SharePoint Error/Resolution, SharePoint How-To, SharePoint Online | 1 Comment

HELP: Unable to Create List using SharePoint 2013 REST API in SPD2013

Précis

So, I am took up @Office365 on their offer of help, but then they directed me to the forums because they said the limitation of twitter made it difficult to respond to my tweet for help

image

So i set about doing that this morning, however even the forums have limits when you try to paint a thorough picture of your problem using screen shots due to image sizes. see here

image

So, i will just blog it and point back to this blog post using the Forum as the initial pointer for anyone who wants to help, fortunately WordPress give me more freedom in images and i can be verbose. So here goes…

Problem Stated

The objective is to Create a new List in SharePoint Online Wave 15 via REST https://site/_api/web/lists and then passing the necessary headers. Also, if I do a GET using the same headers, IT WORKS, this only happens on POST with the addition payload of Request Content(Body) that I am sending, but in Fiddler "using" the same payload, I can create my list. I’ve blogged about this topic and how I go about doing REST via SPD and Fiddler here and here and here, the first and last are probably most appropriate for this line of question. Below are some screen shots of what I am experiencing now. I thank you in advance for any assistance you can provide.

Below are the Request Headers

1

Below is me constructing what will be the Request Content (Body) but to get the desired feel, I had to put the __metadata property inside its own dictionary object to get the curly braces and key value pair, you will see below

2

Below you will see me wrapping this variable inside another to get {‘__metadata’:{‘type’:’SP.List’} to come out properly

3

Next you will see my call to HTTP web Service properties

 4

Next you will see my results. this is my parsing the JSON returned in the Response Code and Response Body

 5

Proof that the same thing works in Fiddler

Here is me doing the same thing in Fiddler. and i also updated my Digest so that it would be current because i know it expires after a while

 

image 

and

image

and the list in O365 is created here

image

 

Update Section

This section will be filled with things I have tried either on my own or on suggestions.

The first thing i will do is take Hugh @hughajwood Wood suggestions and use a simple example on a post I actually praised earlier and do just a simple Add of a List Item in Office 365, i will dubb it TryAlpha

Try Alpha – 09/06/2013 1212 HRS

So the first thing I did was to make the headers as the post describe, to keep things simple i used the exact nomenclature

image

now wrap that header into a Dictionary header named __metadata and add a Title field

image

next use those headers to make my REST call

image

and we can safely say that using the REST API for List Items work. Let me also make some qualifiers

  1. Before doing this, I updated my X-RequestDigest to get a new value and I updated my requestHeaders variable, that was the only thing i did

image

End TestAlpha

Begin Test Bravo

So, I got some information from Paul Schaeflein @paulschaeflein He suggested that rather than hardcoding the Digest Information [which I know also expires after a while, and that is why whenever I run the WF, I redo the contextinfo URI and get a new one] I should do a POST insider my Workflow to get the Digest Information and THEN extract the X-RequestHeader value inside a variable and use it. The thought being, it will be more current than hard coding it, and Pauls Point was that it is safer from a security standpoint if SPD was preventing ‘malicious’ usages or hardcoded values.

Well In the end, it didnt work either. here is the story

Big Picture

image

In more detail I created one header to get the ContextInfo Information i.e. No Digest information was used in that one, and then I set the value coming from the JSON WebContextInformation object mapping to the Digest into a Variable called str_FormDigest.  I then used that Variable in another Header that I have specifically for the POST that I will use to create the List Item. See below

image

And for good measure I logged the output of the Digest so i know it works. here is the Digest Info and another failed attempt

image

End TestBravo

 

 

 

 

 

 

Summary

Any help is appreciated, I will ask that you post your feedback in the comments on this blog and i will update the Ticket in the Forum pointing back to this blog post.

 

Here is the Forum Thread: http://social.msdn.microsoft.com/Forums/en-US/dd9932f5-1e3f-416f-8f0b-e8ab2b792ba1/unable-to-use-spd-2013-and-call-http-service-to-create-a-new-list-in-spo-via-rest

 

 

del.icio.us Tags:

Thanks in advance

September 6, 2013 Posted by | JSON, Office 365, REST, SharePoint 2013, SharePoint Designer 2013, SQL Server | 8 Comments

Using the Call HTTP Web Service REST Calls in SharePoint Online using SPD

Précis

So I’ve had folks say that they still cant make the leap and get my examples to work in Office 365 SharePoint Online, so this post is to Prove out that IT can be done, there is no smoke and mirrors, I just wanted folks to take what I have and use that to go to the next level. This is not like a live session where I have an interactive audience, so I am putting the info out there in his blog.

This post builds on a few out there that I have done explaining how to use SharePoint Designer 2013 with the Dictionary Object to data in JSON Format to be used inside Workflows. Now, I do admit that there are VERY FEW examples out there, if any, that show you, how to do what I am about to tackle in Office 365 SharePoint Online. So here goes.

 

Approach

As usual we have an Initialization Stage and a Workhorse Stage, in the Init stage we will set our variables and make our REST call, we will parse it and limit our dataset to what we need for our results. I think folks are getting tripped up on what is needed in the Headers, as what I am getting as feedback is that the Workflow Starts but it stalls with a message like “Retrying last request. Next attempt scheduled after…” with a correlation ID, it also gives you a Retry Now Link.

So the first thing i will show you is of course what is in my List NOW, I will show it to you via the browser and in Fiddler.

image

now here is the items in Fiddler, i do this so you can appreciate the actions I will take later on in SPD when parsing the JSON data.

image

Now lets look at how we construct the Header, in this post, i have explained why you need certain Headers, so i wont go into it here, the only difference is that I am extrapolating what I did in Fiddler and using it in SharePoint Designer… now for obvious reasons, I am NOT showing you all the Header Information since it is my Valid Token to O365.

image

Now with that done, I have constructed a Site Workflow that will loop through the items and Log it to History, what you do with it for your Use Case can be quite elaborate, you essentially can

  • Communicate with any Office 365 SharePoint Online Tennant you want from a Single Workflow
  • Cross Communicate between an On Prem Farm and a Cloud Farm in one Workflow
  • Create List, Libraries, List Items, and do full CRUD operations

If you go and look at the REST API endpoints from that link i gave you before by Kirk Evans, you can see that its quite a bunch of things that are available now.

So. back to the story.

First Part of the Workflow

image

Second Pare of the Workflow

image

Next lets fire off this Workflow and look at the results.

Proof

Next we go to Site Contents, Site Workflows and click the one we just created after publishing

We should expect to see the below in our History List

image

and upon closer inspection of the Workflow

image

 

Summary

Now what I will say because I have worked quite a bit with SharePoint Online Wave 15 is that it can get squirrely and can crap out on you for no good apparent reason, it gives you obscure feedback when it does and often times it just says “something bad has happened..” and if you Refresh the browser, its back to normal. I don’t pretend to understand why, I don’t have eyes into the Data Centre nor access to the ULS Logs, so I am as much as in the blind as you are. BUT this is the reason why i do Fiddler first then SPD after…visibility.

I hope this helps! Cheers.

 

September 4, 2013 Posted by | JSON, Office 365, REST, SharePoint 2013 Workflows, SharePoint Designer 2013, SharePoint How-To, SharePoint Online, Workflows | , , | 4 Comments

Creating a SharePoint Timer Job using SharePoint Designer 2013

What do i really mean?

Now that I have got your attention with that eye catching title, let me expand and qualify it.  So, what I will be doing exactly is

  1. Inspecting a List (a Task List actually) for Tasks that are Due and Overdue.
  2. I will be checking them nightly (every 24 hours to be exact)
  3. If task are Over Due, then I will take ‘some’ action on them, in this instance, I will be changing a KPI field but this can be quite exhaustive based on your own particular use case. see below for one i have in mind.
  4. Pause the Workflow for 24 hours and Repeat.

By the way, this CAN also be used in SharePoint Online

Use Case

In this use case I have been charged to monitor a Task list (multiple task list from varying SharePoint Sites/ Site Collections) and aggregating them. Once complete, then we need to serve up the tasks that are overdue into a KPI dashboard and send the email link to managers of those offending taskees.

For my POC demo here I will show how to get 1 Site Collection but to get multiple all you have to do is make a separate REST call to that particular Lists API, and the rest is easy.

Pre-Requisites and Technologies Used

In this example I am using an On Premises SharePoint 2013 with Workflows enabled, I am also using SharePoint Designer 2013 as well, that is if for tooling. i.e. NO CODE.  I will be using the SharePoint 2013 REST API to read the SharePoint Task Lists as my Entry Point URI. The generic url will be /_api/web/lists/getListByTitle(‘’)/items">/_api/web/lists/getListByTitle(‘’)/items">http://<sitename>/_api/web/lists/getListByTitle(‘<listNameHere>’)/items which fetches List Items from a Named list using oData. Now by default this will be returned to your in XML, so in order to use this in SharePoint Designer which requires JSON inside the Dictionary Object (see my post here if you need a refresher on that) you will need to modify the headers to accept JSON using the Accept Headers. The rest is a matter of using Loops to iterate through the List Items returned, and pausing for the time allotted.

Approach

As usual I put my URI inside a Variable to obscure the name & any API Keys if any, this time there was no need but it just makes for better programming especially if there are a lot and I want to swap them out from time to time without affecting the core program

image

the full list of properties from the Call HTTP web service is located below since it is truncated in the image

image

The Request Headers are “Accept: application/json; odata=verbose” and you can place that into a string type as well and call it into the HTTP Web Service Properties as well inside a Workflow Dictionary Variable

image

Once you have done that, then as seen in the first image and in my previous blog post on the same topic, you parse the JSON results to get the part of the dataset that you want, in this case i want what is under the “d/results” node and to do that I use the Get Item from Dictionary and parse out what i need then i count the items for good measure, save the “Count” as a variable to be used as my upper bound in my Loop Counter and log it out.

Do Business Logic

After setting up the stage to get the URI, return the JSON data, Parse It to the Node you want, and get a Count, the next thing is to do the business logic.

What you will see below in the image is us using the Variable Count as the upperbound in our Loop and then getting the JSON data into Local Variables to be manipulated in the Workflow Logic.

//MY SOAPBOX

So, i have been talking about this for a while as most people who have known me for a long time, know be because of my work and efforts in SharePoint BCS.  But i have actually moved my thoughts in some aspects to what BCS allows (even in 2013) and what Workflows gives you interms of interacting with External Data. You see my point is, if you do not need to look at that data, i.e. External List, or need to Search on that Data i.e. External Content Type as a Content Source, I THINK that it is to your best interest from a simplicity standpoint, a performance standpoint, best of breed standpoint… to do this as a Workflow calling External Web Services and manipulate the data how you want and then get rid of it (i.e. the Variables) when done. That way the data is only used for its intended purposes then goes away.

//END SOAPBOX

Back to our story, what we do next is inspect the Due Date and use Conditional Statements to affect changes to the Fields for what you need. in this instance I am updating a KPI field based on how far along or how overdue a Due date is on a Task List. Now the Use case here is that I can do this on Any Amount of List both On PREM and in Office 365 in the same logic, I have Scale and Scope down to a Tee 🙂

 

image

 

The logic loops through each item does a Check and then updates the loop counter until it hits the “Count” variable number then exits. Since this is a Timer Job then the next thing we need to do is Pause it and wait, see below

image

Now, this workflow was designed as a Site Workflow so it can be ran independently of any Library or Lists and this guy can run forever.

Summary

So the next time someone ask you to Do a Timer Job for them, especially if they say they want it on Office 365 (SharePoint Online) and it involves a use case similar to mine or at the very least is looking at affecting changes to a List/Library, consider doing a Site Workflow with Conditional Logic and Pause Duration. Its really is that simple.

If this was being done in SharePoint Online, you would also need to capture and pass along the oAuth Tokens FedAuth and rfTa in your dictionary object.

Cheers and Happy SharePointing

September 4, 2013 Posted by | JSON, Office 365, REST, SharePoint 2013, SharePoint Designer 2013, SharePoint How-To, SharePoint Online, Workflows | , , , | 13 Comments

More on SharePoint 2013 REST API with Fiddler and SPD

Précis

So, as of lately when it comes to SharePoint 2013 Workflow, CSOM with Managed Code, REST API, I have been trying to see how far down the rabbit hole i can go, and talking about what I find.  As a result, I have been fielding quite a few questions on the topic; although there is quite a bit of information out there by Kirk (@kaevans) Evans here on MSDN and others, what i have find is that they discuss a lot of the Reading Data, usages in C# and JavaScript and they elude to debugging with Fiddler.  I on the other hand, like to test my work in Fiddler then take it into SPD (SharePoint Designer 2013) or Visual Studio 2012 for either work with Console Apps, Workflows, Event Receivers etc. It also gives me the opportunity to see what is happening under the layers of SPD, or obscured by Visual Studio Activities. Now there are times when that door will get slammed shut in you face especially around SharePoint Online Office365 and then i just rely on Wictor (@wictor )Wilens MSO Helper in a Console App to peruse around. This post will show you how to Add data into a SharePoint List (i.e. ListItem) using the REST API via Fiddler and How to Create a SharePoint LIST as i couldn’t find a decent example out there after suggesting to a work colleague that they use Fiddler to map out their work then do it in SPD. On the other hand, there is a great post here by Borislav Grgic on how to use Add an Item to a SharePoint List using the REST API and POST Method in SharePoint Designer 2013.

What Major Obstacles you will need to Overcome

This is what I found lacking when I did research on how to achieve the stated objectives above.  Most of the examples just told you to either “..type this” or “create a dictionary object to to that…”, and some just omitted major steps that you will get tripped up in and no way to circumvent it. So, taking this by the numbers, lets first assume that you want to get back some List data from SharePoint 2013, we will do this first On-Prem then In-Cloud (Office 365).

On Prem

  1. You will need to construct a Uri representing your target list such as :  //_api/lists/getListByTitle(‘’)/items">http://<site>/<web>/_api/lists/getListByTitle(‘<listNameHere>’)/items in a browser (preferably Chrome) or Fiddler
  2. You will need to set some Headers, specifically the (a) Accept and (b) Content-Type Headers to accept ‘application/json;odata=verbose’ inorder to work with the return items in JSON format which if you are using this in SPD, you MUST do in order to use the Dictionary Object, or if you want to read the data in some logical hierarchy.
  3. If you are ON Prem, and using Fiddler inside “Composer” under Options, i usually set the Automatically Authenticate Flag like so, you will see me talk about Decrypting HTTPS Traffic later on in the Cloud or HTTPS targets later on. In doing so your Cookie header will be automatically set.
    image
  4. Next ensure your Verb is set to GET and fire away.

In Da Cloud

  1. Well steps one (1) and two (2) are the same from the above, however step three (3) is a bit different.
  2. You will need to pass your Cookie Token for the Office 365 Site here, and simply put you will log into the Target Office 365 Site and using using Fiddler you set it to “Capture Traffic” after you log in. If/when you click on a List or Library as an example, inspect your Headers returned (assuming you have already gone under Tools / Fiddler Options and selected to Decrypt HTTPS Traffic) like so
    image
  3. Once you do and inspect the Headers you will find cookies for FedAuth and rtFa under the “Cookie” header. Go ahead and copy and paste that in the Request Headers section of Fiddler
  4. Next ensure your Verb is set to GET and fire away.

What if i want to POST (Add) Data to my SharePoint List?

Well, if you do, you have to do a few more things before you can actually POST that data.

  1. You will need to get the X-RequestDigest Header to send along with your POST, notice this is “not” a GET anymore. Now referencing the post again above by Kirk Evans if you look almost 3/4’s away down on the page or just Cntrl-F to find the word “contextinfo” you will find a description on how to get that Header Information in the d:FormDigestValue node.
  2. Essentially you will need to change your Fiddler VERB from a GET to a POST and send along an empty Request Body, you may need to set a “Content-Length” Request Header as well, and i usually do 104 for that length.
  3. You will get back the information that you will need to add to your Fiddler statement.
  4. In addition you will need to construct a Key/Value Pair (hence working in JSON) to pass along in the Request Content that you want to Add
  5. Now there is one more obscure piece of information that ususally trips people up that you will also need to do, and this information will form a part of your Request Content that holds your Data you are trying to add.
    1. When you did your GET for the site, there was a Node called (underscore undescore metadata) __metadata. You will need to take that value and pass it first ahead of any data you are trying to add. You will see this example below
  6. Once you do all the above, you ensure that your Verb is set to POST and fire away.

Lets look at a few Examples

So taking this from the beginning we will use a use case of a List i have called “AndyTaskListOnPrem” and we will first do some Gets, then a Post and then finally verify our work. Here is an example of the list before the exercise.

image

Next lets look at how you need to construct the GET to look at the data in JSON format in Fiddler

image

And our Outcome. Pay attention to the Metadata Node and the type = SP.Data.AndyTAskListOnPremListItem value. you will need this later on for the Adding of List Item.

image

continued

image

Next lets look at how to get the ContextData information.

Create a POST with the URI of /_api/contextinfo">http://<site>/_api/contextinfo as i have below

image

Ensure the Response Content is blank and execute that, your results should look like mine below. You will need to capture the “FormDigestValue” information for later on in your POST.

image

Next lets Create a List Item

Armed with that information we have enough to create a List Item in this SharePoint List using the
REST API and Fiddler and using the same information plug this into Dictionary Objects in SharePoint Designer along with the Make a HTTP Call to do the same, here’s how…

image

Paying attention to the POST Verb, the X-RequestDigest “Request Headers” and the __Metadata and Addition in the “Request Body” after execution the above you should get

image

a HTTP/1.1 201 Created response. and if we inspect the list we should expect to see

image

 

To round things off, lets assume our Target was Office 365 SharePoint Online, then we would expect to see our Headers contain our Auth Tokens.  I will take this to a next level by using an example someone just asked me about. I will Create a List in Office 365 “not list ITEM, an actual LIST” using the same methods described above. the KEY takeaway here is I need to Pass my Auth Tokens and ofcourse I am using a different REST API Call

Lets Create a SharePoint List using REST and Fiddler

So, in the spirit of openness, here is the REST call to get my Auth Token… well some of the token, I wasnt born yesterday 🙂

image

Here is the Context Info, key here is remember its not HTTPS and you need pass the Token as well.

image

Finally here is me creating A NEW LIST in the Web

image

and with all the confidence in the world [well this is O365 so we also cross our fingers] we expect to get a 201 Create Response back from HTTP Header

image

and visually in the browser we should expect to see

image

Summary

So, in this exercise we accomplished the following

  • Understanding of some of the SharePoint 2013 REST API
  • Usages of that API in Fiddler and the Browser
  • How to Add a SharePoint List Item using the REST API
  • How to create a new LIST using the REST API

This should translate VERY easily into SharePoint Designer 2013 and Visual Studio 2012, that is the point of the exercise. Thats why I am NOT showing how to do it there, I have other post that talk about the Dictionary Object and how to create Headers, just use what I have here and apply it to one of those post and you should be golden. This is me teaching to fish.. 🙂 Cheers.

 

September 3, 2013 Posted by | JSON, Office 365, REST, SharePoint Designer 2013 | , , | 16 Comments

SharePoint Saturday New York 2013 Wrap-up

Praises

Year after year the organizers and volunteers that put on SharePoint Saturday New York City #spsnyc do an amazing job and the intensity and excellence keeps getting better year after year.  Fantastic Job Rebecca “Becky” I, Greg G, Jason G, Tasha S, Tom D, Brandon B, Casey S, and the rest of the crew too many to name.

This year it felt like on all level it came together even with the late lunch 🙂 The audience was exceptionally knowledgeable and engaging, we had fun while learning; i guess it helps that I actually have been seeing them for many years know and we treat each other like extended family.  Even though next year we will be in a new Venue, I will miss the Microsoft facility across form the Hilton where the Halal GUys have their truck.

Regarding my Preso, Deck, Session Information

So, as noted in my presentation, and for those of you that know the way I deliver sessions, I do a lot of demos, hardly any slides. This time however, my session was based on a 2 part blog post I did, I requested that my session attendees not take notes because the blog post will be more information that you had in the session, so please pick it up here

Finally a SharePoint Designer that Developers and BAs will Love – This is the Part 1 of what would have come before the session you sat in and

Part 2 of Finally a SharePoint Designer that Developer and BAs will Love – This is reflective of the session you had

Thank you all for attending, hope to see you again next year.

Cheers.

July 29, 2013 Posted by | JSON, Public Speaking, REST, SharePoint 2013, SharePoint 2013 Workflows, SharePoint Designer 2013, Where is Fabian | , , , | Leave a comment