How to connect multiple external tables into one SharePoint list to perform CRUD operations

Question

I am working with SharePoint 2013 and I have a requirement where I have to perform CRUD operations on multiple external tables (SQL Server) with SharePoint in one List and vice versa. I am able to perform the CRUD operation with one table and one list mapping.

I have performed below operations to achieve this:

1. Created a view for multiple tables and display the data into a list using External Content Type but unable to perform update, insert and delete operations on it.

2. Created various stored procedure for CRUD operation and binding them manually to External Content Type and performing the operation.

The 2nd options is working find for me. But it has some limitations and I am looking for OOB solution if any. Could someone suggest me any OOB option for this requirement.

Thanks in advance.

Answer 1

When it comes to BCS, nothing is Out of the Box. You're working with XML here and that's custom. Anyways, you should be able to configure the External Content Type by specifying stored procedures. Personally, I don't like to do updates via SharePoint. What I would do is as follow:

1. Use SharePoint external lists for displaying data nicely in SharePoint views
2. Use custom code to write back to the SQL database (or whatever source). I prefer using LinQ to SQL.
3. When someone updates something , just call a refresh on the External List.

Answer 2

The best way to do this, I think, is to set up a stored proc on the DB end and tell SharePoint that that is your "table".