Fabian Williams SharePoint Blog

Solving problems with SharePoint day and night

SQL Azure to EF5 to OData to BCS EL

Précis

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

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

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

How To

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

Create a ASP.NET Project

 

image

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

image

We will be targeting a Database called FabianNorthWind

image

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

image

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

image

Finally you will have your EF in your project

image

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

image

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

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

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

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

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

   4: // </copyright>

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

   6: using System;

   7: using System.Collections.Generic;

   8: using System.Data.Services;

   9: using System.Data.Services.Common;

  10: using System.Linq;

  11: using System.ServiceModel.Web;

  12: using System.Web;

  13:  

  14: namespace CPDemoAzureEFBCSEg

  15: {

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

  17:     {

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

  19:         public static void InitializeService(DataServiceConfiguration config)

  20:         {

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

  22:             // Examples:

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

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

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

  26:         }

  27:     }

  28: }

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

   1: namespace CPDemoAzureEFBCSEg

   2: {

   3:     public class NWindCustomers : DataService<FabianNorthwindEntities>

   4:     {

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

   6:         public static void InitializeService(DataServiceConfiguration config)

   7:         {

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

   9:             // Examples:

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

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

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

  13:         }

  14:     }

  15: }

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

image

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

image

Create a SharePoint App Project for your ECT

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

image

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

image

Follow through on the wizard till your project is added.

image

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

image

You provide the URI we have from the first project.

image

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

image

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

image

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

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

here is the ECT below

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

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

   3:   <LobSystems>

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

   5:       <Properties>

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

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

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

   9:       </Properties>

  10:       <AccessControlList>

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

  12:           <Right BdcRight="Edit" />

  13:           <Right BdcRight="Execute" />

  14:           <Right BdcRight="SelectableInClients" />

  15:           <Right BdcRight="SetPermissions" />

  16:         </AccessControlEntry>

  17:       </AccessControlList>

  18:       <LobSystemInstances>

  19:         <LobSystemInstance Name="CustDataFromAzure">

  20:           <Properties>

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

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

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

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

  25:           </Properties>

  26:         </LobSystemInstance>

  27:       </LobSystemInstances>

  28:       <Entities>

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

  30:           <Properties>

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

  32:           </Properties>

  33:           <AccessControlList>

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

  35:               <Right BdcRight="Edit" />

  36:               <Right BdcRight="Execute" />

  37:               <Right BdcRight="SelectableInClients" />

  38:               <Right BdcRight="SetPermissions" />

  39:             </AccessControlEntry>

  40:           </AccessControlList>

  41:           <Identifiers>

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

  43:           </Identifiers>

  44:           <Methods>

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

  46:               <Properties>

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

  48:               </Properties>

  49:               <AccessControlList>

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

  51:                   <Right BdcRight="Edit" />

  52:                   <Right BdcRight="Execute" />

  53:                   <Right BdcRight="SelectableInClients" />

  54:                   <Right BdcRight="SetPermissions" />

  55:                 </AccessControlEntry>

  56:               </AccessControlList>

  57:               <Parameters>

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

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

  60:                 </Parameter>

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

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

  63:                 </Parameter>

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

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

  66:                 </Parameter>

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

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

  69:                 </Parameter>

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

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

  72:                 </Parameter>

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

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

  75:                 </Parameter>

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

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

  78:                 </Parameter>

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

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

  81:                 </Parameter>

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

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

  84:                 </Parameter>

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

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

  87:                 </Parameter>

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

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

  90:                 </Parameter>

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

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

  93:                     <TypeDescriptors>

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

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

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

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

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

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

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

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

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

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

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

 105:                     </TypeDescriptors>

 106:                   </TypeDescriptor>

 107:                 </Parameter>

 108:               </Parameters>

 109:               <MethodInstances>

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

 111:                   <AccessControlList>

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

 113:                       <Right BdcRight="Edit" />

 114:                       <Right BdcRight="Execute" />

 115:                       <Right BdcRight="SelectableInClients" />

 116:                       <Right BdcRight="SetPermissions" />

 117:                     </AccessControlEntry>

 118:                   </AccessControlList>

 119:                 </MethodInstance>

 120:               </MethodInstances>

 121:             </Method>

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

 123:               <Properties>

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

 125:               </Properties>

 126:               <AccessControlList>

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

 128:                   <Right BdcRight="Edit" />

 129:                   <Right BdcRight="Execute" />

 130:                   <Right BdcRight="SelectableInClients" />

 131:                   <Right BdcRight="SetPermissions" />

 132:                 </AccessControlEntry>

 133:               </AccessControlList>

 134:               <Parameters>

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

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

 137:                 </Parameter>

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

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

 140:                     <TypeDescriptors>

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

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

 143:                         <Properties>

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

 145:                         </Properties>

 146:                       </TypeDescriptor>

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

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

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

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

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

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

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

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

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

 156:                     </TypeDescriptors>

 157:                   </TypeDescriptor>

 158:                 </Parameter>

 159:               </Parameters>

 160:               <MethodInstances>

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

 162:                   <AccessControlList>

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

 164:                       <Right BdcRight="Edit" />

 165:                       <Right BdcRight="Execute" />

 166:                       <Right BdcRight="SelectableInClients" />

 167:                       <Right BdcRight="SetPermissions" />

 168:                     </AccessControlEntry>

 169:                   </AccessControlList>

 170:                 </MethodInstance>

 171:               </MethodInstances>

 172:             </Method>

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

 174:               <Properties>

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

 176:               </Properties>

 177:               <AccessControlList>

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

 179:                   <Right BdcRight="Edit" />

 180:                   <Right BdcRight="Execute" />

 181:                   <Right BdcRight="SelectableInClients" />

 182:                   <Right BdcRight="SetPermissions" />

 183:                 </AccessControlEntry>

 184:               </AccessControlList>

 185:               <FilterDescriptors>

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

 187:               </FilterDescriptors>

 188:               <Parameters>

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

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

 191:                     <Properties>

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

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

 194:                     </Properties>

 195:                     <DefaultValues>

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

 197:                     </DefaultValues>

 198:                   </TypeDescriptor>

 199:                 </Parameter>

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

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

 202:                     <TypeDescriptors>

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

 204:                         <TypeDescriptors>

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

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

 207:                             <Properties>

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

 209:                             </Properties>

 210:                           </TypeDescriptor>

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

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

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

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

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

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

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

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

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

 220:                         </TypeDescriptors>

 221:                       </TypeDescriptor>

 222:                     </TypeDescriptors>

 223:                   </TypeDescriptor>

 224:                 </Parameter>

 225:               </Parameters>

 226:               <MethodInstances>

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

 228:                   <AccessControlList>

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

 230:                       <Right BdcRight="Edit" />

 231:                       <Right BdcRight="Execute" />

 232:                       <Right BdcRight="SelectableInClients" />

 233:                       <Right BdcRight="SetPermissions" />

 234:                     </AccessControlEntry>

 235:                   </AccessControlList>

 236:                 </MethodInstance>

 237:               </MethodInstances>

 238:             </Method>

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

 240:               <Properties>

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

 242:               </Properties>

 243:               <AccessControlList>

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

 245:                   <Right BdcRight="Edit" />

 246:                   <Right BdcRight="Execute" />

 247:                   <Right BdcRight="SelectableInClients" />

 248:                   <Right BdcRight="SetPermissions" />

 249:                 </AccessControlEntry>

 250:               </AccessControlList>

 251:               <Parameters>

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

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

 254:                 </Parameter>

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

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

 257:                 </Parameter>

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

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

 260:                 </Parameter>

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

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

 263:                 </Parameter>

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

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

 266:                 </Parameter>

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

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

 269:                 </Parameter>

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

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

 272:                 </Parameter>

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

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

 275:                 </Parameter>

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

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

 278:                 </Parameter>

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

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

 281:                 </Parameter>

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

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

 284:                 </Parameter>

 285:               </Parameters>

 286:               <MethodInstances>

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

 288:                   <AccessControlList>

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

 290:                       <Right BdcRight="Edit" />

 291:                       <Right BdcRight="Execute" />

 292:                       <Right BdcRight="SelectableInClients" />

 293:                       <Right BdcRight="SetPermissions" />

 294:                     </AccessControlEntry>

 295:                   </AccessControlList>

 296:                 </MethodInstance>

 297:               </MethodInstances>

 298:             </Method>

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

 300:               <Properties>

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

 302:               </Properties>

 303:               <AccessControlList>

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

 305:                   <Right BdcRight="Edit" />

 306:                   <Right BdcRight="Execute" />

 307:                   <Right BdcRight="SelectableInClients" />

 308:                   <Right BdcRight="SetPermissions" />

 309:                 </AccessControlEntry>

 310:               </AccessControlList>

 311:               <Parameters>

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

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

 314:                 </Parameter>

 315:               </Parameters>

 316:               <MethodInstances>

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

 318:                   <AccessControlList>

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

 320:                       <Right BdcRight="Edit" />

 321:                       <Right BdcRight="Execute" />

 322:                       <Right BdcRight="SelectableInClients" />

 323:                       <Right BdcRight="SetPermissions" />

 324:                     </AccessControlEntry>

 325:                   </AccessControlList>

 326:                 </MethodInstance>

 327:               </MethodInstances>

 328:             </Method>

 329:           </Methods>

 330:         </Entity>

 331:       </Entities>

 332:     </LobSystem>

 333:   </LobSystems>

 334: </Model>

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

image

Summary

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

Advertisements

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

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