Fabian Williams SharePoint Blog

Solving problems with SharePoint day and night

No Code SharePoint BCS solutions with Workspace 2010 & Outlook 2010 & SPD 2010

Synopsis:

I have not done a blog in a while on SharePoint Business Connectivity Services (BCS) and as I prepare for my next SharePoint Saturday (Arabia), which will go into BCS from a SPD 2010 perspective I wanted to add some additional tid-bits on SharePoint workspace for people unfamiliar with it or its predecessor Microsoft Groove.

 

Below you will see screen shots for how to set up SharePoint Workspace 2010 (formerly Groove), and the integration to SharePoint Server 2010; further, I will demonstrate how you offline List/Libraries with both Workspace and Outlook to a SQL Server LOB system.

clip_image001

Above:  To create a new SharePoint Workspace link, you click “New”

clip_image002

Above:  There are a few options available to you here, in our example we will be doing a copy of SharePoint sites (offlining) to our local environment so we choose SharePoint Workspace

clip_image003

Above:  Enter the site that you are interested in Off-lining to Workspace

clip_image004

Above:  If you click “Configure” button you get the screen above which allows you to see exactly what is coming down to your Workspace… I haven’t played with modifying the default settings

clip_image005

Above:  Once you click “Ok”, you will have various site list and libraries that will sync up to your SharePoint Workspace; notice that the Site Pages are not supported.

clip_image006

Above:  Just as in Microsoft Outlook, as you connect to BCS External Content Types, you have to install a VSTO file on your local client (laptop/desktop) and this now will use BCSSync.exe to direct sync between your Rich Client and the LOB System.

clip_image007

Above:  this is the installation of the VSTO file

clip_image008

Above:  The VSTO file is successfully installed.

clip_image009

Above:  Same for additional list and libraries

clip_image010

Above:  ditto

clip_image011

Above:  ditto

clip_image012

Above:  When competed you see a check box to denote success and any warnings and errors.

clip_image013

Above:  Notice in the External List called “Store Sales” you have both a data view and individual view for the list. You are able to add, edit, and delete items in SharePoint workspace that will sync with SharePoint and the LOB system

clip_image014

Above:  As an example let us look at Elin Woods profile here, but we have renamed it Tiger for now…

clip_image015

Above:  We note that in the SharePoint browser UX we see the same thing as in Worspace

clip_image016

Above:  We make the edit to change the first name back to Elin…

clip_image017

Above:  We commit the change to the Workspace

clip_image018

Above:  We verify that the change is made as seen above….

clip_image019

Above:  Next we will connect Outlook to the same External List. To to this we will click on the “Connect to Outlook” icon in the SharePoint browser UX

clip_image020

Above:  When you do that, you will see the VSTO fill being installed on your local client

clip_image021

Above:  Once complete you now have access to the List in MS Outlook.

clip_image022

Above:  Verification phase… we are in SQL Management Studio looking at the LOB System

clip_image023

Above:  Next we will make a change to Fabian Williams Name

clip_image024

Above:  Save our Changes

clip_image025

Above:  Verify it in the Browser UX

clip_image026

Above:  Verify it in SharePoint Workspace

clip_image027

Above:  Verify it in the LOB System

Advertisements

March 20, 2010 Posted by | Office 2010, SharePoint 2010, SharePoint Designer 2010, SharePoint Development, SharePoint Workspace 2010 | , , , | 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

    Video How To: SharePoint Designer 2010 Beta create External Content Type consumed in SharePoint External List

    Screen cast on how to create an External Content Type in SharePoint Designer 2010 Beta and surface data in SharePoint External List.

    image

    November 26, 2009 Posted by | Business Connectivity Services, Office 2010 Beta, SharePoint Development, SharePoint How-To, Video Screencast | , , | Leave a comment

    SharePoint 2010 Business Connectivity Services Teaser with Multiple External Content Types surfacing in One External List

    This is my first attempt at a webcast with Camtasia bear with me…

    In this teaser I am setting the stage of what we have already built and what we will be doing… to download the Podcast version click here or directly on Screencast.com page .

     

    image

    November 26, 2009 Posted by | Office 2010 Beta, SharePoint 2010, SharePoint How-To, Visual Studio 2010, Webcast | , , | 4 Comments

    Consuming the External Content Type through SharePoint Designer in Microsoft Outlook 2010

    Follow up from: Connect To Outlook: SharePoint External Content Type with SharePoint Designer and SQL LOB System

    Part 2… Consuming the External Data List Columns inside Microsoft Word 2010 through Form Fields

    Synopsis

    In this blog we will demonstrate the following:

    • Reading and Updating LOB System Data surfaced as an Outlook Contact in MS Outlook 2010
    • Validating the effort through Testing

    Process

    We begin the process by picking up where we left off in our previous post Connect To Outlook: SharePoint External Content Type with SharePoint Designer and SQL LOB System Now we will just treat these contact as any other contact in our Outlook System.

    clip_image001

    In the screen shot above we notice on the middle Left Hand Side that we are attached to a SharePoint External List called “Test BCS Site Alpha – AW Company Contact” and we have 290 items returned to us. We will concentrate our efforts on the same Sir Michael Sullivan III we used earlier

    clip_image002

    So under closer inspection above we can see that not only do we have our data we mapped in the External Content Type mapping in SharePoint Designer but 2/3rds down you can see a pane called CompanyContactListDetails where the unmapped data is also present works almost like what Outlook Business Contact Manager gave us. So next I tried to get fancy “jiggy” with it by seeing if I could not only change the fields that were in the LOB system but also what was native as Outlook Properties… but no dice.. it said that the System didnt support it.. I will try to figure that out later..

    Yes I used @MeetDux Twitter Profile Pic.. LOL Im sure he will get a kick.

    clip_image003

    In the end I am just going to modify the Fields that are directly related to the External Content Type and LOB System. I made a change to the Email address and made him Michael.Sullivan@FabianWorld.com

    clip_image004

    As you can see below in Outlook… the changes were persisted…

    clip_image005

    As well as in the SharePoint UX .. see below and ….

    clip_image006

    As well as in the LOB System in SQL Server..

    clip_image007

    So now you have seen how you can make Updates in a Rich Client such as Outlook to affect changes in the LOB System all the way in the Back End.

    November 25, 2009 Posted by | Office 2010 Beta, SharePoint 2010, SharePoint Development, SharePoint How-To | , , | Leave a comment