Fabian Williams SharePoint Blog

Solving problems with SharePoint day and night

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

Advertisements

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

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

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

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

 

The Blog is broken up into sections

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

Part 1: Setup

clip_image001

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

clip_image002

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

clip_image003

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

clip_image004

Above:  Click Applications Management then Manage Service Applications

clip_image005

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

clip_image006

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

clip_image007

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

clip_image008

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

clip_image009

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

clip_image010

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

clip_image011

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

clip_image012

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

clip_image013

Above:  It processes once i click OK

clip_image014

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

clip_image015

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

clip_image016

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

clip_image017

Above: The default look of the Credential Mapping

clip_image018

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

Part 2: Validation and Testing

 

clip_image001[4]

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

clip_image002[4]

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

clip_image003[4]

Above:  We choose SQL from the list of choices

clip_image004[4]

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

clip_image005[4]

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

clip_image006[4]

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

clip_image007[4]

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

clip_image008[4]

Above: Validation that it is complete

clip_image009[4]

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

clip_image010[4]

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

clip_image011[4]

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

clip_image012[4]

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

clip_image013[4]

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

clip_image014[4]

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

 

Part 3: UAT

clip_image015[4]

Above: Logged on as Me…

clip_image016[4]

Above: Logged on as Hardeep

 

Conclusion

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

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