Implementing ECTs in SPD using Stored Procedures

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.

Defining and developing your BCS entities into ECTs

There are many examples online and in books on how to create ECTs using SPD. Most of them focus on building ECTs against SQL Server database tables and show you how to create all CRUD operations without differentiating between the different operational requirements for each CRUD operation. Here I will focus on these differences and what they mean.

A SharePoint Content Type is a way for SharePoint to represent an entity. So if you have external data that represents books and publishers, then you will need to create two External Content Types, one representing publishers and another representing books. All ECTs require two operations as a minimum; Read List and Read Item operations. This is because in order to create an external list, you need to be able to read a list of items (books or publishers) and be able to view a particular item (a book or publisher). Each of these operations is a separate request to the database. It is important to note that the Read Item operation is of particular importance as any Update operations will also require a call to the Read Item operation prior to performing the Update.

An Association operation is how the BCS will allow you to get parent-child data. For example, if you have a publisher’s content type and a books content type, then you can use an Association operation to get all the books that belong to a particular publisher. Essentially you need to implement a Stored Procedure that has a publisher identifier as an input parameter and returns book entity fields. In order to do this consistently in the books content type, you should have the Publisher identifier (your foreign key) as one of the fields of the books content type. That field is then used to map the Find Item and Association methods for your ECT. It is important to note that this foreign field has to be a different field than the field that is used as the unique identifier for your child ECT and it has to be unique on the parent ECT. SPD will allow you to use non-integer fields for this, but to avoid runtime errors, make sure the foreign field is of integer type.