A step by step guide in designing BCS entities by using a SQL stored procedure
In this article, we will see how to design a SharePoint BCS entity from a SQL stored procedure that takes some inputs and returns a list of records. The scope of the article is to show the use of OOB features provided by SharePoint Server and SharePoint designer to achieve this. We will also see how to use the OOB Business Data List web part to display the output of the stored procedure.
Contents
Verify Web Application settings
Configure Business Data Connectivity service
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
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, see Create 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. See Sample 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 Add button. 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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[GetSalesOrders] @territory nvarchar(200) = null, @year int = 2008, @pageNo int = 1, @limit int = 200ASBEGIN 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 ;ENDGO |
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.
Property |
Value |
Name |
SalesOrders |
Display Name |
SalesOrders |
Namespace |
|
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.
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 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.
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 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.
Additional Resources
For more information, see the following resources:
- Business Data Connectivity services administration
- Business Connectivity Services Schema Reference
- Stereotyped Operations Supported By BDC
Comments
Anonymous
July 25, 2011
Thank Jyoti! This blog post is really helpful and timely!Anonymous
November 15, 2011
Thanks Jyoti, the steps are very details. It's helpful.Anonymous
May 13, 2012
Great article. But I got one question. Assume, My external data have 100,000 and more records where I have the select query in the same way with row number and SP will return only rows needed for the next page. But, how can I pass the next page number, or previous page number from Business Data list web part to my input parameter set. I can set Pagenumber / Limit in input paramete set, but it will not be a dynamical way. If I am in 11th page, when I click previous page icon, I need to pass 10 or 11-1 to my stored procedure. How can I do this. Any ideas will be greatly appreciated. Thanks BJoseAnonymous
November 02, 2012
@Ben, There are different ways. One approach would be to use query string parameter. We can use a querystring filter web part and create a connection with Business Data List web part. We can then provide pagination support in the web part by customizing it with XSLT.Anonymous
December 10, 2012
Dear Jyoti! Can you share the paging support xslt customization? It isnt easy... Thanks, IstvanAnonymous
June 04, 2013
Thanks for posting the stepsAnonymous
July 10, 2015
the item cannot be edited when searching with this web part? is there another method to search records the way they are displayed. I'm using SharePoint 2013.