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

The Layer2 Business Data List Connector for SharePoint (BDLC) can be used to connect almost any external data source to a native SharePoint list, even with full CRUD (Create / Update / Delete) support in real-time using default SharePoint list forms or any 3rd party forms app codeless.

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 a GUID field 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 in the BDLC mapping dialog (see below).
• 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 (display name) 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 connections settings like this:

Fig.: Sample connection settings to support full SQL CRUD with SharePoint lists. The Primary key is mandatory.

Please note:

• You have to set the bdlcGUID column as primary key if you use auto mapping, otherwise just enter the given external primary key (in my example "myId").
• If new records are created on the database side as well, you can enable background update to sync, e.g. every hour.
• 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 fields. If you want to make use of a GUID field that is not named bdlcGuid, you have to map this field manually to the SharePoint column "bdlcGuid" (see screen below).

Fig.: Enable custom mapping to connect any external field to your bdlcGuid column.

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

Enter a new entry and leave the bdlcGuid field blank in your SharePoint form. After you saved the entry, BDLC generates a new GUID, fills it in and then syncronizes the new entry to your database.

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.

Summary

The Business Data List Connector allows data replication between SQL tables and SharePoint lists both directions with codeless full CRUD (create, update, delete) support. Line of business application that require data in SQL databases can be easily and completely integrated with SharePoint this way.

Ready to go next steps?