How to full CRUD (Create/Update/Delete) SQL tables using native SharePoint lists and default forms?

It is a typical requirement in SharePoint projects, to have SharePoint list data synchronized with external SQL tables, e.g. for integration of external applications like ERP or CRM. But how to support full CRUD (Create / Update / Delete) operations support for the external data without any coding?

Let's take a look at this step-by-step sample to enable full CRUD completely codeless. In the first step we create a SQL table, with SQL Server in this sample - but does not matter.

Generally you need a primary key on database side to setup. But there will be issues, if using a database side counter or something like this. SharePoint does not know about it on insert. But it will work with a GUID as primary key. Lets create the following sample table for that:

 

Fig.: Sample SQL table with GUID as primary key.

 

Please note:

• You can use the name bdlcGUID to auto-map to SharePoint. Must be bdlcGUID to map automatically, otherwise you have to map manually.
• You can use the newid() function to enable auto-generating GUIDs on SQL side
• If your database does not provide a GUID type column, please use simple one line text of 255 chars.

 As a next step you have to create the SharePoint list as follows:

 

 Fig.: Sample SharePoint list for data acquisition to SQL.

 

Please note:

 

• You have to create a column bdlcGUID as single line of text type. This column is automatically filled with GUIDs when entering new items. The name must be bdlcGUID. You can change the column title later on to whatever you want.

To connect the list with the external table simply click "Connect to external data source" in the list settings as shown above. Please enter conenctions settings like this:

 

Fig.: Sample connection settings to support full SQL CRUD with SharePoint lists.

 

Please note:

• You have to set the bdlcGUID column as primary key.
• If new records are created on the database side as well, you can enable background update to sync, e.g. every hour as shown in sample.
• To write-back changes in SharePoint to database, you have to enable write-back. This operation is done in real-time, e.g. for newly created items, changed items or deleted items.
• You can use the add columns operation to add your database fields as columns. But take care about column types to fit.
• Please use the "Check Mappings" dialog to take a look at the current field - column mapping

Now you can start with data acquisition to SharePoint list and SQL table at the same time.

 

Fig.: SharePoint sample list for data acquisition to SQL in real-time. The bdlcGUID column is filled with a GUID automatically that is used as a primary key to connect records in the database with items in the list for sync.

 

Please note:

• You can't hide form columns in user interface by default in SharePoint. But you can hide the unwanted columns like title or bdlcGUID using SharePoint form customization with SharePoint designer or directly in XML definition. Anyway, the column must be part of the view used in form.