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
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.
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.
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.
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 |
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.
Property | Value |
Name | SalesOrders |
Display Name | SalesOrders |
Namespace | http://contoso.com |
Version | 1.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.
Property | Value |
Database Server | (local) |
Database Name | AdventureWorks2008R2 |
Name | AdventureWorks2008R2_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 procedure
GetSalesOrders 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.
Property | Value |
Display Name | Sales 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.
Property | Value |
New Filter | Sales Territory |
Filter Type | Comparison |
Operation | Equals |
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.
Property | Value |
Display Name | Sales Year |
Default Value | 2008 |
18. In the
Filter Configuration dialog, select
New Filter. Edit the properties as below leaving the rest to their default. Then click
OK.
Property | Value |
New Filter | Sales Year |
Filter Type | Comparison |
Operation | Equals |
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.
Property | Value |
Display Name | Page No |
Default Value | 1 |
21. In the
Filter Configuration dialog, select
New Filter. Edit the properties as below leaving the rest to their default. Then click
OK.
Property | Value |
New Filter | Page No |
Filter Type | Comparison |
Operation | Equals |
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.
Property | Value |
Display Name | Limit |
Default Value | 200 |
24. In the
Filter Configuration dialog, select
New Filter. Edit the properties as below leaving the rest to their default. Then click
OK.
Property | Value |
New Filter | Limit Filter |
Filter Type | Limit |
Is Default | Checked |
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.
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 for
SalesOrders. Click on the entry to go to the External Content Type information page. Verify the following property values,
Property | Value |
External System | AdventureWorks2008R2_SalesOrders |
Filters on Finder Methods | Limit 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 for
AdventureWorks2008R2_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,
Property | Value |
Access Provider | SqlServer |
Authentication Mode | User's Identity |
Database Server | (local) |
Initial Database Name | AdventureWorks2008R2 |
Note: This connection will use the service account to connect to the database.
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.
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 click
OK.
Property | Value |
Filter Name | Sales Territory |
Choices | Australia
Canada
Northwest |
Show (Empty) value | Checked |
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.