{"id":60,"date":"2009-12-01T14:32:42","date_gmt":"2009-12-01T14:32:42","guid":{"rendered":"http:\/\/localhost\/todd_wp\/how-to-programmatically-create-a-sharepoint-2010-external-content-type\/"},"modified":"2011-07-19T17:00:44","modified_gmt":"2011-07-19T17:00:44","slug":"how-to-programmatically-create-a-sharepoint-2010-external-content-type","status":"publish","type":"post","link":"https:\/\/toddbaginski.com\/blog\/how-to-programmatically-create-a-sharepoint-2010-external-content-type\/","title":{"rendered":"HOW TO: Programmatically create a SharePoint 2010 External Content Type"},"content":{"rendered":"

<\/a><\/p>\n

I demonstrated how to make an external content type programmatically during the SPC 405 – Business Connectivity Services Runtime and Object Model Deep Dive<\/strong> session at the 2009 Microsoft SharePoint Conference<\/a>. 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<\/em> 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.<\/p>\n

Sample Business Context<\/h1>\n

To begin, let\u2019s 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. <\/p>\n

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\u2019s 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.<\/p>\n

\"image\"<\/a><\/p>\n

The Solution<\/h1>\n

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.<\/p>\n

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.Administration<\/a>and the Microsoft.SharePoint.BusinessData.Administration.Client<\/a> namespaces. The Microsoft.SharePoint.BusinessData.Administration<\/a>namespace is used when running on the SharePoint server and the Microsoft.SharePoint.BusinessData.Administration.Client<\/a>namespace is used when running on a client machine (machine other than a server in a SharePoint server farm). The Microsoft.BusinessData.MetadataModel<\/a> and Microsoft.BusinessData.Runtime<\/a>namespaces are also used to support creating external content types.<\/p>\n

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\u2019ll 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<\/a>.<\/p>\n

Assembly references<\/h2>\n

To begin, create the appropriate Visual Studio project for your implementation and add references to the following assemblies.<\/p>\n\n\n\n\n\n\n
Assembly<\/strong><\/td>\nLocation<\/strong><\/td>\nUsage<\/strong><\/td>\n<\/tr>\n
Microsoft.BusinessData.dll<\/td>\nC:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14ISAPI<\/td>\nNeeded on both client and server<\/td>\n<\/tr>\n
Microsoft.SharePoint.dll<\/td>\nC:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14ISAPI<\/td>\nSolution runs on a SharePoint server<\/td>\n<\/tr>\n
Microsoft.SharePoint.BusinessData.Administration.Client.dll<\/td>\nC:Program FilesMicrosoft OfficeOffice14<\/td>\nSolution runs on a machine other than a SharePoint server (client)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

This example assumes you are not<\/em> 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.<\/p>\n

\"References\"<\/a><\/p>\n

The Code<\/h2>\n

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.<\/p>\n

Using Statements<\/h3>\n

To begin, add the following using statements to your project.<\/p>\n

\n
using<\/span> Microsoft.SharePoint.BusinessData.Administration.Client;\r\nusing<\/span> Microsoft.BusinessData.MetadataModel;\r\nusing<\/span> Microsoft.BusinessData.Runtime;<\/pre>\n
Connect to the BDC Metadata Store<\/h3>\n
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<\/strong><\/p>\n
\n
AdministrationMetadataCatalog catalog =\r\nAdministrationMetadataCatalog.GetCatalog(\"http:\/\/dev1\/sites\/Team Site\"<\/span>);<\/pre>\n
Create the Model, LobSystem, and LOBSystemInstance<\/h3>\n
Next, create the Model<\/a>, LobSystem<\/a>, and LobSystemInstance<\/a>.  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.<\/p>\n
\n
\/\/Create a new customer model\r\n<\/span>Model customerModel = Model.Create(\"CustomerModel\"<\/span>, true<\/span>, catalog);\r\n\r\n\/\/Make a new Customer LobSystem\r\n<\/span>LobSystem awLobSystem =\r\ncustomerModel.OwnedReferencedLobSystems.Create(\"Customer\"<\/span>, true<\/span>, SystemType.Database);\r\n\r\n\/\/Make a new AdventureWorks LobSystemInstance\r\n<\/span>LobSystemInstance awLobSystemInstance =\r\nawLobSystem.LobSystemInstances.Create(\"AdventureWorks\"<\/span>, true<\/span>);\r\n\r\n\/\/Set the connection properties\r\n<\/span>awLobSystemInstance.Properties.Add(\"AuthenticationMode\"<\/span>, \"PassThrough\"<\/span>);\r\nawLobSystemInstance.Properties.Add(\"DatabaseAccessProvider\"<\/span>, \"SqlServer\"<\/span>);\r\nawLobSystemInstance.Properties.Add(\"RdbConnection Data Source\"<\/span>, \"DEV1\"<\/span>);\r\nawLobSystemInstance.Properties.Add(\"RdbConnection Initial Catalog\"<\/span>, \"Customers\"<\/span>);\r\nawLobSystemInstance.Properties.Add(\"RdbConnection Integrated Security\"<\/span>, \"SSPI\"<\/span>);\r\nawLobSystemInstance.Properties.Add(\"RdbConnection Pooling\"<\/span>, \"true\"<\/span>);<\/pre>\n
Create the Entity<\/h3>\n
Next, create the Entity<\/a>to represent the Customers table and define which column(s) make up the identifier for the Entity<\/a>.<\/p>\n
\n
\/\/Create a new Customer Entity\r\n<\/span>Entity customerEntity =\r\n   Entity.Create(\"Customer\"<\/span>, \"AdventureWorks\"<\/span>, true<\/span>,\r\n   new<\/span> Version(\"1.0.0.0\"<\/span>), 10000, CacheUsage.Default,\r\n   awLobSystem, customerModel, catalog);\r\n\/\/Set the identifier - CustomerID column\r\n<\/span>customerEntity.Identifiers.Create(\"CustomerId\"<\/span>, true<\/span>, \"System.Int32\"<\/span>);<\/pre>\n<\/div>\n
Define the Specific Finder Method, Parameters and Type Descriptors<\/h3>\n
Next, create the specific finder Method<\/a>, specify the query it will use, and define the input and output parameters associated with it.  The specific finder Method<\/a> returns exactly one row of data from the data source, given an identifier.<\/p>\n
\n
\/\/Create the specific finder method\r\n<\/span>Method getCustomerMethod = customerEntity.Methods.Create(\"GetCustomer\"<\/span>, true<\/span>, false<\/span>, \"GetCustomer\"<\/span>);\r\n\/\/Specify the query\r\n<\/span>getCustomerMethod.Properties.Add(\"RdbCommandText\"<\/span>, \"SELECT [CustomerId] ,\r\n   [FirstName] , [LastName] , [Phone] , [EmailAddress] , [CompanyName]\r\n   FROM[Customers].[SalesLT].[Customer]\r\n   WHERE [CustomerId] = @CustomerId\"<\/span>);\r\n\/\/Set the command type\r\n<\/span>getCustomerMethod.Properties.Add(\"RdbCommandType\"<\/span>, \"Text\"<\/span>);\r\n\/\/Create the CustomerID input parameter\r\n<\/span>Parameter customerIDParameter =\r\n   getCustomerMethod.Parameters.Create(\"@CustomerId\"<\/span>, true<\/span>, DirectionType.In);\r\n\/\/Create the TypeDescriptor for the CustomerID parameter\r\n<\/span>customerIDParameter.CreateRootTypeDescriptor(\"CustomerId\"<\/span>, true<\/span>, \"System.Int32\"<\/span>,\r\n   \"CustomerId\"<\/span>, new<\/span> IdentifierReference(\"CustomerId\"<\/span>,\r\n   new<\/span> EntityReference(\"AdventureWorks\"<\/span>, \"Customer\"<\/span>, catalog), catalog),\r\n   null<\/span>, TypeDescriptorFlags.None, null<\/span>, catalog);\r\n\/\/Create the Customer return parameter\r\n<\/span>Parameter customerParameter =\r\n   getCustomerMethod.Parameters.Create(\"Customer\"<\/span>, true<\/span>, DirectionType.Return);\r\n\/\/Create the TypeDescriptors for the Customer return parameter\r\n<\/span>TypeDescriptor returnRootCollectionTypeDescriptor =\r\n   customerParameter.CreateRootTypeDescriptor( \"Customers\"<\/span>, true<\/span>,\r\n   \"System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral,\r\n   PublicKeyToken=b77a5c561934e089\"<\/span>, \"Customers\"<\/span>, null<\/span>, null<\/span>,\r\n   TypeDescriptorFlags.IsCollection, null<\/span>, catalog);\r\nTypeDescriptor returnRootElementTypeDescriptor =\r\n   returnRootCollectionTypeDescriptor.ChildTypeDescriptors.Create( \"Customer\"<\/span>, true<\/span>,\r\n   \"System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral,\r\n   PublicKeyToken=b77a5c561934e089\"<\/span>,\r\n   \"Customer\"<\/span>, null<\/span>, null<\/span>, TypeDescriptorFlags.None, null<\/span>);\r\nreturnRootElementTypeDescriptor.ChildTypeDescriptors.Create( \"CustomerId\"<\/span>, true<\/span>,\r\n   \"System.Int32\"<\/span>, \"CustomerId\"<\/span>, new<\/span> IdentifierReference(\"CustomerId\"<\/span>,\r\n   new<\/span> EntityReference(\"AdventureWorks\"<\/span>, \"Customer\"<\/span>, catalog),\r\n   catalog), null<\/span>, TypeDescriptorFlags.None, null<\/span>);\r\nreturnRootElementTypeDescriptor.ChildTypeDescriptors.Create( \"FirstName\"<\/span>, true<\/span>,\r\n   \"System.String\"<\/span>, \"FirstName\"<\/span>,\r\n   null<\/span>, null<\/span>, TypeDescriptorFlags.None, null<\/span>);\r\nreturnRootElementTypeDescriptor.ChildTypeDescriptors.Create( \"LastName\"<\/span>, true<\/span>,\r\n   \"System.String\"<\/span>, \"LastName\"<\/span>,\r\n   null<\/span>, null<\/span>, TypeDescriptorFlags.None, null<\/span>);\r\n\r\nreturnRootElementTypeDescriptor.ChildTypeDescriptors.Create( \"Phone\"<\/span>, true<\/span>,\r\n   \"System.String\"<\/span>, \"Phone\"<\/span>, null<\/span>, null<\/span>, TypeDescriptorFlags.None, null<\/span>);\r\nreturnRootElementTypeDescriptor.ChildTypeDescriptors.Create( \"EmailAddress\"<\/span>, true<\/span>,\r\n   \"System.String\"<\/span>, \"EmailAddress\"<\/span>, null<\/span>, null<\/span>, TypeDescriptorFlags.None, null<\/span>);\r\nreturnRootElementTypeDescriptor.ChildTypeDescriptors.Create( \"CompanyName\"<\/span>, true<\/span>,\r\n   \"System.String\"<\/span>, \"CompanyName\"<\/span>, null<\/span>, null<\/span>, TypeDescriptorFlags.None, null<\/span>);\r\n\/\/Create the specific finder method instance\r\n<\/span>getCustomerMethod.MethodInstances.Create(\"GetCustomer\"<\/span>, true<\/span>,\r\n   returnRootElementTypeDescriptor, MethodInstanceType.SpecificFinder, true<\/span>);<\/pre>\n
Define the Finder Method, Parameters and Type Descriptors<\/h3>\n
Next, create the finder Method<\/a>, specify the query it will use, and define the output parameters associated with it.  The finder Method<\/a> returns all of the rows of data from the data source which its query defines.<\/p>\n
\n
\/\/Create the Finder method\r\n<\/span>Method getCustomersMethod = customerEntity.Methods.Create(\"GetCustomers\"<\/span>, true<\/span>,\r\n   false<\/span>, \"GetCustomers\"<\/span>);\r\n\/\/Specify the query\r\n<\/span>getCustomersMethod.Properties.Add(\"RdbCommandText\"<\/span>, \"SELECT [CustomerId] ,\r\n[FirstName] , [LastName] , [Phone] , [EmailAddress] , [CompanyName]\r\nFROM [Customers].[SalesLT].[Customer]\"<\/span>);\r\n\/\/Set the command type\r\n<\/span>getCustomersMethod.Properties.Add(\"RdbCommandType\"<\/span>, \"Text\"<\/span>);\r\n\/\/Create the Customer return parameter\r\n<\/span>Parameter customersParameter = getCustomersMethod.Parameters.Create(\"Customer\"<\/span>, true<\/span>,\r\n   DirectionType.Return);\r\n\/\/Create the TypeDescriptors for the Customer return parameter\r\n<\/span>TypeDescriptor returnRootCollectionTypeDescriptor2 =\r\n   customersParameter.CreateRootTypeDescriptor(\"Customers\"<\/span>, true<\/span>,\r\n   \"System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral,\r\n   PublicKeyToken=b77a5c561934e089\"<\/span>, \"Customers\"<\/span>, null<\/span>, null<\/span>,\r\n   TypeDescriptorFlags.IsCollection, null<\/span>, catalog);\r\nTypeDescriptor returnRootElementTypeDescriptor2 =\r\n   returnRootCollectionTypeDescriptor2.ChildTypeDescriptors.Create( \"Customer\"<\/span>, true<\/span>,\r\n   \"System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral,\r\nPublicKeyToken=b77a5c561934e089\"<\/span>, \"Customer\"<\/span>, null<\/span>, null<\/span>,\r\n   TypeDescriptorFlags.None, null<\/span>);\r\nreturnRootElementTypeDescriptor2.ChildTypeDescriptors.Create( \"CustomerId\"<\/span>, true<\/span>,\r\n   \"System.Int32\"<\/span>, \"CustomerId\"<\/span>, new<\/span> IdentifierReference(\"CustomerId\"<\/span>,\r\n   new<\/span> EntityReference(\"AdventureWorks\"<\/span>, \"Customer\"<\/span>, catalog), catalog),\r\n   null<\/span>, TypeDescriptorFlags.None, null<\/span>);\r\nreturnRootElementTypeDescriptor2.ChildTypeDescriptors.Create( \"FirstName\"<\/span>, true<\/span>,\r\n   \"System.String\"<\/span>, \"FirstName\"<\/span>, null<\/span>, null<\/span>, TypeDescriptorFlags.None, null<\/span>);\r\nreturnRootElementTypeDescriptor2.ChildTypeDescriptors.Create( \"LastName\"<\/span>, true<\/span>,\r\n   \"System.String\"<\/span>, \"LastName\"<\/span>, null<\/span>, null<\/span>, TypeDescriptorFlags.None, null<\/span>);\r\nreturnRootElementTypeDescriptor2.ChildTypeDescriptors.Create( \"Phone\"<\/span>, true<\/span>,\r\n   \"System.String\"<\/span>, \"Phone\"<\/span>, null<\/span>, null<\/span>, TypeDescriptorFlags.None, null<\/span>);\r\nreturnRootElementTypeDescriptor2.ChildTypeDescriptors.Create( \"EmailAddress\"<\/span>, true<\/span>,\r\n   \"System.String\"<\/span>, \"EmailAddress\"<\/span>, null<\/span>, null<\/span>, TypeDescriptorFlags.None, null<\/span>);\r\nreturnRootElementTypeDescriptor2.ChildTypeDescriptors.Create( \"CompanyName\"<\/span>, true<\/span>,\r\n   \"System.String\"<\/span>, \"CompanyName\"<\/span>, null<\/span>, null<\/span>, TypeDescriptorFlags.None, null<\/span>);\r\ngetCustomersMethod.MethodInstances.Create(\"GetCustomers\"<\/span>, true<\/span>,\r\n   returnRootCollectionTypeDescriptor2, MethodInstanceType.Finder, true<\/span>);<\/pre>\n
Commit the changes<\/h3>\n
Finally, commit the changes to the BCS Metadata Store.<\/p>\n
\n
\/\/Publish the Customer Entity\r\n<\/span>customerEntity.Activate();<\/pre>\n
Verification<\/h2>\n
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.<\/p>\n
Central Administration<\/h3>\n
To verify the external content type is successfully created follow these steps.<\/p>\n
    \n
  1. Open SharePoint Central Administration<\/li>\n
  2. Click Application Management<\/li>\n
  3. Click Manage service applications<\/li>\n
  4. Click the Business Data Connectivity service<\/li>\n
  5. In the ribbon, click Manage<\/li>\n
  6. In the ribbon, click Edit<\/li>\n
  7. Select BDC Models in the View dropdown list<\/li>\n
  8. Verify the BDC Model is in the list \"BDC<\/a><\/li>\n
  9. In the ribbon, click Edit<\/li>\n
  10. Select External Content Types in the View dropdown list<\/li>\n
  11. Verify the BCS Model is in the list \"External<\/a>  <\/li>\n<\/ol>\n
    Create An External List<\/h3>\n
    To verify the external content type works as expected follow these steps.<\/p>\n
      \n
    1. Open the SharePoint web site where you created the external content type<\/li>\n
    2. Log in as a user who has the permission to create lists<\/li>\n
    3. Click the Site Actions menu<\/li>\n
    4. Select View All Site Content<\/li>\n
    5. Click Create<\/li>\n
    6. In the Filter By section, click List<\/li>\n
    7. Select External List
      \n      \"Create<\/a><\/li>\n
    8. Click the Create button<\/li>\n
    9. In the Name textbox enter Programmatically Generated External Content Type<\/li>\n
    10. Click the button to browse the external content types
      \n       \"External<\/a><\/li>\n
    11. Select the Customer External Content Type in the External Content Type Picker
      \n      \"External<\/a><\/li>\n
    12. Click OK<\/li>\n
    13. Click Create<\/li>\n
    14. View the data from the data source in the external list
      \n      \"Data<\/a><\/li>\n<\/ol>\n
      Wrap Up<\/h1>\n
      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.<\/p>\n
      BCS Team Blog<\/a>
      \n      Nick Swan\u2019s blog<\/a>
      \n      Fabian Williams\u2019 blog<\/a><\/p>\n
      Yeah baby, only 12 days and I\u2019m on vacation in Mexico!!!<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
      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 [\u2026]<\/span><\/p>\n","protected":false},"author":1,"featured_media":231,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[46],"tags":[28,4,10,30,5,6,7,26],"class_list":["post-60","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-most-popular","tag-bcs","tag-bdc","tag-custom-development","tag-external-content-types","tag-helpful-code","tag-how-to-sharepoint","tag-sharepoint","tag-sharepoint-2010"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/toddbaginski.com\/blog\/wp-json\/wp\/v2\/posts\/60","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/toddbaginski.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/toddbaginski.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/toddbaginski.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/toddbaginski.com\/blog\/wp-json\/wp\/v2\/comments?post=60"}],"version-history":[{"count":7,"href":"https:\/\/toddbaginski.com\/blog\/wp-json\/wp\/v2\/posts\/60\/revisions"}],"predecessor-version":[{"id":318,"href":"https:\/\/toddbaginski.com\/blog\/wp-json\/wp\/v2\/posts\/60\/revisions\/318"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/toddbaginski.com\/blog\/wp-json\/wp\/v2\/media\/231"}],"wp:attachment":[{"href":"https:\/\/toddbaginski.com\/blog\/wp-json\/wp\/v2\/media?parent=60"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/toddbaginski.com\/blog\/wp-json\/wp\/v2\/categories?post=60"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/toddbaginski.com\/blog\/wp-json\/wp\/v2\/tags?post=60"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}