If you plan to use Stored Procedures, you will need a separate stored procedure for each CRUD operation. In addition, you will need separate stored procedures for any associations you might need. It is important to note that each Read List, Read Item, and Association stored procedures need to return all the fields that will be required by any other stored procedure defined by that Content Type. In other words, the Read List, Read Item, and Association stored procedures need to return the same exact fields. If they don’t, you will get runtime errors.
Since most examples center on tables, you will often not see a detailed discussion of fields that are required for all the operations as tables always return to you all the fields of that table. So to avoid unintended runtime errors with your ECTs always make sure that your stored procedures return to you all the fields that you think you might need even if you expect not to need them in a particular ECT operation definition. SPD then allows you to define which of these fields should be included in the ECT definition.
The following is a list of field issues that you should be aware of:
Unique Identifiers: Each stored procedure needs to provide a unique identifier of type integer. SPD will allow you to have other types of unique identifiers, but you will run into runtime errors if you try to perform any association, create, update, or delete operations. You need these identifiers to avoid issues even if they are completely meaningless to your solution.
Limit filters (Read List operations): If it is possible that your data will return more than two thousand records, this will become big problem down the line. BCS by default has a 2000 item throttling limit. This limit can be changed, see BCS PowerShell: Introduction and Throttle Management. You can go without limit filters in development and not see any issue even if your database has hundreds of thousands of records as External lists will by default implement paging. Just understand that if you are using the object model (BCS Runtime or Client object models) to access your data, all records will be returned to you. This can be a major cause of performance degradation and you will not likely see it till you are on a production environment where there are greater latency issues (such as distributed servers, zones, and SSL implementations that you are likely not to have in development). One important thing to note is that a limit filter on its own will just limit the items returned; this means that without another filter type you can only access a subset of your data. For example if you want to limit the amount of books returned by a query to 100, you would add a limit filter and add another such as a Wildcard Filter (say for example a book’s partial title or publish date), this will mean you will get a maximum of 100 books which match the Wildcard filter returned. So in order to implement limit filtering on Read List operation, your Read List stored procedure needs to have an input parameter to use for performing an additional filter criteria.
Nullable field types: SPD will give you warnings if it finds fields that are nullable, but it can handle them just fine. Be careful with this as External lists will try to return empty strings to these fields if the fields are not required. This can be a real problem if the field is not of a CHAR, VARCHAR, or some other string type. This will give you runtime errors. If you are using these fields via the object model (BCS Runtime or Client object models), then you can handle this by returning nulls for these field types.
Recently I had a client request a BCS solution to provide custom forms to enable customers to manage their profiles and allow employees and other customers to be able to view those profiles. They wanted a BCS solution instead of a traditional web application with a database backend in order to take advantage of SharePoint Enterprise Search. This approach would allow users to be able to search against any customer information that is stored in SQL Server. In addition, they wanted a SharePoint solution package so that they can deploy/redeploy to multiple environments (Integration, QA, and Production).
Customer’s profile information was in tables but not in a form that was directly meaningful to end users (for example they have different types of contact information as different records in the same table and used as lookups). In addition, some of this data was imported from other systems by timer jobs and triggers. The solution was to abstract the data for the BCS by using stored procedures. So our challenge was to develop a BCS solution package(s) based SQL Server stored procedures.
If you have read any of the marketing materials on the BCS, you would think that the BCS is most ideal way to achieve the client’s goals. SharePoint articles and books would then state that you can create External Content Types (ECTs) and External lists in SharePoint Designer (SPD) and you can be done in an hour or two. To access this data, you can create web parts that use the SharePoint API to access the External lists just like any other SharePoint list.
Easy, right? Well, let’s explore some of the issues that are common to all BCS Solutions and then take a further look at the issues that this particular approach brings up. For each issue, I will explain our design decisions and the final solution.
Surfacing external data in SharePoint enables users to build composite applications that give access to critical information and make their interactions with that information more convenient. Business Connectivity Services (BCS) is the SharePoint service which allows surfacing of external data from SQL Server, Web Services, or.NET Assembly Connector. SharePoint even provides no-code BCS solutions to surface the external data via SharePoint Designer to allow for rapid development and provides External Lists to quickly interacting with that data. You can also secure the data by setting permissions on who can create, read, update, and delete (CRUD) the data. You can even crawl that data using SharePoint Enterprise Search and set a profile page for rendering the search results in a meaningful way. You can rapidly developing a feature rich front end for your external data.
Search enabling your BCS solution will however require more than SharePoint Designer to develop. This is also true if you want to deploy your solution to multiple environments. This does not mean that you have to write code, but it does mean that you will find yourself in Visual Studio modifying declarative markup that SharePoint Designer can produce and packaging that markup into WSP solution packages. This approach will allow you to develop external data solutions that you can quickly develop and deploy to multiple environments consistently and will provide your users the ability to immediately search and render meaningful results.
Now if you do determine that you need to integrate external data with SharePoint, but want to still have custom forms or any kind of richer user experience to interact with that data, you could write code using the SharePoint API against external lists. This approach is often touted as one of the great things about external lists in that you can treat them as any other SharePoint lists. This approach will work for small datasets, but for large datasets and anytime you are concerned about performance of your forms, you will really want to write your code to go against the BCS Runtime or Client Object Models. This is a very powerful approach as you can now develop a very rich custom user interface to interact with your external data within the SharePoint context. You can even access your external data from other applications via the BCS Client Object Model.
As I have discussed, SharePoint provides for rapid development of external data and allows you to apply security and search on your external data. It also provides the capability to build very powerful and very rich custom user experiences for your external data. Implementing these solutions to different environments and making your application production ready presents some challenges that are not well documented. In my next few blog posts, I will go through the process of making an external data solution from beginning to end and show you techniques that will make your solution more stable and production ready.