In a previous post I created a simple weekly sales forecasting table which will be used as a starting point to create an External Content Type. Below is some sample data from the table:
When a External Content Type is created the data source can be a table, view, store procedures or a web services. The External Content support various operation but you must provide functionality for the following two operations:
These two operations must defined in order to use the External Content Type as an External List in SharePoint 2010. For this example I am going to create two stored procedures for the operations:
- dbo.BcsSalesForecastReadList
- dbo.BcsSalesForecastReadItem
Before the External Content Type Operations can be created we need to create the stored procedures that will be used to read an item from the external content type and list all items in the external content type. The Read List stored procedure for our sales forecast data is fairly simple. The stored procedure simple needs to return all the rows and columns we want to be available in the External Content Type. Why use a stored procedure then for this operation? For simple lists of information a using a table or view is fine but if you want to have a high level of flexibility and control then a store procedure is probably a better choice. A Web Service would provide even more power and platform independence but for our sales forecast application our approach will suffice.
dbo.BcsSalesForecastReadList
create procedure dbo.BcsSalesForecastReadList as set nocount on select EnglishMonthName + '-' + WeekNumber + '-' + SalesTerritoryRegion + '-' + Category ID, EnglishMonthName MonthName, WeekNumber, SalesTerritoryGroup SalesGroup, SalesTerritoryRegion SalesRegion, Category ProductCategory, LastYearAmount, ForecastAmount from dbo.WeeklySalesForecast
dbo.BcsSalesForecastReadItem
create procedure dbo.BcsSalesForecastReadItem @ID nvarchar(255) as set nocount on select fcst.ID, fcst.MonthName, fcst.WeekNumber, fcst.SalesGroup, fcst.SalesRegion, fcst.ProductCategory, fcst.LastYearAmount, fcst.ForecastAmount from ( select EnglishMonthName + '-' + WeekNumber + '-' + SalesTerritoryRegion + '-' + Category ID, EnglishMonthName MonthName, WeekNumber, SalesTerritoryGroup SalesGroup, SalesTerritoryRegion SalesRegion, Category ProductCategory, LastYearAmount, ForecastAmount from dbo.WeeklySalesForecast) fcst where ID = @ID
For the purposes of this demo I have created a unique Business Key (ID) which concatenates (joins) the following fields:
- EnglishMonthName
- WeekNumber
- SalesTerritoryRegion
- Category
This combination uniquely identifies each row in the forecast table. Ideally you could used an identity column or create proper indexes using the 4 columns.
Connect to the site that was created in the last post or you can use one of your existing sites. Open SharePoint Designer and open the following site:
Click on the External Content Types site object in the Navigation Pane on the right hand side. Create a New External Content Type. You should see the following screen:
Enter the following:
- Name: BcsSalesForecast
- Display Name: Bcs Sales Forecast
In the External Content Type Operation section click “Click here to discover external data sources and define operations” . You should see a screen similar to this:
Click “Add Connection” and select the Data Source Type as SQL Server. Enter in the following items:
- Database Server: (server name)
- Database Name: AdventureWorksDW2008R2
- Name (optional): Adventure Works
For this example you can connect with the User’s Identity. Look under the “Routines” section and you should see the stored procedures that were created above:
Right click on the BcsForecastReadList procedure and select “New Read List Operation”. Keep the default settings for Operation Name and Display Name. Below is a summary of the Read List wizard:
 |
 |
| 1. Operation Properties |
2. Input Parameters |
 |
 |
| 3. Return Parameter |
4. Read List is created |
Follow a similar process for the Read Item Operation. Right-click on the BcsForecastReadItem procedure and select “New Read Item Operation”. Below is a summary of the Read Item wizard:
 |
 |
| 1. Operation Properties |
2. Input Parameters |
 |
 |
| 3. Return Parameter |
4. Read Item is created |
You have now created your first External Content Type. But we cannot use it yet. In the next post I will show you how to enable security on the External Content Type and create an External List which will be displayed in the Ops Forecast site. Below is a screen shot of the External Content Type being used as an External list:
 |
 |
| 1. List View |
2. View Item |
Troy
