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.
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.
- 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
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.
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
Now you can
start with data acquisition to SharePoint list and SQL table at the same
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.
- 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.