Posted by Troy on August 7, 2010 under Microsoft, SQL Server, SharePoint, Visual Studio, Web, csharp | 
When I first started building External Content Types in (ECT) SharePoint Foundation Server I used SharePoint Designer. SharePoint Designer is a good place to start if you are learning how build ECT’s. It does not require any .NET code and you can build a fairly decent application in little time especially if you have a solid relational database background. But I wanted to try build an External Content Type using Visual Studio 2010. In this case I am going to build a simple BDS Model based on the SalesLT.Customer table in the AdventureWorksLT2008R2 database.
First, lets start by creating an empty SharePoint 2010 Solution (C#) in Visual Studio 2010. Select File > New > Project from the file menu. In the Installed Template section under Visual C# > SharePoint > 2010 select the Empty SharePoint Project and name the project SPDev1. This will create an empty SharePoint project which we can add multiple projects to.
When you select Ok the SharePoint Customization will be displayed. Enter the the site you want to use for debugging the project and make sure you select “Deploy as a farm” solution:
Right-click on the SPDev1 project and select Add New Item:
Now Select Business Data Connectivity Model from the Add New Item list and name it AWLTCustomer. Once you enter the name of the model click Add:
 |
 |
| Add BDC Item |
BDC Solution |
When the BDC Model is added it is fully functional. If you Build the project it will compile without any errors.
You will notice that I named the Blog Post “BDC Models in Visual Studio 2010 for SharePoint Foundation Server 2010” and not “BDC Models in Visual Studio 2010 for SharePoint Server 2010” . If you created this project using SharePoint Server 2010 you can actually deploy this project to the debug site and it will create an BDC Model with an entity called Entity1. But if you are using SharePoint Foundation Server 2010 you will receive the following error when the project is deployed (Right-click the project and click Deploy):
Error 1 Error occurred in deployment step ‘Add Solution’: Failed to load receiver assembly "Microsoft.Office.SharePoint.ClientExtensions, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" for feature "SPDev1_Feature1" (ID: 1a75169a-e716-4d4b-a43c-3c0cf032fc40).: System.IO.FileNotFoundException: …
In order to deploy a BDC Model to Foundation Server a Feature Event Receiver is required to publish the mode to the site:
Publish BDC Model project to SharePoint Foundation 2010 with Visual Studio 2010
The following post on one of the Microsoft Blog sites provides details on how create the Feature Event Receiver.
Deploy a BDC Model project to SharePoint Foundation 2010 using Visual Studio 2010
Basically you need to download the following file from the first link:
Feature Event Receiver (C#)
In the current project, add an Event Receiver to Feature1 by right-clicking Feature1 and select Add Event Receiver:
First copy the existing content of Feature1.EventReceiver.cs to a file using notepad. Next, replace the contents of Feature1.EventReceiver.cs with the contents of the file downloaded from the Microsoft site (FeatureEventReceiver.cs) (Ctrl-C, Ctrl-V). The details from this point on can be found in the Microsoft Blog post mentioned above (Deploy a BDC Model project to SharePoint Foundation 2010 using Visual Studio 2010), but I will provide some additional screenshots along with the steps.
We need to Add a reference to the MicrosoftBusinessData.dll. In order to do this you have to copy the dll from the GAC. Click Start > Run and enter the following:
- C:\Windows\assembly\gac_msil
Find the Microsoft.BusinessData folder and copy the Microsoft.BusinessData.dll to the following folder:
- C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI
Next add the following references to the project:
 |
 |
| System.Web |
Microsoft.BusinessData.dll |
Set the BDC (SharePoint item property) which in our case is located in the AWLTCustomer model and set the following properties located under Feature Receiver Category:
- Assembly = Assembly = $SharePoint.Project.AssemblyFullName$
- Class Name = SPDev1.Features.Feature1.ImportModelReceiver
 |
 |
| Properties Before |
Properties After |
Set the Feature property by double clicking on Feature1.Feature and update the following property:
- Receiver Class = SPDev1.Features.Feature1.ImportModelReceiver
 |
 |
| Properties Before |
Properties After |
One last thing, we need to change the Namespace of the code that was copied into Feature1.EventReceiver.cs from BDCModelReceiver to SPDev1:
I have been writing this post while following Microsoft Blog. So, here is the moment of truth. After the last step we should be able to deploy the BDC model. Right-click on the project and click deploy or click F5 if you want to debug the solution. To view the content type you can open SharePoint Designer and navigate to the External Content Type sit object:
Before you can create an External list based on the new External Content Type the permissions must be set. Check out this post for setting the permissions using the SharePoint Central Administration site:
Setting Permissions for an External Content Type
Once the permissions have been set an External List can be created. In this example the External List is very basic and in fact the model has not been connected to the External table in the Adventure Works. But that is a topic for another post.
For more information on BCS check the Microsoft BCS Team Blog:
http://blogs.msdn.com/b/bcs/
Troy
Posted by Troy on July 19, 2010 under Business, Business Intelligence, Microsoft, Performance Management, SQL Server, SharePoint |
If you have been following along in the last few posts, I have created an External Content Type called BcsSalesForecast with the following operations:
- Read List
- Read Item
- Update
Then an External List was created called Sales Forecast 2010. Up to now the main focus of the discussion has been around creating the External Content Type and the store procedures which are used as its data source. However, if you currently view the list below its presentation as a lot to be desired. First off, its sorted by ID and there are over 700 records with 30 per page which means that it could take awhile to find the record you are looking for.
In order to make the list more “useable” it is possible to modify the view. Before the view is modified a minor update is required to the data. The Sales Forecast 2010 list is a weekly forecast application for 2010 and provides the previous years as a starting point (the data is actually from Adventure Works for 2008). Ideally it would be nice to sort the data by week but I did not include a sort key as part of the original design. But this can fixed by running the following UPDATE statement against the dbo.WeeklySalesForecast table:
update dbo.WeeklySalesForecast set WeekNumber = case when CONVERT( INT,SUBSTRING(WeekNumber,5,3)) < 10 then 'Week 0' + SUBSTRING(WeekNumber,6,1) else WeekNumber end
This will add a leading 0 to all weeks that are less then week 10 which will ensure the sorting by Week will work. Now you can navigate to the Sales Forecast 2010 list and click on the List tab:
Next click Modify View but do not click on Modify in SharePoint Designer (Advanced). For this scenario SharePoint 2010 Designer is not required. The changes to the list can be performed in the browser. Change the First Sort Column from ID to WeekNumber:
Next set the First Group By Column to SalesRegion and the Second Group By Column to ProductCategory:
Change the number of items to display per page from 30 to 300 :
Hide the SalesRegion and ProductCategory since these are redundant because they are being used as Groups. Secondly the ID field can be hidden because it is just required as the unique identifier for the row:
(Note: the position of the columns may be different than what you see when you “Modify” the view)
Click OK to save the changes to the view and now look at the List:
As you can see, a few simple modifications to the list can make a significant difference in terms of the lists usability.
Troy
Posted by Troy on July 17, 2010 under Business, Business Intelligence, IT, Microsoft, Performance Management, SQL Server, SharePoint |
* Please note that the following post is based on SharePoint 2010 Foundation Server and not SharePoint 2010 Server.
In a previous post , I created an External Content Type with 2 operations and then created an External List based on the External Content Type. In short this means that you are now able to create a SharePoint List based on some external data (e.g. database table or store procedure in this example). But currently the external data is read only because we only created the following operations for the External Content Type:
Given that the example was based on Sales Forecast data at the very least we need to give the user the ability to update the data. In order to do this a new operation needs to be added and a new stored procedure is required to support the operation. Before we add the the Update Operation to the BcsSalesForecast External Content Type we need to create a store procedure which will be used for this operation called dbo.BcsSalesForecastUpdate:
CREATE procedure [dbo].[BcsSalesForecastUpdate] @ID nvarchar(512), @ForecastAmount int as set nocount on update dbo.WeeklySalesForecast set ForecastAmount = @ForecastAmount from dbo.WeeklySalesForecast where EnglishMonthName + '-' + WeekNumber + '-' + SalesTerritoryRegion + '-' + Category = @ID
Now that the store procedure is created, SharePoint Designer can be used to add the new operation to the BcsSalesForecast External Content Type. Navigate to the BcsSalesForecast External Content Type and click “Edit External Content Type” on the Ribbon:
Now click on the “Operations Design View” Menu item on the Ribbon:
Right-click on the Routine folder and select Refresh. The dbo.BcsSalesForecastUpdate stored procedure should appear. Right-click on the stored procedure and select “New Update Operation”.
Make sure the @ID is select as the “Map to Identifier”:
select * from dbo.WeeklySalesForecast where ForecastAmount is not null
The BcsSalesForecastReadItem and the BcsSalesForecastReadList have to be updated too. The following fields must be set to “Read Only” or the new Edit Form will not work:
- MonthName
- WeekNumber
- SalesGroup
- SalesRegion
- ProductCategory
- LastYearAmount
Below is an example of setting a field to read only. Remember this must be performed on both the Read Item and Read List operations:
Click Finish and the new operation has been created. The solution is not finished yet. Remember, we also have an External List called “Sales Forecast”. If you view the Sales Forecast list and right-click on a row you will notice that there is a new menu item called “Edit Item” and the “Edit Item” button in the ribbon is no longer grayed out. Try to edit the row. You should receive the following error:
The reason for this is that an Edit Form does not exist for the list. To work around this we can simply create a new External List and call it “Sales Forecast 2010” and the SharePoint designer will create the necessary forms depending on what operations have been defined for the External Content Type:
Select the list in SharePoint Designer and click the “Preview in Browser” button on the Ribbon to try out the new “Edit” functionality. Right-click on any row on the list and select Edit item. The following form will pop-up:
Click Save and the the SharePoint List and the External Data will be updated. Use Management Studio and execute the following query against the dbo.WeeklySalesForecast:
select * from dbo.WeeklySalesForecast where ForecastAmount is not null
Now this example may not be practical especially if one person was responsible for updating all of the forecast data. But this example shows how easy it is to create a simple CRUD (Create, Read, Update, Delete) based application based on External data with little or no code. This example only implemented the Read and Update operations but the Create and Delete operations would be very easy to implement and would require 2 more stored procedures (dbo.BcsSalesForecastCreate, dbo.BcsSalesForecastDelete)
Troy
Posted by Troy on July 9, 2010 under Business Intelligence, IT, Microsoft, SQL Server, SharePoint |
In a previous post, I created an External Content Type based on the following Stored Procedures:
- dbo.BcsSalesForecastReadList
- dbo.BcsSalesForecastReadItem
Before you can use the External Content Type the permissions must be set. The permissions are set using the SharePoint 2010 Central Administration Panel. Open the Central Administration Panel and you should see the following:
Under the Application Management Section click on the Manage Service Application settings link. Next Click on the Business Connectivity Data Service and you should see the External Content Type called BcsSalesForecast:
Hover over the BcsSalesForecast External Content Type and select “Set Permissions” from the drop down box:
Type in the username (Domain\userid) and click check names to verify the user and then click Add. Once the user or users are added you can set the permissions. When I was adding my user id I found that I had to be connected to my domain in order to add my account. For example, if you are using laptop at home and you are not connected to your companies domain you probably won’t be able to add the account to the External Content Type. If you have VPN Access to you company network then you can connect to the Domain and then try adding the Account.
Once the permissions have been set you can now create an External List based on the External Content Type using the SharePoint Designer. Just Navigate to the External Content Type in the SharePoint Designer and Select BcsSalesForecast and then click External List located in the Ribbon as shown below:
This will create an External List called Sales Forecast. Because only ReadList and ReadItem operations are defined for the External Content Type, for now, you can only view all the items in for the Forecast or view the details of one item.
If you have not set the permission for the External Content Type you will receive the following error when you try to access the External List:
Access denied by Business Data Connectivity.
Troy
Posted by Troy on July 2, 2010 under Business Intelligence, IT, Microsoft, Performance Management, SQL Server, SharePoint |
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
