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.

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.

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.

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;

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

//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 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");

//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);

//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);

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