Contents

Overview
Verify Web Application settings
Configure Business Data Connectivity service
Create Stored Procedure
Create External Content Type
Verify the Content Type in the Central Admin
Use Business Data List web part to show external data
Connecting Business Data List web part with other web parts
Additional Resources

Overview

Business Connectivity Services are a set of services and features that provide a way to connect SharePoint solutions to sources of external data and to define external content types that are based on that external data. This article contains a step by step guide to design an External Content Type from SQL stored procedures. The article will broadly cover the below topics,
  • Define stored procedures to be used as a base for the external content type.
  • Create External Content Type from the stored procedures. Add a "Read List" operation and show use of filters.
  • Create a custom page to show a list of items from the content type. Show use of "Business Data List" web part and filters.
  • Customizing the Business Data List web part by connecting it to custom Filter web parts.
Before beginning, you should have the following:
  • Access to SharePoint 2010 Central Administration, including the business data connectivity service.
  • Access to SharePoint Designer.
  • A SharePoint 2010 web application with a site collection (blank site template). For more information, seeCreate a site collection (Office SharePoint Server). We will be using the Sharepoint - 80 application for these examples.
  • Access to Microsoft SQL Server. For our examples we will consider that the SQL server is installed locally.­
  • A sample database for Microsoft SQL Server ("AdventureWorks2008R2" will be used in this example. SeeSample Databases for Microsoft SQL Server 2008R2 to install.)
The objective of this exercise is to write a Stored Procedure that returns a list of Sales Orders from the AdventureWorks database and display these results by using the Business Data List web part. We will also see how we can use various filters (Sales Territory and Sales Year for our example) to limit the result set returned by the stored procedure.

Verify Web Application settings


Follow these steps to verify that the web application that will be used to display the external data is properly configured to use the Business Data Connectivity service.
1. Start SharePoint 2010 Central Administration.
2. Click Manage web applications under the Application Management heading.
3. Select the web application you will be using for these examples (e.g. Sharepoint - 80).
4. Click Service Connections in the ribbon.


5. You need to have checks by Business Data Connectivity Service. If it is not checked, check it and click OK. If you cannot check them, use the drop down at the top to change from default to custom first.


Configure Business Data Connectivity service


Follow these steps to verify the settings for Business Data Connectivity service.
1. Start SharePoint 2010 Central Administration.
2. Click Manage service applications under the Application Management heading.
3. In the Name column, click on Business Data Connectivity Service. Then click on Manage in the ribbon.

4. On the edit page, click Set Metadata Store Permissions in the Permissions group in the ribbon.
5. If the account you will be using is not shown in the dialog, type the account name in the text box next to the Addbutton. Then click Add.
6. In the bottom list, check all of the check boxes (Edit, Execute, Selectable In Clients and Set Permissions). In this example we have given all permissions to NT Authority/Authenticated Users.
7. Check the box for Propagate permissions to all BDC Models, External Systems and External Content Types in the BDC Metadata Store. Then click OK.
 


Create Stored Procedure


In this step, we will be using the 'AdventureWorks2008R2' database to create a stored procedure that takes two optional parameters (Status and Territory) and returns a list of Sales Orders. The code to create the procedure is below.



USE [AdventureWorks2008R2]
GO

/****** Object:  StoredProcedure [dbo].GetSalesOrders]    *****/
IF  EXISTS (SELECT * FROM sys.objects WHERE
       object_id = OBJECT_ID(N'[dbo].[GetSalesOrders]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetSalesOrders]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetSalesOrders]
       @territory nvarchar(200) = null,
       @year int = 2008,
       @pageNo int = 1,
       @limit int = 200
AS
BEGIN
       SET NOCOUNT ON;       DECLARE @startIndex int
       SET @startIndex = ((@pageNo - 1) * @limit) + 1

       DECLARE @endIndex int
       SET @endIndex = @startIndex + @limit - 1

       DECLARE @salesTerritory nvarchar(200) = null
       IF @territory IS NOT null
              SET @salesTerritory = '%' + @territory + '%'

       ;
       WITH [CTE] AS (
              SELECT ROW_NUMBER() OVER (ORDER BY [OrderDate] DESC) AS [RowNumber]
                     ,[SalesOrderNumber]
                     ,[OrderDate]
                     ,[DueDate]
                     ,[ShipDate]
                     ,[dbo].[ufnGetSalesOrderstatusText]([Status]) AS [Status]
                     ,[SO].[CustomerID]
                     ,[CUST].[LastName] + ', ' + [CUST].[FirstName] + ' '
                           + ISNULL([CUST].[MiddleName], '') AS [Customer]
                     ,[ST].[Name] AS [SalesTerritory]
                     ,[SubTotal]
                     ,[TaxAmt]
                     ,[Freight]
                     ,[TotalDue]
              FROM [AdventureWorks2008R2].[Sales].[SalesOrderHeader] AS [SO]
              INNER JOIN [AdventureWorks2008R2].[Sales].[Customer] AS [SC]
                     ON [SO].[CustomerID] = [SC].[CustomerID]
              LEFT JOIN [AdventureWorks2008R2].[Person].[Person] AS [CUST]
                     ON [SC].[PersonID] = [CUST].[BusinessEntityID]
              LEFT JOIN [AdventureWorks2008R2].[Sales].[SalesTerritory] AS [ST]
                     ON [SO].[TerritoryID] = [ST].[TerritoryID]
              WHERE DATEPART(year, [SO].[OrderDate]) = @year
                     AND [ST].[Name] LIKE ISNULL(@salesTerritory, [ST].[Name])
       )

       SELECT * FROM [CTE]
       WHERE [CTE].[RowNumber] >= @startIndex
              AND [CTE].[RowNumber] <= @endIndex
       ;
END
GO

Create External Content Type

In this step, we will use SharePoint Designer to create an External Content Type from the stored procedure created in the previous step.
1. Browse to the "SharePoint - 80" site in your development environment.
2. On the Site Actions drop-down, click Edit in SharePoint Designer.
3. In the Navigation pane, click External Content Types.
4. In the New group of the ribbon, click External Content Type.
5. In the External Content Type Information group, edit each of the parameters as below and leave the remaining to their default settings.



PropertyValue
NameSalesOrders
Display NameSalesOrders
Namespacehttp://contoso.com
Version1.0.0.0
 6. Next to External System, click Click here to discover external data sources and...

 7. Click on the Add Connection button.
8. In the External Data Source Type Selection dialog, choose SQL Server in the drop down. Click OK.
 
9. In the SQL Server Connection dialog, edit the parameters as below and click OK.



PropertyValue
Database Server(local)
Database NameAdventureWorks2008R2
NameAdventureWorks2008R2_SalesOrders
 
 Note: This connection will use the service account to connect to the database. In a production configuration, you may want to use one of the impersonated identities so that database access is controlled independently from that account.
10. In the Data Source explorer, expand the AdventureWorks2008R2_SalesOrders to find the stored procedureGetSalesOrders under the Routines node.
 
11. Right-click on GetSalesOrders to open a pop-up menu and click New Read List Operation.
12. Click the Next > button at the bottom of the Read List dialog.
13. On the Input Parameters page, click @territory in the Data Source Elements pane.
14. In the Properties pane, edit the values as below. Then, click Click to Add against the Filter property.



PropertyValue
Display NameSales Territory
Default Value<<Null>>
 
15. In the Filter Configuration dialog, select New Filter. Edit the properties as below leaving the rest to their default. Then click OK.



PropertyValue
New FilterSales Territory
Filter TypeComparison
OperationEquals
 
16. Back in the Input Parameters page, click @year in the Data Source Elements pane.
17. In the Properties pane, edit the values as below. Then click, Click to Add against the Filter property as shown below.



PropertyValue
Display NameSales Year
Default Value2008
18. In the Filter Configuration dialog, select New Filter. Edit the properties as below leaving the rest to their default. Then click OK.



PropertyValue
New FilterSales Year
Filter TypeComparison
OperationEquals
 
19. Similarly, on the Input Parameters page, click @pageNo in the Data Source Elements pane.
20. In the Properties pane, edit the values as below. Then click, Click to Add against the Filter property as shown below.



PropertyValue
Display NamePage No
Default Value1
21. In the Filter Configuration dialog, select New Filter. Edit the properties as below leaving the rest to their default. Then click OK.



PropertyValue
New FilterPage No
Filter TypeComparison
OperationEquals
 
22. Back on the Input Parameters page, click @limit in the Data Source Elements pane.
23. In the Properties pane, edit the values as below. Then click, Click to Add against the Filter property as shown below.



PropertyValue
Display NameLimit
Default Value200
24. In the Filter Configuration dialog, select New Filter. Edit the properties as below leaving the rest to their default. Then click OK.



PropertyValue
New FilterLimit Filter
Filter TypeLimit
Is DefaultChecked

25. Click the Next > button at the bottom of the Read List dialog.
26. On the Return Parameters page, click SalesOrderNumber in the Data Source Elements pane.
27. In the Properties pane, check the box for Map to Identifier and Show In Picker.

28. Click the Finish button.
29. Click the Save button in the Quick Access Toolbar to commit the changes.
 

Verify the Content Type in the Central Admin


In this step, we will verify the various settings for the External Content Type created in the previous step.
1. Start SharePoint 2010 Central Administration.
2. Click Manage service applications under the Application Management heading.
3. In the Name column, click on Business Data Connectivity Service. Then click on Manage in the ribbon.
4. In the service application information page for the Business Data Connectivity service, verify an entry forSalesOrders. Click on the entry to go to the External Content Type information page. Verify the following property values,



PropertyValue
External SystemAdventureWorks2008R2_SalesOrders
Filters on Finder MethodsLimit Filter (Type: Limit)
Page No (Type: Comparison)
Sales Territory (Type: Comparison)
Sales Year (Type: Comparison)
 
5. Next, to verify the External System (the Database connection) setting, select External Systems in the drop down in the ribbon.

6. Next, to verify the External System (the Database connection) setting, select External Systems in the drop down in the ribbon. The service application information page should list an entry forAdventureWorks2008R2_SalesOrders.

7. Click on the entry to go to the External System Information page. There should be one instance entry as shown below.

8. Click on the instance name to go to the property settings page. Verify the property as below,



PropertyValue
Access ProviderSqlServer
Authentication ModeUser's Identity
Database Server(local)
Initial Database NameAdventureWorks2008R2

Note: This connection will use the service account to connect to the database.

Use Business Data List web part to show external data


In this step we will use a Business Data List web part to display a list of sales orders.
1. Open the web application SharePoint - 80. From the Site Actions menu, click Edit Page.
2. Click on Add a Web Part, in the left web part zone.
3. From the web parts selection menu, select Business Data in the Categories section and then from the list of web parts select Business Data List and click Add.

4. From the web part's action menu, click on Edit Web Part to open the tool pane for this web part.
5. In the Business Data List section of the edit pane, click on the picker icon next to Type field to bring up the External Content Type Picker dialog form.

6. In the dialog form, select the content type created in the previous steps and click OK.

7. For the View Property, select Default (SalesOrders Read List) in the drop down.

8. Edit the Title property of the web part to AdventureWorks Sales Orders and then click OK in the web part's tool pane.
9. In the web part's toolbar, click on the Edit View link.

10. In the Edit View page, select Retrieve All Items option for the Items to Retrieve category. Also, enter 200 for the Item Limit category. Leave the remaining fields to their default values. Then click OK.

11. This will bring out a list of sales orders. To filter the result set for Sales Territory 'Canada' and for the year '2005', first enter Canada against the Sales Territory option. Then click on the Add link.

12. This adds another filtering option. In the second option, select 'Sales Year' in the first drop down and then enter '2005' in the value text box. Then click on the Retrieve Data link to get a filtered list of records.


Connecting Business Data List web part with other web parts


In this example we will see how we can connect the Business Data List web part used in the previous steps to another web part. In this step we will use a Choice Filter web part, which will be used to pass the 'Sales Territory' filter value to the Business Data List web part. Note that, in real applications this may not be necessary as this feature is already built in to the Business Data List web part as shown in our previous step.
1. Browse to the web page used in the previous step to display the use of Business Data List web part. From the Site Actions menu, click Edit Page.
2. Click on Add a Web Part, in the left web part zone.
3. From the web parts selection menu, select Filters in the Categories section and then from the list of web parts select Choice Filter and click Add.

4. From the Choice Filter web part's action menu, click on Edit Web Part to open the tool pane for this web part.
5. Edit the web part with the following properties. Leave rest of the properties to their default settings. Then clickOK.



PropertyValue
Filter NameSales Territory
ChoicesAustralia
Canada
Northwest
Show (Empty) valueChecked

6. From the actions menu of the Choice Filter web part, select "Connections -> Send Filter Values To". This should display an option with "AdventureWorks Sales Order" web part. Select this option.

7. In the Choose Connection dialog form, select Get Query Values From in the Connection Type selection box. Then click Configure.

8. For the Consumer Field Name option in the next window, select Sales Territory and then click Finish.

9. To verify the connection, click on the filter button next to Sales Territory web part. This will bring up a dialog form with the choices entered in the previous step. Select Northwest option and then click OK.

10. This will refresh the Business Data List web part to list sales orders for Northwest Territory alone.