Native SharePoint lists as SQL Azure Web Frontend with full CRUD

​​The Layer2 Cloud Connector for Microsoft Office 365 and SharePoint connects almost any on-premise data source, e.g. ODBC, OLEDB, Microsoft .NET based providers, Files (Excel, XML, CSV), SQL databases like MS SQL Server, SQL Azure, Oracle, MySQL, IBM DB2, IBM AS/400, IBM Informix, Notes, SharePoint, Exchange, Active Directory, Navision, SAP and many more directly to native SharePoint lists in the Microsoft Cloud - or any other Microsoft SharePoint on-prem installation - in just minutes without any programming. For any questions please use the FAQ Web Form or contact sales@layer2solutions.com directly.

​It's easy to connect native Microsoft SharePoint lists to SQL Azure tables and queries codeless and in just minutes. Optionally the connection can be bi-directional with two-way data replication. Full CRUD operation supported: create, update, delete. That means for example, you can use SharePoint on-premise or in the cloud as web frontend for SQL Azure. Please follow this instructions:

First create any data table in SQL Azure. In our sample this table will host announcement items from the SharePoint list with title, body and an expiration date. Please be sure to have some database-side primary key in your table.

Fig.: Create a data table in SQL Azure

In our sample we use a GUID field of type uniqueidentifier and set a new GUID when a new record is created. In this way any record has an unique primary key.

Additionally you have to create the Announcement list on the SharePoint side. We use the Announcement template to create. It's also available for Office 365 or SharePoint Online.

Fig.: A SharePoint Announcement list named AzureCRUD is connected to SQL Azure

To connect the created SQL Azure table with the SharePoint Announcements list we use the Layer2 Cloud Connector with the following connection definition file:

<?xml version="1.0" encoding="utf-8" ?>
<connection xmlns="http://www.layer2.de/schemas/cloudConnections"

interval="10" version="1.2" enabled="true">

<conflictResolution mode="warnAndContinue" /> 

<dataEntities>

<dataEntity name="Azure" 
provider="System.Data.SqlClient" 
connectionString="Server=tcp:myserver.database.windows.net; Database=myDB;
User ID=MyUser@myServer.database.windows.net; Password=myPwd;
Trusted_Connection=False; Encrypt=True;"
selectStatement="Select * from Announcements"
>

</dataEntity>
<dataEntity name="Office365"
provider="Layer2.SharePoint.Provider"
connectionString="Data Source=https://myDomain.sharepoint.com/sites/mySite/;
Initial Catalog=AzureCRUD; Authentication=Office365;
User Id=myO365User@myO365Domain.onmicrosoft.com; Password=myO365Pwd;"
selectStatement=""
>

</dataEntity>
</dataEntities>

<fieldMappings>
 
 <fieldMapping>
  <conflictResolution mode="winnerLoser">
   <winningEntity name="Office365" />
  </conflictResolution>
  <field entity="Azure" name="Title"/>
  <field entity="Office365" name="Title" />
    </fieldMapping> 

 <fieldMapping>
  <conflictResolution mode="winnerLoser">
   <winningEntity name="Office365" />
  </conflictResolution>
  <field entity="Azure" name="Body"/>
  <field entity="Office365" name="Body" />

    </fieldMapping> 
 
  <fieldMapping>
  <conflictResolution mode="winnerLoser">
   <winningEntity name="Office365" />
  </conflictResolution>
  <field entity="Azure" name="Expires"/>
  <field entity="Office365" name="Expires" />
    </fieldMapping> 
 
</fieldMappings>
 
</connection>

Please note:

• An Office 365 SharePoint list is used in this sample as web frontend for the SQL Azure table.
• Both data entities are described in their DataEntity sections.
• The data is not replicated in real-time. In this case the interval is set to 10 minutes (see connection section). 
• Both data entities have primary keys, the GUID on SQL Azure side and the ID column at SharePoint side. The connector automatically recognize and use these PKs for replication settings.
• Database fields are mapped to SharePoint list columns in the field mapping section.
• There is a conflict resolution option on field level. If the SQL Azure field and the mapped SharePoint column are both changed, the winning entity defined above will survive.
• Data types are automatically mapped.

Please contact sales@layer2solutions.com if you have any questions about this sample scenario.

Ready to go next steps?