To create a new BDC service application, you need to have Farm Administration privileges. Open Central Administration

Figure 4-1. Creating a new BDC Service Application

In the Create New Business Data Connectivity Service Application window, enter a new Service Application Name, Database Server, and Database Name, and choose the Database authentication mode. Set the Failover Database Server^[13] name, and choose an existing application pool or create a new one for the BDC service application to run in, as shown in Figure 4-2.

Figure 4-2. Configuring a new BDC Service Application

Once the new BDC Service Application is created, you can manage it through Central Administration

Figure 4-3. Manage BDC Service

You can manage BDC services using various settings, as Figure 4-3 shows. Let's discuss each of them. Notice that these menu items are contextual, based on the BDC View chosen.

There are three views available, BDC Models, External Systems, and External Content Types, each showing or hiding various options. Let's look at the options each view presents.

Based on the type of use and implementation, users can be categorized into two groups.

BCS tools are laid out so that each of these groups can take advantage of specific tools to accomplish their missions. For instance, as you can see in Figure 4-9, advanced solutions on the server side can be designed and developed using a custom .NET assembly connector to allow the use of external system data. At the same time, for a basic client solution, you can use Outlook, SharePoint Workspace, or Word.

Figure 4-9. Tools decision tree

Now that we've introduced the concepts, let's roll with some examples. But wait; there is one last important concept you need to thoroughly understand in order to proceed.

You learned about ECTs at a very high level earlier. Now, let's take a deep dive. Anything and everything that is accomplished using BDC services is with the help of external content types. You can consider ECTs as the backbone for the entire BCS system. They are the content types that bring the external data sources, along with their schema, access capabilities, and behavior, into thin and thick clients, as shown in Figure 4-10. External content types contain business objects and can be created using either SharePoint Designer or by Visual Studio 2010.

Figure 4-10. External content types bring external data sources to clients

Once you have ECTs, you can use them in many ways.

Now you are ready to do a simple exercise. We will do the example first the no-code way, then achieve the same result by writing code.

When an ECT is created, it is added to the BDC Metadata Store and can be accessed under BDC Metadata Store views, from which you need to manually configure the permissions related to the ECT. To do this, open Central Administration and from Application Management

Figure 4-17. Setting permissions on an External Content Type

In the Set Object Permission window (Figure 4-18), in the Add an account box, enter a user account or group and click the Add button. In the Permissions section, choose the set of permissions you'd like to assign to the selected user. Choose or leave the value for propagating permissions and click OK.

Figure 4-18. Setting object permissions to the external content type

Return to your SharePoint site and access the external list you created earlier. You should now see that list populated with Employee table details (Figure 4-19). To edit an item from the ECB menu on the EmployeeID, click on Edit Item. In the Edit Item window, change any value and click on Save, which results in a data update. Similarly, you can perform other operations, such as View Item, Delete Item, as well as create a New Item. Now, why do you have an ECB menu on the EmployeeID column? It's because EmployeeID was chosen as the identifier in this example.

Figure 4-19. Employee details loaded in an external list

Note that in the example, the logged-in user is an Administrator who has the necessary rights on the SQL backend and so was able to view data. However, this will not be the case with every logged-in user. So do you end up giving access to every user of your database? Not a recommended practice.

You will have to set up an unattended service account that has access to the database and will impersonate the logged-in user. Secure Store Service comes to your rescue in such cases. As discussed in previous chapters, SSS replaces single sign-on and allows you configure an unattended service account to avoid authentication issues.

In this section, you'll learn how to configure ECT data connections with the Secure Store Service application. The steps are similar to those you performed in Chapter 2. You can either skim through this section or skip it if you want to.

Launch SharePoint Designer and open your SharePoint site. On the Site Objects menu choose External Content Types and select the ECT you created (EmployeesList_From_HR). In the external content type manage settings window, click on either the Edit Connection Properties button on the ribbon in the Connection Properties section, or click on the External System link in the External Content Type Information Section.

In the Connection Properties windows, on the Default tab, choose Impersonate Windows Identity for the Authentication Mode. Enter the previously created Secure Store Service Application ID in the appropriate box as shown in Figure 4-20 and click OK.

Figure 4-20. Set Secure Store Service Application ID for ECT database connection properties

When a user with insufficient permissions tries to access the external list, he will be presented with a link to authenticate, as shown in Figure 4-21.

Figure 4-21. Authenticate with credentials

On clicking the link, the user will be presented with window for entering his credentials (Figure 4-22), and on successful validation, he will have access to the backend system data.

Figure 4-22. Manage credentials for SSS target application

Now that you have configured ECT with the SSS Application ID, users can access external data via an external list in SharePoint without any issues.

Did you realize that you haven't seen or modified any XML file as you used to do in MOSS 2007? Do you now agree how simple and easy it is to create an ECT in SharePoint 2010? But wait! What if the data source is not a database but is actually a service that is delivering data, and this is the most common scenario your enterprise bus will have? If you use the tools decision tree (refer back to Figure 4-9), you'll see that for advanced solutions, you need to write a custom .NET assembly connector.

As mentioned earlier, you can also access and use the ECT you created from the .Net assembly. Launch SharePoint Designer and use the Open Site option to open your SharePoint site. From the Site Objects window, click External Content Types. In the External Content Types window, you'll see the list of available ECTs, including the one you created earlier, as shown in Figure 4-32.

Figure 4-32. External Content Types in SharePoint Designer

Click on Entity1 to load the Entity1 external content type. From the Lists & Forms section on the ribbon, click the Create Lists & Form button as shown in Figure 4-33. Also note the operations available with the ECT in the External Content Type Operations section.

Figure 4-33. Creating an external list from an exisiting ECT using SharePoint Designer

In the Create Lists and Form for window (Figure 4-34), select Create New External List and enter a value for List Name. Leave the other default input values and click OK. If there are more than one model definitions available in the .NET assembly, you can choose one from the System Instance drop-down.

Figure 4-34. Creating a new external list

Return to your SharePoint site and find the list with the name (External List Created from SPD ECT) you provided in the previous step. Launch the external list and you'll see this new list with view and edit functionality; it is similar to the lists created using other techniques.

Again, even with Visual Studio and writing a custom .NET assembly connector, you haven't seen or edited any XML file so far, either while creating the model or when defining your entity. This I'd say is one of the biggest advantages when compared with the BDC in MOSS 2007.

Another important improvement of this version over the prior is in the runtime object model. There have been phenomenal changes and more capabilities added in the new version. Let's take a look at them.

Launch your SharePoint site and create a document library from Site Actions

Figure 4-37. Creating an external data column

Under Additional Column Settings, select the external content type you created earlier in this chapter. Select all fields or only those you wish (Figure 4-38) and click OK.

Figure 4-38. Choosing fields for the external data column

Return to the document library and upload a document by choosing the Add document link and clicking OK. Note that the document can't be empty. Also, before you save the document to the library, select data from External Items icon. Then enter text in the Title textbox and click the Save button as shown in Figure 4-39.

Figure 4-39. Selecting an external item

The selected data item is added as an entry to the document library with the columns you chose in previous step. From the document's ECB menu, click Check Out, then click OK in the pop-up window. Click on the document to launch the document in Office Word 2010.

Go to Word

Figure 4-40. Choosing document properties under Quick Parts

After you choose the values you're interested in, the final document should look something like the one in Figure 4-41.

Figure 4-41. Document properties displaying data

You can now save the document, check it in, provide any version comments from the Backstage window of the ribbon, and then close the document.

Another important feature of BCS is its integration with SharePoint Workspace 2010. You can work in offline mode and sync back data to SharePoint lists and libraries using SharePoint Workspace.

SharePoint Workspace is the next generation of Office 2007 Groove. SharePoint Workspace is a client application that not only enables access to SharePoint Server 2010 and SharePoint Foundation 2010 document libraries and lists, but also can be used in offline mode. We won't cover it in detail here, but you can find more information at http://technet.microsoft.com/en-us/library/ee649102.aspx. Now we'll discuss how to integrate BCS external lists with SharePoint Workspace.

Launch SharePoint Workspace 2010 on your computer. The first time you start Workspace, the Account Configuration Wizard will launch, as shown in Figure 4-42.

Figure 4-42. SharePoint Workspace Account Configuration Wizard

You can create a new account using an e-mail address or Account Configuration Code. Once the account is created, the Workspace client will launch. If you have an account file (.grv), you can also restore an existing account using the wizard. From the Workspaces section of the Home tab on the ribbon, select New SharePoint Workspace. In the New SharePoint Workspace window, enter your SharePoint site URL location as shown in Figure 4-43. You can choose and configure a library or list and add others later.

Figure 4-43. Setting up a new SharePoint Workspace

SharePoint Workspace will begin syncing your SharePoint site lists to your computer. You will be prompted with couple of approval (Figure 4-44) and information (Figure 4-45) screens. After successful completion, click the Open Workspace button.

Figure 4-44. Verifying the publisher

Figure 4-45. Microsoft Office Customization Installer

Once the synchronization (Figure 4-46) is successful, Workspace opens and displays the newly connected SharePoint site in the Workspaces list, as shown in Figure 4-47.

Figure 4-46. Synchronizing the SharePoint Workspace to the computer

Figure 4-47. The new SharePoint Workspace

Double-click the name of your server under Workspaces to launch content from all available document libraries and lists (or the selected lists). In the External Lists section, click on the external list you created earlier in this chapter. This will retrieve and display list items as shown in Figure 4-48. If you like, you can choose to disconnect your workspace from the active connection to SharePoint and use the data in offline mode. You can perform all CRUD operations on the list and, when you are ready, you can sync data back to the SharePoint list. If you work in online mode and some changes happen on the server, you will be notified about the update and prompted to refresh the data.

You can search for an item in the list or double-click on a specific row item to edit it. In the Edit Item window (Figure 4-49), you can change values and then click the Save & Close button in the List Item section on the ribbon.

Figure 4-48. The Workspace window connected to a SharePoint list and data

Figure 4-49. Editing a list item in Workspace

Here is an important factor to keep in mind. In both offline and online mode, if data for one item is changed both locally and externally, there will be a conflict between the local and external system values. The conflict will be displayed as an icon on the row column, and when you open the item, you'll see a Resolve option on the ribbon on the Error Tools menu. To proceed with any changes, you'll have to resolve the conflict by choosing to keep either the external system value or the local value, as shown in Figure 4-50.

Figure 4-50. Data conflict between local and server versions