Fabian Williams SharePoint Blog

Solving problems with SharePoint day and night

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

Advertisements

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

4 Comments »

  1. Fabian. I haven’t had the opportunity to get my hands on SP 2010 beta yet myself so your blog was especially excellent! Very detailed and quite exciting to see that ability. Very cool. Thank you.

    Rick

    Comment by Ricknology | November 9, 2009 | Reply

    • Thank you appreciate the feedback, more to come…

      Comment by fabiangwilliams | November 10, 2009 | Reply

  2. Excellent post. Thank you for using Office & SharePoint 2010.

    Comment by Gray Knowlton | November 10, 2009 | Reply

    • Thanks Gary, great getting the support of the community, keep coming back, I am crazy for BCS! lol

      Comment by fabiangwilliams | November 10, 2009 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: