Creating SharePoint 2010 external content types programmatically

In this code sample I provide an extension to the standard SharePoint Business Conectivity Services admin API that helps to create external content types and the related external lists using client side APIs. A console application is used to illustrate the usage of the extension.

C# (40.6 KB)
 
 
 
 
 
3.8 Star
(4)
2,178 times
Add to favorites
3/10/2011
E-mail Twitter del.icio.us Digg Facebook
using System;
using System.Data;
using System.Text;
using System.Linq;
using System.Collections.Generic;
using Microsoft.SharePoint.BusinessData.Administration.Client;
using Microsoft.BusinessData.MetadataModel;
using Microsoft.SharePoint.BusinessData.Administration.Client.Parser;
using Microsoft.BusinessData.Runtime;

using Microsoft.SharePoint;
using Microsoft.SharePoint.Client;

namespace BCSModelGeneration
{
    class BCSModelGenerator
    {
        private string _siteUrl;

        static void Main(string[] args)
        {
            // TODO update URL to match the address of your SP site
            BCSModelGenerator generator = new BCSModelGenerator("http://sp2010");

            String modelName = "CustomerModel";

            if (generator.CreateModel(modelName, true))
            {
                Prompt();
                generator.CreateExternalList("AdventureWorks Customer", true);
            }
            Prompt();

        }

        private static void Prompt()
        {
            Console.Write("Press Enter to continue!");
            Console.ReadLine();
        }

        public static List GetListByTitle(ClientContext clientContext, String listTitle)
        {
            List existingList;

            Web web = clientContext.Web;
            ListCollection lists = web.Lists;

            IEnumerable<List> existingLists = clientContext.LoadQuery(
                     lists.Include(
                     list => list.Title)
                     );
            clientContext.ExecuteQuery();

            existingList = existingLists.FirstOrDefault(list => list.Title.ToLower() == listTitle.ToLower());

            return existingList;
        }


        private void CreateExternalList(String listTitle, bool remove)
        {
            ClientContext clientContext = new ClientContext(_siteUrl);
            Web web = clientContext.Web;

            ListCollection lists = web.Lists;

            if (remove)
            {
                List existingList = GetListByTitle(clientContext, listTitle);

                if (existingList != null)
                {
                    Console.WriteLine("Deleting list '{0}'...", listTitle);
                    existingList.DeleteObject();
                    clientContext.ExecuteQuery();
                    Console.WriteLine("List deleted.");
                }
                else
                {
                    Console.WriteLine("List not found, no need to delete.");
                }
            }

            Console.WriteLine("Creating list '{0}'...", listTitle);
            ListDataSource listDataSource = new ListDataSource();
            // set up the list data source properties using the extension method (see Extensions.cs)
            IDictionary<String, String> listDataSourceProp = listDataSource.Initialize("Customer", "AdventureWorks", "AdventureWorks", "GetCustomer");

            // initialize create list info
            // IMPORTANT! The DataSourceProperties is not of type ListDataSource, 
            // as one may expect, but a simple IDictionary<String, String>
            ListCreationInformation listCreateInfo = new ListCreationInformation
            {
                Title = listTitle,
                Description = "AdventureWorks Customers external list created from client-side code",
                DataSourceProperties = listDataSourceProp,
                Url = "awcust_clientcode",
                QuickLaunchOption = QuickLaunchOptions.On
            };

            // create list
            List newList = lists.Add(listCreateInfo);

            // newList.DataSource is read-only, so it is not possible to set it after the list is created
            // the following won't compile
            // newList.DataSource = listDataSource;

            clientContext.ExecuteQuery();

            Console.WriteLine("List created");

        }

        public BCSModelGenerator(String siteUrl)
        {
            _siteUrl = siteUrl;
        }

        private void RemoveModel(String modelName)
        {
            AdministrationMetadataCatalog catalog = AdministrationMetadataCatalog.GetCatalog(_siteUrl);

            ModelCollection models = catalog.GetModels(modelName);

            bool hasFound = false;
            foreach (Model model in models)
            {
                Console.WriteLine("Deleting model '{0}'...", model.Name);
                model.Delete();
                Console.WriteLine("Model deleted.");
                hasFound = true;
            }
            if (!hasFound)
            {
                Console.WriteLine("Model not found, no need to delete.");
            }
        }

        private bool CreateModel(String modelName, bool remove)
        {

            bool result = false;

            if (remove)
            {
                RemoveModel(modelName);
            }

            Console.WriteLine("Creating model '{0}'...", modelName);

            try
            {

                // connect to the metadata catalog
                AdministrationMetadataCatalog catalog = AdministrationMetadataCatalog.GetCatalog(_siteUrl);

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

                // create a new Customer LobSystem
                LobSystem lobSystem = customerModel.OwnedReferencedLobSystems.Create("Customers", true, SystemType.Database);

                // create a new AdventureWorks LobSystemInstance
                LobSystemInstance lobSystemInstance = lobSystem.LobSystemInstances.Create("AdventureWorks", true);

                // set the connection properties
                lobSystemInstance.Properties.Add("AuthenticationMode", "PassThrough");
                lobSystemInstance.Properties.Add("DatabaseAccessProvider", "SqlServer");
                // TODO update values to match your server and database name
                lobSystemInstance.Properties.Add("RdbConnection Data Source", "sp2010");
                lobSystemInstance.Properties.Add("RdbConnection Initial Catalog", "AdventureWorksLT");
                lobSystemInstance.Properties.Add("RdbConnection Integrated Security", "SSPI");
                lobSystemInstance.Properties.Add("RdbConnection Pooling", "true");

                Entity customerEntity = Entity.Create("Customer", "AdventureWorks", true,
                    new Version("1.0.0.0"), 1000, CacheUsage.Default, lobSystem, customerModel, catalog);
                EntityReference customerEntityRef = customerEntity.GetReference();

                // create the identifier
                Identifier customerIdentifier = customerEntity.CreateIdentifier("CustomerID", true, typeof(Int32));
                IdentifierReference customerIdentifierRef = customerIdentifier.GetReference();

                // create the specific finder method
                Method getCustomerMethod = customerEntity.Methods.Create(
                    "GetCustomer", true, false, "GetCustomer");

                // specify the query and command type
                getCustomerMethod.Properties.Add("RdbCommandText", "SELECT [CustomerID], [FirstName], [LastName], [CompanyName], [EmailAddress], [Phone] FROM [AdventureWorksLT].[SalesLT].[Customer] WHERE [CustomerID] = @CustomerID");
                getCustomerMethod.Properties.Add("RdbCommandType", "Text");

                // create the CustomerID input parameter
                Parameter CustomerIDParameter = getCustomerMethod.Parameters.Create("@CustomerID", true, DirectionType.In);

                TypeDescriptorParams standardStringType =
                    new TypeDescriptorParams
                    {
                        IsCached = true,
                        Type = typeof(Int32),
                        Flags = TypeDescriptorFlags.None,
                    };

                TypeDescriptorParams standardIntType =
                    new TypeDescriptorParams(standardStringType)
                    {
                        Type = typeof(Int32),
                    };

                // create the TypeDescriptor for the CustomerID parameter
                CustomerIDParameter.CreateRootTypeDescriptor(new TypeDescriptorParams(standardIntType)
                {
                    Name = "CustomerID",
                    IdentifierReference = customerIdentifierRef,
                    MetadataCatalog = catalog
                });

                // create the Customer return parameter
                Parameter customerParameter = getCustomerMethod.Parameters.Create("Customer", true, DirectionType.Return);

                TypeDescriptorParams returnElementTypeDescriptorParams =
                    new TypeDescriptorParams
                    {
                        Name = "Customers",
                        IsCached = true,
                        Type = typeof(IDataRecord),
                        Flags = TypeDescriptorFlags.None,
                    };

                TypeDescriptorParams returnCollectionTypeDescriptorParams =
                    new TypeDescriptorParams(returnElementTypeDescriptorParams)
                    {
                        Type = typeof(IDataReader),
                        Flags = TypeDescriptorFlags.IsCollection,
                        MetadataCatalog = catalog
                    };

                // create the TypeDescriptors for the Customer return parameter
                TypeDescriptor returnCollectionTypeDescriptor =
                    customerParameter.CreateRootTypeDescriptor(returnCollectionTypeDescriptorParams);

                TypeDescriptor returnElementTypeDescriptor =
                    returnCollectionTypeDescriptor.CreateChildTypeDescriptor(returnElementTypeDescriptorParams);

                returnElementTypeDescriptor.CreateChildTypeDescriptor(new TypeDescriptorParams(standardIntType)
                {
                    Name = "CustomerID",
                    IdentifierReference = customerIdentifierRef,
                });

                returnElementTypeDescriptor.CreateChildTypeDescriptor(new TypeDescriptorParams(standardStringType)
                {
                    Name = "FirstName"
                });

                returnElementTypeDescriptor.CreateChildTypeDescriptor(new TypeDescriptorParams(standardStringType)
                {
                    Name = "LastName"
                });

                returnElementTypeDescriptor.CreateChildTypeDescriptor(new TypeDescriptorParams(standardStringType)
                {
                    Name = "CompanyName"
                });

                returnElementTypeDescriptor.CreateChildTypeDescriptor(new TypeDescriptorParams(standardStringType)
                {
                    Name = "EmailAddress"
                });

                returnElementTypeDescriptor.CreateChildTypeDescriptor(new TypeDescriptorParams(standardStringType)
                {
                    Name = "Phone"
                });

                getCustomerMethod.MethodInstances.Create(getCustomerMethod.Name, true, returnElementTypeDescriptor,
                    MethodInstanceType.SpecificFinder, true);

                // create the finder method
                Method getCustomersMethod = customerEntity.Methods.Create("GetCustomers", true, false, "GetCustomers");

                // specify the query and command type
                getCustomersMethod.Properties.Add("RdbCommandText", "SELECT [CustomerID], [FirstName], [LastName], [CompanyName], [EmailAddress], [Phone] FROM [AdventureWorksLT].[SalesLT].[Customer]");
                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 returnCollectionTypeDescriptor2 =
                    customersParameter.CreateRootTypeDescriptor(returnCollectionTypeDescriptorParams);

                TypeDescriptor returnElementTypeDescriptor2 =
                    returnCollectionTypeDescriptor2.CreateChildTypeDescriptor(returnElementTypeDescriptorParams);

                returnElementTypeDescriptor2.CreateChildTypeDescriptor(new TypeDescriptorParams(standardIntType)
                {
                    Name = "CustomerID",
                    IdentifierReference = customerIdentifierRef,
                });

                returnElementTypeDescriptor2.CreateChildTypeDescriptor(new TypeDescriptorParams(standardStringType)
                {
                    Name = "FirstName"
                });

                returnElementTypeDescriptor2.CreateChildTypeDescriptor(new TypeDescriptorParams(standardStringType)
                {
                    Name = "LastName"
                });

                returnElementTypeDescriptor2.CreateChildTypeDescriptor(new TypeDescriptorParams(standardStringType)
                {
                    Name = "CompanyName"
                });

                returnElementTypeDescriptor2.CreateChildTypeDescriptor(new TypeDescriptorParams(standardStringType)
                {
                    Name = "EmailAddress"
                });

                returnElementTypeDescriptor2.CreateChildTypeDescriptor(new TypeDescriptorParams(standardStringType)
                {
                    Name = "Phone"
                });

                getCustomersMethod.MethodInstances.Create(getCustomersMethod.Name, true, returnCollectionTypeDescriptor2,
                    MethodInstanceType.Finder, true);


                ActivationError[] errors = customerEntity.Validate();

                result = (errors.Length == 0);

                if (result)
                {
                    customerEntity.Activate();
                    Console.WriteLine("Model created");
                }
                else
                {
                    Console.WriteLine("Validation errors:");
                    Array.ForEach(errors,
                        error => Console.WriteLine(error));
                }

            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception:");
                Console.WriteLine(ex.Message);
                result = false;
            }

            return result;

        }
    }
}