Business Intelligence in SharePoint 2010 with Business Connectivity Services : Consuming External Content Types (part 2) - Writing to External Content Types

Writing to External Content Types

In SharePoint 2007’s Business Data Catalog, interaction with external data was read-only. There were no means by which authorized users could write back to the external data source. The other major change with BCS in SharePoint 2010 is that external content types can be defined with full CRUD (Create, Read, Update, and Delete) capabilities.

For many scenarios, writing to an external content type is going to be beyond the scope of what you can do within the confines of SharePoint Designer. It’s likely that the database to which you are connecting will have columns with more sophisticated data requirements. For example, many database tables will have some internal fields whose values are generated by the code in whatever application the database connects to.

The following steps walk you through the process of creating these operations and subsequently demonstrate why there are limitations to using SharePoint Designer for write-back operations:

1.	Click Start, and open SharePoint Designer 2010 from the SharePoint group.
2.	In SharePoint Designer 2010, from the File Ribbon, click Open Site. For example, type http://mss2010.mydomain.com/sites/BCSSample/.
3.	In the Site Objects window, select External Content Types.
4.	BCS Customer and BCS Order should be listed in the External Content Types pane in the bottom-left corner of SharePoint Designer. Click BCS Customer.
5.	In the External Content Types Ribbon, click the Operations Design View icon.
6.	In the Data Source Explorer, right-click the Customer table, and select New Create Operation, as shown in Figure 5. Figure 5. Building a create operation for an external content type.
7.	In the Operation Properties section of the Create Wizard, type Create Customer as both the Operation Name and the Operation Display Name. Click Next.
8.	In the Input Parameters section, no changes are necessary. Several fields from the File Ribbon (PasswordHash, PasswordSalt, and ModifiedDate) will be discussed in more detail shortly. Also notice the opportunity to enter a default value for each of the fields below. Finally, observe that each field can be unchecked; however, doing so results in an error message being added to Errors and Warnings, which will block instantiation of the Create operation. Click Next.
9.	In the Return Parameter portion of the wizard, no changes are necessary. Click Next.
10.	Click Save, or enter CTRL-S to save the changes.

Although this exercise provided a simple set of steps to build a Create operation on the Customers table, some of the fields previously mentioned—Password Hash, Password Salt, and ModifiedDate—present specific problems.

These fields all perform important functions on the database. Yet, at the same time, they aren’t the types of fields meant to be provided by end users. The PasswordHash and PasswordSalt fields, for example, are managed by other applications and provide encryption on the actual password entered by a system user. Finally, the ModifiedDate field, also intended to be system-generated, is used to capture when the field was actually entered.

For fields such as these, SharePoint Designer is only sophisticated enough to allow you the following options: Ignore the Fields on Write Operations, Specify a Default Value, or Leave It Up to the User to Enter a Value. The resulting outcomes of these options would be that a) the field was left blank on the new entry, b) useless data was entered, or c) an invalid entry resulted in an error that prevented the entire record from being added.

So although BCS provides the capability to write back to SQL Server from SharePoint, and although this can be a powerful business tool, it is only realistic for small, simple databases. For most line-of-business database systems, this is most likely going to be a practice best left to a more advanced BCS authoring tool, such as Visual Studio 2010.

Delete operations can pose similar problems on many database applications. A well-designed, normalized database is likely going to have records in one table that depend on records in another table. An Orders table, for example, might have a CustomerID foreign key field, which depends on a related record in the Customers table. In the database, if you try to delete a Customer record when there are dependent records in the Orders table, SQL Server might issue an error message and block the deletion. The error message would be returned to the user and indicate the existence of such a dependency.

A simple Delete operation, however, can be created with the following steps:

1.	In the Data Source Explorer of SharePoint Designer, right-click the Customer table, and select New Delete Operation.
2.	In the Operation Properties section of the Create Wizard, type Delete Customer as both the Operation Name and the Operation Display Name. Click Next.
3.	In the Input Parameters section, no changes are necessary. Note that the CustomerID field is automatically selected, with the Map to Identifier check box automatically checked. Click Finish.
4.	Click Save or press Ctrl-S to save the changes.

To see how these additional operations would be used in SharePoint, and how the problems previously described manifest themselves, complete the following set of exercises:

1.	In Internet Explorer, open the SharePoint site used . The URL, for example, would be http://mss2010.mydomain.com/sites/BCSSample/.
2.	Click the Customers list from the Quick Launch Bar on the left side.
3.	Click the List Ribbon, and click the List Settings icon.
4.	Click Delete this List, and confirm the deletion. Note When including Create or Update operations on an external content type, it is important to create these operations before creating the external list in SharePoint. The reason the operations need to be defined first is that, when SharePoint creates a new list, it creates the form pages as needed for Edit Item and New Item. If no operation is defined, no corresponding form pages will be created when the list is instantiated. If the list is created first, and operations for Create or Update are then retroactively defined, the Add/Edit options show up on the item’s Actions menu. However, selecting one of these options on the external list will result in an error message. This is corrected by deleting and re-creating the external list.
5.	Select Site Actions, View All Site Content.
6.	In the All Site Content page, click Create.
7.	In the Create page, select External List, and click Create.
8.	In the new list page, type Customers as the list name.
9.	For the External Content Type field, an External Content Type picker is available. Click the icon for Select External Content Type. Select BCS Customer, and click OK. BCS Customer (AdventureWorksLT) should display as underlined. Click Create.
10.	After the list is re-created, click the Item Ribbon, and click New Item.
11.	Fill in the New Item record, as shown in Figure 6, and click Save. Figure 6. New item screen for the Customers external list.

Notice a few things about the New Item screen. First, the PasswordHash and PasswordSalt fields provide no guidance to the user completing the form on how to fill in the fields. Additionally, the fields are required, because they are required in the AdventureWorksLT database. Thus, leaving the fields blank is not an option for the user. Also, the ModifiedDate field enables the user to directly input a date, even though it is intended to be controlled by the system.

The record will indeed get added to the AdventureWorksLT database. However, certain data points, crucial to the database and other dependent systems, will end up with invalid data.

To observe the issue with the delete operation, complete the following steps:

1.	In the Customers SharePoint list, click the link for 1 in the CustomerID column, which corresponds to the record for Orlando Gee.
2.	In the Customers – View Item screen, click Delete. Confirm the deletion.
3.	The error message displayed in Figure 7 should result. This is because the Orlando Gee record has records in one or more related tables on the database. Figure 7. Error deleting external item with related records.
4.	On the resulting error screen, click the Go Back to Site link.
5.	Hover over the CustomerID column. Click the yellow arrow, and select Descending to sort the list in reverse order.
6.	The James Smith entry, added in the recent set of exercises, should appear first.
7.	Repeat steps 1 and 2 to attempt to delete the James Smith record. Because this record has no related rows in any other database table, the deletion attempt should succeed.

Creation of the Delete Operation against the Customer table using SharePoint Designer was relatively simple and straightforward. And there were no issues when attempting to delete Customer records through SharePoint when they had no related Order records. But when attempting to delete one that did have a related record, the database protected the integrity of the related data. However, the resulting error message was not indicative of the issue.