We’ve seen from an earlier post how to create a Business Connectivity Services External Content Type using Visual Studio 2010. Now we’re going to take a look at how to get an External Content Type created within SharePoint Designer 2010. SharePoint Designer as a tool has undergone a ton of work by Microsoft and from our early impressions of using it for general non-BCS type stuff this has really paid off in a much more useable – and useful – application. SharePoint Designer 2010 can now also be used to setup the BCS to pull data from your External System such as SQL Server or a web service. The actual connection types available within the tool are:
SQL Server
WCF
.Net Assembly
Over the course of the next few weeks we’ll cover creating an External Content Type with each of these data sources. This first SPD related post though will concentrate on SQL Server. We’re going to connect to the AdventureWorks2000 sample database to create a new External Content Type, here are the steps:
1, Open up SharePoint Designer 2010 and click the big button to ‘Open Site’
[click images to view full size]
2, Enter the URL of your SharePoint site and press enter. SharePoint Designer will now connect and bring back the relevant information about that site. Below you can see the nice site Dashboard view that gives you a quick overview of your SharePoint site
3, Down the left side menu you’ll see the various SharePoint objects you can work with via SharePoint Designer, and the new object type we are interested in is External Content Types. When you click this menu item you’ll get a view of the other External Content Types deployed and also the Ribbon menu at the top of SharePoint Designer will change to reflect the work we can do with it.
4, In the Ribbon, click to create a new External Content Type
5, Upon clicking this button you’ll be presented with the screen to edit and manage your External Content Type.
There are a number of labels that look like Hyperlinks that you can click to set their value. The first two are Name and Display Name. Set these values to be:
Name: Products
Display Name: Products
The Office Item Type will allow you to map your External Content Type to commonly used Microsoft Office objects such as Contacts or Calendar events. We’ll have a blog post on how to utilize these shortly.
For now we just need to choose an external data source to connect to. Click on the link ‘Click here to discover external data sources and define operations’
6, Clicking the link above opens up the Operation Designer view. Click on the button to Add Connection
7, Choose the data source type to be SQL Server and enter in your connection details for where AdventureWorks2000 database is located. Upon clicking on OK the Data Source Explorer will load with your database and the tables you can utilize. Right click on the Products table and choose ‘Create All Operations’
This will spawn a new form that will allow you to configure the operations you can do through the BCS on your Product table. Click Next
8, The first screen you see is the Parameters Configuration screen
Here you can configure the fields your External Content Type will have. If you are using a table with a Primary Key field it will configure the Identifiers for you.
In the Business Data Catalog you had to set the ShowInPicker property for the columns you wanted to appear in the Data Column picker control. This was annoying as many people didn’t realize this and got frustrated why the picker only displayed a list of identifier values. As you’ll see in the Errors and Warnings section of this form if you don’t set the ShowInPicker property then be default all the columns will be displayed – a big improvement I think. Click on Next.
9, The next screen allows you to configure any Filters for your External Content Type. This is particularly useful to return a subset of data to users to make it easier to select the data they want to work with.
For now though we won’t setup any filters so just press the Finish button.
10, Upon clicking finish you’ll see the list of operations created by SharePoint Designer for our External Content Type
11, Our External Content Type is now setup, but nothing has actually been deployed to SharePoint itself. Click on the Save File icon in the top left of SharePoint Designer and it will package it up and save it to the External Content Type Store.
12, Now lets try creating an External List to display our data. External Lists are new to SharePoint 2010 – they allow you to use your External Data within what looks to your SharePoint users as a normal list. If the correct CRUD operations have been setup they will also be able to interact with the data through an External List the same way they can with a normal SharePoint list by editing list items or adding new rows of data. We’ll be digging into External Lists in a lot more detail in a later blog post.
To create an External List for our content type we can either go to the Lists View in SharePoint Designer and click the New External Lists button or create it directly from the External Content Type itself. We’ll do the second option now, so click on the External Content Types left sidebar menu item to bring up the External Content Types listing. Right click on our Products External Content Type and choose to Create External List
Give the list a name of Products and click OK – this will now go and create our External List within SharePoint and hook it up to our Product External Content Type. You can view this by navigating to SharePoint and seeing the Products list on the left hand menu:
13, Clicking on the list brings back all our products.
14, Try editing a line of data. To do this hover your mouse over an list item and use the drop down menu to select the Edit option
15, A nice Dialog Form will pop up to allow us to edit our data
Try changing the name of the product – you’ll see I’ve changed mine to ‘Bearing Ball Hello’ – click the save button and the dialog form will close and show the list updated.
16, The important thing though, has it updated SQL Server for us?! 🙂
Open up SQL Server in Management Studio and check your Products table, you’ll see from below that our data has been successfully updated!
Woohoo!
Hopefully this blog post has given you a teaser of what can be done with SharePoint Designer to create External Content Types. It works very well for simple tables that you want to base your External Content Type off. We’ll have many more blog posts coming in the near future to explain lots more about the BCS+SharePoint Designer so make sure you subscribe to the RSS feed! 🙂
