Business Connectivity Tutorial with SQL Server (AdventureWorks DB)

Business Connectivity Services (BCS) Primer

Overview: The BCS is actually a platform that provides a SharePoint-based framework for creating composite applications, which are built by combining services and functionality from other enterprise applications to address the collaborative needs of users. These composite applications use the Office client user interface in addition to the functionality of the application. This helps people perform their tasks within the familiar user interface of the Office and SharePoint Server systems, and allows people to take actions and make decisions within the context of the problem at hand.

Prerequisites:  To make this demo work, you will need to have the AdventureWorks sample database for SQL Server installed.  These database samples can be downloaded from CodePlex on this link.  Instructions for installing the sample databases can be seen here.

First check if you have the Business Connectivity Service running in SharePoint Central Administration.	Open SharePoint Central Administration. Click Application Management in the Quick Launch. In the Service Applications section select the Manage Service Applications hyperlink. Scroll down to locate the Business Connectivity Service. Look at the Status column on the right to see if the service is started.
An external content type exposes data from an external data source, like a SQL server table, in SharePoint. You can create an external content type using SharePoint Designer 2010. This involves no code at all. In this demo you are going to expose the Contacts table from the AdventureWorks SQL Server database into SharePoint.	Open SharePoint Designer 2010. In the left pane choose the External Content Types collection. It takes a while for the designer to collect all necessary data. Click the External Content Type button in the New group on the ribbon. SharePoint Designer presents you a form where you have to fill out some information before the external content type can be created. Click on the New External Content Type hyperlink next to Name. Give your content type a name like Contacts. This name will automatically be taken over for the display name but you can change it if you want. Set the namespace to Contoso. Select Contact from the dropdown next to Office Item Type. You can also decide whether the list can be taken offline or not. You can work with your SharePoint site offline using the SharePoint Workspace 2010 application, which is part of the Office client tools.
The next step is to define the data source to which you want to connect.	Find the panel External Content Type Operations right under the External Content Type Information panel. Click the hyperlink at the bottom of the panel. Click the Add Connection button. Choose SQL Server from the popup. Fill out data following data: -          Database server: DEMO2010A -          Database name: AdventureWorks -          Connect with the user’s identity. Click the OK button.
When the data source is defined you can start with the definition of the external content type. One of the things you will have to do is define the methods that will be associated with the external content type.	Expand the AdventureWorks treeview. Notice that you can choose between the different tables, views and routines. Right-click the Contact table and notice all the different methods that can be associated with the external content type.
	Choose Create All Operations. This will create all the Finds, Updates and Delete methods. A wizard opens where you will have to answer some questions. Click the Next button. In the next screen you will be able to define the mappings between the columns of the SQL Server table and the fields of the Contact table type. At the bottom you see a number of errors and warnings you need to address before you will be able to create the operations. Select the LastName column of the SQL Server table and map it to the LastName column of the Contact table. Check the Show in Picker check box. In the Office Property dropdown it is indicated that a custom property will be created. Choose LastName from the dropdown. Select the rowguid column of the SQL Server table. Make sure the Required check box is unchecked. You can uncheck all other columns that you don’t want to show up in your external content type. Leave all other columns as is. Click the Next button.
In the next screen of the wizard you are able to define a filter to avoid that too many data is returned to SharePoint.	Click the Add Filter Parameter button. A new filter parameter is created. Select the (Click to Add) hyperlink on the right of the screen. A dialog opens. Choose Limit from the Filter Type dropdown and leave ContactID selected in the Filter Field dropdown. Click the OK button. Right under the filter in the Properties pane, set the Default Value to 100. Click the Finish button to accept the changes. Click the Save button to save the changes. It takes some time before the external content type is created. On the ribbon you can see that you can do different things with this new external content type: you can edit the different operations, but you can also create a list and its forms for this external content type, and create a profile page.
In the Business Connectivity Service you can check the external content types that are created. If you want to create profile pages for your external content types, you have to change a configuration setting of the Business Connectivity Service here.	Return to SharePoint Central Administration. Click Application Management in the Quick Launch. In the Service Applications section select the Manage Service Applications hyperlink. Scroll down to locate and select the Business Connectivity Service. You can see that the Contacts external content type is there. Select the Edit button from the ribbon. Select the Configure button. The only thing to do in the wizard is to enter the URL to your SharePoint site, which is http://intranet.contoso.com in this demo. Now you can select one of the external content types listed and choose Create/Upgrade Profile Page. This means that you can create profile pages from within Central Administration or in SharePoint Designer 2010.
Now you are going to create a list based on the external content type you just created.	Open an internet browser and navigate to your SharePoint site. Choose More Options from the Site Actions menu. Select the Lists tab on the right. Pick the External List template from the wizard and click the Create button. This brings you to a familiar creation page where you can fill out the name of the new list. Give your list a name, like f.e. ECT Contacts. In the Data source configuration section select the right picker button. The External Content Type Picker opens. Choose your content type from the list and click the OK button. Click the Create button to create the list.
The list created from the external content type is just like any other SharePoint list. Only the data is not coming from SharePoint but from an external data source, like a SQL Server database table or view. In SharePoint Designer you can modify the different forms if needed.	When the Contacts list is created, it opens in the All Items view. You can do all the things you can do with a native SharePoint list: select items, view items, edit items, define views,…
In SharePoint 2010 making changes to external data is much easier than in SharePoint 2007.	Select an item and choose Edit. Change the phone number and save your changes. Notice your change in the All Items view. But your changes are also saved back to the Contacts table in the AdventureWorks database.