Welcome back.  This is part two of an eight part series on BDC.  In the first article, I gave you an overview of the BDC from a business standpoint and introduced its architecture.  In this article, the emphasis will be on the Application Definition File (or ADF).  We’ll make sure you have a good understanding of the role it plays, how it works, and a give you a good overview of the XML schema that is used.  We’ll then look at various ways you can create these ADFs, show you how to publish them into your farm, and also cover how security on the ADF is managed.  We have a lot to cover, so let’s get started.

ADF Overview

Since we understand the BDC to be a middle man between the user interface and some back-end system, we need some way of mapping how the back end system is structured.  This mapping can then be consumed from the various interfaces such as Web Parts and searching that we covered in the last article.  Since BDC is designed to pull data from virtually any relational database or SOAP-based Web Service, you’d correctly guess that the ADF must be flexible and generic in nature.  As mentioned already, the ADF is an XML file with a specific schema and its concept can be likened to a logical database diagram.  By logical, I mean that it does not have any vendor-specific features such as data types.  Keep in mind that it must also be flexible enough to connect to Web Services as well, so it can’t be too database-centric as well.

XML makes good sense for the definition as it is a declarative way to define the back-end in a generic way.  By being declarative, and not done programmatically through code, it helps ensure that we have a loosely-coupled design.  This is very important as front-ends and back-ends are likely to change, and we want to make sure the maintenance effort spent on this middle layer is minimized.  As we’ll see later, having it XML based also makes it easier to automatically generate this definition.

In Figure 1 below, you can see a conceptual overview of how the ADF is used.  The browser makes a regular request where BDC data is needed (e.g. a BDC Web Part, Search, etc.).  The MOSS web front end receives the request, and as part of its processing, hands the BDC request off to the Object Model. The Object Model (also known as the API or Application Programming Interface) gets the definition for this ADF from its catalog.  With the details on the ADF known, the Object Model requests the data from the database or Web Service.  The response is returned and then formatted as appropriate for delivery back to the browser.

Figure 1: Data flow overview with BDC

To best understand the ADF, it helps to have an understanding of some of the terminology used.  This will relate directly to structures found in the XML definition.  Those with a database background (and also those developers with knowledge of ADO.NET) should pick these concepts up right away.

Line of Business.  Line of business (or LOB) is just a term that refers to a back-end system.  As you’ll see shortly, a single ADF is comprised of one or more LOB instances.

Entity.  An entity works like a table.  The entity term is also used when designing a logical database diagram, and the concept is the same.

Method.  A method is how we request data (or query) from an entity.  For a database, it is usually a SQL select statement or stored procedure.  For a Web Service, it would be a Web Method.  Each entity supports multiple methods.

Parameter.  Parameters are the inputs and outputs to a method.  Each parameter has a name and a .NET data type (e.g. System.Int32 or System.String).

Filter Descriptor.  Filter descriptors provide an easy way for users to filter records (rows) found in an entity.  It is mostly commonly used with the Business Data List Web Part.  For example, you might want to allow users to quickly find a company by its name.

Association.  Associations are how relationships are represented.  For example, an Employee entity might have an association with a VacationRequest entity.  For a database, these are normally used on child tables that have a foreign key.

Action. Actions are used with entities and allow you to link to external applications or custom SharePoint pages for additional processing.  These are commonly used in write-back situations, and we will look closer at these in the final article in this series.

The ADF Schema

Now that you have a pretty good idea of what the ADF does and some of the vocabulary, let’s go ahead and examine the XML Schema for an ADF.  (Note: this is not intended to be a detailed discussion; for a deeper look, please consult the MOSS SDK (Software Development Kit) here: http://msdn.microsoft.com/en-us/library/ms563661.aspx.)  One of the screen shots from this SDK is very helpful in understanding the high-level hierarchy of the ADF.  You can see it here:

Figure 2: ADF Schema Overview

LobSystem is the root element in the ADF file.  It contains attributes to the required namespaces, specifies whether it is a Web Service or Database, and sets the Name of the ADF.

LobSystemInstance is where you define your connection in the ADF.  Even though the XML schema looks like it supports multiple connections, you can only have a single connection defined in your ADF.  Through Property child elements, you define the connection string to this data source.  Those with ADO.NET experience will quickly recognize this example below as a Windows-integrated login to a SQL Server.

Figure 3: LobSystemInstance

The details of authentication is a complex subject, and we’ll just cover the basics here.  When connecting, you have four options when it comes to authentication.

1) PassThrough.  PassThrough can be used to authenticate the user in one of two ways, using Windows credentials or database credentials. With Windows (shown in Figure 3 above), the client’s Windows credentials are forwarded to the back end system.  This can be used for both databases and Web Services.  The problem with this, however, is that unless you’re using Kerberos (or your Web Service/database server is on the same physical machine as your SharePoint web front end), this will fail.  This is due to Windows NT Lan Manager’s (NTLM) inablility to delegate.  With database credentials, you use a database-specific username and password to log in.  This can only be used against a database server, not a Web Service.

2) RevertToSelf. This means the credentials of the Application pool is used to authenticate the user.  This will solve the the delegation problem from above, but all users will effectively use the same login on the back end.

3) WindowsCredentials/RDBCredentials.  These are used in conjunction with Single Sign On (SSO).  In this model, the user’s credentials (either a Windows login or a database-specific login) for the external system are pulled from the SSO database.

4) Credentials.  This is only used if you are connecting to a Web Service and cannot use Windows authentication.

For more details on authentication, I recommend you start your reading here:  http://msdn.microsoft.com/en-us/library/ms566523.aspx.

Within LobSystemInstance, each entity for this connection is defined.  The entity itself is not very interesting as it just defines a friendly name by which users will reference it.  Within each entity, you define an identifier and methods.  An identifier is the primary key, or the column(s) from the entity that can be used to uniquely identify each row.  These are very important as they are used in a number of ways.  Often, these are ID columns, such as CustomerID as shown here.

Figure 4: Identifier

Also part of each entity are the methods.  These are really the heart of the ADF as it defines the actual data that will be pulled.  It is where you specify a select statement (e.g. select * from table), a stored procedure, or a Web Method if it’s a Web Service.  Since there may be multiple ways to query data from an entity, you can have multiple methods.  Here is a simple example with returning a few columns from a table named Customers.

Figure 5: Method

Within a method, you may want to define one or more ways in which this method can be filtered by users.  This is used within the BDC Web Parts.  For example, for the list of customers above, imagine your company has hundreds of them.  How will your users easily find the one they are looking for?  Start by asking yourself what are the common ways they might want to lookup a customer—perhaps by Name, by Region, or by a Contact Name.  If this makes sense then you understand FilterDescriptors.  Here is a simple look at two FilterDescriptor entries that have been made.  The Name attribute you see here defines the friendly name that users will work with, so make sure this name makes sense to them.

Figure 6: FilterDescriptor

Now that you understand filtering, let’s go back to methods for a bit.  In the ADF, there are different types of methods, and the three primary types we work with are Finder, SpecificFinder and IdEnumerator.  Finder methods are used when you want to return one or more rows from an entity.  For example, find all customers who are in the North America region.  SpecificFinder is used to find a single row and is used in conjunction with the Identifier mentioned above. For example, find me the one Customer that has CustomerID = 42.  IdEnumerator is used to return the identifier value for each row.  In code terms, this would look something like Select CustomerID from Customers.  The BDC uses this when crawling for Enterprise Search integration.  You will find each method type defined within a MethodInstance.

Let’s move on to Parameters.  Parameters allow you to describe the exact input and output for methods.  Input parameters (Direction = “In”) are used with Filter Descriptors.  There are different types of output parameters, but the most common are Return parameters (Direction = “Return”).  These are used to describe each column that is returned from a method.  For each parameter, you define the friendly name and its .NET-matching data type.  In Figure 7 below, we see the two parameters that are defined for our two FilterDescriptors above.  These are input parameters as shown here:

Figure 7: Parameter

Last but not least, let’s cover Associations.  We use Associations to define the actual relationships that exist in the external system.  BDC can directly work with one-to-one and one-to-many types of relationships.  When you define an Assocation, you define the SourceEntity (i.e., the parent or primary) and the DestinationEntity (i.e., the child or foreign).  You also must point to a method in the child entity that returns all the child rows based on a single parent row.  This method will usually “join” these two tables together.  In SQL terms, it would be something like

Select * from Orders
join Customers on Orders.CustomerID = Customers.CustomerID
where Orders.CustomerID = 42

Here is an example on how an Association is defined in the ADF:

Figure 8: Association

Creating ADFs

In the small snippets above, you can probably see that the XML syntax won’t likely roll off your fingertips.  It takes time and practice to get good at writing them.  These can be created in any text or XML editor, including Visual Studio.  If you will be using Visual Studio, I recommend you import the BDCMetaData.xsd schema file so at least you get IntelliSense.  You can follow the guidance on how to set this up from SharePoint MVP Andrew Connell here.

Fortunately, there are tools out there that can reverse engineer the database (or Web Service) and generate most if not all of the ADF file for you. Even if you only have to write one ADF, these tools may be worth it.  The three main ones available to you are

• BDC Definition Editor
• MOSS BDC Design Studio
• BDCMetaMan

BDC Definition Editor is free and comes from Microsoft.  You can install it after you install the MOSS SDK.  Sadly, with this tool, you get what you pay for.  It doesn’t generate Finder methods, crashes quite a lot, and requires you to install SQL Server 2005 Express (even if you have a full SQL Server instance installed).  It’s certainly better than nothing but not recommended for regular use.

MOSS BDC Design Studio is a commercial product from Simego.  The latest version 2.0 has a nice wizard interface when setting up each entity.  It can be used to generate ADFs on common databases like SQL Server and Oracle as well as Web Services. It is reasonably priced at $200 for a single license.

BDCMetaMan from Lightning Tools is the top-of-the-line and de-facto standard for generating ADFs.  It is well written and fully featured.  It is also the most expensive at $1200 per license but well worth it if you use it enough.

Loading the ADF into MOSS

Once your ADF file has been generated, it needs to be loaded into your SharePoint environment.  This is done through your Shared Services Provider (SSP).  To get there from Central Administration, click on your SSP name in the Quick Launch menu on the left, then click Import application definition in the Business Data Catalog section.  Loading it is as simple as browsing to the file and clicking the Import button.  When you upload the ADF, it will validate the XML file. It will then create entries in your SSP Database for each LobSystemInstance and Entity.

Once the ADF is loaded, you might want to take a look at its structure.  Just click on View applications from the main SSP screen, and then navigate down through your named ADF and into each entity.  With the exception of custom actions, everything else is read only, so you can’t really make any changes.

Setting Permissions

When you’re done poking around, you’ll probably want to set permissions on your new definition.  Permissions apply to these levels in the ADF hierarchy: LobSystem (the whole ADF), Entity, Method and MethodInstance.  This means you can secure at a high level such as each ADF, or very granularly such as each Method within an Entity.  Unfortunately, the administration GUI only lets you set permissions down to the Entity level, so this is the lowest level that most people work with. You can also set permissions at a higher level (even the whole BDC) and copy these down to lower levels.  When setting permissions, you have four permissions to choose from.  Each is introduced here:

• Edit.  This permission allows you to delete or add new ADFs into BDC.  You would grant this to those who would be uploading new ADFs into your SSP.
• Execute.  This allows you to execute Method Instances.  If you apply this permission at the entity level, it applies to all methods and method instances for this entity.
• Select in Clients.  This defines whether you can see the entity when configuring BDC Web Parts or adding a BDC column to a list.
• Set Permissions.  This allows you to change permissions for other users at the various levels as described in this section.

That said, the common approach is to give users both the Execute and Select in Clients permission for all entities that they will be working with.  As standard with SharePoint, you can assign permissions to Users or Groups.

With that, we bring this Part Two article to a close.  By now, you should have a solid understanding of how BDC works and how to create, load and secure your ADF entities.  In the next session, we’ll focus more on creating ADFs for Web Service systems.  From there, we’ll then move over to the UI and how to leverage the value that these ADF entries give you.