Fabian Williams SharePoint Blog

Solving problems with SharePoint day and night

SharePoint Fabian Blog-2013 in review

The WordPress.com stats helper monkeys prepared a 2013 annual report for this blog.

Here’s an excerpt:

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 100,000 times in 2013. If it were an exhibit at the Louvre Museum, it would take about 4 days for that many people to see it.

Click here to see the complete report.

December 31, 2013 Posted by | Where is Fabian | | Leave a comment

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

Wrap-up on my SharePoint BCS SPLive360 Sessions

Accolades

On October 10th 2013 i blogged Come see my three session at SPLive360 in Orlando in an effort to promote and create awareness/buzz around my delivery at the event Andrew Connell and Dan Holme put on.  I have to tell you it was a fantastic event, with a slew of talented speakers, engaged and energetic audience who were involved in my session from beginning to end. I delivered 3 sessions on BCS:

  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

image

and by the third session I was able to see and count out at least 50% of the folks who attended my earlier sessions in my last session. Even with the first session being first of the day 0915 & the second and third being right after lunch, the session attendees were eager to learn and contribute their perspectives on the content delivered.

Appreciation

It is with that recognition that i would like to express thanks to the Event Co Chairs Andrew Connell and Dan Holme for accepting me to speak at this event as well as the Event Organizers SP Live 360 for putting on such a well organized and excellent event.  The hotel was spectacular, the rooms and technical staff on point and always available, and overall flow was spot on. 

My Slide deck was delivered to the event organizers but can also be picked up here as well.

What’s Next

If you are in the DC Metro area December 7th 2013 stop by my session (well after you register here) on Workflows using REST API, JSON and all in SharePoint Designer 2013.

As well, I will be delivering three (3) Sessions at SharePoint Conference 2014 March 3rd through 6th 2014 (#SPC14 or @SPConf) in Vegas so keep an eye on the website for times and dates.

 

Cheers & Irie!

See oonuh lateah

November 24, 2013 Posted by | Business Connectivity Services, Public Speaking, SharePoint 2013, SharePoint How-To, Where is Fabian | 1 Comment

Answered: Leave request for SharePoint O365 Sample on MSDN

Précis

So, I as a cure for insomnia, decided to troll the CODE.MSDN site for questions that interested me.  I found this request and figured it was something i could knock out, so i did. There was not much to go on interms of HARD requirements so i posed a question, I assumed if Im up and about, the person making the request must be up too 🙂 but alas no. So, i went ahead and did the sample as I would envision it. In fact, internally at work, we have something On Prem that is similar so this actually made it more appealing. 

I can and will make this example more robust and may submit it up on the MarketPlace, I dont know, just to say that I have, but it seems something that Im sure must already be there, Ill just endeavor to make mine better 🙂

THIS CODE IS AVAILALE ON MSDN: Here

Requirements

As this is an Office 365 SharePoint Online example, you basically don’t need anything as you would for an On-Prem solution, all you need really is:

  • A Office 365 SharePoint Online Site to build against
  • Visual Studio [for completeness I am using 2013 but others will do]
  • Your Brain & some Idle time

What this example will set out to do in this version is the following

  1. Permit an End User to Request Leave off Work/ Holiday/ Vacation etc.
  2. Send a Request for Approval to the End Users Manager/ or the person On Behalf Of
  3. Permit the Approver to Adjudicate the request in an Approval Workflow in SharePoint 2013
  4. Send a ‘Contract of Sorts” to the Requester alerting them of the decision for their acknowledgement
    1. Notification includes a process to Accept the decision or Appeal it
  5. If Approved, update a Leave/Out of Office (OOF) Calendar with the relevant information to show the person being OOF

I plan to extend this example later on to flesh out the Appeals Process, make pretty App Icon and put some more bells and whistles on it. Sexy it up so to speak 🙂

Approach

As with all my work when I set out to do a SharePoint project that involves List/Libraries, Content Types, Workflows/Event Receivers, etc, I often will create my own Site Columns, Content Types, List Definition and Instance (see post here on how to do that). Once I complete that process I typically will Branch my Project (in TFS Online) and start to Dev out the Unit of work/ Story (Agile).  I created a branch for Workflows and set up folders for what I will build out now and later on

image

This is what my Visual Studio Solution looks like now

image

So, with that heavy lifting complete, lets focus on the Workflow.

Workflow Build out

First, you add a Workflow to your project and associate it with your List, in my case I am associating it with the “TimeOffRequest” list instance. I also set the Workflow to Start on “On Item Created” and have New Task List and Workflow History List created. Basically this…

image

Once you do that, you will get a few assets created for you

  • The Workflow itself
  • A History List
  • A Task List

image

The canvas will open up and you will a blank slate with one Sequence added. What I typically do here and I learned this from Andrew Connell in all his Demos/ Preso is to change that Default Sequence to “Root” and then embed your other abstracted logic flow buckets inside that sequences as nested Sequences.  I do this for the following reason

  1. It makes the code much more readable and manageable
  2. It allows you to scope your Variables etc to its own sequence
  3. Helps me troubleshoot if/when I get issues

So mine looks like this now

image

and you can drill down into each section like so

Init

image

Manager Approval

image

Notification

image

image

Update Time Off Calendar

image

Other Important items not so obvious

So, there are variables that are crated along the way either by an objects Return Type or Auto Loaded by Variable Declaration in SPLookupItem Object, etc

For example here is what is in scope for “Root” Sequence

image

here is what is inscope for “Notification”

image

How it Works

So, when you get everything working you publish it to your Office 365 SharePoint Tennant. Were I paid for this I would mock up the Default.aspx page to show links for the Time Off Request and the Calendar with an Appropriate View, but for now we will just use Different Tabs in the Browser

You will have a new App in your Site Contents like so

image

Here are the Tabs

First the TimeOffRequest

image

Second the TimeOffCalendar

image

Dev Testing

Lets put it through the paces now.

image

Now lets submit this Form

Now we have a form submitted

image

lets check the Status of the Workflow, and we can see below we have one already going

image

We even got an email in Outlook. for now, lets adjudicate this as Approved so it will appear in the calendar eventually

image

Here you can see that we have buttons to Approve and Reject and we have meaningful information in the Email Body. Finally you can see that the Workflow is running in the Context of the App Principal at the bottom, look to the left of the buttons.

Next, and just showing the Task and History Log we can see now that a Contract has been sent back to the Requestor after the decision has been made

image

lets go ahead and do that, this time I wont do a screen shot as its similar to the same Approve Reject one earlier.

image

Finally you can see everything done, and the only thing we need to do is check the Calendar to see if the Item has been added.

image

and sure enough it is….

Summary

So, i hope this was helpful, if so, drop me a comment, share this online, etc.. hook a brotha up 🙂

November 10, 2013 Posted by | Office 365, SharePoint 2013, SharePoint 2013 Workflows, SharePoint How-To, SharePoint Online, Visual Studio 2013, Workflows | | Leave a comment

Not SharePoint but cool anyway MVC5 EF6 and Azure

Précis

This post is about using Model View Controller (MVC) 5 in Visual Studio 2013 with Entity Framework 6 to do CRUD operations on a SQL Azure Database.  It is definitely NOT Rocket Science but there are a few GOTCHAS to look out for and definitely a new way of doing business with MVC 5 as it relates to Controllers and Scaffolding with Entity Framework.

My code will be up at Code.MSDN.com and I will put it in Git Hub as well. I will update the post with the links when i upload them.

Create your Project

The first thing you have to do is create your project. So, Launch VS 2013 as Administrator and under the Installed Templates click Web and select the “ASP.NET Web Application”, provide a meaningful name and click OK.

image

The next thing we will do is select MVC since that is what we are doing here today. As this is DEMOware we will not be doing any Unit Testing as well so unselect that if it is selected for you.

 

image

Once complete your project will look as mine does below.

 

image

and a project ReadMe that invites you to customize the Applications etc. will be there for you as well.

Create your Model

Since we are connecting to a Data Source, as in our case it is my Northwind Customers Table in SQL Azure, what we need to do is create an Object (Class) that represents that schema, so take a look at your Columns and lets add a Class file next

image

Above is the Silverlight App that ships with SQL Azure Management and you can see some of the fields we will Model. To Model the entity, you add a new Class to your Project as i have below. Right click on the Model Folder and..

image

Inside that Class go ahead and create the necessary auto generated properties as you can see in the code snippet below

 

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5.  
  6. namespace AzureNorthWindCustMVC5Demo.Models
  7. {
  8.     public class Customer
  9.     {
  10.         public string CustomerID { get; set; }
  11.         public string CompanyName { get; set; }
  12.         public string ContactName { get; set; }
  13.         public string ContactTitle { get; set; }
  14.         public string Address { get; set; }
  15.         public string City { get; set; }
  16.         public string Region { get; set; }
  17.         public string PostalCode { get; set; }
  18.         public string Country { get; set; }
  19.         public string Phone { get; set; }
  20.         public string Fax { get; set; }
  21.     }
  22. }

With that, you have your Model. This is a good time to Build your project so the Model will be available for the Controller which we will do next.

Create your Scaffolding and Controller

This is one place where the cool new features of MVC 5 kicks in, you can Right Click on your Controller Folder and add “New Scaffolded Item”. What does for you is literally ALL THE WORK necessary to have full CRUD capabilities back to your Data Store. It will

  • Create your Context Class for your Database
  • Create your Connection String in the Web.Config [which you will need to Modify anyway because by default it points local]
  • Create an instance of the Context Class in your Controller so you can interact with the Database
  • Create all the Views for you to
    • See a List of All items returned
    • See Single Item
    • See Detailed Items
    • Edit Items
    • Delete Items
    • Create a New Item

Here is how you do it, right click on the Controller Folder in your Project and do Add, New Scaffolded Item…

image

You have the option to do this manually by doing and Empty Controller but since this example is about Entity Framework 6, we will use a controller that will create all views with EF6.

image

Once that is done, you can wire up the Data Connection for the Model and the Data Context. Assuming that you Build your project as i said earlier, you should see as i do a Model Class with the name you need. In my case “Customer”

image

Once you have selected that you can now do “New Data Context” for it to create that Class for you as well. I named mine to be reflective of the Table i am working with. See below…

 

image

One minor change that I did. You will notice above my Controller Name “WAS” Home… but I realized that there was one already created called Home so i changed it to Customer as you can see below.

 

image

Once you click Add, you should see your project grow in leaps and bounds like mine below

 

image

GOTCHA Moment

Now you may think your work is done but there are at least Two (2) more things needed to make this connect to your Azure Database. The first thing is to (1) Modify or ADD the connection string to your SQL Azure Database to the Web.Config in the root of your Project. If you dont MVC will create a NEW database for you locally in SQL Express more than likely if the Controller DOES NOT find a Connection String of its Name inside the Web.Config. That is why you will see below that the Name of MY connections string is the same name of my Context Class.

image

Below is a code snippet of the connection string minus my credentials.

  1. <entityFramework>
  2.   <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
  3.   <providers>
  4.     <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
  5.   </providers>
  6. </entityFramework>
  7. <connectionStrings>
  8.   <add name="CustomerContext" connectionString="Server=tcp:uboxhere.database.windows.net,1433;Database=FabianNorthwind;User ID=UrUserNameHere;Password=P@ssword123;Trusted_Connection=False;Encrypt=True;Connection Timeout=30" providerName="System.Data.SqlClient" />
  9. </connectionStrings>
  10. </configuration>

 

The second thing (number 2) is to Add a command to the Global.asax file which is fired upon App Launch to Initialize the Database connection I want in Azure, you will need to have a reference to the System.Data.Entity namespace as i have shown below when you do this. You will notice it is also bound to the Model Context Class.

image

Just a little weird thing that I noticed.  I did this on my Windows 8.1 box and when I tried to launch the browser to test my work, IIS Express failed with some Process number, so i changed my Build to point to my Local IIS and created a Virtual Directory as you see below.

image

Once I did that, when I clicked F5 to test this out, it worked as expected. The circle is complete 🙂

image

 

Summary

This is really cool, i know I usually talk about SharePoint but this is equally important and you will also see a new MVC Template in SharePoint for Visual Studio 2013 in Cloud Apps, make sure you give that a try. Chaks has a good post out on it here.

My code will be up at Code.MSDN.com and I will put it in Git Hub as well. I will update the post with the links when i upload them.

For now Enjoy.. c u l8r

November 6, 2013 Posted by | Azure, Entity Framework, MVC, Visual Studio 2013 | , , , | 2 Comments

Weird SharePoint Service Error SPHelp Please

Scenario

Update: 10/21/2013 – I didnt bother trying to figure it out or troubleshoot it anymore, I had a HyperV ShapShot from a week earlier, i just restored it to that, and all is working.

 

So, I was looking at my Business Connectivity Service App in Central Admin and when I click on the Service App here

image

I got this error below.

image

taking the Correlation ID and looking in the ULS Log I saw the below of interest. 

   1: 10/20/2013 17:43:16.49     w3wp.exe (0x0934)                           0x0DC8    SharePoint Foundation             Claims Authentication             af32i    Unexpected    SPIisWebServiceAuthorizationManager: SPIisWebServiceApplication with name 'Business Connectivity Service Application' and type 'Microsoft.SharePoint.BusinessData.SharedService.BdcServiceApplication' received request with ServiceSecurityContext whose primary identity has no valid data to check against ACL.     

   2: 10/20/2013 17:43:16.50     w3wp.exe (0x1DC0)                           0x1474    Business Connectivity Services    Business Data                     ajqrj    Unexpected    Logging unknown/unexpected client side exception: SecurityAccessDeniedException. This will cause this application server to be removed from the load balancer queue. Exception: System.ServiceModel.Security.SecurityAccessDeniedException: Access is denied.    Server stack trace:      at System.ServiceModel.Channels.ServiceChannel.ThrowIfFaultUnderstood(Message reply, MessageFault fault, String action, MessageVersion version, FaultConverter faultConverter)     at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)     at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)     at System.ServiceModel.Channels.ServiceChannelProxy.InvokeSer...    f49f4e9c-e6ee-002a-4789-8bf13431fe73

   3: 10/20/2013 17:43:16.50*    w3wp.exe (0x1DC0)                           0x1474    Business Connectivity Services    Business Data                     ajqrj    Unexpected    ...vice(IMethodCallMessage methodCall, ProxyOperationRuntime operation)     at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)    Exception rethrown at [0]:      at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)     at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)     at Microsoft.SharePoint.BusinessData.SharedService.IBdcServiceApplication.GetEntitiesLikeNameAndNamespace(String wildcardedNamespace, String wildcardedName, Int32 callerCultureLcid, Boolean activeOnly, Guid partitionId)     at Microsoft.SharePoint.BusinessData.SharedService.BdcServiceApplicationProxy.<>c__DisplayClassab.<GetEntitiesLikeNameAndNamespace>b__aa(IBdcServiceApplication serviceApplication)     at Micr...    f49f4e9c-e6ee-002a-4789-8bf13431fe73

   4: 10/20/2013 17:43:16.50*    w3wp.exe (0x1DC0)                           0x1474    Business Connectivity Services    Business Data                     ajqrj    Unexpected    ...osoft.SharePoint.BusinessData.SharedService.BdcServiceApplicationProxy.Execute[T](String operationName, UInt32 maxRunningTime, ExecuteDelegate`1 operation, Boolean performCanaryCheck, Boolean isChannelThatDelegatesIdentity)    f49f4e9c-e6ee-002a-4789-8bf13431fe73

   5: 10/20/2013 17:43:16.50     w3wp.exe (0x1DC0)                           0x1474    Business Connectivity Services    Business Data                     afoqa    Monitorable    Exception thrown in method ViewBDCApplication.InitializeEntityView. Exception type is System.ServiceModel.Security.SecurityAccessDeniedException. The error message is An error occurred. Administrators, see the server log for more information.    f49f4e9c-e6ee-002a-4789-8bf13431fe73

   6: 10/20/2013 17:43:16.50     w3wp.exe (0x1DC0)                           0x1474    Business Connectivity Services    Business Data                     afoqb    Unexpected    Inner exception 1:     f49f4e9c-e6ee-002a-4789-8bf13431fe73

   7: 10/20/2013 17:43:16.50     w3wp.exe (0x1DC0)                           0x1474    Business Connectivity Services    Business Data                     afoqc    Unexpected    System.ServiceModel.Security.SecurityAccessDeniedException: An error occurred. Administrators, see the server log for more information.     at Microsoft.SharePoint.BusinessData.SharedService.BdcServiceApplicationProxy.Execute[T](String operationName, UInt32 maxRunningTime, ExecuteDelegate`1 operation, Boolean performCanaryCheck, Boolean isChannelThatDelegatesIdentity)     at Microsoft.SharePoint.BusinessData.SharedService.BdcServiceApplicationProxy.GetEntitiesLikeNameAndNamespace(String wildcardedNamespace, String wildcardedName, Int32 callerCultureLcid, Boolean activeOnly, Guid partitionId)     at Microsoft.SharePoint.BusinessData.Administration.EntityCollection.EnsureEntities()     at Microsoft.SharePoint.BusinessData.Administration.EntityCollection.GetEnumerator()     at Microsoft.Share...    f49f4e9c-e6ee-002a-4789-8bf13431fe73

   8: 10/20/2013 17:43:16.50*    w3wp.exe (0x1DC0)                           0x1474    Business Connectivity Services    Business Data                     afoqc    Unexpected    ...Point.ApplicationPages.ViewBDCApplication.FillTableWithEntities(DataTable entitiesTable, IEnumerable`1 entities)     at Microsoft.SharePoint.ApplicationPages.ViewBDCApplication.InitializeEntityView()    f49f4e9c-e6ee-002a-4789-8bf13431fe73

Now, what I found ODD is that…

  • I can still use ALL the External Content Types
  • I have and ALL my External Lists are working

But I cannot administer the Service in Central Administration.

What I have looked at and Tried

I have checked the amount of RAM i have on my VM…

image

I have started and stopped IIS by doing an IIS RESET, Ive also even bounced the box completely

I have tried creating a new Service App of the same type, it Created correctly, but got the same Error when I clicked on it. I named it different and created a separate DB.

Ive gone into Services on Server and Stopped the service and then Restarted it. No dice.

image

 

Thank for any Comments and Suggestions in Advance

I really appreciate you looking at this. Ive used all my DEV Magic 🙂

October 20, 2013 Posted by | Business Connectivity Services, SharePoint Errors | 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

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

image

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.

Approach

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. 

image

this will be a Farm Solution

image

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

image

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

image

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

image

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.

image

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.

image

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

image

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

GOTCHA ALERT

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.

END GOTCHA ALERT

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");
  5.  
  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");
  5.  
  6.  
  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");
  5.  
  6.  
  7.       Employee emp = new Employee();
  8.  
  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;
  23.  
  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”

GOTCHA ALERT

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

BUT!!!!

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

image

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.

END GOTCHA ALERT

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");
  5.  
  6.         var employeeToUpdate = (from employees in dataContext.Employees
  7.                                where employees.EmployeeID == parameter
  8.                                select employees).Single();
  9.  
  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

 

  1.  
  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");
  6.  
  7.     Employee Employee =
  8.     (from employees in dataContext.Employees.AsEnumerable().Take(20)
  9.      where employees.EmployeeID == employeeID
  10.      select employees).Single();
  11.  
  12.  
  13.     dataContext.Employees.DeleteOnSubmit(Employee);
  14.     dataContext.SubmitChanges();
  15.  
  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;
  6.  
  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");
  15.  
  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.         }
  22.  
  23.         public static IEnumerable<Employee> ReadList()
  24.         {
  25.             NorthWindDataContext dataContext = new NorthWindDataContext
  26.             ("Data Source=Farm1Server1ADSQL;Initial Catalog=Northwind;uid=BCSUser1;pwd=P@ssword1");
  27.  
  28.  
  29.             IEnumerable<Employee> Employees =
  30.                 from employees in dataContext.Employees
  31.                 select employees;
  32.             return Employees;
  33.         }
  34.  
  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");
  39.  
  40.  
  41.             Employee emp = new Employee();
  42.  
  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;
  57.  
  58.             dataContext.Employees.InsertOnSubmit(emp);
  59.             dataContext.SubmitChanges();
  60.             return emp;
  61.         }
  62.  
  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");
  67.  
  68.             var employeeToUpdate = (from employees in dataContext.Employees
  69.                                    where employees.EmployeeID == parameter
  70.                                    select employees).Single();
  71.  
  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.         }
  88.  
  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");
  93.  
  94.             Employee Employee =
  95.             (from employees in dataContext.Employees.AsEnumerable().Take(20)
  96.              where employees.EmployeeID == employeeID
  97.              select employees).Single();
  98.  
  99.  
  100.             dataContext.Employees.DeleteOnSubmit(Employee);
  101.             dataContext.SubmitChanges();
  102.  
  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="1.0.0.71">
  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

image

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.

image

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

Blog Update on SharePoint BCS with full CRUD Part 1 of 3

Précis

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

image

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

  • BCS Full CRUD using SharePoint Designer with a Windows Communications Foundation (WCF) Data Connection [I will actually show you how to build the WCF, otherwise why even bother :-)] using the Users Identity as the Auth (YOU ARE HERE!)
  • BCS Full CRUD with Visual Studio 2012 .Net Assembly using Secure Store [I will put in Code Snippets this time rather than Images :-)]
  • BCS Full CRUD with Visual Studio 2012 OData LOB System Type SharePoint App [Yes App Model] to include the External List Instantiation

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.

Approach

As this is an update, 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. Creating a WCF Source
    1. Basically, creating the WCF Service Library
    2. Moving the Service App DLL & Hosting the WCF in IIS (since automatically it is hosted in the local virtual Visual Studio IIS app)
    3. Using SharePoint Designer 2013 and that WCF Data Connection to do Full CRUD Operations, CRUD-Q infact

Lets Begin – Creating the WCF Service Library

My thought process for this since I am going to be exposing data in an underlying SQL Server is to use Visual Studio Entity Framework 5 to create my access to the Data Store. I will then (I don’t have to but because I want to extrapolate the Entity and manipulate it as my own object) I will create my own class(object) to represent the Northwind Employee and then create my Visual Studio Interface and then my Service Contract.  If all of this sounds like gibberish to you, here is a beginners tutorial on how to create a WCF Service Library (its too involved for this post and will take away from my objective) NOW… I am not going to implement it totally the way the post describes it, as i said above, but I WILL show you the code of my

  • Connecting to the Northwind Database
  • My Northwind Employee Entity (Data Contract)
  • Service Contract (Interface)
  • Service Itself(Implementation of the Interface)

New Project & Connecting to the Database

First you will start Visual Studio as Administrator and create a new Project as such

image

Project > New Project > Dub CF > WCF Service Library – Then name it appropriately

Now by default you will get a Service Contract called (IService1.cs) and a Service Implementation (SErvice1.cs) – I don’t like using the OOB templates that are provided although hey say you can Rename/Refactor it; i just never trust it. So, you can go ahead and delete those, because we will create our own classes for our Data Contract, Service Contract and Service Implementation.

image

Once you have deleted those two files, then next we will do is add an ADO.NET Entity Data Model item to our project, name it appropriately for the Data you are modeling after and click Add.

image

Follow the Wizard by doing “Generate from Database”, make a new connection or select from one already there, because I am using SQL Server Authentication and I want to have this persisted in my project I also select the option for “Yes, include the sensitive data in the connection string” which is basically your Uname and PW along with Data Source and Initial Catalogue. Under Tables I am selecting my Northwind Employee Table, and following the rest of the wizard to the end. When you are done, you should have an entity as i do below as well as a “NameYouChose”.edmx file which shows you the below

image

Once you have done that, now I am actually going to create my own Data Contact class which abstracts from the ADO.NET Employee entity.  You do that by adding a new class to your project. I am calling mine “Emp.cs” as seen below in the code snippet

  1.  
  2. namespace NorthwindServiceLib
  3. {
  4.     [DataContract]
  5.     public class Emp
  6.     {
  7.         [DataMember]
  8.         public Int32 EmployeeID;
  9.         [DataMember]
  10.         public string LastName;
  11.         [DataMember]
  12.         public string FirstName;
  13.         [DataMember]
  14.         public string Title;
  15.         [DataMember]
  16.         public string TitleOfCourtesy;
  17.         [DataMember]
  18.         public DateTime BirthDate;
  19.         [DataMember]
  20.         public DateTime HireDate;
  21.         [DataMember]
  22.         public string Address;
  23.         [DataMember]
  24.         public string City;
  25.         [DataMember]
  26.         public string Region;
  27.         [DataMember]
  28.         public string PostalCode;
  29.         [DataMember]
  30.         public string Country;
  31.         [DataMember]
  32.         public string HomePhone;
  33.         [DataMember]
  34.         public string Extension;
  35.         [DataMember]
  36.         public string Notes;
  37.         [DataMember]
  38.         public string ReportsTo;
  39.         [DataMember]
  40.         public string PhotoPath;
  41.  
  42.     }
  43. }

You will notice that i am decorating the class with the [DataContract] attribute and also setting the fields that i want as [DataMember] then next thing I do is create my Service Contract which as I said before is a Interface; I do that by adding another class called IEmpService.cs as seen in the code snippet below

  1.  
  2. namespace NorthwindServiceLib
  3. {
  4.     [ServiceContract]
  5.     public interface IEmpService
  6.     {
  7.         [OperationContract]
  8.         void CreateEmployee(Emp emp);
  9.         [OperationContract]
  10.         List<Emp> GetAllEmp();
  11.         [OperationContract]
  12.         List<Emp> GetSpecificEmp(Int32 empId);
  13.         [OperationContract]
  14.         void RemoveEmp(Int32 empId);
  15.         [OperationContract]
  16.         void UpdateEmp(Emp emp);
  17.     }
  18. }

Here we decorate the interface with the attribute [ServiceContract] and this is the fist part where we have an eye towards what SharePoint BCS needs. As you can see above we have a the following

  • Create Method called “CreateEmployee” which takes the data contract as a input parameter
  • Read List Method called “GetAllEmp” which returns a list of all the Employees
  • Read Item (Specific Finder) Method called “GetSpecificEmp” which takes the employee Id as an input parameter
  • Delete Method called “RemoveEmp” which also takes employee Id as an input parameter
  • Updater Method called “UpdateEmp” which takes the data contract as an input parameter

We also decorate these as Operation Contract what we MUST implement to use our Interface. Next we will create the service that will implment the Interface and this is done by creating another class called EmpService.cs

  1.  
  2. namespace NorthwindServiceLib
  3. {
  4.     [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
  5.     public class EmpService : IEmpService
  6.     {
  7.         public void CreateEmployee(Emp emp)
  8.         {
  9.             using (var ctx = new NorthwindEntities())
  10.             {
  11.                 List<Employee> empnew = new List<Employee>();
  12.                 Employee e = new Employee();
  13.                 e.EmployeeID = emp.EmployeeID;
  14.                 e.FirstName = emp.FirstName;
  15.                 e.LastName = emp.LastName;
  16.                 e.Title = emp.Title;
  17.                 e.TitleOfCourtesy = emp.TitleOfCourtesy;
  18.                 e.BirthDate = DateTime.Parse(emp.BirthDate.ToString());
  19.                 e.HireDate = DateTime.Parse(emp.HireDate.ToString());
  20.                 e.Address = emp.Address;
  21.                 e.City = emp.City;
  22.                 e.Region = emp.Region;
  23.                 e.PostalCode = emp.PostalCode;
  24.                 e.Country = emp.Country;
  25.                 e.HomePhone = emp.HomePhone;
  26.                 e.Extension = emp.Extension;
  27.                 e.ReportsTo = int.Parse(emp.ReportsTo);
  28.                 e.PhotoPath = emp.PhotoPath;
  29.                 e.Notes = emp.Notes;
  30.                 empnew.Add(e);
  31.                 ctx.Employees.Add(e);
  32.                 ctx.SaveChanges();
  33.                 
  34.             }
  35.  
  36.         }
  37.  
  38.         public List<Emp> GetAllEmp()
  39.         {
  40.  
  41.             using (var ctx = new NorthwindEntities())
  42.             {
  43.                 var currEmp = (from c in ctx.Employees
  44.                             select c).ToList();
  45.  
  46.                 List<Emp> emp = new List<Emp>();
  47.  
  48.                 for (int i = 0; i < currEmp.Count(); i++)
  49.                 {
  50.                     {
  51.                         Emp e = new Emp();
  52.                         e.EmployeeID = currEmp.ElementAt(i).EmployeeID;
  53.                         e.FirstName = currEmp.ElementAt(i).FirstName;
  54.                         e.LastName = currEmp.ElementAt(i).LastName;
  55.                         e.Title = currEmp.ElementAt(i).Title;
  56.                         e.TitleOfCourtesy = currEmp.ElementAt(i).TitleOfCourtesy;
  57.                         e.BirthDate = DateTime.Parse(currEmp.ElementAt(i).BirthDate.ToString());
  58.                         e.HireDate = DateTime.Parse(currEmp.ElementAt(i).HireDate.ToString());
  59.                         e.Address = currEmp.ElementAt(i).Address;
  60.                         e.City = currEmp.ElementAt(i).City;
  61.                         e.Region = currEmp.ElementAt(i).Region;
  62.                         e.PostalCode = currEmp.ElementAt(i).PostalCode;
  63.                         e.Country = currEmp.ElementAt(i).Country;
  64.                         e.HomePhone = currEmp.ElementAt(i).HomePhone;
  65.                         e.Extension = currEmp.ElementAt(i).Extension;
  66.                         e.ReportsTo = currEmp.ElementAt(i).ReportsTo.ToString();
  67.                         e.PhotoPath = currEmp.ElementAt(i).PhotoPath;
  68.                         e.Notes = currEmp.ElementAt(i).Notes;
  69.                         emp.Add(e);
  70.  
  71.                     }
  72.                 }
  73.  
  74.                 return emp;
  75.  
  76.             }
  77.         }
  78.  
  79.         public List<Emp> GetSpecificEmp(int empId)
  80.         {
  81.             using (var ctx = new NorthwindEntities())
  82.             {
  83.                 var currEmp = (from c in ctx.Employees
  84.                                where c.EmployeeID == empId
  85.                                select c).ToList();
  86.  
  87.                 List<Emp> emp = new List<Emp>();
  88.  
  89.                 for (int i = 0; i < currEmp.Count(); i++)
  90.                 {
  91.                     {
  92.                         Emp e = new Emp();
  93.                         e.EmployeeID = currEmp.ElementAt(i).EmployeeID;
  94.                         e.FirstName = currEmp.ElementAt(i).FirstName;
  95.                         e.LastName = currEmp.ElementAt(i).LastName;
  96.                         e.Title = currEmp.ElementAt(i).Title;
  97.                         e.TitleOfCourtesy = currEmp.ElementAt(i).TitleOfCourtesy;
  98.                         e.BirthDate = DateTime.Parse(currEmp.ElementAt(i).BirthDate.ToString());
  99.                         e.HireDate = DateTime.Parse(currEmp.ElementAt(i).HireDate.ToString());
  100.                         e.Address = currEmp.ElementAt(i).Address;
  101.                         e.City = currEmp.ElementAt(i).City;
  102.                         e.Region = currEmp.ElementAt(i).Region;
  103.                         e.PostalCode = currEmp.ElementAt(i).PostalCode;
  104.                         e.Country = currEmp.ElementAt(i).Country;
  105.                         e.HomePhone = currEmp.ElementAt(i).HomePhone;
  106.                         e.Extension = currEmp.ElementAt(i).Extension;
  107.                         e.ReportsTo = currEmp.ElementAt(i).ReportsTo.ToString();
  108.                         e.PhotoPath = currEmp.ElementAt(i).PhotoPath;
  109.                         e.Notes = currEmp.ElementAt(i).Notes;
  110.                         emp.Add(e);
  111.  
  112.                     }
  113.                 }
  114.  
  115.                 return emp;
  116.  
  117.             }
  118.         }
  119.  
  120.         public void RemoveEmp(int empId)
  121.         {
  122.             using (var ctx = new NorthwindEntities())
  123.             {
  124.  
  125.                  Employee empToDel = ctx.Employees.Where(d => d.EmployeeID == empId).Single();
  126.  
  127.                 ctx.Employees.Remove(empToDel);
  128.                 ctx.SaveChanges();
  129.             }
  130.         }
  131.  
  132.         public void UpdateEmp(Emp emp)
  133.         {
  134.             using (var ctx = new NorthwindEntities())
  135.             {
  136.                 var currEmp = (from c in ctx.Employees
  137.                                where c.EmployeeID == emp.EmployeeID
  138.                                select c).Single();
  139.  
  140.                 currEmp.FirstName = emp.FirstName;
  141.                 currEmp.LastName = emp.LastName;
  142.                 currEmp.Title = emp.Title;
  143.                 currEmp.TitleOfCourtesy = emp.TitleOfCourtesy;
  144.                 currEmp.BirthDate = emp.BirthDate;
  145.                 currEmp.HireDate = emp.HireDate;
  146.                 currEmp.Address = emp.Address;
  147.                 currEmp.City = emp.City;
  148.                 currEmp.Region = emp.Region;
  149.                 currEmp.PostalCode = emp.PostalCode;
  150.                 currEmp.Country = emp.Country;
  151.                 currEmp.HomePhone = emp.HomePhone;
  152.                 currEmp.Extension = emp.Extension;
  153.                 currEmp.ReportsTo = int.Parse(emp.ReportsTo);
  154.                 currEmp.PhotoPath = emp.PhotoPath;
  155.                 currEmp.Notes = emp.Notes;
  156.                 ctx.SaveChanges();           
  157.  
  158.             }
  159.         }
  160.     }
  161. }

Thats a heap a code I know, and there are other ways to do this, Im sure, this is just “my way”, you will notice I am employing the Using Statement to encapsulate the Context of the Data Connection then Im either Creating, Updating, Deleting and as in the case of Reading I have one that returns everything and one that returns just one.

THATS IT for the Code part of this!!!

Once all of that is done, you will NEED, YES NEED, to modify the App.config file because it still thinks that the Service Contract is tied to IService1.cs that we deleted earlier. We can also create other endpoiints, yada yada, but thats anotehr story. So here is what you need to do for your App.Config. Right click on it, and click “Edit WCF Configuration”, when you do you will need to change the Service to your EmpService by browsing to the Bin Directory and clicking the Dll & you will also need to set the Contract to IEmpService by doing the same. You will notice I have two endpoints as well, one for the Service Contract endpoint and the other is MEX for the announcement of the Service.

image

This GUI is just and abstract of the .config file so here is the full code for that below

  1. <?xml version="1.0" encoding="utf-8"?>
  2. <configuration>
  3.   <configSections>
  4.     <!– For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 –>
  5.     <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  6.   </configSections>
  7.   <appSettings>
  8.     <add key="aspnet:UseTaskFriendlySynchronizationContext" value="true" />
  9.   </appSettings>
  10.   <system.web>
  11.     <compilation debug="true" />
  12.   </system.web>
  13.   <!– When deploying the service library project, the content of the config file must be added to the host's
  14.   app.config file. System.Configuration does not support config files for libraries. –>
  15.   <system.serviceModel>
  16.     <services>
  17.       <service name="NorthwindServiceLib.EmpService">
  18.         <endpoint address="" binding="wsHttpBinding" bindingConfiguration=""
  19.           contract="NorthwindServiceLib.IEmpService">
  20.           <identity>
  21.             <dns value="localhost" />
  22.           </identity>
  23.         </endpoint>
  24.         <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />
  25.         <host>
  26.           <baseAddresses>
  27.             <add baseAddress="http://localhost:8733/Design_Time_Addresses/NorthwindServiceLib/Service1/" />
  28.           </baseAddresses>
  29.         </host>
  30.       </service>
  31.     </services>
  32.     <behaviors>
  33.       <serviceBehaviors>
  34.         <behavior>
  35.           <!– To avoid disclosing metadata information,
  36.           set the values below to false before deployment –>
  37.           <serviceMetadata httpGetEnabled="True" httpsGetEnabled="True" />
  38.           <!– To receive exception details in faults for debugging purposes,
  39.           set the value below to true.  Set to false before deployment
  40.           to avoid disclosing exception information –>
  41.           <serviceDebug includeExceptionDetailInFaults="False" />
  42.         </behavior>
  43.       </serviceBehaviors>
  44.     </behaviors>
  45.   </system.serviceModel>
  46.   <connectionStrings>
  47.     <add name="NorthwindEntities" connectionString="metadata=res://*/Northwind.csdl|res://*/Northwind.ssdl|res://*/Northwind.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=Farm1Server1ADSQL;initial catalog=Northwind;persist security info=True;user id=oDataSU1;password=P@ssword1;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  48.   </connectionStrings>
  49.   <entityFramework>
  50.     <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
  51.       <parameters>
  52.         <parameter value="v11.0" />
  53.       </parameters>
  54.     </defaultConnectionFactory>
  55.   </entityFramework>
  56. </configuration>

Test your Work

Now that you have done all that you can certainly test your work even before it goes into IIS to be hosted outside of Visual Studio. You can do that by using a nifty tool inside your Visual Studio Command Promt called “WCFTestClient.exe” and passing it the URI of your Service, but the easiest way is just Press F5 in Visual Studio and it will do all that for you LOL, we will use the tool later on when we move it to IIS. Just for giggles sake, I will just test the GetAllEmp for this blog. IF you want to see more come see my VS Live 360 Session that i spoke about on my blog here. 

image

As you can see above, we have the Service exposed and all the methods, and we invoked one them and we can see the results there.

Move this to IIS – Hosting this Service in IIS

Now unless you want to have to start up Visual Studio every time you need to use this WCF, what you need to do is host this in IIS or you can do TCP or NetBinding but We are all working in SharePoint for this blog so we need IIS. Its simple, just add a new Web Site Project to this Visual Studio Solution; for this we will use the WCF Service Template.  This too will add a sample implementation again in the App_Code folder which includes Service1.cs and ISerivce1.cs. Delete those again & and set a reference to the WCF Service Library DLL from the first project.  If you are asking why we deleted both those files again its because we will be using the files from our first project referenced in our .svc page. Your solution may look like this now.

image

You will also have a file called Service.svc, that we will rename to something more descriptive of our Service we are trying to expose, I name mine NWindEmployee.svc Once you have renamed it, you will need to set the Service to the Project Namespace and remove the codebehind attribute since we wont need it.

  1. <%@ ServiceHost Language="C#" Debug="true" Service="NorthwindServiceLib.EmpService" %>

Save your file, and now its time to modify the Web.config file this time to point to our Service Reference, this is JUST like we did for the App.config so I wont bother going into detail here, Ill just include the code snippet for the web.config

  1. <?xml version="1.0" encoding="utf-8"?>
  2. <configuration>
  3.   <configSections>
  4.     <!– For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 –>
  5.     <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  6.   </configSections>
  7.   <appSettings>
  8.     <add key="aspnet:UseTaskFriendlySynchronizationContext" value="true" />
  9.   </appSettings>
  10.   <system.web>
  11.     <compilation debug="true" />
  12.   </system.web>
  13.   <!– When deploying the service library project, the content of the config file must be added to the host's
  14.   app.config file. System.Configuration does not support config files for libraries. –>
  15.   <system.serviceModel>
  16.     <services>
  17.       <service name="NorthwindServiceLib.EmpService">
  18.         <endpoint address="" binding="wsHttpBinding" bindingConfiguration=""
  19.           contract="NorthwindServiceLib.IEmpService">
  20.           <identity>
  21.             <dns value="localhost" />
  22.           </identity>
  23.         </endpoint>
  24.         <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />
  25.         <host>
  26.           <baseAddresses>
  27.             <add baseAddress="http://localhost:57757/Design_Time_Addresses/NorthwindServiceLib/Service1/" />
  28.           </baseAddresses>
  29.         </host>
  30.       </service>
  31.     </services>
  32.     <behaviors>
  33.       <serviceBehaviors>
  34.         <behavior>
  35.           <!– To avoid disclosing metadata information,
  36.           set the values below to false before deployment –>
  37.           <serviceMetadata httpGetEnabled="True" httpsGetEnabled="True" />
  38.           <!– To receive exception details in faults for debugging purposes,
  39.           set the value below to true.  Set to false before deployment
  40.           to avoid disclosing exception information –>
  41.           <serviceDebug includeExceptionDetailInFaults="False" />
  42.         </behavior>
  43.       </serviceBehaviors>
  44.     </behaviors>
  45.   </system.serviceModel>
  46.   <connectionStrings>
  47.     <add name="NorthwindEntities" connectionString="metadata=res://*/Northwind.csdl|res://*/Northwind.ssdl|res://*/Northwind.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=Farm1Server1ADSQL;initial catalog=Northwind;persist security info=True;user id=oDataSU1;password=P@ssword1;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
  48.   </connectionStrings>
  49.   <entityFramework>
  50.     <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
  51.       <parameters>
  52.         <parameter value="v11.0" />
  53.       </parameters>
  54.     </defaultConnectionFactory>
  55.   </entityFramework>
  56. </configuration>

Make this available in IIS

Now you can test this guy again using the Test Client, but the hosting into IIS Is quite simple and Ill just show you that.  All you have to do is either (1) Publish the site into a new Directory for PROD consumption (2) just copy and paste the folder where you want on a box running IIS and create a new Web App / Site pointing to that location and setting a FQDN and as in my case a port number where it can be consumed.

image

As you can see from the image above, I have a site with my FQDN port 57758 where it is located. Navigating to that site will yield

image

and clicking on the end point will show the WSDL

image

The Finale

Now we will use SharePoint Designer to Create an External Content Type (ECT) that will consume this WCF.

Open up SPD2013 and create a NEW ECT, Name it apprpriately, as Ive done here calling it BlogPostWCFExample, Next click on “External SYtems”  link to discover, its the last link on imabe below

image

Once you have done that click Add Connecation

image

Then Select WCF from the Drop Down…and click OK then

image

As a reminder we are using User Identity but i wanted to point out we just as easlily could be using Secure Store, if we were we would use the Target App ID as seen here

image

but for my example you will see it is more like this

image

When all of that is complete you will be able to see all the methods exposed, see below

image

Next you can right click on them individually and create the respective Methods, this has been done many times so i wont belabor it. But once you are complete you should have an ECT like so

image

and you should be able to create a List like so…

image

Summary

So, that is it for my Part 1 of 3, I will tackle the next one later tonight or tomorrow. Cheers, hope this helps, let me know your thoughts, critiques, etc.

 

October 14, 2013 Posted by | Business Connectivity Services, Public Speaking, SharePoint 2013, SharePoint Designer 2013, SharePoint How-To, WCF | , , , , | 2 Comments

« Previous Entries