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

March 20, 2010 Posted by | Office 2010, SharePoint 2010, SharePoint Designer 2010, SharePoint Development, SharePoint Workspace 2010 | , , , | Leave a comment

The New Face of Visual Studio 2010 Ultimate Beta 2

Need i say more…

image

November 12, 2009 Posted by | SharePoint 2010, SharePoint Development, SharePoint How-To, Visual Studio 2010 | , , , | Leave a comment

Using SharePoint Designer 2010 to surface data in External Lists through Business Connectivity Services SharePoint 2010

 

Part 1: Surfacing Data through SharePoint Designer – No CODE! yes, i said it, NO CODE!

Part 2: Surfacing the same Data through Visual Studio – Yes we will crack the seal and Code Away

Part 3: Consuming the Surfaced Data in Outlook Applications and other User Interfaces

 

In this blog I will demonstrate in my opinion “one of” if not “THE MOST” value added component in SharePoint 2010; accessing and surfacing data in Line of Business (LOB) systems with the ability to Read, Write, and Delete in Real-Time. 

So to accomplish this the first thing I will do is create a new Site using the Empty Site Template, Ill dub it BCS Example Alpha. Dont fault me for that name, fault the Military…

image

Once I have completed that process, which is almost the same as you do in MOSS 2007. See my previous post on how to create sites at Creating and Configuring your Sites in SharePoint 2010 – Service Applications and Web Applications Part 1 of 2 for a step by step process on how to accomplish that.  Anywho… once you have completed that process you will then use SharePoint Designer 2010 to open your new site. <aside> so I used to be the one ranting about how SPD 2007 was just FrontPage on steroids and I would only use it to design pages and nothing else, now I am eating crow </aside>

There is sooooo much you can do now in SPD 2010 especially extending solutions build in Visual Studio and writing back to the same .WSP file yep, same DLL. Isnt that freakn awesome. So back to where were were…

 

clip_image001

Above you will notice the Site “BCS Example Alpha” is now opened in SPD2010 and you have details on the site to include:

  • Basic Site Information
  • Site Permissions
  • Any Customizations
  • Any Sub-Sites
  • Basic Settings

Next, we need to create an External Content Type, we do this by clicking on the “Entities” link in the Navigation Pane which will give you an option in the Ribbon called “External Content Type” under the ‘New’ heading. Go ahead and click that bad boy…

clip_image002

Once you have clicked new “External Content Type”, you will be presented with the window below.  Basically, what you are doing here is defining the arms around what was previously your Application Definition File… thank GOD, no more XML [for now], but you are defining your:

  • Entity Nomenclature
  • Data Source
  • Version of your Content Type (app def file)
  • Permissions
  • etc…

 

clip_image003

Here is a good time for us to look a little closer to our Data Source, after all that is what we are going against right. So we are jumping to SQL Management Studio 2008 and looking at the AdventureWorks2008 Database for x64bit systems; you can get it from http://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=34032 and install it.  So… what I am showing you here is the Table that i am going after and also pulling some data so you can see it isnt SharePoint magic. NB. Pay attention to Item 3, we are gonna steal his identity in a bit.

clip_image004

I am also going to create a View on this guy also because more than likely you are not going to want to have direct access to your underlying table [right, right..] so I created a view aptly called “PersonViewforBDC’ and again you can see the output in my Query Window.

clip_image035

So now we are back to SharePoint Designer 2010 and we are going to name our Entity and create our External Content Type [previously our App Def File] with NO … say it.. No Code… No XML

clip_image005

Above we are calling naming the Display Name, Programmatic Name, and leaving the rest to the default; which in and of itself is no short order, we are saying here we want:

  1. Offline Sync for the List

Which is awesome! Take you data go off to the client, manipulate it and come back and sync.. hmmm Windows Mobile, Laptop, etc.. sky is the limit here

In the next few steps here is what we are doing

  1. Creating a new Data Connection
    1. click “Add Connection”

 

clip_image006

2. Define the type of Connection, in this instance I am electing to choose a “SQL Server” connection but the sky is the limit here also.

clip_image007

3. Provide the Server Name, Database Name, and an Optional Name, which is what shows up in the “Data Source Configuration”, “Entity Type” section when you are creating your External List in your Consuming Site.

clip_image009

4. After you click Ok, you go through a validation step as your Entity connects to the Database and Creates your Data Source for SPD 2010

clip_image010

5. Once that process has complete, you will see the window below which shows you your Database Connection, All Tables and Views that you have permission to see and manipulate.

clip_image011

6. As I am a Database person at heart also, and although it may seem weird, yes, I created a View for this demo, and it is that view that we are going to target or data to surface. So go ahead and Click on your Table or View. In my example I am using my view called “PersonViewforBDC”

clip_image030

7. Now here is where I earned new respect for SharePoint Designer 2010; you can just now “Right Click” on the Table or View and select from a myriad of options [Methods] to create. In this example I am going for the fences. So, I clicked on “Create All Basic Operations” and …

clip_image031

8.  Next you will follow a 3 dialog Wizard which allows you to again exercise some control over the data you are exposing. You may at this point

  • Define the Properties – Nomenclature
  • Apply Parameters – Define what will be the Picker i.e. the item in your SharePoint List that will trigger your custom actions or jump you to the details of the row
  • Apply Filter Parameters – Narrow your Result Set

clip_image032

clip_image033

9. Once you are finished, Click “Finish” and after a few seconds, you will see the below Status Message and Eventually your Entity Created. Make sure to Save your Content Type by Clicking the Save Icon at the top LH corner of SPD 2010, regardless if you don’t, you will be prompted to save your work anyway.

clip_image018

clip_image019

10. Yep, you are done… that easy..

Although I believe it worked, you may be a bit skeptical. So for this demo, I will go to Central Administration and investigate if my BDC/ BCS entity was created. So first thing you have to do is:

  • Under Application Management
    • Click “Manage Service Applications”

clip_image020

  • Because this is a Business Connectivity Services (still called BDC in this release but when RTM comes out I will bet you $1 it will change)
    • Click “Business Data Catalog” and click “Manage” in the Ribbon

clip_image021

clip_image022

Once you click on the Ribbon you will be taken to the browser below where you can see all the External Content Types that are hosted on that Server Farm. Below you have the hard work we completed in SharePoint Designer 2010 with … say it… No Code!

clip_image023

The Moment of truth, what you paid your Money For!

Now let us consume that bad boy; so now we go back to our Site we created and we are going to do some really simple steps. 

  1. Click Site Action

clip_image024

2. Click “More Create Options” because what we want to do here is create an “External List” which is under “Custom Lists”

clip_image025

3. Click External List

clip_image026

4. Define the Nomenclature for your list and here is where you will also select the External Content Type created earlier.  Easy Peasy huh..

clip_image027

To get the Window Below just Click the far right Icon under the “Data Source Configuration” section by Entity Type…

clip_image028

Select your Entity Here… and Click OK

clip_image029

Once you have Clicked “OK”, Go ahead and take a deeeeep breath, Click Create and Voila! there you have it.

clip_image034

Your External List is now created in SharePoint and it is a “Dynamic” representation of your LOB system Data in Real time… but wait… I did say we can perform Live Updates didn’t I?

Lets get our sanity check first….”Trust, but Verify”! So here is my SQL Query where I am looking at my data source… pay close attention to List Item 3 Mr. Roberto Tamburello

clip_image035

In SharePoint, in my newly created External List under my Picker I choose from when I defined my Content Type, I clicked on my down arrow and select “Edit”

clip_image036

Here is the Before below…

clip_image037

I am modding this guy to the following:

  • Making him a Knight
  • Giving him my name as his Middle name
  • and making him a Junior

Poor guy, just cant get a break.

clip_image038

Once I click “OK” notice that in the SharePoint External List, it is already represented as a changed item…. but what about good old LOB System SQL…

clip_image039

If i do my Select Statement… what do we have… YES! he is changed there also!

clip_image040

So in summary, without a stitch of code. We were able to surface data from a LOB System, bring it into SharePoint, manipulate it and Persist it back to the Original Source.

clip_image041

Hope you enjoyed! More to come.. feedback welcomed

November 9, 2009 Posted by | Office 2010 TP, SharePoint 2010, SharePoint Development, SharePoint How-To, SQL Server | , , , , | 4 Comments