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

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

Come see my three session at SPLive360 in Orlando

What am I speaking on

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

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

SAVE MORE when your REGISTER by using PromoCode: SPLSP21

image

What to expect?

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

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

Emphasis on External Data

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

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

Takeaway

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

 

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

Enabling ReverToSelf on your BDC/BCS Service Application Instance

As i am getting ready to do a Preso at #DevConnections in Las Vegas in less than a month

ConnectionsEsignature_Speaking

I am sprucing up my Demo and to make sure I appease the Demo Gods I am removing the rooms for error by managing permissions to its least complex denominator.  As such I am going to run in the context of the BCS Service App Pool Account. In order to do that you will need to run a PowerShell command as it is disabled by default Out of Box (OOB), here is how you do it.

The How

In my case I am using the IDE and when you do that you need to make sure that you add the SharePoint Snap-In

Add-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue

then you will run the following script preferable already put in a ps1. file

   1: $bdcs = Get-SPServiceApplication | where {$_ -match "BDC"}

   2: $bdcs.RevertToSelfAllowed = $true

   3: $bdcs.Update()

 

Once you execute that badboy you should be ok

vlpebccb

 

Cheers!.

September 11, 2013 Posted by | Business Connectivity Services, SharePoint 2013 | | Leave a comment

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

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

How To: Using SPMetal Utility in SharePoint 2010 to “presumably” manipulate data on a BCS External List

Synopsis

So this blog started out with me trying to prove that can use LINQ to SharePoint via the SPMetal Utility in SharePoint 2010 to gain access to Business Connectivity Services (BCS) External Content Types // External Lists. In plain English, I wanted to use LINQ to SharePoint in as little code as possible and without using the BCS Template to do CRUD actions to External Lists created in SharePoint. I found out out that I was unable to using SharePoint 2010 BETA and Visual Studio 2010 Beta 2… basically SPMetal did not create the necessary Entity Classes for External Lists in the resultant C# file.

Therefore, I will turn this blog into a How-To for using the SPMetal Utility and point you to Serge Luca Blog for some great Web Cast on just how to use LINQ to SharePoint to access Lists in SharePoint 2010!

So the first thing you can do is bone up on SPMetal. You can read up on it at http://msdn.microsoft.com/en-us/library/ee538255(office.14).aspx or BING “SPMetal SharePoint”

clip_image001

Next I picked a SharePoint 2010 Site that had few clutter and still had List Created OOB that I could play with.

clip_image002

I settled on my “Charlie” Site

clip_image003

Next, I added an External Content Type to create an External List called “A Few People I Know” which resulted in the two screen shots below…

clip_image004

clip_image005

Then just to make sure I do my due diligence, I created two Custom Lists

  • One List that will do lookups to my external List but remains a custom list
  • One List that will just be a custom list with regular columns

clip_image006

So in my list called “Regular People I know List” I am doing lookups to my External Lists Data…

clip_image007

My Resulting List looks like below…

clip_image009

Then I created my RegularPeopleNOLookup List which is just a vanilla list with stuff in it.

clip_image010

Now Time for the FUN! Stuff, we get to crack open Visual Studio 2010 Beta 2 and Code Away!

Steps I took that led be to below..

  • Created a Console Application
  • Set the Build Type Platform to “Any CPU” since i am on a x64 bit system
  • Add References to
    • Microsoft.SharePoint
    • Microsoft.SharePoint.Linq

clip_image011

Next I will use SPMetal to create located in the “BIN” folder under the “14-Hive”, is that what we are calling it… I cant remember, but I know we were given the nomenclature in Vegas! 🙂

clip_image012

The syntax for the utility against my Charlie Site is below..

clip_image013

At this time now as you can see in my working folder, I have a file created called “CharlieSiteLists.cs” which is where the Entity Classes are located for the Lists in the “Charlie” site… My HOPE is/was that I will get my External List showing up too.. bummer it didn’t…

Below you have the “CharlieSiteListDataContext” which is basically your wrapper to the Content Database through LINQ

clip_image014

clip_image015

 

clip_image016

Below you can see the Entity Classes representative of the SharePoint List in the “Charlie” Site.

clip_image017

 

clip_image018

What helps us out here is that the Entity List Class implements IQueryable and IEnumerable which will help us out in our LINQ operations.

image

So here is where I will bail out…since I wasn’t able to get to my SharePoint –> Business Connectivity Services –> External Content Type –> External List that I created in my Lab. So Just to show you how it LINQ to SharePoint works see below how I add a data element to my List… MUCH better examples and walkthru are available on Serge Luca’s Blog.

clip_image019

clip_image020

Have Fun with That! Hopefully the story changes with External List when RTM comes out… that’d be really cool.

January 3, 2010 Posted by | Business Connectivity Services, SharePoint 2010, SharePoint Development, SharePoint How-To | , , | 5 Comments

Video How To: Create a Multi Master Relationship with SharePoint 2010 BCS External List using SharePoint Designer 2010

Synopsis:  In this video webcast I am doing the following:

  • Using multiple (two) disparate LOB Systems
    • Two Independent SQL Server Databases
    • Separate Security Model
    • No Explicit Relationship between data elements
  • Using two External Content Types in SPD 2010
  • Using two External List in SharePoint
  • Creating a Site Page to establish a Multi Master Page which will allow one to key off a data element and see detail information resulting from another LOB System.

Follow this link or click the image below: http://www.screencast.com/t/NzJiOWMzZDQ

MultiMasterScreenCastImage

Happy New Year… Stay tuned in 2010 for more SharePoint 2010 from Me! Come out and see me at SharePoint Saturday

  • Virginia Beach
  • Indianapolis
  • Boston

and at SharePoint Tech Con in San Francisco

December 31, 2009 Posted by | Business Connectivity Services, Office 2010 Beta, SharePoint 2010, SharePoint Development, SharePoint General, SharePoint How-To, Video Screencast, Webcast | , , , | 2 Comments

Come out and See Me [Fabian Williams] at SharePoint Saturday Virginia Beach on Jan 9 2010

Just Added as a speaker for SharePoint Saturday Virginia Beach. I am looking forward to sharing the podium with my distinguished SharePoint community experts and evangelists.  I will be presenting on my favorite topic… Business Connectivity Service and I promise… I will not run out of time this go around.

image

Come on out… details below

image

register at https://www.clicktoattend.com/invitation.aspx?code=143142 

And come and learn how “Composites” will save your LIFE!

December 6, 2009 Posted by | Business Connectivity Services, Office 2010 Beta, SharePoint 2010, SharePoint How-To, SharePoint Saturdays, Where is Fabian | , , | Leave a comment

SharePoint Saturday Washington DC – 12/5/2009 – Session on BCS

<Thanks>

First I want to thank Dux, Usher, and Gino for a well put together SharePoint Saturday http://www.sharepointsaturday.org/dc today in the DC Metro Area. Despite today being the first Snow Day of the season for the area, I am told we had over one hundred and eighty (180) attendees.  The speaker list was awesome and I tried to attend as much session I could (albeit i had my kiddies with me there today) and I was very impressed with everyone i saw.

</Thanks>

I was honored to be there and provide my session below

image

As requested by the attendees in my session, I have posted my deck for download on SlideShare the code may be accessed from the Blog https://fabiangwilliams.wordpress.com/2009/12/03/creating-a-sharepoint-2010-external-content-type-with-crud-methods-using-linq-and-a-sql-lob-system/

Hope you all had fun and learned something!

December 6, 2009 Posted by | Business Connectivity Services, Office 2010 Beta, SharePoint 2010, SharePoint How-To, SharePoint Saturdays, Where is Fabian | , , | 2 Comments

Creating a SharePoint 2010 External Content Type with CRUD Methods using LINQ and a SQL LOB System

Synopsis:

First, this lab builds on an article I saw on MSDN which discussed how to create an External List using Business Data. I noticed that they showed methods for the code but showed no foundation or a Use Case, nor did it discuss the LOB System and how the class entity relationship is defined.

In this Lab/Demo we will show a SQL LOB system which holds Personnel data. Imagine if you will, a system such as SAP, PeopleSoft, Dynamics, etc.  Imagine also that this LOB system is the Source of Record for your organization for things such as Human Resource related data which may be consumed by Active Directory, Sales, Marketing, Finance(Payroll) etc.  How can we surface that data in a Read, Create, Update and Delete fashion maintaining fidelity and ease of use?  here is how…

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

 

First

  • Check out your Database and pay special to the Columns which will be your fields in your Class Definition, also document your data types.
  • Create a Visual Studio Project using the Business Data Connectivity Model Template
  • Connect to your Database
  • Model the Entity
  • Use LINQ to create the methods to LOB Systems
  • Create the External List
  • Test and Verify

clip_image001

Above: First we need to have our database ready. Above we can infer that we will be going against a server called “Tico”, a Database called “FabianPlayPen” and a Table called “Employee” please take time to notice the current data records currently in there. Once you are done, crack open your Visual Studio 2010 Beta 2.

clip_image002

Note: To work with the Beta 2 bits of SharePoint 2010 you MUST use Visual Studio 2010 Beta 2 for the Templates to work.

clip_image003

Above:  Create a new Project of type “Business Data Connectivity Model” the guy you need for BCS.  I have my VS 2010 set for C# only but you can do this with VB.NET also. I have named my Project “SPSDCEmployees”; this is important because I will be using this name in conjunction with the Entity Class to be created later on in this demo/lab.

clip_image004

Above:  Once you have selected and named your Project you are prompted to choose the site for debugging. I have already created a site called “http://tico/sites/BCSAlpha” for this venture and i have so identified it in my dialog box.

clip_image005

Above:  Once the Project is created, the first order of business is to create a connection to our database.  We are doing this as the means to:

  1. Create a connection to our LOB System
  2. Use it through LINQ to SQL as our model to abstract our Class/Entity

image

Above:  Once you click on Add Connection you will be presented with the dialog box above, please indicate your Server Name and the Database you are interested in.  In my example my Server is my doggie’s name “Tico” and the Database is called “FabianPlayPen” very apropos wouldn’t you say?

image

Above:  After connecting your Database we need to include another  Template to our project. This time we are including a “LINQ to SQL” Template as a means to create our Entity

image

Above:  By default and design, when you create a BCS Project an Entity called “Entity1” will be created, shortly we will remove that entity because as part of that process two classes are also created which we will subsequently delete. Reason being, there is a lot of code already inplace that we will need tie into, by deleting the Entity Object and the related Classes and subsequently recreating our own, we have  better control over our build process. In our example we named our New Item “FabianPlayPen” and a file called FabianPlayPen.dbml is crated in our solution.

image

Above:  The blank design window is what you are initially presented with and is the framework where we will model our Entity Class.

image

Above:  I followed the steps below:

  • On the View menu, click Server Explorer.
  • In Server Explorer, expand the node that represents the Employee database, and then expand the Tables node.
  • Drag the Employee table onto the O/R Designer.

image

Above:  The newly created entity is now present in the OR designer nomenclature is the name of the table in the LOB System.  An entity class is created and appears on the design surface. The entity class has properties that map to the columns in the Employee table.  Now that that process is complete, we need to do some clean-up to aid our design process.

image

Above:  In Solution Explorer we dobule click on BdcModel1.bdcm and we get the desing pane above.

  • In Solution Explorer, expand the BdcModel1 node, and then double-click the BdcModel1.bdcm file.
  • The Business Data Connectivity model file opens in the BDC designer.
  • In the designer, right-click Entity1, and then click Delete.
  • In Solution Explorer, right-click Entity1.cs, and then click Delete.
  • Right-click Entity1Service.cs, and then click Delete.

as you see below…

image

Above and Below: The process to delete the associated .cs files.

image

—————————————————————————————–

image

Above: We will now create our new entity which will abstract our Employee LOB System table from our FabianPlayPen database.

  • On the View menu, click Toolbox.
  • From the BusinessDataConnectivity tab of the Toolbox, drag an Entity onto the BDC designer.
  • The new entity appears on the designer. Visual Studio adds a file to the project named EntityService.cs
  • On the View menu, click Properties Window.
  • In the Properties window, set Name to Employee
  • On the designer, right-click the entity, click Add, and then click Identifier.
  • A new identifier appears on the entity.
  • In the Properties window, change the name of the identifier to EmployeeID

See Below

image

imageimage

Below is a representation of our work so far, it includes the newly created Entity and our Identifier. Next we will create our Methods to Create, Read, Update and Delete.

image

Above:  We will begin the process to create a Finder Method. This method is used to basically surface a List of “ALL” items in the database

  1. On the BDC designer, select the Employee entity.
  2. On the View menu, click Other Windows, and then click BDC Method Details.
  3. In the BDC Method Details window, from the Add a Method drop-down list, select Create Finder Method.

image

  1. Visual Studio adds a method, a return parameter, and a type descriptor.
  2. In the BDC Method Details window, click the drop-down list that appears for the EmployeeList type descriptor, and then click Edit as seen below

image

  1. The BDC Explorer opens. The BDC Explorer provides a hierarchical view of the model.
  2. In the Properties window, set the Type Name property to System.Collections.Generic.IEnumerable`1[SPSDCEmployees.Employee, BdcModel1] as seen below

image

image

  • In the BDC Explorer, expand the EmployeeList node and select the Contact node.
  • In the Properties window, set the Type Name property to SPSDCEmployees.Employee, BdcModel1.
  • In the BDC Explorer, right-click the Employee, and then click Add Type Descriptor.
  • A new type descriptor named TypeDescriptor1 appears in the BDC Explorer.
  • In the Properties window, set the Name property to EmployeeID.
  • Set the Type Name property to System.Int32.
  • From the Identifier drop-down list, select ContactID.
  • Repeat step 8 to create a type descriptor for each of the following fieldsAs seen in the below 3 ScreenShots.

    image

    Screen Shot 1

    image

    Screen Shot 2

    image

    Screen Shot 3

    Now we need to create the remainder of the Type Descriptors for the Employee Entity; here is where we go back to our LOB System or our Entity Class created earlier and get the Names and Data Type of the columns to create our TypeDescriptors. Once we have done that it is time to add the code for our  ReadList Method

    image

    Above:  Double-Click on the ReadList in the Employee Entity to enter the code view as seen below.  By default a method is created and we need to remove the entry in the method and replace with our own.

    image

    image

    Above:  Our code is inserted in the ReadList Method

    • A connection string is created to attach to our LOB System
    • An Instance of the Employee List is created and populated with all the data from the LOB System

    That it. Easy isnt it! Next we need to create a Specific Finder Method which is responsible for returning a single item as requested in the SharePoint UX. As before we go back to the BCS Designer to add the Method and add our code… see below next three screen shots.

    image

    image

    image

    1. In the BDC designer, select the Employee entity.
    2. In the BDC Method Details window, collapse the ReadList node.
    3. From the Add a Method drop-down list that appears below the ReadList method, select Create Specific Finder Method.
    4. Visual Studio adds the following elements to the model. These elements appear in the BDC Method Details window.
      1. A method named ReadItem.
      2. An input parameter for the method.
      3. A return parameter for the method.
      4. A type descriptor for each parameter.
      5. A method instance for the method.
    5. In the BDC designer, on the Employee entity, double-click the Readitem method and add the code you see in the second (2nd) screen shot.

    image

    image

    We will create two additional method Create and Update.. lets go with Create first then the Update Method.  As before we begin from the Entity and select new method from the BDC Method Details Pane. Click the Method and add the code as reflected the screen shot.

    image

    image

    image

    image

    image

    Next we create the Update Method

    image

    image

    TEST AND VERIFY

    Finally we get to deploy our solution and see it in action. Click build then Deploy Solution to get our code up to our Farm.

    image

    Above:  Click Build then Deploy Solution…

    image

    Above:  To verify that the solution is uploaded we go to Central Admin under Application Management, Click on Business Data Connectivity to see if the Build was uploaded to the server Farm, see below…

    image

    Above:  Evidenced that our External Content Type is on the Server Farm, we now move to creating our External List to surface the information abstracted by the External Content Type.

    image

    Above:  Under Site Actions, click Create More, then choose External List.

    image

    Above:  Select the External Content Type we created as above…

    image

    Above:  Create a name and ensure that we have the correct Data Source Configuration

    image

    Above:  So… Viola! we have our external content type representative of the SQL Database Table called Employee, neat huh!

    image

    Above:  as part of our verification process we have confirmation that our columns are consistent to the Entity and LOB System. Now lets kick the tires on the newly created methods..

    image

    Above:  Lets click “Add New Item” this will instantiate and fire off the Create Method of our External Content Type. The next four screen shots represent the new form, the entered values, the post and final resultant Read List with the created item.

    image

    Above: The blank auto-generate form when you click Add New Item

    image

    Above:  Filling in the form with what will be the new data

    image

    Above:  Awesomness! we have our newly created item in our List… later we will do a SQL query to validate the record. Next, let us investigate the Update Method…

    image

    Above:  In this example we click on the custom action by the Picker field and we elect the “Edit Item” Once we click it we see the below…

    image

    Above:  The item in question is noted in the Update Form… I am picking  on my buddy Todd Baginski here, he does and awesome work on BCS and other SharePoint 2010 elements. Visit him here

    image

    Above:  Well because his name is synonymous to  good ol Frodo I relocated Todd to the Shire…

    image

    Above:  Confirm the changes above…

    image

    Above:  Finally we check the LOB system and we can see our Create, and Our Update, come to the SPS DC for the Delete… LOL

  • December 3, 2009 Posted by | Business Connectivity Services, SharePoint 2010, SharePoint Development, SharePoint General, SharePoint How-To, SharePoint Saturdays, Visual Studio 2010 | , , | 50 Comments