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…
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…
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…
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)
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.
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.
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
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:
- 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
- Creating a new Data Connection
- click “Add Connection”
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.
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.
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
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.
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”
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 …
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
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.
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”
- 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
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!
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.
- Click Site Action
2. Click “More Create Options” because what we want to do here is create an “External List” which is under “Custom Lists”
3. Click External List
4. Define the Nomenclature for your list and here is where you will also select the External Content Type created earlier. Easy Peasy huh..
To get the Window Below just Click the far right Icon under the “Data Source Configuration” section by Entity Type…
Select your Entity Here… and Click OK
Once you have Clicked “OK”, Go ahead and take a deeeeep breath, Click Create and Voila! there you have it.
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
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”
Here is the Before below…
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.
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…
If i do my Select Statement… what do we have… YES! he is changed there also!
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.
Hope you enjoyed! More to come.. feedback welcomed