SharePoint 2010 External Content Type With CRUD Operations

Step by Step Business connectivity services With CRUD Operations

Expose line of business data (LOB) through SharePoint. Its descended from Business data Catalog. Ability to provide a read write data from

1. Database,
2. WCF services,
3. Net Assemblies

Part of SharePoint Foundation

Here * mean features only available on SharePoint Standard and Enterprises Server.

BCS : Expose the LOB data as content types from the external data source. It expose the data as list. Allow users to use the LOB data as lookups in standard list and libraries. Expose data to no-code and programmatic workflows. Allows creation of custom menu and ribbons action items. Allow search of LOB* data. It is allows to create the custom pages and InfoPath forms* to work with LOB data.

Concepts

1. External Content type
2. External List
3. Association
4. Action
5. Custom Action
6. Profile Page*

External Content type :  A core concept of Microsoft Business Connectivity Services (BCS) is the external content type. Used throughout the functionality and services offered by Business Connectivity Services, external content types are reusable metadata descriptions of connectivity information and data definitions plus the behaviors you want to apply to a certain category of external data. External content types enable you to manage and reuse the metadata and behaviors of a business entity such as Customer or Order from a central location, and enable users to interact with that external data and processes in a more meaningful way.

External List: The Business Data Connectivity (BDC) service enables SharePoint to display business data from back-end server applications, Web services, and databases.

Association : As a platform and object model, Business Connectivity Services provides many different association types. The SharePoint Designer 2010 External Content Type Designer allows you to easily create a subset of all possible association types, by supporting the following: one-to-many associations based on a foreign key.

An example of the one-to-many type of association is the relationship between a musical band and an album that contains the band's music. One band can produce several albums, yet each album is always associated with the one band that created it.

A foreign key must be present in the external system, which means that the album's metadata (title, price, release date, and other information) must contain a unique piece of information that identifies that one band.

Action : Implimenting the ribbon action with external content type.

Custom Action: Custom actions can be used to run a workflow, show an existing form, or navigate to another Web page.

Profile Page* : A profile page in BCS (Business Connectivity Services) allows to display all the information for an external content (entity instance). For example, a profile page can display all the fields in a record for a specific customer. It can also display all the orders associated with the customer.

Following steps to create the external content type.

Creating a External Content type you need to fist set the appropriate permission. Go to the Central Administration Home page under the Application Management Section Click the Manage Service applications. 

Click the business connectivity services. Provide the appropriate permission to user that able to read write execute the permission.

Following page open when you click the business data services. if there any existing data services available it will show into this list.

 Click the set meta data store permission as marked red showing in the bellow image. when you click meta data permission dialog box open. see in the next image.

 I added  administrator here. That have a full permission. Make sure when you going to add the user the bellow check box must be checked "propagate permission to all BCS model, External system ..... ".

I had downloaded Pub database and attached that database and implemented the "publishers" table as a external list.

Open the SharePoint Designer and click to the external Content types link on the left navigation of SPD. 

 Provide the Name, display name and NameSpace to the External Content type. (Later i will use this to insert and update operation)

Give the same ie "publishers" to  name and display name and NameSpace text boxes. we have a various type of office content type like generic list, Appointments, Contact , Task ( both Contact and task we later later syc. with outlook) etc.

 Keep the  default version as well as office item type drop down list. Next we have to defined the data source. we have to click the External system link.

 We have a three type of connection operation. But we choose SQL Server.

 Give a database server name, database name and authentication mode we choose user identity mode.

 Now the processing is going on and making a connection with the external Pub database.

 After making a successively connection. We have table views and routines to use as a external data.

 we choose publishers table and right click on it.

 we have a options we choose here Create all operation because we have to perform a all operations.

 The external content type wizard open. it has two main pains top pain showing the operation and the bottom pain sowing the errors and warnings. we have two main steps first setting the  parameters  second setting the filter parameters. Click next

Fist step : It will showing 2 error.

 1. data source pub_id is read only 2. no field selected for picker list.

To remove the 2ed error click to the pub_name column and checked the "show in picker" check box. The error will remove. Click to the next

 We have to must specify the filter parameter option to limit the number of rows return by the list. we click to add filter parameter button.

Than pub_id will added as a filter parameter now we have to set the filter property. we have to click next to the filter link button in the parameters "(click to add)". The filter window open.

 we set the filter type to limit and click Ok .

 Than Give default value (limit value not more than 2000 records)

 Here we set the 100 as a default value then press tab the error will remove. Next step click finish.

 Now time to save the External Content Type. Press Ctrl+S to save it.

 Create list from the External Content type Click create list from button from the ribbon as showing in this image.

 The dialog box open give a name to the list and leave the default options and press save.

 You can see the list name ie "publishers" under external content type in a navigation.

Items in a External Content type List

 When you edit the items form SharePoint list automatically save to the database as well.

 See the example Pub_id 9901 having a  space in a city "M nchen". We remove the space.

 See the reflected value in a table.

Inserting and Updating Code

Publishers table columns are

1. pub_id
2. pub_name
3. City
4. State
5. Country

So we need to create and update the records using code

we have to use the following namespces.

using Microsoft.BusinessData;

using Microsoft.BusinessData.MetadataModel;

using Microsoft.BusinessData.Runtime;

using Microsoft.BusinessData.Infrastructure;

using Microsoft.BusinessData.MetadataModel.Collections;

using Microsoft.SharePoint.BusinessData.SharedService;

using Microsoft.SharePoint.BusinessData.Runtime;

using Microsoft.SharePoint.BusinessData;

using Microsoft.SharePoint.Administration;

using Microsoft.SharePoint;

Find Method

#region Find Publishers By ID

        protected void BtnFindByPublishersNumber_Click(object sender, EventArgs e)

        {

            IEntityInstance ieiSpPublishers = null;

            string EntityNamespace = "Publishers";  // above we defined the content type name space 

            string PublishersEntityName = "Publishers"; // above we defined the External content type name.

            IEntity entity = null;

            //====================================

            //Check the SharePoint database, first

            try

            {

                using (new Microsoft.SharePoint.SPServiceContextScope(

                  SPServiceContext.GetContext(SPContext.Current.Site)))

                {

                    // Get the BDC service and metadata catalog.

                    BdcService service = SPFarm.Local.Services.GetValue<BdcService>(String.Empty);

                    IMetadataCatalog catalog = service.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);

                    // Get the entity by using the specified name and namespace.

                    //IEntity entity =

                    entity = catalog.GetEntity(EntityNamespace, PublishersEntityName);

                    ILobSystemInstance LobSysteminstance = entity.GetLobSystem().GetLobSystemInstances()[0].Value;

                    // Create an Identity based on the specified Customer ID.

                    string pub_id = "9901";

                    //Int64 pub_id = Convert.ToInt64(strCustomerID);

                    identity = new Identity(pub_id);

                    // Get a method instance for the SpecificFinder method.

                    IMethodInstance method = entity.GetMethodInstance("Read Item", MethodInstanceType.SpecificFinder);

                    //methodRead2.

                    // Execute the Specific Finder method to return the customer data.

                    ieiSpPublishers = entity.FindSpecific(identity, LobSysteminstance);

                    string pub_id = ieiSpPublishers["pub_id"];

                    string pub_name = ieiSpPublishers["pub_name"];

                    string City = ieiSpPublishers["City"];

                    string State = ieiSpPublishers["State"];

                    string Country = ieiSpPublishers["Country"];

                }

            }

            catch (Microsoft.BusinessData.Runtime.ObjectNotFoundException exObjNotFound)

            {

            }

            catch (Exception exBad)

            {

        }

        #endregion

Create Publisher Method

#region create Publishers

        protected void BtnCreatePublishers _Click(object sender, EventArgs e)

        {

            IEntityInstance ieiSpPublishers = null;

            string EntityNamespace = "Publishers";

            string PublishersEntityName = "Publishers";

            IEntity entity = null;

            //====================================

            //Check the SharePoint database, first

            try

            {

                using (new Microsoft.SharePoint.SPServiceContextScope(

                  SPServiceContext.GetContext(SPContext.Current.Site)))

                {

                    // Get the BDC service and metadata catalog.

                    BdcService service = SPFarm.Local.Services.GetValue<BdcService>(String.Empty);

                    IMetadataCatalog catalog = service.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);

                    // Get the entity by using the specified name and namespace.

                    //IEntity entity =

                    entity = catalog.GetEntity(EntityNamespace, PublishersEntityName);

                    ILobSystemInstance LobSysteminstance = entity.GetLobSystem().GetLobSystemInstances()[0].Value;

                     IView createView = entity.GetCreatorView("Create");

                    IFieldValueDictionary valueDictionary =

                        createView.GetDefaultValues();

                    valueDictionary["pub_id "] = “111”;

                    valueDictionary["pub_name"] =”abc”;

                    valueDictionary["City"] = “chd”;

                    valueDictionary["State "] =”chd”;

                    valueDictionary["Country"] =”India”;

                    Identity id = entity.Create(valueDictionary, LobSysteminstance);

                }

            }

            catch (Microsoft.BusinessData.Runtime.ObjectNotFoundException exObjNotFound)

            {

            }

            catch (Exception exBad)

            {

        }

        #endregion

Update Publisher Method

#region Update Publishers By ID

        protected void BtnUpdateByPublishersNumber_Click(object sender, EventArgs e)

        {

            IEntityInstance ieiSpPublishers = null;

            string EntityNamespace = "Publishers";

            string PublishersEntityName = "Publishers";

            IEntity entity = null;

            //====================================

            //Check the SharePoint database, first

            try

            {

                using (new Microsoft.SharePoint.SPServiceContextScope(

                  SPServiceContext.GetContext(SPContext.Current.Site)))

                {

                    // Get the BDC service and metadata catalog.

                    BdcService service = SPFarm.Local.Services.GetValue<BdcService>(String.Empty);

                    IMetadataCatalog catalog = service.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);

                    // Get the entity by using the specified name and namespace.

                    //IEntity entity =

                    entity = catalog.GetEntity(EntityNamespace, PublishersEntityName);

                    ILobSystemInstance LobSysteminstance = entity.GetLobSystem().GetLobSystemInstances()[0].Value;

                    // Create an Identity based on the specified Customer ID.

                    string pub_id = "9901";

                    //Int64 pub_id = Convert.ToInt64(strCustomerID);

                    identity = new Identity(pub_id);

                    // Get a method instance for the SpecificFinder method.

                    IMethodInstance method = entity.GetMethodInstance("Read Item", MethodInstanceType.SpecificFinder);

                    //methodRead2.

                    // Execute the Specific Finder method to return the customer data.

                    ieiSpPublishers = entity.FindSpecific(identity, LobSysteminstance);

                    ieiSpPublishers["pub_name"]=”test”

                    ieiSpPublishers["City"]=”test”

                    ieiSpPublishers["State"]=”tete”

                    ieiSpPublishers["Country"]=”test”

                     ieiSpPublishers.Upate()

                }

            }

            catch (Microsoft.BusinessData.Runtime.ObjectNotFoundException exObjNotFound)

            {

            }

            catch (Exception exBad)

            {

        }

#endregion