Synchronizing database tables with SharePoint lists

Introduction

Today's world is all about integration of information. We want to enable free data flow between the different applications we use in our organization. And we expect the tool we choose to use to enable this integration in a way that is easy, fast and simple.

Ultimate Forms is a complete platform for creating business solutions in SharePoint and beyond. And as such it provides multiple integration features and capabilities. It works the same both in SharePoint Online (Microsoft 365) and on premises.

One of the most common requirement is the ability to synchronize data from external database SharePoint lists. And Ultimate Forms is the perfect tool for this particular task.

Using Ultimate Forms it's easy to establish two way synchronization process between a database table and a SharePoint list. It requires no developer knowledge and no specialized connectors.

Scope of Implementation

In this article I am about to show you how to accomplish synchronization between SharePoint and database table. We support multiple types of database servers. The most common would be Microsoft SQL Server, Oracle and MySQL.

We are going to achieve the following functionality:

• Database and SharePoint column mapping according to our own logic (even when the column names do not match)
• New items are automatically created in the list when they are added to the DB table
• Existing items are updated in the list when they are updated in the DB table
• BONUS! Two-way synchronization between SharePoint and SQL database.

The Import component of Ultimate Forms will handle importing new and existing items from the database to the list. This versatile component is capable of importing from a variety of external data sources:

• Emails, including Exchange, Exchange Online, Gmail, POP3
• Database tables
• SMS messages
• REST APIs
• Web services
• Excel files
• Comma, semicolon and tab-delimited files

For sending updates back from the list to the database, we'll be using Actions, our most versatile component. It has the ability to run stored procedure in any database with parameters coming from a SharePoint list item.

Prerequisites

To successfully synchronize a database table, it has to contain the following columns (the column names do not matter):

• Unique identifier - any text/number field that is unique across all items in the list.
• Created Date - date/time column containing the creation date of the item. This column value must remain constant for the lifetime of the item.
• Modified Date - date/time column containing the last modification date of the item. When you first create an item, its Created and Modified dates should be the same.

In the SharePoint list, you would need to store the database unique identifier in any suitable column. I strongly recommend that you index the column to improve performance. You would also need to create suitable columns to contain database column values without losing the data types. You can always import any database column into a SharePoint text column.

One way synchronization

The following steps will create an import profile for the SharePoint list. The profile will automate the process of synchronizing database items to the SharePoint list.

• Navigate to the list in your browser.
• Click on the Design button on the list toolbar.
• Click on Import settings, then click on Add new profile.
• Give an easily identifiable name to your new profile.
• Specify the polling schedule, the import can be as frequent as every 15 minutes.
• Switch to the Data provider tab.
• Change the provider type to Database.
• Select the suitable database type.
  
• Enter a valid connection string, according to the database type and authentication mechanism
• Choose authentication type:
  • Connection string - username/password are provided in the connection string.
  • Service account - SharePoint Timer service account credentials are used.
  • This account - enter username and password to be use for authentication. Username and password are going to be encrypted and stored within the profile. This setting is only available on premises.
• Click on Connect to display a list of available tables/views.
• Select the relevant table or view.
• Select update date column, it should be the Modified date in your database table.
• You do not need to fill out File content and File name columns.
• You can now create your import action. For our purposes the best type would be Sync action. It combines the functionality of Create and Update actions. It uses the unique ID to match an item in the SharePoint list. If the action cannot find one, it creates a new one, otherwise, it updates the existing item.
• Select Sync list item action type.
• Enter an action name.
• Map values to the SharePoint columns. You can use text, database column names and functions. Use the value picker to generate valid expressions.
• Under Item section, enter a condition to compare unique ID value in both the SharePoint list and the database.
• Save the action.
• Click Save to save the profile.

We've now established a one-way synchronization from the database to SharePoint. If your solution requires two way synchronization, continue reading.

Two way synchronization

The Actions component is capable of running a stored procedure from the SharePoint list. It passes the data from the SharePoint list item as parameters for the stored procedure.

You would need a stored procedure in your database that updates an item with the values it receives as parameters. Next, you need to create an Action to run this stored procedure whenever a list item update occurs.

• If you are still inside Ultimate Forms, click on Actions in the left-side menu.
• Otherwise, navigate to the list, click on Design and then on Actions.
• Click on Add new action
• Select Run DB stored procedure as your action type.
• Ensure that the only selected item under the When to execute section is Item is modified.
• Switch to Action tab.
• Select the suitable database type.
  
• Enter the connection string.
• Click Connect.
• Select the stored procedure.
• Map the procedure parameters to the column values and/or expressions. Make sure that your stored procedure accept the unique identifier as one of the parameters and uses that parameter to select a single item to be updated.
• Save the action.

That's it, your two-way synchronization with an external database table is now established. The synchronization interval depends on the polling schedule settings of the Import profile.

Wait for the import profile to fill the list with the database items. Then start updating the items and see the changed reflected in both the database and in the list.

Summary

In this article I explained in simple steps how to establish SharePoint-database synchronization. Once established, the process requires no additional intervention and will run automatically in the background.