Dynamically creating SharePoint 2013 external content type

Question

Background: There is requirement of calling external list from SQL to SharePoint 2013. We have done it using BCS, external content Type and SecureStore. There are also some modification done using InfoPath forms to view external list data more user friendly.

Tools Used: SharePoint Designer to create external content type

Problem: When there is change in list structure of SQL database table, external lists are no longer working and we have to follow external content type and external list creation process. Moreover, modification in InfoPath as well.

Possible resolution:

1. If there is any way to create external content type dynamically, that we can handle using Feature activation. (Farm Scoped)
2. Creation of external content type dynamically using SharePoint App model. (App Scoped) <-- Preferred

Questions:

1. What is possible way for handling this situation dynamically?
2. Has anyone real world experience for similar situation where it need to be flexible with SQL table structure while creation of external list. And external content type / list adopts new structure dynamically, if there is any change at database level [SQL table structure, no. of columns, column type etc]

Any help or suggestions would be highly appreciated.

Answer 1

1. No way of doing it dynamically OOTB as far as I know..
2. I have a situation where I need to be flexible about SQL table changes and I use editing of existing content type instead of creating ECT again from scratch every time the table changes.. You can edit your existing content type like this:
   • Open external content types in your sp designer
   • Open the external content type summary for the ECT you want to update by clicking on it
   • Under the External Content Type Operations select desired operation, and then on the ribbon click edit operation
   • Follow the wizard which is the same one you have when you are creating the new ECT except it is in edit mode now
   • After finishing the wizard, do the same for all the other operations you want to update
   • After all, don't forget to save the changes and you should see them right away on your old external list (without creating a new one).. (I'm not using the Infopath so I don't have to change anything there, but the logics tells me that you can open your current form and update it as you want)

Hope this helps