HOW TO: Programmatically create a SharePoint 2010 External Content Type

By
Todd Baginski
– December 1, 2009
I demonstrated how to make an external content type programmatically during the SPC 405 – Business Connectivity Services Runtime and Object Model Deep Dive session at the 2009 Microsoft SharePoint...

I demonstrated how to make an external content type programmatically during the SPC 405 – Business Connectivity Services Runtime and Object Model Deep Dive session at the 2009 Microsoft SharePoint Conference. Since the conference I have received a couple of dozen emails about the this topic. Most of the emails ask for the code samples and an explanation of the code, and at least half of the emails inquire about when and why you may need to create an external content type programmatically. Because this is obviously a hot topic I decided to write it up in a blog post.

Sample Business Context

To begin, let’s take a moment to understand what types of business situations lend well to programmatically creating external content types. The diagram below illustrates a common business scenario where creating external content types programmatically makes a lot of sense.

For example, an engineer in the engineering department creates a specification for a new product. The purchasing department then contacts multiple suppliers to determine which suppliers the company will work with to secure the parts and components used to create the new product. The new product and the suppliers are entered into the company’s ERP system. Customer service representatives who support the product need quick access to the supplier data in the ERP system. The customer service representatives have not been trained to use the ERP system, however they are familiar with web based applications. To provide the customer service representatives with real time access to the product and supplier information in the ERP system an external content type may be automatically created when a new product and its associated supplier information is entered into the ERP system. The external content type will correspond to the new product and supplier information and provide the ability for the customer service representatives to access the data within a SharePoint web site.

image

The Solution

To build on the example above the first thing that needs to be done is to hook the creation of a new product and associated suppliers in the ERP system to the creation of an external content type. There are several ways to do this and the appropriate method will depend on the system where the data is stored. Workflows, batch jobs, triggers, and event callouts may all be viable options. No matter which option is the best solution, eventually you will use the the Business Data Connectivity (BDC)administration API to automate the creation of an external content type in SharePoint 2010.

The BDC administration API allows you to make changes to the metadata in the BDC metadata store. This API is primarily found in the Microsoft.SharePoint.BusinessData.Administrationand the Microsoft.SharePoint.BusinessData.Administration.Client namespaces. The Microsoft.SharePoint.BusinessData.Administrationnamespace is used when running on the SharePoint server and the Microsoft.SharePoint.BusinessData.Administration.Clientnamespace is used when running on a client machine (machine other than a server in a SharePoint server farm). The Microsoft.BusinessData.MetadataModel and Microsoft.BusinessData.Runtimenamespaces are also used to support creating external content types.

As I mentioned earlier, the exact implementation you choose will depend on your environment. Instead of providing a click by click tutorial to create a console application, Web/WCF service, Web Part, or the other usual suspects, I’ll just focus on the code you need to get the job done and leave the implementation details up to you. This example creates an external content type for the Customers table in the AdventureWorks sample database. This is the same example I demoed at the 2009 Microsoft SharePoint Conference, however I updated it to run on Beta 2. You can download the complete source code, updated for SharePoint 2010 Beta 2, here.

Assembly references

To begin, create the appropriate Visual Studio project for your implementation and add references to the following assemblies.

Assembly Location Usage
Microsoft.BusinessData.dll C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14ISAPI Needed on both client and server
Microsoft.SharePoint.dll C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14ISAPI Solution runs on a SharePoint server
Microsoft.SharePoint.BusinessData.Administration.Client.dll C:Program FilesMicrosoft OfficeOffice14 Solution runs on a machine other than a SharePoint server (client)

This example assumes you are not running on the SharePoint server and uses the Microsoft.SharePoint.BusinessData.Administration.Client.dll assembly. Therefore, the Microsoft.SharePoint.BusinessData.Administration.dll assembly is not added as a reference.

References

The Code

The BDC administration API is easy to use. The classes, properties, and methods you use to create an external content type map directly to the metadata structures which define a BDC Model. If you are already familiar with BDC Models (Application Definitions in MOSS 2007 speak) this code will look familiar to you as well.

Using Statements

To begin, add the following using statements to your project.

using Microsoft.SharePoint.BusinessData.Administration.Client;
using Microsoft.BusinessData.MetadataModel;
using Microsoft.BusinessData.Runtime;

Connect to the BDC Metadata Store

Next, connect to the BDC Metadata Store where the definitions for external content types are stored. In this case I am connecting to the BDC Metadata Store associated with the site located at the following URL: http://dev1/sites/Team Site

AdministrationMetadataCatalog catalog =
AdministrationMetadataCatalog.GetCatalog("http://dev1/sites/Team Site");

Create the Model, LobSystem, and LOBSystemInstance

Next, create the Model, LobSystem, and LobSystemInstance. Pay special attention to the names you provide for these items because they appear in the External Content Type picker and your users will see them. See the screenshot near the end of this article for more details.

//Create a new customer model
Model customerModel = Model.Create("CustomerModel", true, catalog);

//Make a new Customer LobSystem
LobSystem awLobSystem =
customerModel.OwnedReferencedLobSystems.Create("Customer", true, SystemType.Database);

//Make a new AdventureWorks LobSystemInstance
LobSystemInstance awLobSystemInstance =
awLobSystem.LobSystemInstances.Create("AdventureWorks", true);

//Set the connection properties
awLobSystemInstance.Properties.Add("AuthenticationMode", "PassThrough");
awLobSystemInstance.Properties.Add("DatabaseAccessProvider", "SqlServer");
awLobSystemInstance.Properties.Add("RdbConnection Data Source", "DEV1");
awLobSystemInstance.Properties.Add("RdbConnection Initial Catalog", "Customers");
awLobSystemInstance.Properties.Add("RdbConnection Integrated Security", "SSPI");
awLobSystemInstance.Properties.Add("RdbConnection Pooling", "true");

Create the Entity

Next, create the Entityto represent the Customers table and define which column(s) make up the identifier for the Entity.

//Create a new Customer Entity
Entity customerEntity =
   Entity.Create("Customer", "AdventureWorks", true,
   new Version("1.0.0.0"), 10000, CacheUsage.Default,
   awLobSystem, customerModel, catalog);
//Set the identifier - CustomerID column
customerEntity.Identifiers.Create("CustomerId", true, "System.Int32");

Define the Specific Finder Method, Parameters and Type Descriptors

Next, create the specific finder Method, specify the query it will use, and define the input and output parameters associated with it. The specific finder Method returns exactly one row of data from the data source, given an identifier.

//Create the specific finder method
Method getCustomerMethod = customerEntity.Methods.Create("GetCustomer", true, false, "GetCustomer");
//Specify the query
getCustomerMethod.Properties.Add("RdbCommandText", "SELECT [CustomerId] ,
   [FirstName] , [LastName] , [Phone] , [EmailAddress] , [CompanyName]
   FROM[Customers].[SalesLT].[Customer]
   WHERE [CustomerId] = @CustomerId");
//Set the command type
getCustomerMethod.Properties.Add("RdbCommandType", "Text");
//Create the CustomerID input parameter
Parameter customerIDParameter =
   getCustomerMethod.Parameters.Create("@CustomerId", true, DirectionType.In);
//Create the TypeDescriptor for the CustomerID parameter
customerIDParameter.CreateRootTypeDescriptor("CustomerId", true, "System.Int32",
   "CustomerId", new IdentifierReference("CustomerId",
   new EntityReference("AdventureWorks", "Customer", catalog), catalog),
   null, TypeDescriptorFlags.None, null, catalog);
//Create the Customer return parameter
Parameter customerParameter =
   getCustomerMethod.Parameters.Create("Customer", true, DirectionType.Return);
//Create the TypeDescriptors for the Customer return parameter
TypeDescriptor returnRootCollectionTypeDescriptor =
   customerParameter.CreateRootTypeDescriptor( "Customers", true,
   "System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral,
   PublicKeyToken=b77a5c561934e089", "Customers", null, null,
   TypeDescriptorFlags.IsCollection, null, catalog);
TypeDescriptor returnRootElementTypeDescriptor =
   returnRootCollectionTypeDescriptor.ChildTypeDescriptors.Create( "Customer", true,
   "System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral,
   PublicKeyToken=b77a5c561934e089",
   "Customer", null, null, TypeDescriptorFlags.None, null);
returnRootElementTypeDescriptor.ChildTypeDescriptors.Create( "CustomerId", true,
   "System.Int32", "CustomerId", new IdentifierReference("CustomerId",
   new EntityReference("AdventureWorks", "Customer", catalog),
   catalog), null, TypeDescriptorFlags.None, null);
returnRootElementTypeDescriptor.ChildTypeDescriptors.Create( "FirstName", true,
   "System.String", "FirstName",
   null, null, TypeDescriptorFlags.None, null);
returnRootElementTypeDescriptor.ChildTypeDescriptors.Create( "LastName", true,
   "System.String", "LastName",
   null, null, TypeDescriptorFlags.None, null);

returnRootElementTypeDescriptor.ChildTypeDescriptors.Create( "Phone", true,
   "System.String", "Phone", null, null, TypeDescriptorFlags.None, null);
returnRootElementTypeDescriptor.ChildTypeDescriptors.Create( "EmailAddress", true,
   "System.String", "EmailAddress", null, null, TypeDescriptorFlags.None, null);
returnRootElementTypeDescriptor.ChildTypeDescriptors.Create( "CompanyName", true,
   "System.String", "CompanyName", null, null, TypeDescriptorFlags.None, null);
//Create the specific finder method instance
getCustomerMethod.MethodInstances.Create("GetCustomer", true,
   returnRootElementTypeDescriptor, MethodInstanceType.SpecificFinder, true);

Define the Finder Method, Parameters and Type Descriptors

Next, create the finder Method, specify the query it will use, and define the output parameters associated with it. The finder Method returns all of the rows of data from the data source which its query defines.

//Create the Finder method
Method getCustomersMethod = customerEntity.Methods.Create("GetCustomers", true,
   false, "GetCustomers");
//Specify the query
getCustomersMethod.Properties.Add("RdbCommandText", "SELECT [CustomerId] ,
[FirstName] , [LastName] , [Phone] , [EmailAddress] , [CompanyName]
FROM [Customers].[SalesLT].[Customer]");
//Set the command type
getCustomersMethod.Properties.Add("RdbCommandType", "Text");
//Create the Customer return parameter
Parameter customersParameter = getCustomersMethod.Parameters.Create("Customer", true,
   DirectionType.Return);
//Create the TypeDescriptors for the Customer return parameter
TypeDescriptor returnRootCollectionTypeDescriptor2 =
   customersParameter.CreateRootTypeDescriptor("Customers", true,
   "System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral,
   PublicKeyToken=b77a5c561934e089", "Customers", null, null,
   TypeDescriptorFlags.IsCollection, null, catalog);
TypeDescriptor returnRootElementTypeDescriptor2 =
   returnRootCollectionTypeDescriptor2.ChildTypeDescriptors.Create( "Customer", true,
   "System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089", "Customer", null, null,
   TypeDescriptorFlags.None, null);
returnRootElementTypeDescriptor2.ChildTypeDescriptors.Create( "CustomerId", true,
   "System.Int32", "CustomerId", new IdentifierReference("CustomerId",
   new EntityReference("AdventureWorks", "Customer", catalog), catalog),
   null, TypeDescriptorFlags.None, null);
returnRootElementTypeDescriptor2.ChildTypeDescriptors.Create( "FirstName", true,
   "System.String", "FirstName", null, null, TypeDescriptorFlags.None, null);
returnRootElementTypeDescriptor2.ChildTypeDescriptors.Create( "LastName", true,
   "System.String", "LastName", null, null, TypeDescriptorFlags.None, null);
returnRootElementTypeDescriptor2.ChildTypeDescriptors.Create( "Phone", true,
   "System.String", "Phone", null, null, TypeDescriptorFlags.None, null);
returnRootElementTypeDescriptor2.ChildTypeDescriptors.Create( "EmailAddress", true,
   "System.String", "EmailAddress", null, null, TypeDescriptorFlags.None, null);
returnRootElementTypeDescriptor2.ChildTypeDescriptors.Create( "CompanyName", true,
   "System.String", "CompanyName", null, null, TypeDescriptorFlags.None, null);
getCustomersMethod.MethodInstances.Create("GetCustomers", true,
   returnRootCollectionTypeDescriptor2, MethodInstanceType.Finder, true);

Commit the changes

Finally, commit the changes to the BCS Metadata Store.

//Publish the Customer Entity
customerEntity.Activate();

Verification

After the code executes check Central Administration to verify the external content type is successfully created. Then create an external list to verify the external content type works as expected.

Central Administration

To verify the external content type is successfully created follow these steps.

  1. Open SharePoint Central Administration
  2. Click Application Management
  3. Click Manage service applications
  4. Click the Business Data Connectivity service
  5. In the ribbon, click Manage
  6. In the ribbon, click Edit
  7. Select BDC Models in the View dropdown list
  8. Verify the BDC Model is in the list BDC Model List
  9. In the ribbon, click Edit
  10. Select External Content Types in the View dropdown list
  11. Verify the BCS Model is in the list External Content Types List

Create An External List

To verify the external content type works as expected follow these steps.

  1. Open the SharePoint web site where you created the external content type
  2. Log in as a user who has the permission to create lists
  3. Click the Site Actions menu
  4. Select View All Site Content
  5. Click Create
  6. In the Filter By section, click List
  7. Select External List
    Create External List
  8. Click the Create button
  9. In the Name textbox enter Programmatically Generated External Content Type
  10. Click the button to browse the external content types
    External Content Type Picker
  11. Select the Customer External Content Type in the External Content Type Picker
    External Content Type Picker
  12. Click OK
  13. Click Create
  14. View the data from the data source in the external list
    Data in external list

Wrap Up

As always, I hope this article saves you time and effort and helps you understand how to put this technology to use. There are several good BCS articles appearing on the Internet lately. You may check them out at the following blogs.

BCS Team Blog
Nick Swan’s blog
Fabian Williams’ blog

Yeah baby, only 12 days and I’m on vacation in Mexico!!!

Tags: , , , , , , ,