Todd Baginski's Blog - A SharePoint, Mobile, Office, and Azure Blog

HOW TO: Programmatically create a SharePoint 2010 External Content Type

Waterfall at Magnolia Resevoir

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!!!

18 responses on “HOW TO: Programmatically create a SharePoint 2010 External Content Type

  1. Chetan says:

    Very good artical. It works also.
    I am able to create ADD and Delete methods but my Update is not working.
    Would you help me in Update method.

  2. Todd Baginski has a great series of how-to posts that demonstrate multiple capabilities of Business Connectivity

  3. Mohamed Saleh says:

    Great Post…
    Thanks

  4. Chirag Goradia says:

    Hi Todd,

    Great post !

    There’s one typo. "Therefore, the Microsoft.SharePoint.BusinessData.Administration.dll assembly is not added as a reference." should have actually been – "Therefore, the Microsoft.SharePoint.dll assembly is not added as a reference.".

    I have a question posted on MSFT forum… Is the BCS Server DLL missing GetCatalog() implementation of Administration Metadata Catalog? – social.msdn.microsoft.com/…/56916187-320d-473…

    Please help. Thanks !

    Regards,
    Chirag

  5. Todd says:

    Hi Jennet,

    Great question. You can use the BdcService.GetAdministrationMetadataCatalog method when running on the server.

  6. FormBuilder says:

    Yes, this is really great post! Soon I’ve not met such detailed and well-structured approach. Regards!

  7. Priyanka says:

    Hi,

    Thanks for the great post.
    I have a question.
    The External content Type -Tables configured from SQL server database have columns of data type Numeric and small int.
    These when used in External List has data type of Decimal and Interger respectively.

    The problem I have here is,I cannot sum the column with datatype "Decimal"(in external list).

    The External list usually have "Number" as its data type.But BDC on conversion used "Decimal" and "Integer".

    Any thoughts on how I can change the data type in sharepoint? or how can I get the sum of decimal columns?

    Thanks.

  8. Todd says:

    You could use a .NET assembly connector to define the connection between the external system and BCS. This gives you the ability to control the data types and specify exactly what they are so that the lists in SharePoint can work with them as you desire.

  9. James J Moore says:

    Hello..

    J&L is a leading risk management consulting firm. Our consultants are experts in workers compensation reserve reviews, premium analysis, and policy audits. We can help you cut costs by reducing your insurance budget.
    Workers’ Compensation premiums

  10. G. DEUB says:

    LO,

    Thanks for all your great posts.
    I’m looking for a way to give some arguments to a finder method for filtering data in the method.
    I’m imagine that is possible to put the parameter in the url but i failed to do that. Have you solutions or workarounds for the browser (end user) can filter data ?

    PS : I can’t use business data list

    Thanks

  11. Ayman El-Hattab, MVP says:

    Great article Todd, keep up the good job.

  12. Fabian Williams says:

    Yet another great post Todd. and thanks for the personal note.

  13. Kaustubh says:

    Great post! I however need a help.

    I created one custom External Content type and it works just fine, hiowever if I create a new ECT on the same machine and deploy it, the first one stops working, and displays this error:

    Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.

    ULS logs show the following error:

    Error while executing web part: Microsoft.BusinessData.MetadataModel.InvalidMetadataPropertyException: Entity (External Content Type) ‘Entity1′ has Property ‘Class’ defined as Type ‘BCS_SP.BdcModel1.Entity1Service, BdcModel1′. Type ‘BCS_SP.BdcModel1.Entity1Service, BdcModel1′ cannot be found in the assembly.

    This error suggests that assembly cannot be found, however I’ve verified that assembly does exist in GAC.

    I’ve been trying hard to get around this issue, but invain. Could you please help?

  14. Pal says:

    Thanks for the post. It’s really helped me a lot.
    I am trying to create External Conten Type using WCF. How do I create it programmatically.I would appreciate any help.

    Thanks,
    Pal

  15. This is a good job. Well done indeed.

  16. Logo design says:

    I feel delighted to read such a good post, I would like to thank the Author for this marvelous efforts. This post is good in regards of both knowledge as well as information. Thanks for the post.

  17. Carpet Cleaning in Canberra says:

    Amazing! this was such an impressive idea. Thanks for the share. Carpet Cleaning in Canberra