Implement Business Connectivity Services in SharePoint 2013 with CRUD forms

Implement Business Connectivity Services in SharePoint 2013 with CRUD forms:

You can use SharePoint 2013 as a hub for creating rich productivity and collaboration solutions that can work with a variety of external systems. Business Connectivity Services (BCS) provides the infrastructure that enables SharePoint 2013 to bring data from those external systems into a central system. By providing a flexible and extensible means to describe the external system data source and how to interact with it, BCS makes a compelling argument for using SharePoint 2013 as the central interface for working with legacy business systems in addition to new apps for SharePoint.

What can BCS do?

BCS provides mechanisms to enable experienced users, developers, and business unit IT professionals to do the following much more easily:

• Reveal external data from enterprise applications, web services, and OData services in SharePoint Server 2013 and in rich-client Office applications.
• Provide Office-type behaviors (such as Contacts, Tasks, and Appointments) and capabilities to external data and services.
• Provide complete interaction with the data, including write-back capabilities from Office applications and SharePoint Server to the underlying external system data and business objects.
• Enable offline use of external data and processes.
• Bridge the unstructured world of documents and people and the appropriate structured data that is locked in external systems.

What is an external content type?

The external content type is a core concept of Business Connectivity Services (BCS). Used throughout the functionality and services offered by BCS, 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 a customer or order from a central location, and enable users to interact with that external data and processes in a more meaningful way.

To create external content types with external lists which allow CRUD operations to sql db , please follow the below mentioned steps: 

1) Connect With External Data Source (here we are using SQL Server 2014)

Open SPD and click on the open site icon:

2) Insert the site link for the site we need to open:

3) Enter the admin credentials and once the site opens , click on the External Content Types link as below and then click on Add Connection > External Data Source Type Selection > SQL Server :

4) On clicking the SQL Server , a dialog opens wherein we enter the credentials required to connect to database:

5) In this we can view all the tables present in the database as well as views and routines as below :

6) Select the table on which you want to perform CRUD operations :

7) Make the parameter configurations as per your requirements , similar to  below screenshot :

8) Make the filter configurations as per your requirements , similar to below screenshot :

9) Once this is completed , we need to create external lists as below : 

10)  Enter the list name and description(optional) as below :

11) After that, go the SharePoint site and refresh it, we can see the external list here and click on the list we get the "Access denied by Business Data Connectivity" error as below :

12) To fix this issue, go to Central Admin > Manage Service Applications > Business Data Connectivity Service Application. In this service application, you will find your External Content Type :

In this, select your External Content type by marking the checkbox. After that, click on Set Object Permissions.

13) In this step, set the permissions as per your requirement :

14) After that, refresh the site and hope this will work… but again, it has a problem. The error message like Login failed for user “NT AUTHORITY\ANONYMOUS LOGON”.

15) To fix this error, we need to change the connection property to BDC identity. Go to your external content type and click on Edit Connection Property as below:

16) Once this is done, open you SharePoint managed powershell and run the below commands:

$bdc = Get-SPServiceApplication | 
where {$_ -match “Business Data Connectivity Service”}
$bdc.RevertToSelfAllowed = $true
$bdc.Update();

After that, refresh the external list in the site. It would work as expected and you would be able to do the CRUD operations.

17) In case there are too many records , you can get an error related to resource throttling as below:

Database Connector has throttled the response.
The response from database contains more than '2000' rows. 
The maximum number of rows that can be read through Database Connector is '2000'. 

The limit can be changed via the 'Set-SPBusinessDataCatalogThrottleConfig' cmdlet 

To fix this error , run the below commands:

$bcs = Get-SPServiceApplicationProxy | where{$_.GetType().FullName 
-eq (‘Microsoft.SharePoint.BusinessData.SharedService.’ + ‘BdcServiceApplicationProxy’)}

$BCSThrottle = Get-SPBusinessDataCatalogThrottleConfig -Scope database 
-ThrottleType items -ServiceApplicationProxy $bcs

Set-SPBusinessDataCatalogThrottleConfig -Identity $BCSThrottle -Maximum 1000000 -Default 20000