The following article is published in the online digital magzine of the SDN for SharePoint. The complete article can also be read below:
Many organizations use a combination of different systems to incorporate their business data and processes. Depending on the need one or more different back-end systems are used to store and maintain business data. Employees will need to have access to all those different back-end systems to get information they need. What if you could expose all that information through one Portal? This will cut maintenance costs and support to employees because they are using only one entrance to reach their information. It also enables employees and suppliers to get access to business data from outside the company through a computer or mobile device while your back-end systems are not exposed to the outside world. SharePoint 2010 has a solution for this called Business Connectivity Services.
SharePoint as an integration platform
SharePoint is often seen as only a collaboration and Portal product on the market. SharePoint is mostly used as an internet facing website, extranet or intranet. But with the release of Microsoft’s newest version of SharePoint called SharePoint 2010, this will absolutely change. Today SharePoint 2010 can best be seen as a platform product which exists of many capabilities on many levels like social media, record management, collaboration, document management and more. The power of SharePoint is combining all those capabilities. One of them is integrating and exposing different business data and processes. The integration with Office and working offline and its ability to connect to external data sources has been improved dramatically.
With Microsoft Office SharePoint Server 2007, also called MOSS 2007, you were already able to connect to external data sources. But there were some major disadvantages. Building up a connection had to be done completely in Xml. Secondly data was only viewed and could not be edited without creating custom coding. There are some great tools around like “BDC Metaman” but still for a lot of people using Business Data Catalog (BDC) was a nightmare.
With SharePoint 2010 this has been improved. It actually gets to be fun creating connections to external data. SharePoint 2010 allows you to expose very easily business data from back-end systems. Business Connectivity Services (BCS) will bring different worlds of information together. And it gets even better. You are able to delete, update and even add new items to that back-end system. Don’t get me wrong. There are still some challenges to overcome and if you want to connect to something which is not supported OOB; you will need to write code.
BCS Architecture
The BCS architecture exists of two parts mainly, exposing external data through lists in SharePoint 2010 and exposing external data through parts in an Office client. BCS uses External Content Types (ECT) and External lists to expose data inside SharePoint 2010. From their using VSTO packages data is exposed through External Business Parts in an Office client.
Figure 1: BCS Architecture
External data can be accessed through a SQL Server connection, WCF, .NET Connector or your own written custom connection. SharePoint 2010 supports OOB a number of systems which can be exposed through its environment without writing any line of coding. Examples are SQL Server and SAP.
When business data from external systems is exposed in SharePoint 2010 as a list it is nothing more than a view on that data. The data itself is not stored in SharePoint 2010. But you are able to browse, view, change and even search through it. Before we go into exposing external data there are some new types of components in SharePoint 2010 we need to understand better.
External Content Types
Solutions based on BCS rely on the use of External Content Types to expose and integrate external data into SharePoint. The External Content Type is just like a Content Type and describes the schema and data access capabilities of an external data source and its behavior inside SharePoint and Office clients. It uses a database connection, web service, .NET connector or custom code to connect to the external data source. External Content Types are created in SharePoint Designer 2010 or with Visual Studio 2010. It is also possible to define an External Content Type in an Xml file and import it into the Visual Studio environment.
External Lists
The data exposed through an External Content Type is shown in an External list. The External list looks just like an ordinary list in SharePoint. Some options you have on ordinary SharePoint lists are not available to the External list. For example adding extra columns, changing existing columns and using Workflow is not allowed.
Associations
It is also possible to associate External Content Types to enrich your data. Think of showing the orders a customer has made. SharePoint Designer 2010 supports only two ways of creating associations. The first one is a one-to-many association based on a foreign key and the second one is self-referential associations based on a foreign key.
With Visual Studio 2010 you have also the ability to create one-to-many associations and many-to-many associations without a foreign key. And it is even possible to create multiple External Content Type associations when multiple External Content Types are required to identify a unique item.
Solutions
When we talk about exposing external data using BCS we have two options. The first is called “simple” solution and is created using SharePoint Designer 2010. It delivers an OOB UX solution on thin and rich clients like Outlook and Groove based on External lists. Connections are only made to existing back-end integration services (SQL Server, SAP …) or other simple databases using simple BDC models (few ECTs and simple associations).
Secondly is called “Advanced” solution and is created using Visual Studio 2010. You will be using this type of solutions when custom UX and data integration on thin and rich clients is needed or when the need is for custom back-end connectivity through .NET objects. This will often involve complex BDC models (many ECT’s and complex associations). Such a solution is built from scratch or based on an import from an export of a “simple” solution made in SharePoint Designer 2010.
BCS and SharePoint Designer 2010
So, how does it work? Actually using SharePoint Designer 2010 will allow you to create a Business Connectivity Service solution without writing any line of code. In our example we use the “Customers” database of a company called Contoso. This database contains customers, orders and the products they have ordered. We will be using an intranet website called intranet.contoso.com. We start SharePoint Designer 2010 and open this intranet website. At the left navigation under “Site Objects” we choose the tab called “External Content Types”. The ribbon gives us the option to add a new External Content Type as can be seen in figure 2.
Figure 2: Setting up an External Content Type
The name “Customer” is entered and de link behind “External System” is clicked on. This will allow us to choose an external system. We connected to the database “Customers” on the local server under the current users’ identity. In a real life scenario you would not be using the current user’s identity but a separate account.
Figure 3: Choose operations from the connection view
The view shows us all available routines, views and tables in the selected database. By right-clicking on the “Customer” table, see figure 3, and selecting the option “Create All Operations” the operations on that table are generated for you automatically. These actions will startup a wizard which allows you to select the fields from the table and specify the primary key as the identifier which can be seen in figure 4. It is also possible to add some filter parameters but we will not do that for now.
Figure 4: Define the parameters for the operation
In the wizard are messages and errors shown at the bottom of each step which guide you through the process of setting up the operations. These messages and errors tell you what you have to change or to correct in that step. It is also possible to create each operation separately. Remember that both operations “Read list” and “Read Item” are needed for browsing and viewing when you want to create an External list based on this External Content Type. The others are needed for deleting and updating existing items and adding new items.
Figure 5: Overview of an External Content Type
Finally we need to save the External Content Type which is shown in figure 5. The configuration data has been stored into the Metadata store of the Business Data Service. We will need to do the same for the “Orders” table. If everything went well you will now have two External Content Types called “Customer” and “Order”.
We will need to create two External lists based on each of the External Content Types to view the data inside SharePoint 2010. These lists will appear under “All Site Content” of the intranet website just like ordinary SharePoint lists.
It would be nice if we were able to view the profile of a customer and the orders he has made. For that we need to create an association. We go back into SharePoint Designer 2010 and select the External Content Type called “Order”. We need to add from there an association to the External Content Type “Customer”. An association is just like an operation and needs to be added under the ”Operations Design View”.
You will need to start creating the association from the same table as you did for the other operations. Again right-click the “Orders” table in the “Data Source Explorer” and select the option “New Association”. We change the name to “GetOrderForCustomer” and browse to the Related External Content Type called “Customer” and associate the primary key with the foreign key as can been seen in figure 6.
Figure 6: Setting up an association
The next step, figure 7, is defining the input parameters. Because we need to get the orders based on the customer, the field “CustomerID” is mapped to the identifier. By doing this the association will understand the relation setup between both External Content Types.
Figure 7: Define the input for the association
Finally we press the finish button and the association is created. We will need a Profile page to see the effect of the association. But before we can use a Profile page to show our association, we will need to specify the URL where the Profile page is going to be hosted. This is done through the configuration of the Business Data Service in Central Administration. This configuration, see figure 8, is called “Configure External Content Type Profile Page Host”.
Figure 8: Configure the External Content Type Profile Page Host
The Profile page can now be created via SharePoint Designer 2010 or directly through the administration of the Business Data Service Application. Whenever you decide to change the association you need to upgrade the Profile page. The previous Profile page will be overwritten. A URL is generated and expects an identifier of the customer.
The Profile page contains several Business Data Web Parts. A Business Data Item Builder Web Part, which is invisible, is used for retrieving the input parameter in the query string of the URL. A Business Data Item Web Part for showing the customer information and a Business Data Related List Web Part for showing the orders are both connected to the Business Data Item Builder Web Part. By calling the generated URL with a customer identifier will show the customer and the orders he has made. An example is seen in figure 9.
Figure 9: Customer profile page
As you can see we expose related data from a “simple” database model into SharePoint 2010 External lists while not writing any line of code. You can imagine how easy it is for you and your customers to expose business data using SharePoint Designer 2010.
BCS and Visual Studio 2010
But what if you want to do for example a many-to-many relation between objects? SharePoint Designer 2010 will not offer you directly an OOB solution. And what if you want to influence the way how data is returned and even want to put some business logic in between? You could use a WCF service or database views and connect from SharePoint Designer 2010 directly to it. But in most cases you will need more freedom. In those cases Visual Studio 2010 will do the trick.
In the following example we will use the relation between students and lessons at a school. Each student will follow multiple lessons and lessons are followed (in most cases) by multiple students. To store such information, see figure 10, you would use three tables in your database like one table for storing students and one table for storing lessons and one for storing the combination between them.
Figure 10: Database diagram of many-to-may relation
In this example I have translated the three tables to two data classes called “Lesson” and “Student” and one manager class called “SchoolManager”. Figure 11 shows the class diagram of these classes. The “Lesson” class contains a collection to “Students” classes. The “SchoolManager” class is a singleton containing all “Student” and “Lesson” classes and a method for returning a collection of “Lessons” classes based on the identifier of the student. There are other ways to resolve this into a manageable solution but for now we used this one.
Figure 11: Data classes overview
In a real life example your data classes would access the database. Instead of using a database we have an initializer method in the “SchoolManager” class which generates some dummy data for us.
Visual Studio 2010 offers us more project templates for SharePoint 2010 than for MOSS 2007. We will be using the “Business Data Connectivity Model” template for this project. During the creation process of the project a URL for a local site is requested for debugging. The site behind this URL will also be used when deploying the solution. The only option further is “Deploy as a farm solution”. You will not be able to deploy this solution as a sandboxed solution. The first time when the project is created it contains an example BDC model including an entity. Further the project contains a Farm feature to install the BDC model in SharePoint 2010.
Figure 12: Solution explorer view of BDC project
The “Solution Explorer”, see figure 12, contains a leaf called “BdcModel1”. This leaf contains the BDC model file with extension “bdcm”. When double clicking on this leaf or the model file will open the BDC model diagram. This is a visual representation of your BDC model. We remove the example entity from the diagram and add two new entities. The entities are renamed to “Lesson” and “Student”.
BDC diagram model
Before we continue some explanation is needed to understand how this diagram works. The diagram is stored as an Xml file. This Xml file is translated to an External Content Type by the Business Data Connectivity Service in SharePoint 2010. For each entity a service class is generated. The diagram allows you to specify identifiers and methods per entity. For each method like Blank, Creator, Deleter, Finder, Specific Finder and Updater which is added to the entity, a static method is generated in the service class. These methods are called by for example the External List to view, edit, delete or update items returned by the External Content Type. The Xml file describes all identifiers and methods per entity.
It is very important that the Xml file contains the correct information otherwise it will not work. You will notice that when playing around with the diagram some inconsistency could appear between the diagram and the service classes which mean that you could end up updating the Xml file yourself. The structure of the Xml file is not that difficult to understand but we want to avoid updating it.
Defining properties and methods
As you can imagine the actual classes, WCF service or other means providing the data is called from those service classes. This means that you are free to define your data layer in your application as it suits you. In our example we have the three classes providing all the data we need.
At first we define per entity an identifier. The identifier will be used by the associations between entities. In our case we define “lessonId” and “studenId” both based on a “System.Int32” type. Right click on the entity and choose “Add Identifier”. Use the properties window to change the name and the type.
Secondly we need to define the Finder method and Specific Finder method which are the “ReadList” and “ReadItem” methods. You will recognize their names from the example earlier with SharePoint Designer 2010. Click on the entity to make it the active one and go into the BDC Methods Details window to add methods. You will notice that it allows you to add one of the predefined methods. By adding such a method, Visual Studio 2010, renames it accordingly to “ReadList” and “ReadItem”.
Per method you are able to specify “In”, “InOut”, “Out” and “Return” parameters using TypeDescriptors. At first we use the BDC Methods Details window and properties window to specify the TypeDescriptor for the “Return” parameter for both methods. The “ReadList” method should return an “IEnumerable” of the data class as follow:
System.Collections.Generic.IEnumerable’1[School.Data.Lesson, BdcModel1]
The “ReadItem” method returns an instance of the data class as follow:
School.Data.Lesson, BdcModel1
The “ReadItem” method also needs a TypeDescriptor with an “In” parameter to specify which item needs to be returned. The identifier of the TypeDescriptor is set to the identifier of the entity itself.
Figure 13: Data class properties nested with TypeDescriptors
Using the BDC Methods Details window and the properties window your service class is updated accordingly. But now comes the hard part. You will need to use TypeDescriptors to describe the class and its properties returned. At this moment only your class is described by a TypeDescriptor. The BDC diagram model has no knowledge about your data class’s properties. You have to tell it what it will expect there.
You need to do this in the Xml file itself. To view the diagram as an Xml file we need to close the diagram and reopen it using right click on the diagram in the solution explorer and use the option “Open with…” to open the same diagram in an Xml editor. Figure 13 shows you what Xml is needed to add to the TypeDescriptor of the Entity “Lesson”.
Now close the Xml editor and reopen the diagram to check if it isn’t broken. Repeat this step for each method in each entity. The BDC diagram model should now be the same as in figure 14.
Figure 14: BDC diagram model of two entities
When opening the service class for the “School” entity, see figure 15, you will see that it generated two methods accordingly to your settings made in the BDC Methods Details window and the properties window. We use our “SchoolManager” class to get the data. You will need to do this also for the “Student” entity.
Figure 15: Generated static members in service class
Before we continue and setup an association between both entities we build and deploy the solution first. After the deployment of the solution we go into “Central Administration” and open the “Business Data Connectivity” service under “Manage Service Applications”. The two entities appear as External Content Types under the service as shown in figure 16.
Figure 16: Business Data Connectivity Service Application
There are now two ways to create an External list based on the External Content Type. We could do this in SharePoint 2010 or via SharePoint Designer 2010. Be careful here. If your BDC model contains an error, it can in some cases still deploy. Creating an External list based on an incorrect External Content Type will result in a created list without any forms and thus hard to remove. In SharePoint 2010 you need to rely on error messages in your log file. While SharePoint Designer 2010 gives you an exact error in a popup window and even let you easily remove the list.
We open the web application using SharePoint Designer 2010 and navigate inside “Site Objects” to “Lists and Libraries”. An option “External List” in the ribbon allows us to create an External list. Select the External Content Type “School.BdcModel1.Lesson”, enter a name and description, and generate the list. The list appears in “Lists and Libraries” under “External Lists”. Do the same for External Content Type “School.BdcModel1.Student”. To view the data presented by the External Content Type in the External List go into your web application and open the list itself. The External list will look similar to figure 17.
Figure 17: Output of the External List named “Lessons”
Creating the association
Just like the first demo using SharePoint Designer 2010 we would like to have on both lists a profile page showing its own and related data. Go back to Visual Studio 2010 and open the BDC diagram model. Right click the “Lesson” entity and choose “Add Association…”. This will open the Association Editor dialog shown in figure 18.
Figure 18: Association editor
Uncheck the “Is Foreign Key Association” and add two association methods. The first association method is of type “AssociationNavigator” created in the entity “Lesson” and is renamed to “GetStudents”. The Second association is of the same type “AssociationNavigator” created in the entity “Student” and is renamed to “GetLessons”. Both your service classes will be updated accordingly. A separate method will appear in your service class like in figure 19.
Figure 19: Generated static member for association in service class
Some code is added to both members, to retrieve the students based on the identifier of the lesson, and retrieve the lessons based on the identifier of the student. Finally the BDC diagram model is finished and looks similar to the one in figure 20.
Figure 20: Final BDC diagram model
Again we build and deploy the solution to our web application. Go back into “Central Administration” again and open the “Business Data Connectivity” service under “Manage Service Applications”. Right click on the “Lesson” External Content Type and choose the option “Create/Upgrade Profile Page”. This will generate the Profile page. The URL to the Profile page is shown in the last column of this view. Do the same for the “Student” External Content Type.
Finally, see figure 21, we can view the Profile page which is the same as in the previous demo. The Profile page shows the related lessons for the student, and the related students for the lesson depending on which Profile page you are. You need to specify an identifier to each of the Profile pages to get results.
Figure 21: Profile page of the student
Visual Studio 2010 gives you more flexibility and allows you to implement complex and advanced solution models.
Conclusion
Business Connectivity Service is a great way for exposing external data and back-end systems to your users in a trustful environment. It has been improved dramatically and allows you to expose data very easily. SharePoint Designer 2010 can be used for “simple” solutions to expose data without writing any code, while Visual Studio 2010 will offer you more flexibility to your business logic and allows you to implement “advanced” solutions.
Recent Comments